SQL语句插入数据库2种方式

来源：互联网发布：matlab 遍历矩阵编辑：程序博客网时间：2024/05/29 16:27

数据库批量插入2种方法：
1.直接插入
sql = “insert into rpt_count_log values(‘pow_power=”+i+”’,’”+starttimes+”’,’”+endtimes+”’,’1’,’9’,’0x053502’,’1’)”;
sta.executeUpdate(sql);

一条一条的执行，这种方法效率非常低，不适合大数据量的插入。

2.批量插入
首先，最重要的一点：将数据库自动提交关闭con.setAutoCommit(false);

public class DBConn {private static Statement sta;public static String url = "10.9.88.61";private final static String dbURL = "jdbc:sqlserver://"+url+":1433;DatabaseName=POW";private final static String dbUSR = "sa";private final static String dbPSW = "Power123";private Connection con = null;  // 连接数据库public Statement connMsg(){     try     {         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();         con=DriverManager.getConnection(dbURL,dbUSR,dbPSW);         con.setAutoCommit(false);  //最重要的一点         sta = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);     }     catch(Exception e)     {              System.out.println(e);               }     return sta; }// 获得连接public Connection  getConnection(){    return this.con;}//关闭数据库public void closeMsg(){     try     {         sta.close();         con.close();                 } catch(Exception e)     {         System.out.println(e);               }        }

}

其次，设置批量插入的规模。例如此处是1620=810*2条记录再一次性提交。
PS:由于之前将自动提交关闭，在批量执行之后要提交commit；添加一条要addBatch()。

public void insert(int NESum, int sn, String start, String end) throws SQLException{    DBConn DBcon = new DBConn();    Statement sta = DBcon.connMsg();    Connection con = DBcon.getConnection();    final int batchSize = 810;    String sql;    Random random = new Random();    for(int e = 1; e <= NESum; e++)    {                       sql = "insert into rpt_mains_fail_counts values('pow_power="+ e + "','" + start + "','" + end + "','" + sn + "','0x002700','" + random.nextInt(10000) + "')";        sta.addBatch(sql);        sql = "insert into rpt_mains_fail_counts values('pow_power=" + e + "','" + start + "','" + end + "','" + sn + "','0x053200','" + random.nextInt(10) + "')";        sta.addBatch(sql);          if(e % batchSize == 0)         {            sta.executeBatch();            con.commit();        }    }           sta.executeBatch();    con.commit();    DBcon.connMsg(); }

}

0 0