本文共 6011 字，大约阅读时间需要 20 分钟。

方式一：使用Statement实现

Connection conn = DBCUtils.getConnection();  Statement st = conn.createStatement(); for(int i = 1;i <= 20000;i++){
    		String sql = "insert into goods(name)values('name_" + i + "')";  		st.execute(sql);}

方式二：使用PreparedStatement

/**     * 批量插入的方式二：使用PreparedStatement     */    @Test    public void testInsert1(){
           Connection conn = null;        PreparedStatement ps = null;        try {
               conn = DBUtil.getConnection("test");            String sql = "insert into goods(name) values (?)";            ps = conn.prepareStatement(sql);            long start = System.currentTimeMillis();            for (int i = 1; i <= 500; i++) {
                   ps.setString(1,"name" + i);                ps.execute();            }            long end = System.currentTimeMillis();            System.out.println("一共花费的时间为：" + (end - start));//17901        } catch (Exception e) {
               e.printStackTrace();        } finally {
               DBUtil.close(conn,ps,null);        }    }

方式三：攒SQL：addBatch()

1.addBatch()、executeBatch()、clearBatch()

/**     *批量插入的方式三：     * 1.addBatch()、executeBatch()、clearBatch()     * 2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。     * 		 ?rewriteBatchedStatements=true 写在配置文件的url后面     * 3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar     */    @Test    public void testInsert2(){
           Connection conn = null;        PreparedStatement ps = null;        try {
               conn = DBUtil.getConnection("");            String sql = "insert into goods(name) values (?)";            ps = conn.prepareStatement(sql);            long start = System.currentTimeMillis();            for (int i = 1; i <= 500; i++) {
                   ps.setString(1,"name" + i);                //1."攒"sql                ps.addBatch();                if(i % 50 == 0){
                       //2.执行batch                    ps.executeBatch();                    //3.清空batch                    ps.clearBatch();                }            }            long end = System.currentTimeMillis();            System.out.println("一共花费的时间为：" + (end - start));//378        } catch (Exception e) {
               e.printStackTrace();        } finally {
               DBUtil.close(conn,ps,null);        }

方式四：设置连接不允许自动提交数据（最佳）

/**     * 批量插入的方式四：设置连接不允许自动提交数据     */    @Test    public void testInsert3(){
           Connection conn = null;        PreparedStatement ps = null;        try {
               conn = DBUtil.getConnection("");            String sql = "insert into goods(name) values (?)";            ps = conn.prepareStatement(sql);            //设置不允许自动提交数据            conn.setAutoCommit(false);            long start = System.currentTimeMillis();            for (int i = 1; i <= 500; i++) {
                   ps.setString(1,"name" + i);                //1."攒"sql                ps.addBatch();                if(i % 50 == 0){
                       //2.执行batch                    ps.executeBatch();                    //3.清空batch                    ps.clearBatch();                }            }            //提交数据            conn.commit();            long end = System.currentTimeMillis();            System.out.println("一共花费的时间为：" + (end - start));//138        } catch (Exception e) {
               e.printStackTrace();        } finally {
               DBUtil.close(conn,ps,null);        }    }

DBUtil：

package loey.DBUtil;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;/** * JDBC工具类，简化JDBC编程 * */public class DBUtil {
       /**     * 工具类中的构造方法是私有的     * 因为工具类中的方法都是静态的，直接通过类名去调即可。     */    private DBUtil() {
       }    private static Properties getProperties(){
           //ResourceBundle bundle = ResourceBundle.getBundle("jdbc");        //String driver = bundle.getString("driver");        //String url = bundle.getString("url");        //String user = bundle.getString("user");        //String password = bundle.getString("password");        //String sql = bundle.getString("sql");        // 1.读取配置文件中的4个基本信息        Properties pros = new Properties();        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");        try {
               pros.load(is);        } catch (IOException e) {
               e.printStackTrace();        }        return pros;    }    /**     * 静态代码块，类加载的时候执行     * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用     */    static{
           Properties pros = getProperties();        String driver = pros.getProperty("driver");        try {
               Class.forName(driver);        } catch (ClassNotFoundException e) {
               e.printStackTrace();        }    }    /**     * @return 获取连接     * @throws SQLException     */    public static Connection getConnection(String database) throws Exception {
           Properties pros = getProperties();        String url = pros.getProperty("url");        String user = pros.getProperty("user");        String password = pros.getProperty("password");        Connection conn = DriverManager.getConnection(url + database, user, password);        return conn;    }    public static void close(Connection conn, Statement ps, ResultSet rs) {
           if (rs != null) {
               try {
                   rs.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }        if (ps != null) {
               try {
                   ps.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }        if (conn != null) {
               try {
                   conn.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }    }}

配置文件：

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=trueuser=rootpassword=1127

转载地址：http://uauki.baihongyu.com/