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

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>