限制和偏移量的jooq问题 [英] jooq issue with limit and offset

问题描述

我已经将jooq与spring集成在一起，并且对于所有类型的对数据库(MySQL)的查询，我正在使用spring的JDBC模板.这里使用jooq库来生成传递给jdbc模板的sql查询.

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();

我收到如下错误:

org.springframework.jdbc.BadSqlGrammarException:StatementCallback; 错误的SQL语法[从表t中选择table_name，其中(t.table_schema 像测试")限制?抵消 ?];嵌套的例外是 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:您有一个 您的SQL语法错误；检查与您的手册相对应的手册 MySQL服务器版本附近使用正确的语法吗?抵消 ?'在 第1行

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

其中明确指出，传递给limit和offset方法的值未附加到查询中.

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生成具有绑定值的SQL.当您使用jOOQ仅渲染SQL并使用Spring执行它时，您实际上具有以下任何选项:

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:

像这样，您可以明确地告诉jOOQ不要为LIMIT .. OFFSET子句呈现任何绑定变量

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();

让jOOQ内联所有绑定值:

像这样，您可以告诉jOOQ根本不渲染任何绑定变量:

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);
// [...]

从jOOQ的查询中提取绑定值:

像这样，您可以让jOOQ在适当的位置呈现绑定变量，并以正确的顺序提取它们:

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();

有关jOOQ对绑定值的理解的更多信息:

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

请注意，您的查询有些容易出错，因此应检查schemaName并对其进行转义，以防它源自用户输入.您可以像这样为它创建一个绑定值:

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)

这篇关于限制和偏移量的jooq问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！