SpringBoot+MybatisPlus实现批量添加的两种方式

第一种：

　　因为Mysql数据每次发送sql语句的长度不能超过1M,所以，每次发送insert语句以固定长度发送：

　　将sql语句在provider中，以固定长度装入List集合中，然后返回service层，调用mapp,发送sql语句

//provider类
public class sql {
	private List <String> sqlList = new ArrayList<>();

       private void makeUpSql(StringBuilder sql, StringBuilder paramSql2) {
		 sqlList.add(sql.append(paramSql2.substring(0, paramSql2.length()-1)).toString());	
	}
	
	@SuppressWarnings("unchecked")
	public List<String> batchInsert(Map<String, Object> map) throws  Exception {
		List<IBean> beans = (List<IBean>) map.get("list");
		int  length = Numbers.parseInt(map.get("length"));
		Class<?> bclz = beans.get(0).getClass();
		StringBuilder sql = batchInsertBeforeSql(beans);
		int sizeBefore = sql.toString().getBytes().length;
		
		StringBuilder paramSql2 = new StringBuilder();
			
		for (IBean bean : beans) {
			int sizeSql1 = paramSql2.toString().getBytes().length+sizeBefore;
			StringBuilder  paramSql2c = new StringBuilder(paramSql2.toString()); 
			paramSql2.append("(");
			for(Field f : bclz.getDeclaredFields()) {
				f.setAccessible(true);
				if (!Modifier.isStatic(f.getModifiers())) {// 非静态资源
					String fname = f.getName();				
					if("id".equals(fname) ) {
						continue;
					}
					String value = Strings.of(f.get(bean)).replaceAll("\"", "\\\\\"");
					value = value.replaceAll("\'", "\\\\\'");
					paramSql2.append("\"").append(value).append("\", ");
				}
			}
			paramSql2 = new StringBuilder(paramSql2.substring(0,        paramSql2.length()-2));
			paramSql2.append("),");
			if(sizeSql1<length && paramSql2.toString().getBytes().length+sizeBefore>=length) {
				makeUpSql(sql,paramSql2c);
				Map<String, Object> map1 = new HashMap<>();
				List<IBean> nextList = beans.subList(beans.indexOf(bean), beans.size());
				map1.put("list", nextList);
				map1.put("length", length);
				return batchInsert(map1);
			}
		}
		
		 sqlList.add(sql.append(paramSql2.substring(0, paramSql2.length()-1)).toString());
		 return sqlList;		
	}

         public StringBuilder batchInsertBeforeSql(List<IBean> beans) {
		StringBuilder sql = new StringBuilder("insert into ");		
		Class<?> bclz = beans.get(0).getClass();		
		sql.append(bclz.getSimpleName().toLowerCase()).append(" (");
		
		StringBuilder paramSql = new StringBuilder();
		for(Field f : bclz.getDeclaredFields()) {
			f.setAccessible(true);
			if (!Modifier.isStatic(f.getModifiers())) {// 非静态资源
				String fname = f.getName();				
				if("id".equals(fname) ) {
					continue;
				}
				paramSql.append(",").append(fname);
			}
		}
		return sql.append(paramSql.substring(1)).append(") values ");
	}

//service层
public void sendSql(List<String> listSql) {
		for (String sql : listSql) {
			Mapper.batchInsert3(sql);
			
		}
//mapper层
@Insert("${sql}")
int batchInsert3(@Param("sql") String sql);                        

 

 

第二种：

　　创建一个session，将每条insert语句单条调用mapper,使用session统一commit

//若指定有多个数据源，需要添加注解@Qualifier，指明使用哪个数据库的SqlSessionTemplate 
@Qualifier("kwSqlSessionTemplate")
@Autowired
private SqlSessionTemplate sqlSessionTemplate;

public void batchInsert(List<Issue> beanList) {
		SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
		IssueMapper mapper = session.getMapper(IssueMapper.class);
		try {
			for (int i = 0;i<beanList.size();i++) {
				mapper.insert(beanList.get(i));
				if(i%1000==999 || i==beanList.size()-1) {
					session.commit();
					session.clearCache();
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
			session.rollback();
		}finally {
			session.close();
		}
	}