I'm implementing a REST service on top of a complex data model using Spring Data JDBC, and I'm hitting the N + 1 problem, where I'm seeing multiple extra queries per record returned. I'm on 3.3.7 and I tried using single query loading, but it doesn't help, because I have six tables, some of which are nested.
I've been through the Spring Data doc on projections, and I created a "DTO-lite" which is just a subset of the full DTO with all the mapped associations. I added a class parameter to all the repository methods so they will return either the full or "lite" DTO.
When I implemented this, it does return the type that I requested, but it still runs all the extra queries as if I had requested the full DTO. I duplicated this in an example. I'll paste in the relevant code, and I'm happy to upload the full example code if needed to duplicate it.
I ran a similar test in debug and set a breakpoint in JdbcTemplate.execute() which is where it logs the SQL. The first time it hits is for the top level query; subsequent queries seem to be running at a deeper level, where the MappedCollection fields are being populated. It seems that the RowMapper or an associated class is referencing the full DTO, instead of the mini-DTO which was passed as the second parameter, so it is still working on the full set of properties.
Am I missing something? Is there a bug which is fixed in 3.4.x? Thanks for any help!
Full DTO classes:
@Table("MODELS")
public record Model(
@Id @Column("MODEL_ID") Long id,
@Column("MODEL_NAME") String name,
@Column("MODEL_VERSION") String version,
@MappedCollection(idColumn = "MODEL_ID") Set<ModelParameter> parameters
) {}
Table("MODEL_PARAMS")
public record ModelParameter(
@Id @Column("MODEL_PARAM_ID") Long id,
@Column("PARAM_NAME") String name,
@Column("PARAM_MIN") BigDecimal min,
@Column("PARAM_MAX") BigDecimal max) {}
The DTO-lite:
@Table("MODELS")
public record MiniModel(
@Id @Column("MODEL_ID") Long id, // DB
@Column("MODEL_NAME") String name,
@Column("MODEL_VERSION") String version) {}
The repo:
public interface ModelRepository extends CrudRepository<Model, Long> {
<T> Set<T> findByName(String name, Class<T> theClass);
}
The test:
@Test
void dynamicProjection() {
var params = Set.of(
new ModelParameter(null, "param1", BigDecimal.ZERO, BigDecimal.ONE),
new ModelParameter(null, "param2", BigDecimal.ZERO, BigDecimal.ONE)
);
var model = new Model(null, "model", "1", params);
var saved = modelRepo.save(model);
LOG.info("model: {}", saved);
LOG.info("getting model as full DTO (expecting two queries)");
modelRepo.findByName("model", Model.class).forEach(m -> LOG.info("find returned model {}", formatRecord(m)));
LOG.info("getting model as DTO-lite (expecting one query)");
modelRepo.findByName("model", MiniModel.class).forEach(m -> LOG.info("find returned minimodel {}", formatRecord(m)));
}
Test output with spring jdbc debug on; observe that two queries are being run, no matter which DTO class is passed as the second parameter of findByName():
2025-02-06T17:06:22.474-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL update and returning generated keys
2025-02-06T17:06:22.475-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO "MODELS" ("MODEL_NAME", "MODEL_VERSION") VALUES (?, ?)]
2025-02-06T17:06:22.485-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO "MODEL_PARAMS" ("MODEL_ID", "PARAM_MAX", "PARAM_MIN", "PARAM_NAME") VALUES (?, ?, ?, ?)]
2025-02-06T17:06:22.490-08:00 INFO 82143 --- [ main] e.s.jdbc.basics.qbug.QueryBugTests : model: Model[id=0, name=model, version=1, parameters=[ModelParameter[id=1, name=param2, min=0, max=1], ModelParameter[id=0, name=param1, min=0, max=1]]]
2025-02-06T17:06:22.494-08:00 INFO 82143 --- [ main] e.s.jdbc.basics.qbug.QueryBugTests : getting model as full DTO (expecting two queries)
2025-02-06T17:06:22.505-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query
2025-02-06T17:06:22.505-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT "MODELS"."MODEL_ID" AS "MODEL_ID", "MODELS"."MODEL_NAME" AS "MODEL_NAME", "MODELS"."MODEL_VERSION" AS "MODEL_VERSION" FROM "MODELS" WHERE "MODELS"."MODEL_NAME" = ?]
2025-02-06T17:06:22.510-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query
2025-02-06T17:06:22.510-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT "MODEL_PARAMS"."MODEL_PARAM_ID" AS "MODEL_PARAM_ID", "MODEL_PARAMS"."PARAM_MAX" AS "PARAM_MAX", "MODEL_PARAMS"."PARAM_MIN" AS "PARAM_MIN", "MODEL_PARAMS"."PARAM_NAME" AS "PARAM_NAME" FROM "MODEL_PARAMS" WHERE "MODEL_PARAMS"."MODEL_ID" = ?]
2025-02-06T17:06:22.512-08:00 INFO 82143 --- [ main] e.s.jdbc.basics.qbug.QueryBugTests : find returned model Model[
id=0, name=model, version=1, parameters=[
ModelParameter[
id=0, name=param1, min=0.000, max=1.000
], ModelParameter[
id=1, name=param2, min=0.000, max=1.000
]
]
]
2025-02-06T17:06:22.512-08:00 INFO 82143 --- [ main] e.s.jdbc.basics.qbug.QueryBugTests : getting model as DTO-lite (expecting one query)
2025-02-06T17:06:22.513-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query
2025-02-06T17:06:22.513-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT "MODELS"."MODEL_ID" AS "MODEL_ID", "MODELS"."MODEL_NAME" AS "MODEL_NAME", "MODELS"."MODEL_VERSION" AS "MODEL_VERSION" FROM "MODELS" WHERE "MODELS"."MODEL_NAME" = ?]
2025-02-06T17:06:22.514-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query
2025-02-06T17:06:22.514-08:00 DEBUG 82143 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT "MODEL_PARAMS"."MODEL_PARAM_ID" AS "MODEL_PARAM_ID", "MODEL_PARAMS"."PARAM_MAX" AS "PARAM_MAX", "MODEL_PARAMS"."PARAM_MIN" AS "PARAM_MIN", "MODEL_PARAMS"."PARAM_NAME" AS "PARAM_NAME" FROM "MODEL_PARAMS" WHERE "MODEL_PARAMS"."MODEL_ID" = ?]
2025-02-06T17:06:22.517-08:00 INFO 82143 --- [ main] e.s.jdbc.basics.qbug.QueryBugTests : find returned minimodel MiniModel[
id=0, name=model, version=1
]
This is currently a known limitation: https://github.com/spring-projects/spring-data-relational/issues/1821
There is a PR that addresses an underlying issue which needs to get resolved first: https://github.com/spring-projects/spring-data-relational/pull/1967
What you could do as a workaround, is to use a separately mapped entity to the same table with only the fields you are interested in.
But be careful to only use it for reading.
That said: deeply nested aggregates are suspicious and might better be replaced by smaller aggregates.