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