CODEWITHSUNDEEP
javajdbi
Aliaksei
Aliaksei

Reputation: 1447

JDBI3 dynamically create a WHERE clause

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

Upvotes: 9

Views: 3428

Answers (2)

Manoj Sehrawat
Manoj Sehrawat

Reputation: 1303

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);
}

Upvotes: 0

qualidafial
qualidafial

Reputation: 6822

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>

Upvotes: 17

Related Questions