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.
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:
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();
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);
// [...]
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();
http://www.jooq.org/doc/latest/manual/sql-building/bind-values/
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)