java批量添加、批量更新操作数据库（1）三种插⼊操作的⽅法
1.1 利⽤for循环的批量插⼊
⽰例xml
<insert id="insertUser">
insert into test_user (u_name,create_date) value (#{userName},SYSDATE())
</insert>
⽰例代码：
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("sql.jdbc.Driver");
conn = Connection("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();
}
connmit();
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
1.3 采⽤mybatis的批量插⼊⽅法
其实也是根据⼀个list 拼接成⼀个sql
⽰例xml
<insert id="batchInsertList">
insert into test_user(u_name,create_date)
values
<foreach item="item" index="index" collection="userList" separator=",">
(#{item.userName},SYSDATE())
</foreach>
</insert>
⽰例代码
List<User> userList = new ArrayList<User>();
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）三种批量更新的⽅法
2.1 利⽤for循环批量更新
⽰例xml
<update id="updateUser">
update test_user set test_user.u_name = (#{updateUserName}) where test_user.u_name = (#{userName})
</update>
⽰例代码
for (int i = 1; i <= num; i++) {
User user = new User();
user.setUserName("a" + i);
user.setUpdateUserName("b" + i);
userDao.updateUser(user);
}
2.2 jdbc 批量更新
⽰例代码
Connection conn;
try {
Class.forName("sql.jdbc.Driver");
conn = Connection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
conn.setAutoCommit(false);
// 保存当前⾃动提交模式
boolean autoCommit = AutoCommit();
// 关闭⾃动提交
conn.setAutoCommit(false);
Statement stmt =ateStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);            for (int i = 1; i <= num; i++) {
stmt.addBatch("update test_user set test_user.u_name = ('d"+i+"') where test_user.u_name = ('c"+i+"')");
}
connmit();
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
2.3 mybatis 批量更新
其实是利⽤了mysql的批量更新的语法
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();
批量更新sql语句
user.setUserName("a" + i);
user.setUpdateUserName("b" + i);
userList.add(user);
}
userDao.batchUpdateList(userList);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。