How to implement foreign composite key between two table when part of FK key are already in primary key in hibernate JPA

Question

I have a problem implementing my tables in my Java code with Hibernate / JPA.

I have two dependent tables:

Table A
-------
FieldA1 (PK) Date
FieldA2 (PK) String
FieldA3      String
...

Table B
-------
FieldB1 (PK) Date
FieldB2 (PK) String
FieldB3      String
...

My two tables are linked by two foreign keys:

FieldA1 / FieldB1

FieldA3 / FieldB2

I am looking to do a query on the two tables (starting from table A) to obtain all the fields from table A and field B3 from table B.

I created the Entities Below:

@Entity
@Table(name = "table_a")
public class TableA implements Serializable {

   @EmbeddedId private TableAId id;

   @Column(name = "field_a3")
   private String fieldA3;


@Embeddable
public class TableAId implements Serializable {

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

   @Column(name = "field_a2", nullable = false)
   private Date fieldA2
}


@Entity
@Table(name = "table_b")
public class TableB implements Serializable {

   @EmbeddedId private TableBId id;

   @Column(name = "field_b3")
   private String fieldB3;


@Embeddable
public class TableBId implements Serializable {

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

   @Column(name = "field_b2", nullable = false)
   private Date fieldB2
}

I can't implement ForeignKeys between the two tables with @OneToOne because part of the join key is already in @Embeddable Key

The request I want to execute :

@Query("SELECT a FROM TableA a, Table b WHERE b.field3 = :mycode ")

Do you know how I can do it?

UPDATE 1 - Adding OneToOne test

        @MapsId("FieldA1") <- KO
      @JoinColumns({
        @JoinColumn(name = "FieldA1", referencedColumnName = "FieldB1"),
        @JoinColumn(name = "FieldA3", referencedColumnName = "FieldB2")
      })
@OneToOne

When I build, I have this message

Could not locate field name [FieldB2] on class [java.util.Date]

Answer 1

A can be mapped a number of ways. One with the least amount of changes:

@Entity
@Table(name = "table_a")
public class TableA implements Serializable {

   @EmbeddedId private TableAId id;

   @Column(name = "field_a3")
   private String fieldA3;

   @OneToOne
   @JoinColumns({
     @JoinColumn(name = "FieldA1", referencedColumnName = "FieldB1", insertable=false, updatable=false),
     @JoinColumn(name = "FieldA3", referencedColumnName = "FieldB2", insertable=false, updatable=false)
   })
   private TableB b;
}

You will still need to set TableA's fieldA3 and id.fieldA1 properties for the fk columns to be set.