Atypic JPA OneToOne Relationship

Question

I have a problem with JPA.

I have tables:

----------------- | TableA | |---------------| | ID: INT | | ... | | ESTATUS1: INT | | ESTATUS2: INT | ----------------- ----------------- | EstatusTags | |---------------| | COD: VARCHAR |---> COD and VALUE are a concatenated PK | VALUE: INT | | DESC: VARCHAR | ----------------- 

EstatusTags is a table for storing sets of [VALUE, DESC] pairs, given COD.

Before using JPA, I used a query of this kind of data like this:

 SELECT ID, ESTATUS1, ESTATUS2, E1.DESC DESC1, E2.DESC DESC2 FROM TABLEA A INNER JOIN ESTATUSTAGS E1 ON E1.COD = "a_estatus1" AND E1.VALUE = A.ESTATUS1 INNER JOIN ESTATUSTAGS E2 ON E2.COD = "a_estatus2" AND E2.VALUE = A.ESTATUS2 

I am trying to use JPA to model this using two entity classes:

 @Entity @Table(name = "EstatusTags") public class EstatusTags implements Serializable { @EmbeddedId private ValueTagPK id; @Column(name="VVA_DESC") private String desc; @Column(name="VVA_ORDEN") private Integer orden; } @Entity @Table(name = "TableA") public class A implements Serializable { @Column(name="ID") private String desc; @OneToOne(???) private EstatusTag estatus1; @OneToOne(???) private EstatusTag estatus2; } 

I have strong doubts about how to model relationships. Can this be done with annotations? Is it necessary to use JPQL for this structure?

I hope someone can help me with this.

Many thanks.

Answer 1

The problem is that your object model does not match the structure of the table. In your entity model, you have a one-to-one relationship between A and EstatusTag , while in your table model you have a relationship of one A and several Estatustags (for one value , there may be several Etatustags entries)

You overcome the problem that table A does not have a cod column by adding something like a virtual cod column E1.COD = "a_estatus1" to your SQL query.

What you can do is map the value column of two EstatusTag properties once to composite pk, and at another time to one property as follows. A simple value is made available through access to properties, but marked as not updated, not inserted, the setter also does not work and private is made.
Note. I don't know if this works with all JPA implementations. Tested with sleep mode 4.3.8.

 @Entity @Table(name = "EstatusTags" ) @Access(AccessType.FIELD) public class EstatusTag implements Serializable{ private @EmbeddedId ValueTagPK id; @Column(name="VVA_DESC") private String desc; @Column(name="VVA_ORDEN") private Integer orden; @Column(name="value", updatable=false, insertable=false) @Access(AccessType.PROPERTY) public int getValue() { return id.value; } private void setValue(int value) { // only because otherwise hibernate complains about a missing setter. } } @Entity @Table(name = "TableA") public class A implements Serializable{ @Id @Column(name="ID") @GeneratedValue(strategy=GenerationType.TABLE) private int id; @OneToOne() @JoinColumn(name="estatus1",referencedColumnName="value") public EstatusTag estatus1; @OneToOne() @JoinColumn(name="estatus2",referencedColumnName="value") public EstatusTag estatus2; }