'Why are namedJdbcTemplate not working with clickhouse-jdbc?

I am new to ClickHouse. I am running version 27.2.2.1.

I have a Java/Kotlin project. I am using clickhouse-jdbc, version 0.3.2-patch5 to connect to the database.

Here is how I am connecting to the database

@Bean(name = ["clickhouseDS"])
fun clickhouseDataSource(): DataSource {
    val dataSource = SimpleDriverDataSource()
    dataSource.setDriverClass(ClickHouseDriver::class.java)
    dataSource.url = jdbcUrl
    dataSource.username = dbUsername
    dataSource.password = dbPassword
    val properties = Properties()
    properties.setProperty("namedParameter", "true")
    dataSource.connectionProperties = properties
    logger.info("postgresDS: $jdbcUrl | $dbUsername")
    return dataSource
}

I can verify that I am connected to the db and can make queries if I hard code the parameters. Here is an example which works:

SELECT *
   FROM parcel p
   WHERE p.parcel_id IN ('c41f29ad-885a-4199-b1b3-475ee0d5913e', 'e5682ace-61af-489e-8000-1c7577a58a0f')

I am using

namedParameterJdbcTemplate.query(sql.sql, sql.params) { rs: ResultSet, _: Int -> createEntity(rs) }

If I try to create a parameterized version of the same query

SELECT *
  FROM parcel p
  WHERE p.parcel_id IN (:parcelIds)

And then call the query the same way, I get this error

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [
            SELECT *
            FROM parcel p
         WHERE p.parcel_id IN (?, ?) ]; SQL state [HY000]; error code [0]; Parameter index must between 1 and 0 but we got 1; nested exception is java.sql.SQLException: Parameter index must between 1 and 0 but we got 1
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:738) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:794) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:209) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at com.aeonai.parcel.repository.ParcelRepository.findAllByIds(ParcelRepository.kt:30) ~[classes/:na]
    at com.aeonai.parcel.grpcservice.ParcelGrpcService.loadParcelGeometry(ParcelGrpcService.kt:38) ~[classes/:na]
    at com.aeonai.parcel.proto.ParcelServiceGrpc$MethodHandlers.invoke(ParcelServiceGrpc.java:347) ~[classes/:na]
    at io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:182) ~[grpc-stub-1.44.0.jar:1.44.0]
    at io.grpc.internal.ServerCallImpl$ServerStreamListenerImpl.halfClosed(ServerCallImpl.java:340) ~[grpc-core-1.43.2.jar:1.43.2]
    at io.grpc.internal.ServerImpl$JumpToApplicationThreadServerStreamListener$1HalfClosed.runInContext(ServerImpl.java:866) ~[grpc-core-1.43.2.jar:1.43.2]
    at io.grpc.internal.ContextRunnable.run(ContextRunnable.java:37) ~[grpc-core-1.43.2.jar:1.43.2]
    at io.grpc.internal.SerializingExecutor.run(SerializingExecutor.java:133) ~[grpc-core-1.43.2.jar:1.43.2]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]
Caused by: java.sql.SQLException: Parameter index must between 1 and 0 but we got 1
    at com.clickhouse.jdbc.SqlExceptionUtils.clientError(SqlExceptionUtils.java:43) ~[clickhouse-jdbc-0.3.2-patch5-http.jar:clickhouse-jdbc 0.3.2-patch5 (revision: 3d921d4)]
    at com.clickhouse.jdbc.internal.SqlBasedPreparedStatement.toArrayIndex(SqlBasedPreparedStatement.java:214) ~[clickhouse-jdbc-0.3.2-patch5-http.jar:clickhouse-jdbc 0.3.2-patch5 (revision: 3d921d4)]
    at com.clickhouse.jdbc.internal.SqlBasedPreparedStatement.setObject(SqlBasedPreparedStatement.java:379) ~[clickhouse-jdbc-0.3.2-patch5-http.jar:clickhouse-jdbc 0.3.2-patch5 (revision: 3d921d4)]
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:409) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:231) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:146) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:278) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:241) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-5.3.16.jar:5.3.16]
    ... 15 common frames omitted

The query that is created is

SELECT *
  FROM parcel p
  WHERE p.parcel_id IN (?, ?)

The ids are never added into the query. I am not sure what I am missing. I use this exact pattern all the time with a PostgreSQL db and an Oracle db, but when I am using the ClickHouse Driver it does not work.

What am I missing?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source