Bind values and parameters

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# Bind values

Bind values are used in SQL / JDBC for various reasons. Among the most obvious ones are:

• Protection against SQL injection. Instead of inlining values possibly originating from user input, you bind those values to your prepared statement and let the JDBC driver / database take care of handling security aspects.
• Increased speed. Advanced databases such as Oracle can keep execution plans of similar queries in a dedicated cache to prevent hard-parsing your query again and again. In many cases, the actual value of a bind variable does not influence the execution plan, hence it can be reused. Preparing a statement will thus be faster
• On a JDBC level, you can also reuse the SQL string and prepared statement object instead of constructing it again, as you can bind new values to the prepared statement. This is currently not supported by jOOQ, though

# Ways to introduce bind values with jOOQ

Bind values are omni-present in jOOQ. Whenever you create a condition, you're actually also adding a bind value:

// In jOOQ, "Poe" will be the bind value bound to the condition
LAST_NAME.equal("Poe");

The above notation is actually convenient way to explicitly create a bind value for "Poe". You could also write this, instead:

// The Factory allows for explicitly creating bind values
LAST_NAME.equal(Factory.val("Poe"));

// Or, when static importing Factory.val:
LAST_NAME.equal(val("Poe"))

Once created, bind values are part of the query's syntax tree (see the manual's section about jOOQ's architecture for more information about jOOQ's internals), and cannot be modified directly anymore. If you wish to reuse a query and modify bind values between subsequent query executions, you can access them again through the org.jooq.Query interface:

// Access the first bind value from a query. Indexes are counted from 1, just as with JDBC
Query query = create.select().from(T_AUTHOR).where(LAST_NAME.equal("Poe"));
Param<?> param = query.getParam("1");

// You could now modify the Query's underlying bind value:
if ("Poe".equal(param.getValue())) {
    param.setConverted("Orwell");
}

The org.jooq.Param type can also be named explicitly using the Factory's param() methods:

// Create a query with a named parameter. You can then use that name for accessing the parameter again
Query query1 = create.select().from(T_AUTHOR).where(LAST_NAME.equal(param("lastName", "Poe")));
Param<?> param1 = query.getParam("lastName");

// Or, keep a reference to the typed parameter in order not to lose the <T> type information:
Param<String> param2 = param("lastName", "Poe");
Query query2 = create.select().from(T_AUTHOR).where(LAST_NAME.equal(param2));

// You can now change the bind value directly on the Param reference:
param2.setValue("Orwell");

The org.jooq.Query interface also allows for setting new bind values directly, without accessing the Param type:

Query query1 = create.select().from(T_AUTHOR).where(LAST_NAME.equal("Poe"));
query1.bind(1, "Orwell");

// Or, with named parameters
Query query2 = create.select().from(T_AUTHOR).where(LAST_NAME.equal(param("lastName", "Poe")));
query2.bind("lastName", "Orwell");

NOTE: Should you wish to use jOOQ only as a query builder and execute queries with another tool, such as Spring Data instead, you can also use the Factory's renderNamedParams() method, to actually render named parameter names in generated SQL:

-- The named bind variable can be rendered

SELECT *
FROM T_AUTHOR
WHERE LAST_NAME = :lastName

create.renderNamedParams(
    create.select()
          .from(T_AUTHOR)
          .where(LAST_NAME.equal(
                 param("lastName", "Poe"))));

# Inlining bind values

Sometimes, you may wish to avoid rendering bind variables while still using custom values in SQL. jOOQ refers to that as "inlined" bind values. When bind values are inlined, they render the actual value in SQL rather than a JDBC question mark. Bind value inlining can be achieved in two ways:

1. By using the settings and setting the org.jooq.conf.StatementType to STATIC_STATEMENT. This will inline all bind values for SQL statements rendered from such a Factory.
2. By using Factory.inline() methods.

In both cases, your inlined bind values will be properly escaped to avoid SQL syntax errors and SQL injection. Some examples:

// Use dedicated calls to inline() in order to specify
// single bind values to be rendered as inline values
// --------------------------------------------------
create.select()
      .from(T_AUTHOR)
      .where(LAST_NAME.equal(inline("Poe")));

// Or render the whole query with inlined values
// --------------------------------------------------
Settings settings = new Settings()
    .withStatementType(StatementType.STATIC_STATEMENT);

// Add the settings to the factory
Factory create = new Factory(connection, SQLDialect.ORACLE, settings);

// Run queries that omit rendering schema names
create.select()
      .from(T_AUTHOR)
      .where(LAST_NAME.equal("Poe"));