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
);
Best Answer
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 - 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: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).