springboot利用mybatis批量写入clickhouse报错及解决方法

springboot利用druid和mybatis批量数据写入clickhouse时，刚开始的时候，完全按照mysql的写法，出现无法写入的问题。经过不断的尝试，发现所使用的驱动包，驱动类配置以及sql写法上都需要特别注意，不然批量写入会抛出各种异常导致写入失败。

虽然clickhouse的官方说明中能够支持大部分的sql语法，在某些特定的场景下，比如批量写数据，还是需要做一些特别的配置。

这里用的clickhouse的版本是：version 22.2.2.1

1. 添加依赖

这个是可以实现批量写入的版本。

<!--clickhouse-->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.2-patch8</version>
</dependency>

刚开始的时候添加的是下面这个版本：

<!--
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>
-->

使用这个版本的时候，批量写入数据总是报错。

2. 配置连接信息

url: jdbc:clickhouse://192.168.17.81:8123/default
username: username
password: password
# driverClassName: ru.yandex.clickhouse.ClickHouseDriver
driverClassName: com.clickhouse.jdbc.ClickHouseDriver

之前配置为ru.yandex.clickhouse.ClickHouseDriver时，批量写入报错。

3. mapper.xml中的sql写法

<insert id="insertBatchCkPerm" useGeneratedKeys="false" parameterType="java.util.List">
        insert into plat_access_perm(access_id, developer_id, developer_name, access_key, access_secret)
        FORMAT Values
        <foreach collection="list" item="item" index="index" separator=",">
        (#{item.accessId},#{item.developerId},#{item.developerName},#{item.accessKey},#{item.accessSecret})
        </foreach>
</insert>

这里需要特别说明的是：按照mysql的写法，只需要values关键字即可，但是这里使用values的时候，单条记录写入是可以的，但是多条记录就会报错，报的是空指针错误。需要修改为FORMAT Values

useGeneratedKeys="false" 设置时为了避免自动生成主键id，clickhouse不支持。

4. mapper接口的写法

public int insertBatchCkPerm(List list);

5. 遇到过的报错信息

报错信息如下：

### Error updating database. Cause: java.sql.SQLFeatureNotSupportedException ### The error may exist in file
 
Cause: java.sql.SQLFeatureNotSupportedException ; null; nested exception is java.sql.SQLFeatureNotSupportedException

错误消息：

nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: java.lang.NullPointerException

错误堆栈：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.NullPointerException
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy84.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy193.insertBatchCkPerm(Unknown Source)
	at com.ftsafe.operate.service.impl.PlatAccessPermServiceImpl.insertBatchCkPerm(PlatAccessPermServiceImpl.java:90)
	at com.ftsafe.operate.service.impl.PlatAccessPermServiceImpl$$FastClassBySpringCGLIB$$71c31b4d.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)	
	......		
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.NullPointerException
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)	
	... 133 common frames omitted
Caused by: java.lang.NullPointerException: null
	at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.prepareStatement(ClickHouseConnectionImpl.java:618)
	at com.clickhouse.jdbc.ClickHouseConnection.prepareStatement(ClickHouseConnection.java:104)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:535)
	at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:908)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:116)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:531)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:326)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:362)
	at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
	at com.sun.proxy.$Proxy97.prepareStatement(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	... 151 common frames omitted
13:22:56.656 [http-nio-80-exec-8] WARN  o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver - [logException,208] - Resolved [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.NullPointerException]

以上错误时，批量插入多条数据时，没有添加FORMAT时出现的报错信息。