SpringBoot Mybatis 批量插入 ClickHouse 接口实现

SpringBoot Mybatis 批量插入 ClickHouse 接口实现

1. xml配置

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   
   <mapper namespace="cn.ac.iie.mapper.AuditInfoMapper">
         <insert id="insertAudit" parameterType="cn.ac.iie.audit.entity.Audit" useGeneratedKeys="false">
           INSERT INTO audit
           (insertDate,dateTime, userType, clientIp,pid, msg, acct, exe, hostname, addr, terminal, res)
           VALUES
           <foreach collection="audits" item="Audit" index="index" separator=",">
               (#{Audit.insertDate,jdbcType=DATE},#{Audit.dateTime,jdbcType=DATE}, #{Audit.userType,jdbcType=VARCHAR},
               #{Audit.clientIp,jdbcType=VARCHAR}, #{Audit.pid,jdbcType=VARCHAR},
               #{Audit.msg,jdbcType=VARCHAR}, #{Audit.acct,jdbcType=VARCHAR}, #{Audit.exe,jdbcType=VARCHAR},
               #{Audit.hostname,jdbcType=VARCHAR}, #{Audit.addr,jdbcType=VARCHAR}, #{Audit.terminal,jdbcType=VARCHAR},
               #{Audit.res,jdbcType=VARCHAR})
           </foreach>
       </insert>
      
   

   友情提示：使用IDEA不能再xml里面的Insert、Select等Sql语句里面注释，注释会报如下错误

   Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'Audit' not found. Available parameters are [audits, param1]] with root cause
   

   

   这个问题找了各种方法都没有解决，最后把注释的部分删除就好了。

2. mapper层

   @Mapper
   public interface AuditInfoMapper extends BaseMapper<Audit> {
   
       int insertAudit(@Param("audits") List<Audit> audits);
   
   }
   

3. service

   # service 接口
   public interface AuditService extends IService<Audit> {
       int insertAudit(List<Audit>  auditInfo);
   
   }
   
   # serviceimpl 实现
       
       @Service
   @Slf4j
   public class AuditServiceImpl extends ServiceImpl<AuditInfoMapper, Audit> implements AuditService {
       @Autowired
       AuditInfoMapper auditInfoMapper;
   
       @Override
       public int insertAudit(List<Audit> auditInfo) {
           auditInfoMapper.insertAudit(auditInfo);
           log.info("insert条数为 ==> {}", auditInfo.size());
           return auditInfo.size();
       }
   }
   
   

4. controller层

   @RestController
   public class ClickHouseController {
        @Autowired
       AuditService auditService;
       
        @PostMapping("/insertAudit")
       public void insertAudit(@RequestBody List<Audit>  auditInfo){
           List<Audit> list = new ArrayList<>();
           // 待添加（用户）数据   测试
           for (int i = 0; i < 10000; i++) {
               Audit audit = new Audit();
               audit.setInsertDate("2022-08-03 17:15:47");
               audit.setUserType("USER_START");
               audit.setPid("4154545");
               list.add(audit);
           }
           auditService.insertAudit(auditInfo);
       }
       
   

5. entity 实体类 省略 根据自己的业务来创建

6. application.yml 配置文件

   spring:
     datasource:
       type: com.alibaba.druid.pool.DruidDataSource
       click:
         driverClassName: ru.yandex.clickhouse.ClickHouseDriver
         url: jdbc:clickhouse://127.0.0.1:8123/xxx
         initialSize: 10
         maxActive: 100
         minIdle: 10
         maxWait: 6000
         username: xxx
         password: xxxx
         
        
   mybatis-plus:
     mapper-locations: classpath*:mapper/*.xml
     configuration:
       log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
       map-underscore-to-camel-case: true
       cache-enabled: true
       lazy-loading-enabled: true
       multiple-result-sets-enabled: true
       use-generated-keys: true
       default-statement-timeout: 60
       default-fetch-size: 100
       call-setters-on-nulls: true
     type-aliases-package: xx.xx.xxx    
         
   

项目大致如上。