@Cacheable duplicates inner list of DTO returned from JOOQ query

Question

I'm new to JOOQ and Spring caching and using version 3.10.6. I'm trying to cache a query result so I don't need to go to database every time. The fetching of the query goes smoothly there is no problem in that but when you execute this query again, it goes to the cache which has duplicate records in the inner lists. Also every time this query is called and it falls to the cache, the duplications grow in number. Now I can put a Set instead of a List but I want to know why this duplication occurs.

Here is my JooqRepo method

@Cacheable(CachingConfig.OPERATORS)
public List<MyDto> getAllOperatorsWithAliases() {
    return create.select(Tables.MY_TABLE.ID)
            .select(Tables.MY_TABLE.NAME)
            .select(Tables.MY_INNER_TABLE.ID)
            .select(Tables.MY_INNER_TABLE.ALIAS)
            .select(Tables.MY_INNER_TABLE.PARENT_ID)
            .select(Tables.MY_INNER_TABLE.IS_MAIN)
            .from(Tables.MY_TABLE)
            .join(Tables.MY_INNER_TABLE)
            .on(Tables.MY_TABLE.ID.eq(Tables.MY_INNER_TABLE.PARENT_ID))
            .fetch(this::createMyDtoFromRecord);
}

private MyDto createMyDtoFromRecord(Record record) {
    MyInnerDto myInnerDto = new MyInnerDto();
    myInnerDto.setId(record.field(Tables.MY_INNER_TABLE.ID).getValue(record));
    myInnerDto.setAlias(record.field(Tables.MY_INNER_TABLE.ALIAS).getValue(record));
    myInnerDto.setParentId(record.field(Tables.MY_INNER_TABLE.PARENT_ID).getValue(record));
    myInnerDto.setIsMain(record.field(Tables.MY_INNER_TABLE.IS_MAIN).getValue(record) == 1);

    MyDto myDto = new MyDto();
    myDto.setId(record.field(Tables.MY_TABLE.ID).getValue(record));
    myDto.setName(record.field(Tables.MY_TABLE.NAME).getValue(record));
    myDto.setInnerDtos(Collections.singletonList(myInnerDto));
    return myDto;
}

and here are the Dtos

@Data
public class MyDto {
    private Long id;
    private String name;
    private List<MyInnerDto> innerDtos;
}

@Data
public class MyInnerDto {
    private Long id;
    private String alias;
    private Long parentId;
    private Boolean isMain;
}

The first call MyDto1 has the list innerDtos of size 1 and with each call that falls to the cache this number goes up by 3 and I think the reason of it is because there are 3 parent dtos being returned in the query.

I've tried adding @EqualsAndHashCode to these dtos but when I add it the query now returns an empty list.

I'm sorry if this was asked before but I couldn't find it.

Are you sure you're not modifying that list outside of your `getAllOperatorsWithAliases()` method? You can check that by making it e.g. a `Collections.unmodifiableList()`, which will help you track down who's changing the list via a stack trace. Note, you can probably replace the logic in your `createMyDtoFromRecord` method by using jOOQ's [`MULTISET` support for nesting collections](https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/) — Lukas Eder, Sep 07 '22 at 08:44
I can't use MULTISET since it requires JOOQ 3.15 and up meanwhile I can't upgrade from 3.10. Also `createMyDtoFromRecord` method used Collections.singletonList() as its mapping so yeah I'm pretty sure they are unmodifiable. — barisc, Sep 07 '22 at 09:12

score 0 · Accepted Answer · answered Sep 14 '22 at 15:53

I found the problem and it was not related to JOOQ but it was about @Cacheable and using in memory caches.

I was using in memory cache and getting rid of the duplicates inside the service layer via putting the contents of the query inside a Map<Long, MyDto> to collect the MyInnerDto's under the same id. But the problem here is; in memory caches return the object itself meanwhile caches like Redis returns a copy of that object. So when I changed the cache object, it was directly changed inside the cache as well, hence the duplication issue.

To get rid of this problem here's the revised version of the query:

@Cacheable(CachingConfig.OPERATORS)
public List<MyDto> getAllOperatorsWithAliases() {
    Map<MyDto, List<MyInnerDto>> result = create.select(Tables.MY_TABLE.ID)
            .select(Tables.MY_TABLE.NAME)
            .select(Tables.MY_INNER_TABLE.ID)
            .select(Tables.MY_INNER_TABLE.ALIAS)
            .select(Tables.MY_INNER_TABLE.PARENT_ID)
            .select(Tables.MY_INNER_TABLE.IS_MAIN)
            .from(Tables.MY_TABLE)
            .join(Tables.MY_INNER_TABLE)
            .on(Tables.MY_TABLE.ID.eq(Tables.MY_INNER_TABLE.PARENT_ID))
            .fetchGroups(
                r -> r.into(Tables.MY_TABLE).into(MyDto.class),
                r -> r.into(Tables.MY_INNER_TABLE).into(MyInnerDto.class)
            );

    result.forEach(MyDto::setInnerDtos);
    return new ArrayList<>(result.keySet());
}

@Cacheable duplicates inner list of DTO returned from JOOQ query

1 Answers1