Spring Data JDBC read optimization

Question

I am just starting out with Spring Data JDBC. In my test project I now have a request that is very slow in the result, because a lot of queries are sent to the database.

Here is an extract to illustrate the principle of my problem. There will be several thousand data records in the real database.

There is a model with articles and prices in a 1:n relation. This results in this example data model:

@Table("articles")
public class ArticleModel {

    @Id
    @Column("id")
    private Integer id;

    @Column("name")
    private String name;

    @Column("size")
    private String size;

    @MappedCollection(idColumn = "article_id", keyColumn = "id")
    private Set<ArticlePriceModel> prices;
   //getter setter
}

@Table("article_prices")
public class ArticlePriceModel {
    @Id
    @Column("id")
    private Integer id;

    @Column("type_id")
    private String priceType;

    @Column("price")
    private BigDecimal price;

    @Column("article_id")
    AggregateReference<ArticleModel, Integer> articleReference;
    //getter setter

I use this repository for queries:

public interface ArticleRepository extends CrudRepository<ArticleModel, Integer> {

    @Query("SELECT * FROM articles WHERE name LIKE :name")
    List<ArticleModel> findByName(String name);

    @Query(value =  """
            SELECT a.*, ap.* FROM articles a
                INNER JOIN article_prices ap ON a.id = ap.article_id
                WHERE type_id LIKE 'retail' AND name LIKE :name
            """
            , rowMapperClass = SinglePriceRowMapper.class)
    List<ArticleModel> findByNameWithRetailPrice(String name);
}

This means that with findByName, a further SELECT is executed for each result row (==ArticleModel) to determine the prices. However, the data could also be collected all together directly using a JOIN. In the current implementation it is very slow for a large resultset. Here is the first question: Is there a way for Spring Data JDBC to create the data structure directly from a join with just one select command?

The special feature of my second select findByNameWithRetailPrice is that you can create the structure completely with one line in the resulset. Is there a way to do this automatically?

As I haven't found anything for this so far, I have created my own implementation with the RowMapper.

public class SinglePriceRowMapper implements RowMapper<ArticleModel> {

    @Override
    public ArticleModel mapRow(ResultSet rs, int rowNum) throws SQLException {
        ArticleModel result = new ArticleModel();

        result.setId(rs.getInt("id"));
        result.setName(rs.getString("name"));
        result.setSize(rs.getString("size"));

        ArticlePriceModel price = new ArticlePriceModel();
        price.setId(rs.getInt(findIndex(rs, "article_prices", "id")));
        price.setPrice(rs.getBigDecimal("price"));
        price.setPriceType(rs.getString("type_id"));

        result.setPrices(Set.of(price));

        return result;
    }

However, my problem is that I have to write a lot of code that also contains the column names hard-coded in the source code. The @Column annotations of the models are not accessed. Is there any way to simplify this so that you don't have to build the RowMapper 100% yourself? It would be practical if you could use a basic RowMapper here and onlIt would be practical if you could use a basic RowMapper here and then implement your own additional mappings. RowMapper is an interface and I haven't seen any implementations that you could simply overwrite.

I have created a small test project which can be found here: https://github.com/Malte-Neu/spring-data-jdbc-test

Answer 1

The experimental feature of Single Query Loading should solve your problem.

Note that it doesn't support nested collections. A single level of collection as you have in your model shouldn't be a problem.