Search code examples
joinspring-data-jdbc

Using @query on CrudRepository(Spring data jdbc) with table join

I am trying spring data jdbc with @query and I met a question: If I have a query that joins 2 tables like this: 

@Query("select a.*, b.* from master a, detail b where b.master_id=a.id and a.id=:id")

How do I get the response? The official Spring Data JDBC, References, and Aggregates didn't give any hint on it. Any suggestion?

Solution

  • You have multiple options:

    Use the default RowMapper

    Use as the return type a class (or a collection of that class) that has properties for all the fields you are selecting. This will return a single element for each row.

    Since you are referring to the two tables as master and detail you probably don't want that in this case.

    Use a custom RowMapper

    The @Query annotation allows you to specify your own rowMapperClass which will get instantiated and passed to a NamedParameterJdbcTemplate together with your query.

    Again this will result in on result element per row selected and therefore probably isn't what you want.

    Use a custom ResultSetExtractor

    This can be again specified in the @Query annotation. And allows you to construct the result in an arbitrary way. See the documentation for ResultSetExtractor for more details.

    Remark: If you are using a ResultSetExtractor to create a single Master with multiple Detail instances from multiple rows of your query result make sure to add an ORDER BY master.id. Otherwise the order of rows is likely as desired but actually not guaranteed.