提高插入大量数据时的运行效率

插入大量数据时 个人推荐先把sql语句拼接好 再执行一次插入操作 运行效率能提高很多 不过 好像最多是10w+ 实际操作我没试过 有试过的 补充一下吧

import java.sql.*;

public class inserttest {

    public static String classname = "com.mysql.jdbc.Driver";
    public static String url = "jdbc:mysql://localhost:3306/jdbctest";
    public static String username = "root";
    public static String password = "123456789";
    public static Connection con;
    public static Statement stmt;
    public static ResultSet rs;
    public static PreparedStatement pstmt;

    public static void connect() {

        // 建立连接
        try{
            Class.forName(classname );
            System.out.println("加载驱动成功!");
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            System.out.println("数据库连接成功!"); 
        }catch(SQLException e){
            System.out.println("数据库连接失败!");
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            System.out.println("加载驱动失败!");
            e.printStackTrace();
        }

    }

    public static void close() {
        try{
         if(rs!=null)
                rs.close();
         if(stmt!=null)
                stmt.close();
         if(con!=null)
                con.close();
        }catch(Exception e)
        {
         e.printStackTrace();
        }
       }

    //逐句插入
    public static void insert1(String sql) {
        long starttime = System.currentTimeMillis();
        try{
            for(int i=0 ; i<100; i++){
            stmt.executeUpdate(sql);
            }
            //System.out.println("数据插入成功!");
        }catch (Exception e) {
            //System.out.println("数据插入失败!");
            e.printStackTrace();
        }
        long endtime = System.currentTimeMillis();
        System.out.println("用时1："+(endtime-starttime));
    }
    //拼接sql后一次性插入
    public static void insert2(String sql) {
        long starttime = System.currentTimeMillis();
        String sql_t = "";
        try{
            for(int i=0 ; i<100; i++){
                if(i==0){
                    sql_t = sql_t + "('123',"+i+")";
                }else{
                    sql_t = sql_t + ",('123',"+i+")";
                }
            }
            sql = sql + sql_t;
            stmt.executeUpdate(sql);
            //System.out.println("数据插入成功!");
        }catch (Exception e) {
            //System.out.println("数据插入失败!");
            e.printStackTrace();
        }
        long endtime = System.currentTimeMillis();
        System.out.println("用时2："+(endtime-starttime));
    }

    public static void main(String[] args) {
        connect();
        String sql1 = "insert into test (id,name) values('123','xxx')";
        insert1(sql1);
        close();

        connect();
        String sql2 = "insert into test (id,name) values ";
        insert2(sql2);
        close();

    }
}

实验证明 拼接前后同样插入100条数据 拼接前用了2457ms 拼接后才44ms 效率大大提高了