JDBC批处理读取指定Excel中数据到Mysql关系型数据库

  1 import java.io.FileInputStream;
  2 import java.io.IOException;
  3 import java.io.InputStream;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.ArrayList;
 10 import java.util.List;
 11 
 12 import jxl.Cell;
 13 import jxl.Workbook;
 14 
 15 /**
 16  * Excel模板中的数据分析导入到Mysql中.
 17  * @author CDV-DX7
 18  *
 19  */
 20 public class ExcelToMysql {
 21     public static void main(String[] args) {
 22         String excelUrl = "C:\\Users\\CDV-DX7\\Desktop\\节目信息输入数据.xls";
 23         excel2db(excelUrl);
 24     }
 25 
 26     public static void excel2db(String importPath) {
 27         List<Cell[]> rowList;
 28         rowList = jxlGetExcelRows(importPath);
 29         try {
 30             long start = System.currentTimeMillis();
 31             Class.forName("com.mysql.jdbc.Driver");
 32             Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/50million?rewriteBatchedStatements=true","root","root");
 33             connection.setAutoCommit(false);
 34             PreparedStatement cmd = connection.prepareStatement("insert into asset (assetname,director,screenwriter,actor,programtype,region,language,releasetime,duration,alias) "
 35                     + "values(?,?,?,?,?,?,?,?,?,?)");
 36             
 37             String assetname = null;
 38             String director = null;
 39             String screenwriter = null;
 40             String actor = null;
 41             String programtype = null;
 42             String region = null;
 43             String language = null;
 44             String releasetime = null;
 45             String duration = null;
 46             String alias = null;
 47             int totalCount = 0;
 48             for (int k=1;k<442478;k++){
 49                 //50万对应.........................redis是0号数据库
 50                 //100万对应循环次数8851---用时65680---redis是1号数据库
 51                 //500万对应循环次数44248---用时469947---redis是2号数据库
 52                 //1000万对应循环次数88496---用时1385016---redis是3号数据库
 53                 //5000万对应循环次数442478---用时C盘空间不够了....暂时不做数据了..
 54                 for (int m=1; m<rowList.size(); m++) {
 55                     Cell[] cells = rowList.get(m);
 56                     for(int i=0; i<cells.length-1; i++){
 57                         totalCount++;
 58                         String contents = cells[i].getContents(); 
 59                         switch(i){
 60                             case 0:
 61                                 assetname = contents;
 62                                 break;
 63                             case 1:
 64                                 director = contents;
 65                                 break;
 66                             case 2:
 67                                 screenwriter = contents;
 68                                 break;
 69                             case 3:
 70                                 actor = contents;
 71                                 break;
 72                             case 4:
 73                                 programtype = contents;
 74                                 break;
 75                             case 5:
 76                                 region = contents;
 77                                 break;
 78                             case 6:
 79                                 language = contents;
 80                                 break;
 81                             case 7:
 82                                 releasetime = contents;
 83                                 break;
 84                             case 8:
 85                                 duration = contents;
 86                                 break;
 87                             case 9:
 88                                 alias = contents;
 89                                 break;
 90 
 91                         }
 92                     }
 93                     cmd.setString(1,assetname);
 94                     cmd.setString(2,director);
 95                     cmd.setString(3,screenwriter);
 96                     cmd.setString(4,actor);
 97                     cmd.setString(5,programtype);
 98                     cmd.setString(6,region);
 99                     cmd.setString(7,language);
100                     cmd.setString(8,releasetime);
101                     cmd.setString(9,duration);
102                     cmd.setString(10,alias);
103                     
104                     cmd.addBatch();
105                     if(totalCount%10000==0){
106                         cmd.executeBatch();
107                     }
108                 }
109             }
110             cmd.executeBatch();
111             connection.commit();
112             long end = System.currentTimeMillis();//
113             System.out.println("批量插入需要时间:"+(end - start));
114             cmd.close();
115             connection.close();
116         } catch (Exception e) {
117             e.printStackTrace();
118         } 
119     }
120     
121     /**
122      * 得到指定Excel文件中(可以再指定标签)中的行....
123      * @param filename
124      * @return
125      */
126     private static List<Cell[]> jxlGetExcelRows(String filename) {
127         InputStream is = null;
128         jxl.Workbook rwb = null;
129         List<Cell[]> list = new ArrayList<Cell[]>();
130         try {
131             is = new FileInputStream(filename);
132             rwb = Workbook.getWorkbook(is);
133             // Sheet[] sheets = rwb.getSheets();
134             // int sheetLen = sheets.length;
135             jxl.Sheet rs = rwb.getSheet(0); // 默认先读取第一个工作表的数据
136             
137             //getRows() 获取总共多少行...getRow(n)获取第n行...
138             for(int i=0; i<rs.getRows(); i++ ){
139                 list.add(rs.getRow(i));
140             }
141         } catch (Exception ex) {
142             ex.printStackTrace();
143         } finally {
144             rwb.close();
145             try {
146                 is.close();
147             } catch (IOException e) {
148                 e.printStackTrace();
149             }
150         }
151         return list;
152     }
153 }