JDBI3 dynamically create a WHERE clause

Question

How I can create dynamic where

public interface ThingDAO {
   @SqlQuery("SELECT * FROM things <where>)
   List<Thing> findThingsWhere(@Define("where") String where);
}

JDBI How can I dynamically create a WHERE clause while preventing SQL Injection?

But it's not actually for JDBI3

Haeres

I don't know your API at all, but generally speaking most prepared statement APIs don't allow the entire WHERE clause to be changeable. Only placeholders for specific values inside the WHERE clause are allowed. You might want to include why you think you need this. – Mural

For example simple grid filters. User choose column1 column2 etc. So "select * from table where column1=:column1 and...:" – Haeres

Answer 1

There are two main approaches to achieving dynamic filtering in queries:

• Use a static WHERE clause, and make a NULL parameter mean "no filtering on this parameter." I recommend you always try this approach first.
• Use a template engine. This takes time to set up and validate, and makes queries harder to reason about due to the intermixing of SQL and template languages. Use this when the static WHERE clause does not work for your use case, or you want to eliminate duplication between multiple queries.

Static WHERE clause approach:

SELECT * FROM things
WHERE (:foo IS NULL OR foo_column = :foo)
AND (:bar IS NULL or bar_column = :bar)

• If :foo is null, then things rows will not be filtered on foo_column. Otherwise, only rows with the specified :foo value will be returned.
• Likewise, if :bar is null, then things rows will not be filtered on bar_column. Otherwise, only rows with the specified :bar value will be returned.
• If both parameters are null, all rows will be returned.

Template engine approach

Out of the box, Jdbi 3 only provides simple templating that replaces e.g. <where> with your @Define("where") parameter.

This default template engine can be overridden with whatever you like. Jdbi provides additional template engines for StringTemplate 4, and for Freemarker.

StringTemplate 4 is no longer actively maintained, so I'll just show you the example for Freemarker.

FreeMarker

Add a dependency:

<dependency>
  <groupId>org.jdbi</groupId>
  <artifactId>jdbi3-freemarker</artifactId>
</dependency>

The @UseFreemarkerEngine annotation can be used on a SQL object, which causes the query to first be rendered as a Freemarker template.

The @UseFreemarkerSqlLocator is like @UseFreemarkerEngine, but with the added bonus of loading SQL from files on the classpath. This permits refactoring commonly used SQL patterns into reusable files, which can be referenced through an #include directive.

<#include "/org/jdbi/v3/freemarker/util.ftl">
<#include "util2.ftl">
select name from something
where id in (<#list somethings as something>${something.id}<#sep>, </#list>)
<@groupBy field="name" />
<@orderBy field="name" />

util.ftl:

<#macro orderBy field order="ASC">
  ORDER BY ${field} ${order}
</#macro>

util2.ftl:

<#macro groupBy field>
  GROUP BY ${field}
</#macro>

Noun

Answer 2

A little different but related use-case, I find it a bit weird with IN clause and imo one of the clean way to achieve this is using UseStringTemplateEngine with something like this:

public interface ThingDAO {
   @SqlQuery("SELECT * FROM things WHERE 1 = 1 "
             + "<if(someField)> AND field1 IN (<someField>) <endif> "
             + "<if(someIds)> AND some_id IN (<someIds>) <endif> ")
   @UseStringTemplateEngine
   List<Thing> findThingsWhere(
       @BindList(value = "someField", onEmpty = NULL_VALUE) List<String> values,
       @BindList(value = "someIds", onEmpty = NULL_VALUE) List<UUID> someIds);
}

Athey