Currently exploring the possibilities of JOOQ, I want to start carefully and only use JOOQ as an SQL builder. I defined my database schema as classes derived from CustomTable to get type safety.
This leads to code like
Param<Integer> pId = ...
Query query = context.select(sometable.somefield.max())
.from(sometable)
.where(sometable.id.eq(pId)
;
where sometable is a variable holding one of my table instances.
What I currently do with the query is akin to
PreparedStatement pstmt = connection.prepareStatement(query.getSQL());
pstmt.setObject(1, pId.getValue(), pId.getDataType().getSQLType());
But as soon as there are more parameters for the statement, I start to get intricate dependencies on JOOQ's implementation with regard to setObjects first parameter.
I thought to use query.getBindValues(), but this returns only the plain Java objects to bind to the statement. I have to assume that the order matches the position order for setObject and in addition I now miss the getSQLType(). So this is no good way either.
Then I hoped to find something like query.getPreparedStatement(connection) to create the statement from the provided connection with all parameters nicely filled in, but this does not seem to exist.
Is there some neat way to get a PreparedStatement out of a JOOQ Query that I am missing?
By default, jOOQ internally creates PreparedStatements every time you run Query.execute(), or any of the various ResultQuery.fetch() methods. I think that it would probably be a good idea to allow for users preparing the statement, and accessing such unexecuted statements via a new method Query.statement(). I've added this as a feature request:
Already today, you can extract the SQL and bind variables yourself using the API you've mentioned, more or less in the way you described (note, there's also Query.getParams()), which returns Param types in bind order.
Note, however, there usually aren't really any good reasons (except for SQL string caching in very high throughput scenarios) for extracting the SQL string and executing it yourself via JDBC directly. jOOQ is mostly used to render SQL and to execute it.