UnableToCreateStatementException JDBI3 using in caluse rowMapper

Question

I have this query that when is executed I have an expection

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

exception

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)

I use springboot 2.7, H2, Jdbi3 in my pom I have these dependecies

<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>

My mapper :

public class TableARsMapper implements RowMapper<GlossaryKeys> {

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

I don't understand why he gives me an error in the BindList and IN clause. Can anyone please help me?

Reading the documentation, the annotations are correct, I expect the query to run correctly

Answer 1

VALUES is a reserved word in SQL, it cannot be used as unquoted identifier. You need to write such identifier as "VALUES" or "values" depending on definition of your column.

" inside Java string literals needs to be escaped with \ (AND \"values\" IN).