fix: When sql statements in mybatis query SQL Server data using clickhouse's jdbc function, #{} cannot set parameter values

[liuyudong-pacvue]

Describe the bug

When I use mybatis, I need to find sqlserver data through the clickhouse jdbc function, but I found that sqlserver statements cannot use #{} to set parameters, but sql outside the jdbc function can use #{} to set parameters

Specifically, when I use the following SQL statement:

<select id="selectDownloadLogList" resultType="com.test.DownloadLogDTO">
    select * from jdbc ("commerce","select * from DownloadLog where id = #{query.id}") 
</select>

Error log

Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='query.id', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Can't set parameter at index 1 due to no JDBC style '?' placeholder found in the query Caused by: java.sql.SQLException: Can't set parameter at index 1 due to no JDBC style '?' placeholder found in the query at com.clickhouse.jdbc.SqlExceptionUtils.clientError(SqlExceptionUtils.java:73) at com.clickhouse.jdbc.internal.AbstractPreparedStatement.toArrayIndex(AbstractPreparedStatement.java:26) at com.clickhouse.jdbc.internal.SqlBasedPreparedStatement.setLong(SqlBasedPreparedStatement.java:319)

Configuration

Environment

• Client version: 0.4.6
• Language version: jdk17
• OS: windows11
• DriverClass: com.clickhouse.jdbc.ClickHouseDriver

[liuyudong-pacvue]

I found in com.clickhouse.jdbc.JdbcParameterizedQuery#parse method "ClickHouseUtils. IsQuote (ch)" will skip ['] and ["] in the middle of the content will not go to determine whether it will have[?] This makes it very difficult for me. Is there any good solution?

[zhicwu]

Hi @liuyudong-pacvue, it's expected that SQL parameter cannot be part of a string. Perhaps you can make the whole statement as a parameter?

Although it could be fun to play with jdbc table function, but please do NOT use it on production. This is because that it has issue in both design and implementation. Besides stability issue, the most severe one is probably ClickHouse/ClickHouse#36385.

[SinceNovember]

Hi, If using the jdbc function is not recommended, is there a better way to perform joins and other related operations with other types of databases? If so, please let me know

[zhicwu]

Hi @SinceNovember, if you're talking about federated query on ClickHouse, perhaps url table function is more reliable, although it requires you to implement a web service to bridge the query to corresponding database. If it's just about small amount of data for convenience as shown above, you may consider to issue separate query for external data, put it into an external table like below on ClickHouse for joining.

https://github.com/ClickHouse/clickhouse-java/blob/f2ea0e133b9f2c19416de2161019589de141fca7/examples/jdbc/src/main/java/com/clickhouse/examples/jdbc/Advanced.java#L63-L83

[liuyudong-pacvue]

Thank you for your reply and I will refer to your comments!