Java不寫檔案，LOAD DATA LOCAL INFILE大批量匯入資料到MySQL的實現

壹頁書發表於2015-11-15
JavaMySql
大家都知道當插入大批量資料MySQL的時候，
MySQL使用load data local infile 從檔案中匯入資料比insert語句要快，MySQL文件上說要快20倍左右。
但是這個方法有個缺點，就是匯入資料之前，必須要有檔案，也就是說從檔案中匯入。這樣就需要去寫檔案，
以及檔案刪除等維護。某些情況下，比如資料來源併發的話，還會出現寫檔案併發問題，很難處理。
那麼有沒有什麼辦法，可以達到同樣的效率，直接從記憶體（IO流中）中匯入資料，而不需要寫檔案呢？
前段時間，去MySQL社群的時候發現了這樣一個方法：setLocalInfileInputStream（），此方法位於com.mysql.jdbc.PreparedStatement 類中

下面是具體實現：

通過使用 MySQL JDBC 的setLocalInfileInputStream 方法實現從java InputStream中load data local infile 到MySQL資料庫中。


準備測試表 
SQL如下：


[sql] view plaincopy
  1. use test;  
  2. CREATE TABLE `test` (  
  3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  4.   `a` int(11) NOT NULL,  
  5.   `b` bigint(20) unsigned NOT NULL,  
  6.   `c` bigint(20) unsigned NOT NULL,  
  7.   `d` int(10) unsigned NOT NULL,  
  8.   `e` int(10) unsigned NOT NULL,  
  9.   `f` int(10) unsigned NOT NULL,  
  10.   PRIMARY KEY (`id`),  
  11.   KEY `a_b` (`a`,`b`)  
  12. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  

Java程式碼如下：

[java] view plaincopy
  1. package com.seven.dbTools.DBTools;  
  2.   
  3. import java.io.ByteArrayInputStream;  
  4. import java.io.InputStream;  
  5. import java.sql.Connection;  
  6. import java.sql.PreparedStatement;  
  7. import java.sql.SQLException;  
  8. import org.springframework.jdbc.core.JdbcTemplate;  
  9.   
  10. import javax.sql.DataSource;  
  11.   
  12. import org.apache.log4j.Logger;  
  13.   
  14. /** 
  15.  * @author seven 
  16.  * @since 07.03.2013 
  17.  */  
  18. public class BulkLoadData2MySQL {  
  19.   
  20.     private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);  
  21.     private JdbcTemplate jdbcTemplate;  
  22.     private Connection conn = null;  
  23.   
  24.     public void setDataSource(DataSource dataSource) {  
  25.         this.jdbcTemplate = new JdbcTemplate(dataSource);  
  26.     }  
  27.   
  28.     public static InputStream getTestDataInputStream() {  
  29.         StringBuilder builder = new StringBuilder();  
  30.         for (int i = 1; i <= 10; i++) {  
  31.             for (int j = 0; j <= 10000; j++) {  
  32.   
  33.                 builder.append(4);  
  34.                 builder.append("\t");  
  35.                 builder.append(4 + 1);  
  36.                 builder.append("\t");  
  37.                 builder.append(4 + 2);  
  38.                 builder.append("\t");  
  39.                 builder.append(4 + 3);  
  40.                 builder.append("\t");  
  41.                 builder.append(4 + 4);  
  42.                 builder.append("\t");  
  43.                 builder.append(4 + 5);  
  44.                 builder.append("\n");  
  45.             }  
  46.         }  
  47.         byte[] bytes = builder.toString().getBytes();  
  48.         InputStream is = new ByteArrayInputStream(bytes);  
  49.         return is;  
  50.     }  
  51.   
  52.     /** 
  53.      *  
  54.      * load bulk data from InputStream to MySQL 
  55.      */  
  56.     public int bulkLoadFromInputStream(String loadDataSql,  
  57.             InputStream dataStream) throws SQLException {  
  58.         if(dataStream==null){  
  59.             logger.info("InputStream is null ,No data is imported");  
  60.             return 0;  
  61.         }  
  62.         conn = jdbcTemplate.getDataSource().getConnection();  
  63.         PreparedStatement statement = conn.prepareStatement(loadDataSql);  
  64.   
  65.         int result = 0;  
  66.   
  67.         if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {  
  68.   
  69.             com.mysql.jdbc.PreparedStatement mysqlStatement = statement  
  70.                     .unwrap(com.mysql.jdbc.PreparedStatement.class);  
  71.   
  72.             mysqlStatement.setLocalInfileInputStream(dataStream);  
  73.             result = mysqlStatement.executeUpdate();  
  74.         }  
  75.         return result;  
  76.     }  
  77.   
  78.     public static void main(String[] args) {  
  79.         String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";  
  80.         InputStream dataStream = getTestDataInputStream();  
  81.         BulkLoadData2MySQL dao = new BulkLoadData2MySQL();  
  82.         try {  
  83.             long beginTime=System.currentTimeMillis();  
  84.             int rows=dao.bulkLoadFromInputStream(testSql, dataStream);  
  85.             long endTime=System.currentTimeMillis();  
  86.             logger.info("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!");  
  87.         } catch (SQLException e) {  
  88.             e.printStackTrace();  
  89.         }  
  90.         System.exit(1);  
  91.     }  
  92.   
  93. }  

提示：
 String testSql ="LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";

        使用setLocalInfileInputStream方法，會直接忽略掉檔名稱，而直接將IO流匯入到資料庫中。
 
參考：
http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf
http://jeffrick.com/2010/03/23/bulk-insert-into-a-mysql-database/


原創文章，歡迎轉載，轉載請註明出處：http://write.blog.csdn.net/postedit/9237495

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1839492/，如需轉載，請註明出處，否則將追究法律責任。

相關文章