jooq绑定在正则表达式模式内（MySQL）

huangapple
go评论115阅读模式
英文:

jooq binding within regexp pattern (MySQL)

问题

I have a small Java service connected to a MySQL DB which stores documents. Each document has a name, and when a document is duplicated, the new document gets the name <previous name> (#). The documents table looks something like this:

id name
1 A Document
2 A Document (1)
3 A Document (2)
4 Another Document
5 Another Document (1)

I have a working MySQL query which selects a document and all of its duplicates, which I am attempting to translate into jooq.

  1. select * from documents
  2. where name regexp '(TITLE_GOES_HERE|TITLE_GOES_HERE \\([:digit:]+\\))$';
  4. -- For instance, to select rows 1, 2, and 3 of the above table,
  5. -- I would replace `TITLE_GOES_HERE` with `A Document`, resulting
  6. -- in the following MySQL query:
  7. select * from documents
  8. where name regexp '(A Document|A Document \\([:digit:]+\\))$';

Note: If you would like a working regex tester, you can find it here. It's slightly different than the MySQL version, but it suffices for demonstration purposes.

In order for this query to work programmatically, I have to replace "TITLE_GOES_HERE" in the regular expression with the name of the document for which I want to find. However, that throws an unexpected error in jooq.

  1. public List<Document> getOriginalAndDuplicates(String docName) {
  2.   SelectQuery<DocumentRecord> select = getDSLContext().selectQuery(DOCUMENT);
  4.   select.addCondition(
  5.     DSL.condition(
  6.       DSL.field(
  7.         // Binding for both the document AND the name
  8.         // Notice that the name binding is inside the regular expression
  9.         "{0} regexp '({1} \\([:digit:]+\\))'",
  10.         Boolean.class,
  11.         DOCUMENT.NAME,
  12.         docName)));
  14.   Result<DocumentRecord> records = select.fetch();
  15.   List<Document> docs = records.stream().map(documentConverter::convertTo).toList();
  16.   return docs;
  17. }

When I run this, I get the following error:

  1. Caused by: org.jooq.exception.DataAccessException: SQL [select document.id, document.name from document where (document.name regexp '({1} \\([:digit:]+\\))')]; Syntax error in regular expression on line 1, character 2.
  2. 	at org.jooq_3.14.16.MYSQL.debug(Unknown Source)
  3. 	at org.jooq.impl.Tools.translate(Tools.java:2903)
  4. 	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
  5. 	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
  6. 	... 27 common frames omitted
  7. Caused by: java.sql.SQLException: Syntax error in regular expression on line 1, character 2.
  8. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
  9. 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  10. 	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
  11. 	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
  12. 	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
  13. 	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
  14. 	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
  15. 	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
  16. 	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4217)
  17. 	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:283)
  18. 	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:375)
  19. 	... 27 common frames omitted

The error is occurring because the first binding ({1}) is not being replaced with the document name.

Has anyone ever encountered anything like this?

英文:

I have a small Java service connected to a MySQL DB which stores documents. Each document has a name, and when a document is duplicated, the new document gets the name &lt;previous name&gt; (#). The documents table looks something like this:

id name
1 A Document
2 A Document (1)
3 A Document (2)
4 Another Document
5 Another Document (1)

I have a working MySQL query which selects a document and all of its duplicates, which I am attempting to translate into jooq.

  1. select * from documents
  2. where name regexp &#39;(TITLE_GOES_HERE|TITLE_GOES_HERE \\([:digit:]+\\))$&#39;;
  4. -- For instance, to select rows 1, 2, and 3 of the above table,
  5. -- I would replace `TITLE_GOES_HERE` with `A Document`, resulting
  6. -- in the following MySQL query:
  7. select * from documents
  8. where name regexp &#39;(A Document|A Document \\([:digit:]+\\))$&#39;;

> Note: If you would like a working regex tester, you can find it here. It's slightly different than the MySQL version, but it suffices for demonstration purposes.

In order for this query to work programmatically, I have to replace "TITLE_GOES_HERE" in the regular expression with the name of the document for which I want to find. However, that throws an unexpected error in jooq.

  1. public List&lt;Document&gt; getOriginalAndDuplicates(String docName) {
  2.   SelectQuery&lt;DocumentRecord&gt; select = getDSLContext().selectQuery(DOCUMENT);
  4.   select.addCondition(
  5.     DSL.condition(
  6.       DSL.field(
  7.         // Binding for both the document AND the name
  8.         // Notice that the name binding is inside the regular expression
  9.         &quot;{0} regexp &#39;({1} \\\\([:digit:]+\\\\))&#39;&quot;,
  10.         Boolean.class,
  11.         DOCUMENT.NAME,
  12.         docName)));
  14.   Result&lt;DocumentRecord&gt; records = select.fetch();
  15.   List&lt;Document&gt; docs = records.stream().map(documentConverter::convertTo).toList();
  16.   return docs;
  17. }

When I run this, I get the following error:

  1. Caused by: org.jooq.exception.DataAccessException: SQL [select document.id, document.name from document where (document.name regexp &#39;({1} \\([:digit:]+\\))&#39;)]; Syntax error in regular expression on line 1, character 2.
  2. 	at org.jooq_3.14.16.MYSQL.debug(Unknown Source)
  3. 	at org.jooq.impl.Tools.translate(Tools.java:2903)
  4. 	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
  5. 	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
  6. 	... 27 common frames omitted
  7. Caused by: java.sql.SQLException: Syntax error in regular expression on line 1, character 2.
  8. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
  9. 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  10. 	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
  11. 	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
  12. 	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
  13. 	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
  14. 	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
  15. 	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
  16. 	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4217)
  17. 	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:283)
  18. 	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:375)
  19. 	... 27 common frames omitted

The error is occurring because the first binding ({1}) is not being replaced with the document name.

Has anyone ever encountered anything like this?

> Additional info: When I replace the {1} with a hardcoded value, such as A Document, the query succeeds without any problems. It only causes problems when I try to bind the {1} to a variable value.

答案1

得分: 1

I'll provide translations for the non-code parts:

为什么你的普通SQL模板不起作用

jOOQ 普通SQL模板 不会在字符串文字或注释中进行任何替换，以及其他内容。根据文档：

> ## 解析规则
> 在处理这些普通SQL模板时，会运行一个小型解析器，处理诸如
> - 字符串文字
> - 引用名称
> - 注释
> - JDBC转义序列
> - 索引 (?) 或命名 (:标识符) 绑定变量占位符
>
> 上述内容由模板引擎识别，并在替换编号占位符和/或绑定变量时忽略其中的内容。

您的模板必须编写如下：

  1. DSL.field(
  2.     &quot;{0} regexp concat(&#39;(&#39;, {1}, &#39;\\\\([:digit:]+\\\\))&#39;&quot;,
  3.     Boolean.class,
  4.     DOCUMENT.NAME,
  5.     docName
  6. )

请注意，所需的 \\ 数量取决于 NO_BACKSLASH_ESCAPES 设置，您还可以在 jOOQ 中配置此设置。如果您不使用普通SQL模板，jOOQ会自动处理此问题。

使用jOOQ API而不是模板

但为什么要使用模板呢？jOOQ对此运算符具有本机支持，可使用 Field.likeRegex() 来实现：

  1. DOCUMENT.NAME.likeRegex(concat(val(&quot;(&quot;), val(docName), val(&quot;\\([:digit:]+\\))&quot;)))
英文:

Why your plain SQL template didn't work

A jOOQ plain SQL template will not make any substitutions in string literals or comments, and other things. As per the documentation:

> ## Parsing rules
> When processing these plain SQL templates, a mini parser is run that handles things like
> - String literals
> - Quoted names
> - Comments
> - JDBC escape sequences
> - Indexed (?) or named (:identifier) bind variable placeholders
>
> The above are recognised by the templating engine and contents inside of them are ignored when replacing numbered placeholders and/or bind variables.

Your template has to be written like this:

  1. DSL.field(
  2.     &quot;{0} regexp concat(&#39;(&#39;, {1}, &#39;\\\\([:digit:]+\\\\))&#39;&quot;,
  3.     Boolean.class,
  4.     DOCUMENT.NAME,
  5.     docName
  6. )

Note the number of \\ needed depends on the NO_BACKSLASH_ESCAPES setting, which you can also configure in jOOQ. jOOQ handles this automatically if you're not using a plain SQL template.

Using jOOQ API instead

But why bother using a template? jOOQ has native support for this operator using Field.likeRegex():

  1. DOCUMENT.NAME.likeRegex(concat(val(&quot;(&quot;), val(docName), val(&quot;\\([:digit:]+\\))&quot;)))

huangapple
  • 本文由 发表于 2023年4月20日 10:10:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/76060031.html
  • java
  • jooq
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定