Issue
I am using JOOQ with Microsoft SQL Server 2019. It states in this reference,
https://www.jooq.org/doc/3.0/manual/sql-execution/performance-considerations/
It takes some time to render SQL strings. Internally, jOOQ reuses the same java.lang.StringBuilder for the complete query, but some rendering elements may take their time. You could, of course, cache SQL generated by jOOQ and prepare your own java.sql.PreparedStatement objects
I am reading resources on Java Prepared statements Prepared SQL This line does not make sense to me, You could, of course, cache SQL generated by jOOQ and prepare your own java.sql.PreparedStatement objects . Wouldn't using PreparedStatement just add another language and reduce the whole purpose of JOOQ of being type/string safe? How can JOOQ even be used with Prepared statement? Is there code explanation for this? Trying to write code using this strategy.
String sql = "select * from people where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setLong(123);
Does jooq has any performance lead over simple sql in java
Solution
As mentioned in that linked documentation page ("optimise wisely") and in my previous answer, you probably shouldn't worry about these things - certainly not a priori.
To answer your question, you can extract the SQL string and bind values from any jOOQ query like this:
String sql = query.getSQL();
List<Object> binds = query.getBindValues();
And then do whatever you like with those, including caching the values and preparing your own statement, instead of executing the query directly with jOOQ.
But again, and as disclaimed in that linked page, you shouldn't worry about any such overheads up front. The overhead of SQL string generation is measurable when running 10000s of identical trivial queries against an in-memory H2 database with almost no data for example, but not when running ordinary queries over the network against a remote SQL Server with a real world workload.
Also, using this approach, you're giving up on a lot of nice features that jOOQ offers when it executes your queries (e.g. the new MULTISET operator)
Answered By - Lukas Eder
