JDBC mysql批量插入

最新推荐文章于 2023-03-11 13:04:32 发布

jakie_wang_163

最新推荐文章于 2023-03-11 13:04:32 发布

阅读量160

点赞数

分类专栏： java开发文章标签： mysql jdbc java

本文链接：https://blog.csdn.net/gotowqj/article/details/84624466

版权

java开发专栏收录该内容

19 篇文章 0 订阅

订阅专栏

使用JDBC连接MySQL数据库进行数据插入的时候，特别是大批量数据连续插入（10W+），如何提高效率呢？

在JDBC编程接口中Statement 有两个方法特别值得注意：

1	`void addBatch() throws SQLException`

Adds a set of parameters to this PreparedStatement object's batch of commands.

1	`int` `[] executeBatch() throws SQLException`

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.
通过使用addBatch（）和executeBatch（）这一对方法可以实现批量处理数据。
不过值得注意的是，首先需要在数据库链接中设置手动提交，connection.setAutoCommit(false)，然后在执行Statement之后执行connection.commit()。

 
         package cyl.demo.ipsearcher; 
        
         import java.io.BufferedReader; 
        
         import java.io.FileInputStream; 
        
         import java.io.IOException; 
        
         import java.io.InputStreamReader; 
        
         import java.sql. 
         Connection 
         ; 
        
         import java.sql.DriverManager; 
        
         import java.sql.PreparedStatement; 
        
         import java.sql.SQLException; 
        
         public 
         class DbStoreHelper { 
        
         private String insert_sql; 
        
         private String charset; 
        
         private boolean debug; 
        
         private String connectStr; 
        
         private String username; 
        
         private String  
         password 
         ; 
        
         public 
         DbStoreHelper() { 
        
         connectStr =  
         "jdbc:mysql://localhost:3306/db_ip" 
         ; 
        
         // connectStr +=  
         "?useServerPrepStmts=false&rewriteBatchedStatements=true" 
         ; 
        
         insert_sql =  
         "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)" 
         ; 
        
         charset =  
         "gbk" 
         ; 
        
         debug =  
         true 
         ; 
        
         username =  
         "root" 
         ; 
        
         password 
         =  
         "***" 
         ; 
        
         } 
        
         public 
         void storeToDb(String srcFile) throws IOException { 
        
         BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream(srcFile), charset)); 
        
         try { 
        
         doStore(bfr); 
        
         } catch (Exception e) { 
        
         e.printStackTrace(); 
        
         } finally { 
        
         bfr. 
         close 
         (); 
        
         } 
        
         } 
        
         private void doStore(BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException { 
        
         Class.forName( 
         "com.mysql.jdbc.Driver" 
         ); 
        
         Connection 
         conn = DriverManager.getConnection(connectStr, username, 
         password 
         ); 
        
         conn.setAutoCommit( 
         false 
         ); // 设置手动提交 
        
         int 
         count 
         = 0; 
        
         PreparedStatement psts = conn.prepareStatement(insert_sql); 
        
         String line =  
         null 
         ; 
        
         while ( 
         null 
         != (line = bfr.readLine())) { 
        
         String[] infos = line.split( 
         ";" 
         ); 
        
         if (infos.length < 5)    
         continue 
         ; 
        
         if (debug) { 
        
         System. 
         out 
         .println(line); 
        
         } 
        
         psts.setLong(1, Long.valueOf(infos[0])); 
        
         psts.setLong(2, Long.valueOf(infos[1])); 
        
         psts.setString(3, infos[2]); 
        
         psts.setString(4, infos[3]); 
        
         psts.setString(5, infos[4]); 
        
         psts.addBatch();          // 加入批量处理 
        
         count 
         ++;             
        
         } 
        
         psts.executeBatch(); // 执行批量处理 
        
         conn. 
         commit 
         ();  // 提交 
        
         System. 
         out 
         .println( 
         "All down : " 
         +  
         count 
         ); 
        
         conn. 
         close 
         (); 
        
         } 
        
         }