JDBC从入门到放弃（三）

本节，我们为大家介绍下JDBC的批处理问题。

向student表中插入一万条数据

package com.iotek.advance;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.iotek.common.ConnectionFactory;
import com.iotek.common.UtilsJdbc;

public class BatchTest {
    public static void main(String[] args) {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        
        try {
            connection = ConnectionFactory.getConnection();
            connection.setAutoCommit(false);
            String insertSQL = "INSERT INTO student VALUES (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(insertSQL);
            
            long time1 = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                preparedStatement.setInt(1, i+1);
                preparedStatement.setString(2, "zhangsan" + i);
                preparedStatement.setString(3, "beijing" + i);
                preparedStatement.setString(4, "male");
                preparedStatement.setInt(5, 20);
                preparedStatement.executeUpdate();
                count++;
            }
            connection.commit();
            
            long time2 = System.currentTimeMillis();
            System.out.println("insert " + count + " rows takes " + (time2 - time1) + " ms");
            
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            UtilsJdbc.close(connection, preparedStatement, null);
        }
    }
}

运行结果为：2556 ms

采用批处理方式插入数据，关键方法是addBatch()和executeBatch()

package com.iotek.advance;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.iotek.common.ConnectionFactory;
import com.iotek.common.UtilsJdbc;

public class BatchTest {
    public static void main(String[] args) {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        
        try {
            connection = ConnectionFactory.getConnection();
            connection.setAutoCommit(false);
            String insertSQL = "INSERT INTO student VALUES (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(insertSQL);
            
            long time1 = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                preparedStatement.setInt(1, i+1);
                preparedStatement.setString(2, "zhangsan" + i);
                preparedStatement.setString(3, "beijing" + i);
                preparedStatement.setString(4, "male");
                preparedStatement.setInt(5, 20);
                preparedStatement.addBatch();
                count++;
            }
            
            preparedStatement.executeBatch();
            connection.commit();
            
            long time2 = System.currentTimeMillis();
            System.out.println("insert " + count + " rows takes " + (time2 - time1) + " ms");
            
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            UtilsJdbc.close(connection, preparedStatement, null);
        }
    }
}

运行结果为：166 ms

由此，我们可以看到批处理仅仅占用了之前写法的零头毫秒，大大提升了效率，所以在大量处理数据库数据的时候，最后使用批处理，将参数或者是SQL语句，放到statement维护的缓存中，然后统一，在一次与数据库的连接中，统一提交，这样可以大大提升性能。