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<PlatAccessPerm> 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时出现的报错信息。