Storing grandchildren (indirect, transitive) in postgres using Spring data jdbc

Question

I'm having trouble retrieving (but not storing) grandchildren objects using Spring data jdbc. In other words, the root aggregate has a child object, which in turn has a list of child objects. Saving the data works fine - all relationships appear in the database correctly populated. However, I can't get the list of grandchild objects to load properly. Here's the object structure:

@Data
public class Quote {
    @Id
    Long id;

    QuoteResponse response;
}

@Data
public class QuoteResponse {
    @Id
    Long quoteResponseId;

    @MappedCollection(idColumn = "quote_response")
    public List<ItemQuoteResponse> itemQuoteResponses = new ArrayList<>();
}

@Data
public class ItemQuoteResponse {

    @Id
    Long id;
}

And here are the tables

CREATE TABLE quote (
  id BIGSERIAL PRIMARY KEY NOT NULL,
}
CREATE TABLE quote_response (
   quote_response_id BIGSERIAL PRIMARY KEY NOT NULL,
   quote int not null
);

CREATE TABLE item_quote_response (
        id BIGSERIAL PRIMARY KEY,
        quote_response int not null,
        FOREIGN KEY (quote_response) REFERENCES quote_response(quote_response_id),
        quote_response_key INTEGER NOT NULL
);

I'm using a simple CrudRepository to get and save quote. It's baffling to me that spring data jdbc is storing Quote, QuoteResponse, and ItemQuoteResponse correctly, and all the foreign keys are correctly populated, but when I try to get quote, the ItemQuoteResponses are not retrieved, and instead I get an empty list.

More perplexing is the debug logs:

2024-09-13T15:38:05.372-04:00 DEBUG 25076 --- [dev] [nio-8080-exec-4] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT
 "item_quote_response"."id" AS "id", "item_quote_response"."status" AS "status",
 "item_quote_response"."quote_response_key" AS "quote_response_key" FROM 
"item_quote_response" WHERE "item_quote_response"."id" = ? ORDER BY "quote_response_key"]

It's trying to use item_quote_response.id to lookup the grandchildren, when it should be using item_quote_response.quote_response (the foreign key to its parent).

I can't seem to find any documentation discussing this type of relationship in Spring data jdbc, or if I'm tripping up some naming convention that's doing the autoconfiguration.

Answer 1

I'm adding my "solution" for posterity, since I was strangely unable to find anything about this issue despite assiduous googling, and even the AIs seemed bewildered.

For simplicity, I'll rewrite the scenario as class A having property of class B, which has a List of class C.

It appears that Spring Data JDBC doesn't track identity relationships (except the root) unless the relationship is one-to-many. So in recursively looking up the entities decending from A, it is able to lookup B because its relationship identity is defined on the root - the a_id on B. However, when it descends to loading B, it has lost track of the relevant id to use when looking up C. It should be using the b_id field on C, but it hasn't kept track of it, so it winds up using A's id. Which doesn't work.

To get things working, I made the realtionship of A to B one-to-many. In other words, instead of a single B, A now has a list of Bs. This one change fixed the problem instantly. In loading the "list" of B's, Spring added B's identity information to its internal representation, and when it decended to load the Cs, it had access to the correct id - that of B.

In short, I think that Spring data JDBC supports one-to-many-to-many relationships (A-B-C), but it doesn't support one-to-one-to-many (A-B-*C).

I'm stupefied that it would have such a glaring defect, and I'm hoping that by Cunningham's law someone will appear and explain how the error was in fact mine. But if you are stumbling across this post because you ran into a similiar issue and nobody has yet responded, then this may be the ultimate answer.