JPA mapping :Primary key column of one table to non Pk/Fk column of another table

Question

@Entity
@Table(name = "TableA")
public class TableAEntity{
        
        @Id
        @Column(name = "RUL_ID"
        private Integer rulId;
        
        @Column(name = "COMMENT"
        private Integer comment;
        
        @OneToOne
        @JoinColumn(name = "RUL_ID" referencedColumnName ="PRNT_ID", insertable=false, updatable=false)
        private TableBEntity tableB;
  
        //GETTERS AND SETTERS
        
}
        
@Entity
@Table(name = "TableB")
public class TableBEntity{
        
        @Id
        @Column(name = "ADD_ID"
        private Integer addID;
        
        @Column(name = "PRNT_ID"
        private Integer prntId;  
     
        //GETTERS AND SETTERS

}

There are 2 DB tables.

1. TableA with primary key as rulId.
2. TableB with primary key as addID.

I have to implement a customized JOIN query using JPA native query.

Java Code is:

StringBuilder querySql = "select a.rulId, b.prntId from  TableA a JOIN TableB b ON a.rulID = b.prntId"  
Query tabQuery = entityManager.createNativeQuery(querySql.toString, TableAEntity.class)  
List<TableAEntity> entityList = tabQuery.getResultList(); 

How to establish this OneToOne(TableA:TableB) relationship when they are not linked with any key(pk/fk). I am unable to map ResultList to my entity class.Primary key of TableA "rulId" always gets linked to PrimaryKey of TableB "addId", wherein I want to get it associated to "prntId".

Can anyone please help on this.

Answer 1

A couple of things to note:

• For JPA query, you have to use createQuery (createNativeQuery is for SQL queries);
• @Table(name=...) will define the name of the table in the database but not when you write a JPQL query. For that you can use @Entity(name="..."). In your case, it should be @Entity(name="TableA");
• The return value of the query is two fields, not TableAEntity. So passing it as parameter to createQuery is wrong;
• It's weird to return a.id and b.id. If you want the entities, you can return a and b.

If there is an association between TableA and TableB, for example:

@Entity(name = "TableA")
public class TableAEntity {
...
        @OneToOne
        @JoinColumn(referencedColumnName ="PRNT_ID", insertable=false, updatable=false)
        public TableBEntity tableB;
}

then you can run the following query:

String jpqlQuery = "from TableA a join fetch a.tableB b";
List<TableAEntity> entityList = entityManager.createQuery(jpqlQuery, TableAEntity.class).getResultList()

entityList.foreach( tableAEntity -> {
   TableBEntity tabB = tableAEntity.tableB;
});

If there is no association between TableA and TableB:

String jpqlQuery = "select a, b from TableA a JOIN TableB b ON a.rulID = b.prntId";
List<Object[]> entityList = entityManager.createQuery(jpqlQuery).getResultList()

entityList.foreach( row -> {
   TableAEntity tabA = (TableAEntity) row[0];
   TableBEntity tabB = (TableBEntity) row[1];
});

But if you really just need the ids, this will work too:

String jpqlQuery = "select a.rulId, b.prntId from  TableA a JOIN TableB b ON a.rulID = b.prntId";
List<Object[]> entityList = entityManager.createQuery(jpqlQuery).getResultList()

entityList.foreach( row -> {
   Integer tabAId = (Integer) row[0];
   Integer tabBId = (Integer) row[1];
   ...
});

Note that you can change the select and mix the two approaches.

But because there is an association between TableAEntity and TableBEntity, you could rewrite all this as:

String jpqlQuery = "from TableA";
List<TableAEntity> entityList = entityManager.createQuery(jpqlQuery, 
TableAEntity.class).getResultList()

entityList.foreach( entity -> {
   TableAEntity tabA = entity;
   TableBEntity tabB = entity.getTableB();
   ...
});

With or without the association, you can return from the select clause all the combinations of values you need:

select a, b.addID from ...
select a, b from ...