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

Question

I am trying to load entities containing a reference to another entity (1-n) with the help of JOOQ (based on spring-data-jdbc).

I'm started extending the spring-data-jdbc-jooq-example.

The adjusted model with the 1-n relation:

@Data
public class Category {

    private @Id Long id;
    private String name, description;
    private AgeGroup ageGroup;
    private Set<SubCategory> subCategories;

    public Category() {}

    public Category(Long id, String name, String description, AgeGroup ageGroup) {
        this(id, name, description, ageGroup, new HashSet<>());
    }

    public Category(Long id, String name, String description, AgeGroup ageGroup, Set<SubCategory> subCategories) {
        this.id = id;
        this.name = name;
        this.description = description;
        this.ageGroup = ageGroup;
        this.subCategories = subCategories;
    }
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class SubCategory {

    private @Id Long id;
    private String title;

}

I wrote two queries, one via the @Query-Annotation in the CrudRepository and one with the help of JOOQ in the JooqRepository.

interface CategoryRepository extends CrudRepository<Category, Long>, JooqRepository {

    @Query("SELECT * FROM category")
    List<Category> findAllWithQuery();

}

public interface JooqRepository {

    List<Category> findAllWithJooq();
}

public class JooqRepositoryImpl implements JooqRepository {

    private final DSLContext dslContext;

    public JooqRepositoryImpl(DSLContext dslContext) {
        this.dslContext = dslContext;
    }

    @Override
    public List<Category> findAllWithJooq() {
        return dslContext.select()
                .from(CATEGORY)
                .fetchInto(Category.class);
    }
}

(for me both methods should return the same result-set b/c they execute the same query?!)

But my unit-test fails:

@Test
public void exerciseRepositoryForSimpleEntity() {
  // create some categories
  SubCategory sub0 = new SubCategory(null, "sub0");
  SubCategory sub1 = new SubCategory(null, "sub1");
  Category cars = new Category(null, "Cars", "Anything that has approximately 4 wheels", AgeGroup._3to8, Sets.newLinkedHashSet(sub0, sub1));

  // save category
  repository.saveAll(asList(cars));

  // execute
  List<Category> actual = repository.findAllWithJooq();
  List<Category> compare = repository.findAllWithQuery();
  Output.list(actual, "JOOQ");
  Output.list(compare, "Query");

  // verify
  assertThat(actual).as("same size of categories").hasSize(compare.size());
  assertThat(actual.get(0).getSubCategories()).as("same size of sub-categories").hasSize(compare.get(0).getSubCategories().size());
}

with

java.lang.AssertionError: [same size of sub-categories] 
Expecting actual not to be null

As you can see in the following output the sub-categories queried by JOOQ will not be loaded:

2019-11-26 16:28:00.749  INFO 18882 --- [           main] example.springdata.jdbc.jooq.Output      : ==== JOOQ ====
Category(id=1,
    name=Cars,
    description=Anything that has approximately 4 wheels,
    ageGroup=_3to8,
    subCategories=null)
2019-11-26 16:28:00.749  INFO 18882 --- [           main] example.springdata.jdbc.jooq.Output      : ==== Query ====
Category(id=1,
    name=Cars,
    description=Anything that has approximately 4 wheels,
    ageGroup=_3to8,
    subCategories=[SubCategory(id=1,
    title=sub0),
    SubCategory(id=2,
    title=sub1)])

This is the used database-shema:

CREATE TABLE IF NOT EXISTS category (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  description VARCHAR(2000),
  age_group VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS sub_category (
    id INTEGER IDENTITY PRIMARY KEY,
    title VARCHAR(100),
    category INTEGER
)

score 0 · Answer 1 · answered Nov 27 '19 at 06:11

In the JOOQ variant, JOOQ does the conversion from ResultSet to object instances. Since JOOQ doesn't know about the interpretation of aggregates as it is done by Spring Data JDBC it only hydrates the Category itself, not the contained Set of SubCategory.

Spring Data JDBC on the other hand interprets the structure of the Category and based on that executes another statement to load the subcategories.

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

1 Answers1