Fetch an object that has a object inside using JOOQ

Question

I have this association in my database

User(1) <-----------> Project(n)

I'm trying to fetch the data into a single object

dsl.select()
    .from(PROJECT.join(USERS)
    .on(USERS.ID.eq(PROJECT.ID_USERS))).where(PROJECT.ID.eq(id))
    .fetchOneInto(ProjectEntity.class)

This is the User

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class UserEntity {
    
    private int id;
    private String username;
    private String password;
    private String email;
    //private List<ProjectEntity> projects;   

}

This is my ProjectEntity:

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class ProjectEntity {
    
    private Integer id;
    private String name;
    private String description;
    private String location;
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd-MM-yyyy hh:mm:ss")
    private LocalDateTime creationDate;
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd-MM-yyyy hh:mm:ss")
    private LocalDateTime lastModifiedDate;
    private Integer idUsers;
    
}

When I try to retrieve the object, the error below occurs:

org.jooq.exception.DataTypeException: No Converter found for types java.lang.Integer and com.xmobots.domain.entities.UserEntity

Why does this happen and how can I fix it?

Answer 1

The best way to fetch to-many relationships in jOOQ is to use the MULTISET operator. It adds type safety to your mapping, and allows for projecting arbitrary levels of nesting:

List<UserEntity> list =
dsl.select(
        USERS.ID,
        USERS.USERNAME,
        USERS.PASSWORD, // Not convinced you want to project this, though?
        USERS.EMAIL
        multiset(
            select(
                PROJECT.ID,
                PROJECT.NAME,
                PROJECT.DESCRIPTION,
                PROJECT.LOCATION,
                PROJECT.CREATION_DATE,
                PROJECT.LAST_MODIFIED_DATE
            )
            .from(PROJECT)
            .where(PROJECT.ID_USERS.eq(USERS.ID))
        ).convertFrom(r -> r.map(Records.mapping(ProjectEntity::new)))
    )
    .from(USERS)
    .where(USERS.ID.eq(1))
    .fetch(Records.mapping(UserEntity::new));

You can also work with your JOIN if you believe that's faster / more suitable for your case, in case of which you can nest the collection using MULTISET_AGG. This alternative would work because you only have 1 nested collection.