Java批量操作数据库的方式

1.MyBatis的标签

2.MyBatis的Batch.type

3.jdbc的preparedStatement

4.StringBuffer拼接sql

MyBatis的标签

     @Insert("<script>" +
            "insert into t_user (id, name)
            "        <foreach collection=\"list\" item=\"user\" separator=\",\">\n" +
            "            (#{user.id},#{user.name})\n" +
            "        </foreach>" +
            "</script>")

每次最大支持1000条

MyBatis的Batch.type

通用的批量插入

    public static <M, T> void batchInsert(List<T> list, Class<M> clazz, BiConsumer<M, T> biConsumer, String sqlSessionFactoryName) {
        if (list == null || list.size() == 0) {
            log.info("BatchInsertUtil batchInsert list data is null!");
            return;
        }
        SqlSessionFactory sqlSessionFactory = SpringUtils.getBean(sqlSessionFactoryName, SqlSessionFactory.class);
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            M mapper = session.getMapper(clazz);
            list.forEach(a -> {
                biConsumer.accept(mapper, a);
            });
            session.commit();
            session.clearCache();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("BatchInsertUtil batchInsert is exception！clazz={}", clazz.getName(), e);
            session.rollback();
        } finally {
            session.close();
        }
    }

SpringUtils

通过反射机制获取Spring中的Bean

@Slf4j
@Component
public class SpringUtils implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if (SpringUtils.applicationContext == null) {
            SpringUtils.applicationContext = applicationContext;
        }
    }

    //获取applicationContext
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    //通过name获取 Bean.
    public static Object getBean(String name) {
        return getApplicationContext().getBean(name);
    }

    //通过class获取Bean.
    public static <T> T getBean(Class<T> clazz) {
        return getApplicationContext().getBean(clazz);
    }

    //通过name,以及Clazz返回指定的Bean
    public static <T> T getBean(String name, Class<T> clazz) {
        return getApplicationContext().getBean(name, clazz);
    }
}

每次最大支持1000条

jdbc的PreparedStatement

这里区分一下Statement和PreparedStatement

Statement不会预编译sql，在循环中执行多次sql操作，效率低下

PreparedStatement会预编译sql，connection.prepareStatement(sql); sql在循环外以字符串的形式传入，效率高

网上有很多增删改查的介绍，就不一一列举了，也可去文档查看PreparedStatement提供的方法

StringBuffer拼接sql

<select id="selectNameList" resultType="java.lang.String" resultMap="map">
    select guid, data_name from t_user where 1=1
    <if test="guidList != null and guidList !=''">
        and guid in ${guidList}
    </if>
</select>

StringBuffer拼接

 private StringBuffer toStringBuf(List<String> idList) {
        StringBuffer sb = new StringBuffer();
        sb.append("(");
        for (String guid : idList) {
            sb.append("'").append(guid).append("'").append(",");
        }
        sb.deleteCharAt(sb.toString().length() - 1);
        sb.append(")");
        return sb;
    }

UserService.selectUser(idList.toString);

同样的in查询时，每次最大支持1000条

故提供方法，将几十万的list，分为每次1000条进行操作

      int listGroupSize = idList.size() % 1000 == 0 ? idList.size() / 1000 : (idList.size() / 1000 + 1);
        System.out.println("1000个元素为一组截取数量 " + listGroupSize);
        List<String> listSub = null;
        List<UserName> userNameList = new ArrayList<>();
        for (int i = 0; i < listGroupSize; i++) {
            if ((i + 1) * 1000 <= idList.size()) {
                listSub = idList.subList(i * 1000, (i + 1) * 1000);
                StringBuffer sb = toStringBuf(listSub);
                List<UserName> names = updateService.selectUser(sb.toString());
                nameList.addAll(names);
            } else {
                listSub = idList.subList(i * 1000, guidList.size());
                StringBuffer sb = toStringBuf(listSub);
                List<UserName> names = updateService.selectUser(sb.toString());
                nameList.addAll(names);
            }
        }