[FIXED] spring-data-jdbc: JdbcQueryCreator: Cannot query by nested entity

Issue

I'm in the process of creating a service using Spring Boot (first time using Spring). I have an entity that is identified by a triple (mission, cycle, id) in the database, this is logically the primary key.
To work around the requirement of Spring-data-jdbc of having an @Id Long primary key I have created an IDENTITY primary key and added UNIQUE constraints for these columns. In my experiment I have two tables that are keyed this way (the second has an addition column in the logical key):

CREATE TABLE IF NOT EXISTS submission_counter (
    id IDENTITY NOT NULL PRIMARY KEY,
    mission VARCHAR(10) NOT NULL,
    cycle INTEGER NOT NULL,
    proposal_id INTEGER NOT NULL,
    count INTEGER NOT NULL DEFAULT 1,
    UNIQUE (mission, proposal_id, cycle),
    CHECK (count >= 1),
    CHECK (proposal_id >= 1)
);

CREATE TABLE IF NOT EXISTS submission (
    id IDENTITY NOT NULL PRIMARY KEY,
    mission VARCHAR(10) NOT NULL,
    cycle INTEGER NOT NULL,
    proposal_id INTEGER NOT NULL,
    number INTEGER NOT NULL,
    CHECK (number >= 1),
    CHECK (proposal_id >= 1),
    UNIQUE (mission, cycle, proposal_id, number)
);

To package the key triple in the Java code I've created an embedded record for the two entity classes.

enum Mission {A, B, C}
public record SubmissionCount(int count){}
public record ProposalId(int id) {}
public record SubmissionNumber(int number) {}
public record SubmissionKey(Mission mission, Cycle cycle, ProposalId proposalId) {}
public record Submission(@Id Long id, SubmissionKey key, SubmissionNumber number) {}
public record SubmissionCounter(@Id Long id,  SubmissionKey key, SubmissionCount count) {}

Previously, I had all the SubmissionKey fields inlined in the records where they are used and this all worked fine. Now, when attempting to write a query for the key I get an exception from JdbcQueryCreator

This repository definition results "Cannot query by nested entity":

public interface SubmissionRepository extends CrudRepository<Submission, Long> {
    Streamable<Submission> findByKey(SubmissionKey key);
}

Caused by: java.lang.IllegalArgumentException: Cannot query by nested entity: key
    at org.springframework.data.jdbc.repository.query.JdbcQueryCreator.validateProperty(JdbcQueryCreator.java:147)

I've tried writing the repository query method a number of ways. My latest attempt is based on a number of questions stating that the method name can do field navigation.

public interface SubmissionCounterRepository extends CrudRepository<SubmissionCounter, Long> {
    Optional<SubmissionCounter> findByKeyMissionAndKeyCycleAndKeyProposalId(SubmissionKey key);
}

This results in

Caused by: java.lang.IllegalStateException: Query method expects at least 2 arguments but only found 1. This leaves an operator of type SIMPLE_PROPERTY for property key.cycle unbound.
    at org.springframework.data.relational.repository.query.RelationalQueryCreator.throwExceptionOnArgumentMismatch(RelationalQueryCreator.java:126)
    at org.springframework.data.relational.repository.query.RelationalQueryCreator.validate(RelationalQueryCreator.java:110)
    at org.springframework.data.jdbc.repository.query.JdbcQueryCreator.validate(JdbcQueryCreator.java:117)

If I were hand rolling everything I would put the (mission, cycle, proposal_id) triple only in the submission_counter table and join that with the submission for reads.

Is there a better way to do this with Spring? Can I do field navigation using @Query and named parameters?

Solution

Circling back to this after a few days, the issue was the missing @Embedded annotation on the SubmissionKey element.

Added @Embedded like this solved the issues:

public record Submission(@Id Long id, @Embedded(onEmpty = Embedded.OnEmpty.USE_EMPTY) SubmissionKey key, SubmissionNumber number) {}

public record SubmissionCounter(@Id Long id,  @Embedded(onEmpty = USE_EMPTY) SubmissionKey key, SubmissionCount count) {}

This allows me to define findByKey methods on my repository interfaces and the queries are correctly derived:

public interface SubmissionRepository extends CrudRepository<Submission, Long> {
    Streamable<Submission> findByKey(SubmissionKey key);
}

public interface SubmissionCounterRepository extends CrudRepository<SubmissionCounter, Long> {
    Optional<SubmissionCounter> findByKey(SubmissionKey key);
}

I think the error saying "Cannot Query by nested entity" meant that Spring JDBC had concluded that SubmissionKey was a separate entity nested inside the Submission entity. Obvious in hindsight.

Answered By - asm
Answer Checked By - Marie Seifert (JavaFixing Admin)