JDBC中的SQL批处理
1899942,新疆-乌鲁木齐1899944,新疆-哈密
1899946,新疆-吐鲁番
1899948,新疆-塔城
1899950,新疆-克拉玛依
1899952,新疆-克拉玛依
1899954,新疆-昌吉
1899956,新疆-昌吉
1899958,新疆-伊犁
1899960,新疆-库尔勒
1899962,新疆-库尔勒
1899964,新疆-喀什
1899966,新疆-阿克苏
1899968,新疆-哈密
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
有几万条这样的数据需要插入数据库
public class Test2 {public static void main(String[] args) {long start = System.currentTimeMillis();String sql = "insert into mobile_place(number,place) values(?,?)";int count=0;PreparedStatement pstmt = null;Connection conn = JDBCUtil.getConnection();try {pstmt = conn.prepareStatement(sql);InputStreamReader is = new InputStreamReader(new FileInputStream(new File("D:/CC.txt")),"utf-8");BufferedReader br = new BufferedReader(is);//设置数据手动提交，自己管理事务conn.setAutoCommit(false);String s1 = null;String s2 = null;while(br.readLine() != null){count++;//每读取一行数据，计数器+1String str = br.readLine().toString().trim();//读取一行数据s1 = str.substring(0, str.indexOf(","));//取逗号以前的一段s2 = str.substring(str.indexOf(",")+1,str.length());//取逗号之后的一段pstmt.setString(1, s1);pstmt.setString(2, s2);pstmt.addBatch();//用PreparedStatement的批量处理if(count%1000==0){//当增加了1000个批处理的时候再提交pstmt.executeBatch();//执行批处理conn.commit();//提交conn.close();//关闭数据库conn = JDBCUtil.getConnection();//重新获取一次连接conn.setAutoCommit(false);pstmt = conn.prepareStatement(sql);}System.out.println("已插入"+count+"条数据");}if(count%1000!=0){//while循环外的判断，为了防止上面判断后剩下最后少于500条的数据没有被插入到数据库pstmt.executeBatch();conn.commit();}long end = System.currentTimeMillis();System.out.println("共花费时间:"+(end-start));} catch (Exception e) {e.printStackTrace();}finally{try {pstmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}}}//getConnection()为获取数据库连接 public static Connection getConnection(){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { conn = DriverManager.getConnection(url, userName, password); } catch (SQLException e) { e.printStackTrace(); } return conn; }
页:
[1]