3

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?

Harald
  • 4,575
  • 5
  • 33
  • 72
  • For completeness' sake, could you maybe add a short explanation on why you don't want to use jOOQ for executing your query? – Lukas Eder Oct 26 '14 at 23:21
  • I am not yet ready to sell my soul completely to JOOQ:-) On top of that I read that JOOQ started out as an SQL builder and I think there is a natural separation of three responsibilities: 1) get the query or statement right, 2) handling the connection to the db with pools, connection, statement, etc., 3) reading data out of query results. By getting a PreparedStatement in exchange for a Connection and a JOOQ Query would allow for the separation between (1) and (2) quite nicely. – Harald Oct 27 '14 at 12:06
  • Your soul can run, but it cannot hide. jOOQ has been preparing statements for 5 years now - used by large corporations in productions... Not sure what you're worrying about :) From the very beginning, jOOQ bound variables to PreparedStatement. The SQL extraction feature came only later. Anyway, I'll give an answer – Lukas Eder Oct 27 '14 at 18:41

1 Answers1

3

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks for taking this as a feature request. If I have a say, I would strongly prefer `ResultQuery.statement(Connection con)`, because only this allows me keep owning the connection, only borrowing it to JOOQ for statement creation. – Harald Oct 28 '14 at 11:05
  • @Harald: While you might prefer that particular API for your given use-case, it would be very surprising in the overall context of the jOOQ API. There is virtually no reference to `Connection` whatsoever in any comparable method, so it wouldn't make sense to add it there. In essence, there will be `ResultQuery.statement()`, and perhaps `DSLContext.statement(ResultQuery)`. Both methods would then be consistent with the existing [`ResultQuery.keepStatement()`](http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#keepStatement-boolean-) behaviour. I hope this makes sense? – Lukas Eder Oct 28 '14 at 22:39
  • I am not sure the method call should then be added, because what would I do with this statement? It comes from a connection that I do not really own at the place of the call, and I wonder if it is sensible then to do anything with this statement. But I admit, I am not yet deep enough into JDBC to tell. – Harald Oct 30 '14 at 15:53
  • It's an edge case indeed, but there is already the possibility in jOOQ to access the JDBC ResultSet, so accessing the PreparedStatement wouldn't be surprising. One use case is manual batching, or consecutive execution of a cached statement with new bind values each time... Well, edge cases :-) – Lukas Eder Oct 30 '14 at 18:43