How to use many optional query parameters in Spring Data JDBC?

Question

We want to query a table using many optional search criteria that are stored in a POJO.

public class TemperatureMeterCriteria {

    private String state;
    private String title;
    private LocalDate date;
    private Year year;
    private Integer month;
    private Integer week;
    private LocalDate startDate;
    private LocalDate endDate;
    private DateQuery dateQuery;
    private AlertLevel alertLevel;
    ...

}

It's super easy with MyBatis : https://mybatis.org/mybatis-3/dynamic-sql.html

<select id="findTemperatureMeter" resultType="TemperatureMeter">
    SELECT * FROM TEMPERATURE_METER
    <where>
        <if test="state != null">
            state = #{state}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
    ...
    </where>

However, how to do the same thing with Spring Data JDBC ?

https://docs.spring.io/spring-data/jdbc/docs/current/reference/html

Answer 1

Currently the only option is to create a custom method and inside use a third party library like Querydsl, jOOQ or MyBatis.

There is work in progress though to offer methods via JdbcAggregateTemplate which will accept a Condition, which could be used for such a purpose. The first steps (delete only) will arrive as a side effect of https://github.com/spring-projects/spring-data-jdbc/issues/771