Dynamic Order in JDBI SQL Object Queries

Question

How do you do ordering with SQL Object Queries in JDBI?

I want to do something like:

@SqlQuery(
    "SELECT * FROM users " +
    "WHERE something = :something " +
    "ORDER BY :orderBy :orderDir"
)
List<User> getUsers(
    @Bind("something") Integer something
  , @BindOrderBy("orderBy") String orderBy
  , @BindOrderDir("orderDir") String orderDir
);

or

@SqlQuery(
    "SELECT * FROM users " +
    "WHERE something = :something " +
    "ORDER BY :orderBy :orderDir"
)
List<User> getUsers(
    @Bind("something") Integer something
  , @Bind("orderBy") OrderBy orderBy
  , @Bind("orderDir") OrderDir orderDir
);

Godbey

Answer 1

I've recently been exploring DropWizard which comes bundled with JDBI and quickly came across the same problem. Unfortunately JDBI has lackluster documentation (JavaDoc and some sample unit tests on it's git repository don't cut it alone) which is disappointing.

Here's what I found that achieves a dynamic order in a Sql Object API for JDBI based on my sample DAO:

@UseStringTemplate3StatementLocator
public interface ProductsDao {

    @RegisterMapperFactory(BeanMapperFactory.class) // will map the result of the query to a list of Product POJOs(Beans)
    @SqlQuery("select * from products order by <orderby> <order> limit :limit offset :offset")
    List<Product> getProducts(@Define("orderby") String orderBy, @Define("order") String order,
                                     @Bind("limit") int limit, @Bind("offset") int offset);

    @SqlQuery("select count(*) from products")
    int getProductsCount();

}

@UseStringTemplate3StatementLocator - this annotation is what allows us to use the <arg> syntax in the queries. These args are going to be replaced with whatever value we provide via the @Define annotation.

To be able to use this feature I had to additionally add this dependency to my pom.xml file:

<dependency>
  <groupId>antlr</groupId>
  <artifactId>stringtemplate</artifactId>
  <version>2.3b6</version> <!-- I am not sure if this specific version is meant to be used though -->
</dependency>

SQL INJECTION WARNING It should be noted that this opens us up to Sql Injection since the values are directly inserted to the query. (In contstrast to :arg syntax in the query and @Bind annotation which uses prepared statements and protects against sql injection). At the very least you should sanitize the parameters that are going to be used for the @Define fields. (Simple example for DropWizard below).

@Path("/products")
@Produces(MediaType.APPLICATION_JSON)
public class ProductsResource {
  private static ImmutableSet<String> orderByChoices = ImmutableSet.of("id", "name", "price", "manufactureDate");

  private final ProductsDao dao;

  public ProductsResource(ProductsDao dao) {
    this.dao = dao;
  }

  @GET
  // Use @InjectParam to bind many query parameters to a POJO(Bean) instead. 
  // https://jersey.java.net/apidocs/1.17/jersey/com/sun/jersey/api/core/InjectParam.html
  // i.e. public List<Product> index(@InjectParam ProductsRequest request)
  // Also use custom Java types for consuming request parameters. This allows to move such validation/sanitization logic outside the 'index' method.
  // https://jersey.java.net/documentation/1.17/jax-rs.html#d4e260 
  public List<Product> index(@DefaultValue("id")  @QueryParam("orderby") String orderBy,
                             @DefaultValue("asc") @QueryParam("order")   String order,
                             @DefaultValue("20")  @QueryParam("perpage") IntParam perpage,
                             @DefaultValue("0")   @QueryParam("page")    IntParam page)

   int limit, offset;

   order = order.toLowerCase(); 
   orderBy = orderBy.toLowerCase();    

   if (!orderByChoices.contains(orderBy)) orderBy = "id"; //sanitize <orderby>
   if (order != "asc" && order != "desc") order = "asc";  //sanitize <order>

   limit = perpage.get();
   offset = page.get() < 0 ? 0 : page.get() * limit;

   return dao.getProducts(orderBy, order, limit, offset);

  }
}

Geosyncline

This has been driving me mad all night, especially the lack of documentation. @Geosyncline your answer is thorough and extremely helpful, thank you. if possible, could you explain why @Bind("foo") String foo will not substitute the string into the query using :foo ? or if you have found documentation that covers this? – Griffin

This answer save my life too. 'org.antlr:stringtemplate:3.2.1' works for me too but the latest 4.0.2 doesn't. May I ask why this annotation @UseStringTemplate3StatementLocator needs such dependency to make it work – Deify

What if my orderby, order, limit, offset come from an object (say Page). For example: dao.getProducts(Page page). How can I @Define them in this case? – Formulate

Answer 2

I think its because the String Template library is assumed to be provided and that assumption fails at runtime. Adding following to the application POM should fix the issue:

<dependency>
  <groupId>org.antlr</groupId>
  <artifactId>stringtemplate</artifactId>
  <version>3.2.1</version>
</dependency>

By looking at JDBI 2 pom, you can see following:

<dependency>
  <groupId>org.antlr</groupId>
  <artifactId>stringtemplate</artifactId>
  <version>3.2.1</version>
  <optional>true</optional>
</dependency>

Meaning JDBI wont complain on absence of stringtemplate lib.

Encase

Answer 3

well it turns out that you add the ORDER BY to your query like so

@SqlQuery("SELECT * FROM incident_events WHERE incident_id=:incidentId ORDER BY event_time DESC LIMIT :limit OFFSET :offset")
List<IncidentEvent> getPaginated(@Bind("incidentId") int incidentId, @Bind("limit") int limit, @Bind("offset") int offset);

Trifocals

I think it was about dynamic choice of the field you're ordering by – Lytta