这个demo是有一个Excel中的数据,我需要读取其中的数据然后导入到关系型数据库中,但是为了向数据库中插入更多的数据,循环N次Excel中的结果.
关于JDBC的批处理还可以参考我总结的如下博文:
http://www.cnblogs.com/DreamDrive/p/5757693.html
此外用到了读取Excel文件的jxl.jar包
下载地址:http://files.cnblogs.com/files/DreamDrive/jxl.rar
附代码:
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 }
本文转自SummerChill博客园博客，原文链接：http://www.cnblogs.com/DreamDrive/p/5826997.html，如需转载请自行联系原作者
