mysql之批量修改数据

1. 对于某个字段，批量修改数据的方式

mysql> select * from emp;
+----+----------+-----+------+-------+----------+
| id | name     | age | sex  | depId | hiredate |
+----+----------+-----+------+-------+----------+
|  1 | king     |  24 | 保密 |     1 | NULL     |
|  2 | queue    |  25 | 保密 |     2 | NULL     |
|  3 | mooc     |  26 | 保密 |     1 | NULL     |
|  4 | lily     |  27 | 保密 |     1 | NULL     |
|  5 | rose     |  28 | 保密 |     3 | NULL     |
|  6 | john     |  29 | 保密 |     3 | NULL     |
|  7 | 测试用户 |  39 | 保密 |     6 | NULL     |
+----+----------+-----+------+-------+----------+
7 rows in set (0.01 sec)

如上表，我想要给user表中的数据最后一个hiredate字段都添加上数据，逐条更新数据可以，但是在数据量很大时，修改比较麻烦。

mysql> update emp set hiredate = '1992-03-04' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set hiredate = '1988-02-18' where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

用case -end语句 进行批量修改

mysql> update emp set hiredate= case id
    -> when 3 then '1996-02-01'
    -> when 4 then '2001-08-21'
    -> when 5 then '2003-01-01'
    -> when 6 then '1996-07-28'
    -> when 7 then '1999-12-30'
    -> end
    -> where id IN (3,4,5,6,7);
Query OK, 5 rows affected (0.10 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from emp;
+----+----------+-----+------+-------+------------+
| id | name     | age | sex  | depId | hiredate   |
+----+----------+-----+------+-------+------------+
|  1 | king     |  24 | 保密 |     1 | 1992-03-04 |
|  2 | queue    |  25 | 保密 |     2 | 1988-02-18 |
|  3 | mooc     |  26 | 保密 |     1 | 1996-02-01 |
|  4 | lily     |  27 | 保密 |     1 | 2001-08-21 |
|  5 | rose     |  28 | 保密 |     3 | 2003-01-01 |
|  6 | john     |  29 | 保密 |     3 | 1996-07-28 |
|  7 | 测试用户 |  39 | 保密 |     6 | 1999-12-30 |
+----+----------+-----+------+-------+------------+
7 rows in set (0.01 sec)

2. jdbc中批量插入数据的方式

2.1 jdbc中标准插入多条数据的方式

@Test
    /**
     *
     *标准方式插入若干数据
     */
    public void demo1(){
        Connection con = null;
        PreparedStatement pstmt = null;
        try{
            long startTime = new java.util.Date().getTime();
            //获得连接
            con = JDBCUtils.getConnection();
            con.setAutoCommit(false);
            //编写sql
            String sql = "insert user values(null, ?, ?, ?, ?, ?)";
            for(int i=100; i < 150; i++){
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, "stu" + i);
                pstmt.setInt(2,  18 + i);
                pstmt.setString(3, "stu" + i + "@qq.com");
                pstmt.setString(4, "this is stu" + i);
                pstmt.setDate(5, Date.valueOf("2000-09-12"));
                pstmt.executeUpdate();
            }
            con.commit();
            long endTime = new java.util.Date().getTime();
            System.out.println(endTime - startTime);

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.releaseResources(pstmt, con);
        }
    }

2.2 jdbc中批量插入多条数据的方式

@Test
    /**
     *
     *jdbc的批处理操作
     */
    public void demo2(){
        Connection con = null;
        PreparedStatement pstmt = null;
        try{
            long startTime = new java.util.Date().getTime();
            //获得连接
            con = JDBCUtils.getConnection();
            con.setAutoCommit(false);
            //编写sql
            String sql = "insert user values(null, ?, ?, ?, ?, ?)";
            pstmt = con.prepareStatement(sql);
            for(int i=150; i < 200; i++){

                pstmt.setString(1, "stu" + i);
                pstmt.setInt(2,  18 + i);
                pstmt.setString(3, "stu" + i + "@qq.com");
                pstmt.setString(4, "this is stu" + i);
                pstmt.setDate(5, Date.valueOf("2000-09-12"));
                //pstmt.executeUpdate();
                pstmt.addBatch();// 将参数加入批处理任务
            }
            pstmt.executeBatch();//执行批处理任务
            con.commit();
            long endTime = new java.util.Date().getTime();
            System.out.println(endTime - startTime);

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.releaseResources(pstmt, con);
        }
    }

请查看JDBCUtils类