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

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. — Tim Biegeleisen, Oct 18 '18 at 09:03
For example simple grid filters. User choose column1 column2 etc. So "select * from table where column1=:column1 and...:" — LeshaRB, Oct 18 '18 at 10:32

score 16 · Accepted Answer · answered Oct 24 '18 at 16:20

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>

JDBI3 dynamically create a WHERE clause

1 Answers1

Static WHERE clause approach:

Template engine approach

FreeMarker

Static `WHERE` clause approach: