springboot2.0+mybatis的批量插入mysql数据库

旧城等待， 2022-05-14 06:30 510阅读 0赞

我用的方式是：利用mysql特性，拼写insert sql

先写在mapper文件中加入下面的代码：

<insert id="batchInsert" parameterType="List">
        insert into TB_SystemInfo (systemID, systemName, createTime,createID,updateUserID, updateTime) values
        <foreach collection="list" item="SystemInfo" index="index" separator="," >
          (#{SystemInfo.systemID}, #{SystemInfo.systemName}, #{SystemInfo.createTime}, #{SystemInfo.createID}, #{SystemInfo.updateUserID}, #{SystemInfo.updateTime}, )
        </foreach>
      </insert>

然后在代码中的用法：

@Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    
    
    @Override
    public void updateSystemList() {
    	long t3 = System.currentTimeMillis();
    	System.out.println("-----------------------开始插入: " + System.currentTimeMillis());
    
    	SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//关闭session的自动提交;
    	//        SqlSession sqlSession = sqlSessionFactory.getObject().openSession(ExecutorType.BATCH);
    	try {
    						
    	  sqlSession.insert("com.lagou.mapper.SystemInfoMapper.batchInsert", systemInfoList);
    				sqlSession.commit();
    			} finally {
    				sqlSession.close();
    			}
    
    	long t4 = System.currentTimeMillis();
    	System.out.println("-----------------------结束: " + System.currentTimeMillis());
    	System.out.println("-----------------------用时: " + (t4 - t3));
    }

也可以这么用：

首先将mapper接口中加入批量方法：

int batchInsert(List<SystemInfo> systemInfoList);

然后直接用mapper调用：

@Autowired
    private SystemInfoMapper systemInfoMapper;
    
    @Override
    public void updateSystemList() {
        systemInfoMapper.batchInsert(systemInfoList);
    }

这两种方式我试了，效果是一样的。

参考文章：[https://blog.csdn.net/songjianyue12345/article/details/78774011][https_blog.csdn.net_songjianyue12345_article_details_78774011]

[https_blog.csdn.net_songjianyue12345_article_details_78774011]: https://blog.csdn.net/songjianyue12345/article/details/78774011