How to use named parameter in plain sql with jooq

I’m using JOOQ with plain/raw SQL, so that means i’m not using any code generation or the fluid DSL thingy.

The following code works:

Connection connection = ...;
DSLContext context = DSL.using(connection, ...);
String sql = "select * from mytable t where (t.id = ?)"; 
String id = ...; //
Result<Record> result = context.fetch(sql, id);

Now let’s say i have a query with multiple parameters like this:

String sql = "select * from mytable t where (t.id = ?) " + 
             "and (t.is_active = ?) and (t.total > ?)"; 

How do i use a named parameter with these types of queries? I’m thinking something like :

String sql = "select * from mytable t where (t.id = :id) " + 
             "and (t.is_active = :is_active) and (t.total > :total)"; 

ResultQuery<Record> rq = context.resultQuery(sql);
rq.getParam("id").setValue(...); 
rq.getParam("is_active").setValue(...);
rq.getParam("total").setValue(...);
Result<Record> result = rq.fetch();

But the above code doesn’t work (for obvious reasons). Thanks in advance.

Answer

jOOQ currently doesn’t support executing SQL with named parameters. You can use jOOQ to render named parameters if you’re executing the query with another API, such as Spring JDBC. For more information, consider the manual:

http://www.jooq.org/doc/latest/manual/sql-building/bind-values/named-parameters

But the plain SQL templating API allows for re-using templates, e.g.

String sql = "select * "
           + "from mytable t "
           + "where t.id = {0} or (t.id != {0} and t.name = {1})";
ResultQuery<Record> q = ctx.resultQuery(sql, val(1), val("A"));

This way, you can at least re-use values several times.