Spring Data JDBC one-to-many relation causes lots of log warnings

Question

Summary: Spring Data emits warnings as it SELECTs the the same column twice. The warning is:

(...) WARN o.s.d.j.core.convert.ResultSetAccessor   : ResultSet contains id multiple times

This is spring-boot-starter-data-jdbc via Spring Boot 2.5.5 (documentation). I'm working with a very basic one-to-many relationship:

I've reduced the entity classes as much as possible for brevity, while retaining the warnings:

Outlet.java

(...)
@Table("outlet")
public class Outlet {
    @Id
    private String outletId;
    @MappedCollection(idColumn = "outlet_id", keyColumn = "id")
    private Map<String, OfferedService> offeredServices;
    (...)
}

OfferedService.java

(...)
@Table("offered_service")
public class OfferedService {
    @Id
    private String id;
    private String outletId;
    (...)    
}

The repository is also most basic...

OutletRepository.java

(...)
public interface OutletRepository extends CrudRepository<Outlet, String> {}

...and in my application code, I just do a findAll on this repository:

(...)
outletRepo.findAll();
(...)

This leads to a query which selects the the same column twice, and subsequently a warning from Spring Data's ResultSetAccessor:

DEBUG o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [
    SELECT "outlet"."outlet_id" AS "outlet_id" FROM "outlet"]
DEBUG o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
DEBUG o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [
    SELECT "offered_service"."id" AS "id", 
        "offered_service"."outlet_id" AS "outlet_id", 
        "offered_service"."id" AS "id"
    FROM "offered_service"
    WHERE "offered_service"."outlet_id" = ?]
TRACE o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: 
    column index 1, parameter value [xyz012], value class [java.lang.String], SQL type 12
WARN  o.s.d.j.core.convert.ResultSetAccessor   : ResultSet contains id multiple times
WARN  o.s.d.j.core.convert.ResultSetAccessor   : ResultSet contains id multiple times
(...repeated many times...)

What am I doing wrong here? Also, for findAll, shouldn't this be a JOIN in the first place?

Answer 1

You have two things that get mapped to the column offered_service.outlet_id: The one-to-many mapping from Outlet to OfferedService and the attribute OfferedService.outletId.

If these are supposed to be different you can use annotations to change the columns these are mapped to, but I assume you are intentionally doing this, to have the outletId available in the OfferedService entity.

While this works, it is kind of a hack and might break in future versions.

The recommended way of doing something like this is to make outletId transient using @Transient and use plain java code to set its value when an OfferedService is added to an Outlet.

There is an blog article about just that: https://spring.io/blog/2021/09/22/spring-data-jdbc-how-do-i-make-bidirectional-relationships

There also seems to be another problem where Spring Data JDBC does select the OfferedService.id twice. I'm not sure why that happens. Looks like a bug to me. But you probably can remove the id completely since the map key and the outlet_id should form a perfectly fine primary key.