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