java 批量添加、批量更新 操作数据库

（1）三种插入操作的方法

1.1 利用for循环的批量插入

示例xml

    "insertUser">
        insert into test_user (u_name,create_date) value (#{userName},SYSDATE())
    

示例代码：

for (int i = 1; i <= num; i++) {
    User user = new User();
    user.setUserName("a" + i);
    user.setCreateDate(new Date());
    userDao.insertUser(user);
}

1.2 采用jdbc

示例代码：

        Connection conn;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
            conn.setAutoCommit(false);
            String sql = "insert into test_user (u_name,create_date) value (?,SYSDATE())";
            PreparedStatement prest = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            conn.setAutoCommit(false);

            for (int i = 1; i <= 100; i++) {
                prest.setString(1, "a" + i);
                prest.addBatch();
            }
            prest.executeBatch();
            conn.commit();


            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

1.3 采用mybatis的批量插入方法

其实也是根据一个list 拼接成一个sql 
示例xml

    "batchInsertList">
        insert into test_user(u_name,create_date)
        values
        <foreach item="item" index="index" collection="userList" separator=",">
            (#{item.userName},SYSDATE())
        foreach>
    

示例代码

        List userList = new ArrayList();

        for (int i = 1; i <= num; i++) {
            User user = new User();
            user.setUserName("a" + i);
            user.setCreateDate(new Date());
            userList.add(user);
        }
        userDao.batchInsertList(userList);

（2）三种批量更新的方法

case when 语法】 
示例xml

    <update id="batchUpdateList">
        update test_user
        <trim prefix="set" suffixOverrides=",">

            <trim prefix="u_name =case" suffix="end,">
                <foreach item="item" collection="userList">
                    when test_user.u_name = (#{item.userName})
                    then #{item.updateUserName}
                foreach>
            trim>

        trim>
        where
        <foreach item="item" collection="userList" separator="or">
            (test_user.u_name = (#{item.userName}))
        foreach>

    update>

示例代码

        for (int i = 1; i <= num; i++) {
            User user = new User();
            user.setUserName("a" + i);
            user.setUpdateUserName("b" + i);
            userList.add(user);
        }
        userDao.batchUpdateList(userList);