spring数据jdbc:JdbcQueryCreator:无法按嵌套实体进行查询

Question

我正在使用Spring Boot创建服务(第一次使用Spring(。我在数据库中有一个由三重(mission, cycle, id)标识的实体，这在逻辑上是主键
为了解决Spring数据jdbc具有@Id Long主键的要求，我创建了一个IDENTITY主键，并为这些列添加了UNIQUE约束。在我的实验中，我有两个表是这样键控的(第二个表在逻辑键中有一个加法列(：

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)
);

为了在Java代码中封装密钥三元组，我为这两个实体类创建了一个嵌入记录。

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) {}

以前，我将所有SubmissionKey字段都内联在使用它们的记录中，这一切都很好。现在，当试图为密钥编写查询时，我从JdbcQueryCreator中得到一个异常

这个储存库定义的结果是"；无法按嵌套实体"进行查询：

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)

我已经尝试过多种方法来编写存储库查询方法。我最近的尝试是基于一些问题，这些问题表明方法名称可以进行字段导航。

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

这导致

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)

如果我手动滚动所有内容，我会将(mission, cycle, proposal_id)三元组仅放在submission_counter表中，并将其与submission连接以进行读取。

Spring有更好的方法吗？我可以使用@Query和命名参数进行现场导航吗？

Answer 1

几天后，问题是SubmissionKey元素上缺少@Embedded注释。

添加这样的@Embedded解决了问题：

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) {}

这允许我在我的存储库接口上定义findByKey方法，并且查询是正确派生的：

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

我认为错误的说法是"；无法按嵌套实体查询"；意味着Spring JDBC已经得出结论，SubmissionKey是嵌套在Submission实体中的一个独立实体。事后看来显而易见。