Is it possible to return custom Java objects combining multiple aggregates in Spring Data JDBC?

Question

I have multiple aggregate classes, such as Request, Scribe, Candidate, and Exam.

Sample schema:

Request (id, scribe_id, candidate_id, exam_id, status)

Scribe (id, name)

Candidate (id, name)

Exam (id, name, schedule)

As you can see, Request table has references to Scribe, Candidate, and Exam tables. For one of the requirements, I need to return all requests based on a condition by including all the corresponding details of scribe, candidate, and exam.

For this, the query in my repository class will be similar to the following:

SELECT r.id, r.status, c.name, s.name,
                e.schedule, e.name
            FROM request r 
            JOIN candidate c ON r.candidate=c.id
            JOIN scribe s ON r.scribe=s.id
            JOIN exam e ON r.exam=e.id
            WHERE <some-condition>

Now, is there a way to map the result of this query directly to a custom Java object and return the same in Spring Data JDBC? I believe another alternative is to use the Spring JDBC template. Curious, any out-of-the-box support from Spring Data JDBC?

Thanks.

Answer 1

I am able to return custom Java object by setting rowMapperClass value of org.springframework.data.jdbc.repository.query.Query annotation. For this need to define RowMapper for custom Java object.

Changes look similar to the following:

public class RequestResourceRowMapper implements RowMapper<RequestResource> {
    @Override
    public RequestResource mapRow(ResultSet resultSet, int rowNumber) throws SQLException { ... }
}

In repository class, need to set rowMapper value.

 @Query(value = """
              SELECT r.id, r.status, c.name, s.name,
                e.schedule, e.name
            FROM request r 
            JOIN candidate c ON r.candidate=c.id
            JOIN scribe s ON r.scribe=s.id
            JOIN exam e ON r.exam=e.id
            WHERE <some-condition>
            """,
            rowMapperClass = RequestResourceRowMapper.class)
    List<RequestResource> searchRequestResources(...);