How to join separate table data column in Hibernate

Question

Is there any possible variant to insert data from another table into Hibernate entity. For example we have the following DB-model:

ItemTable:
ITEM_ID NUMBER not null,
ITEM_NAME VARCHAR2,
I_OBJECT_ID NUMBER;

ObjectTable:
OBJECT_ID NUMBER not null,
OBJECT_NAME VARCHAR2;

And the following java-code:

@Entity
@Getter
@Setter
@Table(name = "ITEMTABLE")
public class ItemDTO {

   @Id
   @Column(name = "ITEM_ID")
   private long id;

   @Column(name = "ITEM_NAME")
   private String itemName;

   @Column(name = "I_OBJECT_ID")
   protected long objectId;

   **//TODO: here we need OBJECT_NAME column form ObjectTable**
   private String objectName;

}

So we need just one column not the whole ObjectTable entity for objectName prop.

I've tried construction like:

@JoinTable(name = "OBJECTTABLE", joinColumns = {
        @JoinColumn(name = "OBJECT_ID", referencedColumnName = "I_OBJECT_ID") })
@Column(name = "OBJECT_NAME")

But it fails with:

oracle.jdbc.OracleDatabaseException: ORA-00904: "ITEMDTO0_"."OBJECT_NAME": invalid identifier

Every example of @JoinTable or @JoinColumn assume that I need to include another entity as a prop which will be redundant and slow.

Can this be solved in any way?

P.S. just two cents about why I do not want include second entity into first one, is because I want my JpaRepository will allow me to sort/filter directly over joined column.

Please advise.

You could consider mapping the property through a SecondaryTable — Jan-Willem Gmelig Meyling, Jun 09 '20 at 20:35
@Jan-WillemGmeligMeyling it doesn't work, see my comment under **zforgo**'s answer — EtherealDeath, Jun 10 '20 at 08:49
Creating entities for your DTO's is a bit hacky. Consider using Blaze-Persistence Entity Views for creating your DTO's. Its considerable the best DTO projection library for JPA out there: https://persistence.blazebit.com/documentation/1.3/entity-view/manual/en_US/ — Jan-Willem Gmelig Meyling, Jun 10 '20 at 09:08

score 1 · Answer 1 · answered Jun 09 '20 at 21:06

Using @SecondaryTable annotation it's easy to map multiple tables to the same entity. More precisely JPA joins the primary table with the secondary table and populates the fields.

@Entity
@Table(name = "ItemTable")
@SecondaryTable(name = "ObjectTable", pkJoinColumns = 
    @PrimaryKeyJoinColumn(name = "OBJECT_ID", referencedColumnName="I_OBJECT_ID")
)
public class ItemDTO {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ITEM_ID")
    Long id;

    @Column(name = "ITEM_NAME")
    String name;

    @Column(name = "OBJECT_NAME", table = "ObjectTable")
    private String objectName;

}

@Entity
@Table(name = "ObjectTable")
public class ObjectsEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "OBJECT_ID")
    private Long id;


    @Column(name = "OBJECT_NAME")
    private String name;
}

BTW: In the question the names of the columns and tables are different in the data model and the @Table and @Column annotations. It should be consolidated.

I've tried this solution and here what I've got: org.hibernate.AnnotationException: SecondaryTable JoinColumn cannot reference a non primary key. — EtherealDeath, Jun 10 '20 at 07:37

How to join separate table data column in Hibernate

1 Answers1