I have an issue where I have an object that takes some fields, and one of these fields is a List (0-2, so nullable) and need to save it in a database (PostgreSQL is what I use). Now I know that having a separate table with a foreign key reference is recommended, one-to-many relationship. So I have to establish a foreign key from the child table to the main entity. I used @MappedCollection in Spring Data JDBC to map the relation. I also don't need to a separate repository for the child table because Spring Data JDBC treats it as a part of the aggregate owned by the parent Table.

record ParentEntity(@Id Long id, other fields, @MappedCollection(idColumn = "parent_table_id") Set<ChildEntity> childField){}
record ChildEntity(String childField){}

now when testing it manually, everything works fine. The problem is that I need to test this. I tried using LEFT JOIN because obviously the tests will always complain that it can't find the child column when we try to retrieve the parent entity This is the query I used to solve it:

"SELECT pt.*, ct.child_table " +
            "FROM parent_table pt " +
            "LEFT JOIN child_table ai ON ct.parent_table_id = pt.id " +
            "WHERE pt.position_id = ?"

But the problem is that this will now give me two rows with the same fields except the child column, and thats not what we want, we want to have only one row, because the parent entity takes a set

Any ideas? the framework takes care of the production code and I don't really know how to do with the tests.

I have an issue where I have an object that takes some fields, and one of these fields is a List (0-2, so nullable) and need to save it in a database (PostgreSQL is what I use). Now I know that having a separate table with a foreign key reference is recommended, one-to-many relationship. So I have to establish a foreign key from the child table to the main entity. I used @MappedCollection in Spring Data JDBC to map the relation. I also don't need to a separate repository for the child table because Spring Data JDBC treats it as a part of the aggregate owned by the parent Table.

record ParentEntity(@Id Long id, other fields, @MappedCollection(idColumn = "parent_table_id") Set<ChildEntity> childField){}
record ChildEntity(String childField){}

now when testing it manually, everything works fine. The problem is that I need to test this. I tried using LEFT JOIN because obviously the tests will always complain that it can't find the child column when we try to retrieve the parent entity This is the query I used to solve it:

"SELECT pt.*, ct.child_table " +
            "FROM parent_table pt " +
            "LEFT JOIN child_table ai ON ct.parent_table_id = pt.id " +
            "WHERE pt.position_id = ?"

But the problem is that this will now give me two rows with the same fields except the child column, and thats not what we want, we want to have only one row, because the parent entity takes a set

Any ideas? the framework takes care of the production code and I don't really know how to do with the tests.

• java
• postgresql
• jdbc
• spring-data
• one-to-many

Share Improve this question Follow asked Mar 11 at 10:22 1892937118929371 144 bronze badges 1

• This is how SQL works; it gives you one record for each match. If a parent has two children, you get two records. – Frank Heikens Commented Mar 11 at 14:48

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You're on the right track with setting up a one-to-many relationship in PostgreSQL using Spring Data JDBC. The issue you're facing is common when using LEFT JOIN, It duplicates the parent row for each child, which isn't ideal since you're using a Set<ChildEntity>.
Since Spring Data JDBC automatically maps child entities when fetching a parent, you don’t need to manually join tables. Instead of using LEFT JOIN, just fetch the parent entity directly or fetch separately the child records separately.
SELECT * FROM parent_table WHERE position_id = ?; or SELECT * FROM child_table WHERE parent_table_id = ?;