How to map a column of a composite foreign key to a column of a composite primary key in JPA

Question

Given there are two tables client and device with a one(client)-to-many(devices) relation. The simplified schemas of the table are:

TABLE client (
  id,
  import_date,
  PRIMARY_KEY (id, import_date)
)

and

TABLE device (
  id,
  import_date,
  client_id,
  PRIMARY_KEY (id, import_date),
  FOREIGN_KEY (client_id -> client.id, import_date -> client.import_date)
)

Note: The column device.import_date is part of the primary key as well as part of the foreign key.

Unfortunately I fail to describe the above structure in JPA.
This is my humble attempt (getters, setters, implemented interfaces, etc. omitted):

public class ImportId {
    private Integer id;
    private LocalDate importDate;
}

@Entity
@IdClass(ImportId.class)
public class Client {
    @Id
    private Integer id;
    @Id
    private LocalDate importDate;
}


@Entity
@IdClass(ImportId.class)
public class Device {
    @Id
    private Integer id;
    @Id
    private LocalDate importDate;
    @ManyToOne
    //@MapsId("importDate") <-- does not work
    //@JoinColumn
    //@JoinColumn           <-- also not working
    private Client client;
}

Using @MapsId together with @IdClass does nothing special resulting in an extra column client_import_date in the device table, which is undesirable.

I've also tried using @MapsId in conjunction with @Embeddable without success. It fails with

org.hibernate.PropertyNotFoundException: Could not locate field name [id] on class [java.time.LocalDate]

Can someone help me with this issue?
Thanks a lot!

Update 1 (responding to Phil Ku's approach)

At first glance, this looks promising. Hibernate does not create the "client_import_date" column and sets the foreign key correctly. However, I can not save a device entity. Executing this code

// var client = clientRepository.save(new Client(1234, LocalDate.now());
deviceRepository.save(new Device(567, LocalDate.now(), client));

Hibernate generates the following SQL:

INSERT INTO device(client_id, import_date, id) VALUES(?,?,?);

then tries to bind the following parameters:

[1] as [INTEGER] - [1234]
[2] as [DATE] - [2023-03-22]
[3] as [INTEGER] - [567]
[4] as [DATE] - [2023-03-22]

and then throws the exception Invalid value "4" for parameter "parameterIndex". Of course it fails, because there is no fourth parameter to bind to. Any thoughts why Hibernate's parameter list does not match the insert statement?

Update 1.1

The Hibernate exception described in Update 1 appears to occur only in conjunction with the H2 platform. So integration testing might be broken in this scenario. Tested versions: Hibernate 6.1.7 and H2 2.1.214, both shipped with Spring Boot 3.0.4.
Using MariaDB driver everything of Phil Ku's approach works fine. Tracing the Hibernate output reveals that there are still four parameter candidates, selecting the first three doesn't seem to be a problem though.

Update 2

The next transaction related issue arises.
When I execute the following code

var importDate = LocalDate.now();
var client = clientRepository.save(new Client(100, importDate));
var device1 = new Device(1, importDate, client);
var device1 = new Device(2, importDate, client);
deviceRepository.saveAll(List.of(device1, device2));

the exception

SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`device`, CONSTRAINT `...` FOREIGN KEY (`client_id`, `import_date`) REFERENCES `client` (`id`, `import_date`))

is thrown.

When I instead call deviceRepository.save for each device, everything works just fine.
I'm not able to mimic this behavior outside of JPA/Hibernate context. Executing the appropriate INSERT statements directly on the database – either in one or in two transactions – never results in a constraint violation.

Any fresh ideas on that?

Update 3

Based on K.Nicholas' and Phil Ku's comments I have re-designed my database schemas. Now, the client table has an ordinary numeric single column primary key and the import_date column is just part of a unique constraint key with the (client-)id column. The primary key is then referenced by the device table.
Feels a bit like a workaround, but it works perfectly fine though.
Thanks everybody for helping.

score 0 · Answer 1 · answered Mar 22 '23 at 03:55

0

I can make the schema work but the only way I can insert is to use a native query. If you figure out a better way perhaps edit your original post.

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Device {
    @EmbeddedId
    private DeviceId id;

    @MapsId("clientId")
    @JoinColumns({
            @JoinColumn(name = "clientId", referencedColumnName = "id"),
            @JoinColumn(name = "importDate", referencedColumnName = "importDate")
    })
    @ManyToOne()
    @ToString.Exclude
    private Client client;
}

answered Mar 22 '23 at 03:55

K.Nicholas

10,956
4
46
66

Thanks for the answer. I'm not able to make your code run. How does your Client class look like? Nevertheless using natives queries is not preferred. On the other hand, Phil Ku's approach currently also works only with native queries. – darefilz Mar 22 '23 at 22:19
Its was a slightly amusing exercise in seeing how JPA handles rediculous questions. Why would you need `ImportDate` in `Device` if the `Client` already has it? – K.Nicholas Mar 23 '23 at 17:17
You are right, it is mainly a technical reason for that import date on devices than a semantical one. How would you redesign the schemas considering, that there might be an extended hierarchie, i.e. a client might belong to an organization (1-n) and a device might contain assets or something (1-n)? All together imported on one specific date. Thanks. – darefilz Mar 24 '23 at 07:20
Is it an import-date or and update-date? In other words, do you need history or is it just a property field? If you need history I think it's a bad idea to keep history in the same table as a working table. If you have 1-n, 1-n, 1-n, 1-n, then that's what you have. FKs are not inherently expensive in DB. Joining them can be though. Good luck. – K.Nicholas Mar 24 '23 at 13:18
I would suggest only having `importDate` for `Client`, as all `Device` under the same `Client` would share the same `importDate` of the `Client` – Phil Ku Mar 24 '23 at 16:12
@K.Nicholas: Yes, an actual import date. Indeed, it is a history, but not in an archive like way. The typical queries would be something like 'give me all clients and device of date ' or '... between date and '. @PhilKu That would be perfectly fine with me, but how do you model that considering, that the import date of `Client` is part of the primary key? Okay, we could make `client.id` and `client.import_date` form a unique key constraint and the primary key would be a classic sequence number. When modeling the database schema this seemed unnessecary to me. – darefilz Mar 25 '23 at 18:14

Phil Ku · Answer 2 · 2023-03-23T06:22:36.953

UPDATED:

After trying to replicate your question, tricky part is the importDate in Device, which serves as its Primary Key and also as Foreign Key of Client, following is a possible solution.

Using @JoinColumnsOrFormulas for client in Device:

@ManyToOne
@JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula = @JoinFormula(value = "import_date", referencedColumnName = "importDate")),
    @JoinColumnOrFormula(column = @JoinColumn(name = "client_id", referencedColumnName = "id"))
})
private Client client;

Setting importDate to client.importDate when setting client in Device:

public void setClient(Client client){
    this.client = client;
    this.importDate = client.getImportDate();
}

Device.java:

@Entity
@IdClass(ImportId.class)
public class Device {

    @Id private Integer id;
    
    @Id
    @Column(name = "import_date")
    private LocalDate importDate;

    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(formula = @JoinFormula(value = "import_date", referencedColumnName = "importDate")),
        @JoinColumnOrFormula(column = @JoinColumn(name = "client_id", referencedColumnName = "id"))
    })
    private Client client;

    
    public void setClient(Client client){
        this.client = client;
        this.importDate = client.getImportDate();
    }
    
}

Hope it helps.

@darefilz updated, have not had the chance to test, please test for me, thanks — Phil Ku, Mar 22 '23 at 14:54
Same error. Even if I make the relation bidirectional, add a device to the client's device list and save the client, the same error occurs. — darefilz, Mar 22 '23 at 21:22

komutanlogar · Answer 3 · 2023-03-22T00:15:03.223

This error occurs when Java searches for a field named 'id' in the LocalDate class but such a field cannot be found. The solution to this error is to make sure that you are using the correct field names and data types during database operations. If you are using an Entity class, make sure that its fields are properly annotated and configured.

So, to summarize, you can solve this error by:

Checking the Entity class and database table to understand why the error occurred if you are not using a 'id' field in the LocalDate class. Ensuring that the fields in the Entity class are properly annotated, for example, by using the @Id, @GeneratedValue and @Column annotations correctly. Making sure that the field names in the Entity class match the column names in the database table. By following these steps, you can identify the cause of the error and solve it.

How to map a column of a composite foreign key to a column of a composite primary key in JPA

Update 1 (responding to Phil Ku's approach)

Update 1.1

Update 2

Update 3

3 Answers3