JpaRepository with Native @Query on different tables

Question

I just stumbled upon some unexpected behavior in spring data. To demonstrate I set up some spring boot application with Initializr (https://start.spring.io/) adding JPA, Web, H2.

The application contains two tables and some data:

data.sql:

create table table1 (
  id int,
  name varchar(50)
);

create table table2 (
  id int,
  name varchar(50)
);

insert into table1 (id, name) values (1, 'First row from table 1');
insert into table1 (id, name) values (2, 'Second row from table 1');
insert into table1 (id, name) values (3, 'Third row from table 1');
insert into table1 (id, name) values (4, 'Fourth row from table 1');

insert into table2 (id, name) values (1, '** TABLE 2: 1st ROW UPPERCASE **');
insert into table2 (id, name) values (2, '** TABLE 2: 2nd ROW UPPERCASE **');

There is only one model for this table structure because the structure is the same on both tables. I created a JpaRepository for this table

SampleDAO:

@Repository
public interface SampleDAO extends JpaRepository<SampleModel, Integer> {

  @Query(value = "select id, name from table1", nativeQuery = true)
  List<SampleModel> findAllFromTable1();

  @Query(value = "select id, name from table2", nativeQuery = true)
  List<SampleModel> findAllFromTable2();
}

Finally I added a controller (TestController):

@RestController
public class TestController {

  @Autowired
  private final SampleDAO sampleDAO;

  public TestController(SampleDAO sampleDAO) {
    this.sampleDAO = sampleDAO;
  }

  @GetMapping(path = "/")
  @ResponseBody
  public String testNativeQuery() {
    List<SampleModel> list1 = this.sampleDAO.findAllFromTable1();
    List<SampleModel> list2 = this.sampleDAO.findAllFromTable2();

    SampleModel m1 = list1.get(0);
    SampleModel m2 = list2.get(0);
    System.out.println("*****************************************");
    System.out.println("Data from findAllFromTable1():");
    list1.forEach(l -> {
      System.out.println(l.getName());
    });
    System.out.println("*****************************************");
    System.out.println("Data from findAllFromTable2():");
    list2.forEach(l -> {
      System.out.println(l.getName());
    });
    System.out.println("*****************************************");

    return "Done";
  }
}

I expected list1 and list2 to contain the data of my two tables but surprisingly the result only of the first table is fetched:

*****************************************
Data from findAllFromTable1():
First row from table 1
Second row from table 1
Third row from table 1
Fourth row from table 1
*****************************************
Data from findAllFromTable2():
First row from table 1
Second row from table 1
*****************************************

You can find the sample project here: https://github.com/steinmann321/nativequerydemo

Is this the expected behavior or am I doing something wrong?

(Please note: This is only a sample project, the real-life queries are much more complex but the result is the same)

Answer 1

You are having an issue with clashing of identity across persistent entities. You can see that you are getting four records from the first query and two records from the second which matches records in the database. But as the entitymanager is processing records from the second set it is seeing that there are already persistent entities with Ids 1 and 2, therefore it's not going to create new entities.

If you want this to work you need to indicate you have a composite key which is made up of id and name. This will also match the business logic in your equals method.

Create an JPA embeddable key:

@Embeddable
public class MyKey implements Serializable {

    private static final long serialVersionUID = 1L;

    @Column(name = "id", nullable = false)
    private String id;

    @Column(name = "name", nullable = false)
    private String name;

    public String getId() {
      return id;
    }

    public void setId(String id) {
      this.id = id;
    }

    public String getName() {
      return name;
    }

    public void setName(String name) {
      this.name = name;
    }

    @Override
    public boolean equals(Object o) {
      if (this == o) {
        return true;
      }
      if (o == null || getClass() != o.getClass()) {
        return false;
      }
      MyKey that = (MyKey) o;
      return Objects.equals(id, that.getId()) && Objects.equals(name, that.getName());
    }

    @Override
    public int hashCode() {
      return Objects.hash(id, name);
    }
}   

Then use the embedded key within your SampleModel entity:

@Entity
public class SampleModel {

  @EmbeddedId
  private MyKey myKey;

  public MyKey getMyKey() {
    return myKey;
  }

  public void setMyKey(MyKey myKey) {
    this.myKey = myKey;
  }
}

Your controller will change just a bit to pull the values from the key.

System.out.println("*****************************************");
System.out.println("Data from findAllFromTable1():");
list1.forEach(l -> {
  System.out.println(l.getMyKey().getName());
});
System.out.println("*****************************************");
System.out.println("Data from findAllFromTable2():");
list2.forEach(l -> {
  System.out.println(l.getMyKey().getName());
});
System.out.println("*****************************************");

Then try out your TestController. I did try this out and here is the result:

*****************************************
Data from findAllFromTable1():
First row from table 1
Second row from table 1
Third row from table 1
Fourth row from table 1
*****************************************
Data from findAllFromTable2():
** TABLE 2: 1st ROW UPPERCASE **
** TABLE 2: 2nd ROW UPPERCASE **
*****************************************