spring-data-jdbc: query containing entity with a 1-n relation

Question

How can I write Queries for entities containing a 1-n reference?

Based on the spring-data-jdbc examples I will explain it with the following unit-test:

@Test
public void customQuery_ReferenceMultipleInstances() {
  // prepare
  LegoSet smallCar = createLegoSet("Small Car 01", 5, 12);
  smallCar.setManual(new Manual("Just put all the pieces together in the right order", "Jens Schauder"));
  smallCar.addModel("suv", "SUV with sliding doors.");
  smallCar.addModel("roadster", "Slick red roadster.");
  repository.save(smallCar);

  // execute
  List<LegoSet> actual = repository.findByName("Small Car 01");
  Iterable<LegoSet> compare = repository.findAll();

  // verify
  assertThat(actual).as("same number of lego sets").hasSize(Lists.newArrayList(compare).size());
  assertThat(actual.get(0).getModels()).as("same number of models").hasSize(Lists.newArrayList(compare).get(0).getModels().size());
  assertThat(actual.get(0).getModels().get(0)).as("model must not be null").isNotNull();
  assertThat(actual.get(0).getModels().get(0).getName()).as("model must have a name").isNotEmpty();
}

This models a LegoSet referencing 2 Models. The repository.findByName() is annotated with a custom query; the repository.findAll() is the standard spring-boot-data method of the CrudRepository (just as reference).

The custom query in version 1:

@Query("SELECT ls.id, ls.name, ls.min_age, ls.max_age, " +
      "h.handbuch_id AS manual_handbuch_id, h.author AS manual_author, h.text AS manual_text " +
      "FROM lego_set ls JOIN handbuch h ON ls.id = h.handbuch_id " +
      "WHERE name = :name")
List<LegoSet> findByName(@Param("name") String name);

In this version the test fails w/

java.lang.AssertionError: [model must not be null] 
Expecting actual not to be null

Okay, after that I add another JOIN to model:

@Query("SELECT ls.id, ls.name, ls.min_age, ls.max_age, " +
      "h.handbuch_id AS manual_handbuch_id, h.author AS manual_author, h.text AS manual_text, " +
      "m.* " +
      "FROM lego_set ls JOIN handbuch h ON ls.id = h.handbuch_id " +
      "JOIN model m ON ls.id = m.lego_set " +
      "WHERE name = :name")
List<LegoSet> findByName(@Param("name") String name);

Now the test fails w/

java.lang.AssertionError: [same number of lego sets] 
Expected size:<1> but was:<2> in:
<[LegoSet(id=1, name=Small Car 01, minimumAge=P5Y, maximumAge=P12Y, manual=Manual(id=1, author=Jens Schauder                                                                                       , text=Just put all the pieces together in the right order), models={suv=Model(name=suv, description=SUV with sliding doors.), roadster=Model(name=roadster, description=Slick red roadster.)}),
    LegoSet(id=1, name=Small Car 01, minimumAge=P5Y, maximumAge=P12Y, manual=Manual(id=1, author=Jens Schauder

So how do I have to write that query correctly?

Very well put question. Reproducing the issue was a breeze! Thank you. — Jens Schauder, Nov 27 '19 at 10:55

score 1 · Accepted Answer · answered Nov 27 '19 at 10:53

The first query is actually the correct one and it is working fine.

The problem is in your test. models is a Map, but you treat it like a List which compiles because a list index is also a valid map key.

If you change the last two assertions in your test like this they will succeed:

assertThat(actual.get(0).getModels().get("suv")).as("model must not be null").isNotNull();
assertThat(actual.get(0).getModels().get("suv").getName()).as("model must have a name").isNotEmpty();
// ----------------------------------------^

An alternative is to use the second query but with a custom ResultSetExtractor to collect multiple rows for the multiple models into a single LegoSet.

score 0 · Answer 2 · answered Sep 21 '20 at 17:44

I'm in a similar situation, but i don't see what is different in my situation.

I have three models: Client: id, name.. Project: id, name, clientId, List ProjectMember

Here is my had coded query

  @Query("""
       select
          project.id,
          project.legacy_id,
          project.code,
          project.client_id,
          project.is_archived,
          project.name,
          project.budget_type,
          project.hours_budget_max,
          project.money_budget_max
       from project
       join client on client.id = project.client_id
       where client.name ilike '%:name%'
      """)
  List<Project> findByClientNameContainingIgnoreCase(final String name);

As you can see i'm looking for project where client name (using a join) match a pattern.

Executing the request by hand give me the expected results. But going through spring data jdbc give me no results

Digging a bit deeper, it seams to be related to my usage of '% and %' I think spring jdbc produce simple quote around the value name, which will change to request I expect to see executed — benzen, Sep 21 '20 at 17:49

spring-data-jdbc: query containing entity with a 1-n relation

2 Answers2