jooq issue with limit and offset

bind-variables, java, jdbctemplate, jooq, spring

Solution

jOOQ generates SQL with bind values. As you're using jOOQ to render SQL only, executing it with Spring, you essentially have any of these options:

Use inlined bind values, explicitly:

Like this, you can explicitly tell jOOQ not to render any bind variables for your `LIMIT .. OFFSET` clause

create.select(DSL.field("table_name"))
      .from("tables t")
      .where("t.table_schema LIKE '" + schemaName + "'")
      .limit(DSL.inline(10))
      .offset(DSL.inline(2))
      .getSQL();

Let jOOQ inline all bind values:

Like this, you can tell jOOQ not to render any bind variables at all:

Settings settings = new Settings();
settings.setStatementType(StatementType.STATIC_STATEMENT);
DSLContext create = DSL.using(connection, dialect, settings);
// [...]

Extract bind values from jOOQ's query:

Like this, you can let jOOQ render bind variables where appropriate, and extract them in the right order:

Query query =
create.select(DSL.field("table_name"))
      .from("tables t")
      .where("t.table_schema LIKE '" + schemaName + "'")
      .limit(DSL.inline(10))
      .offset(DSL.inline(2));

String sql = query.getSQL();
List<Object> bindValues = query.getBindValues();

More information on jOOQ's understanding about bind values:

http://www.jooq.org/doc/latest/manual/sql-building/bind-values/

Note about syntax integrity and SQL injection

Beware that your query is somewhat error-prone, in that `schemaName` should be checked and escaped in case it originates from user input. You can create a bind value for it as such:

create.select(DSL.field("table_name"))
      .from("tables t")
      .where("t.table_schema LIKE ?", schemaName)

Problem

I have integrated jooq with spring and for all types of querying to the database (MySQL), I am using JDBC Template of spring. jooq library is used here to generate the sql query to pass to jdbc template. Though my rest of the query works fine until I add limit and/or offset to the query. I am generating query as follows: ``` create.select(Factory.field("table_name")) .from("tables t") .where("t.table_schema LIKE '" + schemaName + "'") .limit(10) .offset(2) .getSQL(); ``` I am getting error as follows: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select table_name from tables t where (t.table_schema LIKE 'test') limit ? offset ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? offset ?' at line 1 Which clearly says that the values passed to limit and offset method are not appended to the query. I searched on the documentation but not found any other way to achieve this.

Original source