Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Static statements vs. Prepared Statements
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
With JDBC, you have full control over your SQL statements. You can decide yourself, if you want to execute a static java.sql.Statement without bind values, or a java.sql.PreparedStatement with (or without) bind values. But you have to decide early, which way to go. And you'll have to prevent SQL injection and syntax errors manually, when inlining your bind variables.
With jOOQ, this is easier. As a matter of fact, it is plain simple. With jOOQ, you can just set a flag in your Configuration's Settings, and all queries produced by that configuration will be executed as static statements, with all bind values inlined. An example is given here:
-- These statements are rendered by the two factories: SELECT ? FROM DUAL WHERE ? = ? SELECT 1 FROM DUAL WHERE 1 = 1
// This DSLContext executes PreparedStatements DSLContext prepare = DSL.using(connection, SQLDialect.ORACLE); // This DSLContext executes static Statements DSLContext inlined = DSL.using(connection, SQLDialect.ORACLE, new Settings().withStatementType(StatementType.STATIC_STATEMENT)); prepare.select(val(1)).where(val(1).eq(1)).fetch(); inlined.select(val(1)).where(val(1).eq(1)).fetch();
Reasons for choosing one or the other
Not all databases are equal. Some databases show improved performance if you use java.sql.PreparedStatement, as the database will then be able to re-use execution plans for identical SQL statements, regardless of actual bind values. This heavily improves the time it takes for soft-parsing a SQL statement. In other situations, assuming that bind values are irrelevant for SQL execution plans may be a bad idea, as you might run into "bind value peeking" issues. You may be better off spending the extra cost for a new hard-parse of your SQL statement and instead having the database fine-tune the new plan to the concrete bind values.
Whichever aproach is more optimal for you cannot be decided by jOOQ. In most cases, prepared statements are probably better. But you always have the option of forcing jOOQ to render inlined bind values.
Inlining bind values on a per-bind-value basis
Note that you don't have to inline all your bind values at once. If you know that a bind value is not really a variable and should be inlined explicitly, you can do so by using DSL.inline(), as documented in the manual's section about inlined parameters
Feedback
Do you have any feedback about this page? We'd love to hear it!