UnableToCreateStatementException JDBI3在caluse rowMapper中的使用

Question

我有一个疑问，什么时候执行我有一个期望

@SqlQuery("SELECT * FROM TABLEA WHERE m= :mAND  values in (<values>)")
@RegisterRowMapper(TableARsMapper.class)
Optional<TableAObject> getByValues(@BindList("values") List<String> values, @Bind("m") String m);

异常

org.jdbi.v3.core.statement.UnableToCreateStatementException: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM TABLEA WHERE m = ? AND  values [*]in (?)"; expected "ROW, (, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT * FROM tablea WHERE m = ? AND  values in (?) [42001-210] [statement:"SELECT * FROM tablea WHERE m = :m AND  values in (:__values_0)", arguments:{positional:{1:TEST}, named:{m:TEST,__values_0:camini}, finder:[]}]
    at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1775)
    at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:64)
    at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:57)


at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM tablea WHERE m = ? AND  values [*]in (?)"; expected "ROW, (, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT * FROM tablea WHERE m = ? AND  values in (?) [42001-210]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
    at org.h2.message.DbException.getSyntaxError(DbException.java:265)

我在我的pom中使用了springboot 2.7，H2，Jdbi3 --我有这些依赖

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-core</artifactId>
    <version>3.34.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jdbi/jdbi3-sqlobject -->
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.34.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jdbi/jdbi3-spring5 -->
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-spring5</artifactId>
    <version>3.34.0</version>
</dependency>

我的地图：

public class TableARsMapper implements RowMapper<GlossaryKeys> {

    @Override
    public TableAObject map(ResultSet r, StatementContext ctx) throws SQLException {
    ....
    }

我不明白为什么他在BindList和in子句中给了我一个错误。有人能帮帮我吗？

阅读文档时，注释是正确的，我希望查询能够正确运行。

Answer 1

VALUES是SQL中的保留字，不能用作未引号的标识符。根据列的定义，需要编写"VALUES"或"values"这样的标识符。

Java文本中的"需要用\ (AND \"values\" IN)转义。