这个demo是有一个Excel中的数据,我需要读取其中的数据然后导入到关系型数据库中,但是为了向数据库中插入更多的数据,循环N次Excel中的结果.
关于JDBC的批处理还可以参考我总结的如下博文:
/DreamDrive/p/5757693.html
此外用到了读取Excel文件的jxl.jar包
下载地址:/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 { 21public static void main(String[] args) { 22 String excelUrl = "C:\\Users\\CDV-DX7\\Desktop\\节目信息输入数据.xls"; 23 excel2db(excelUrl); 24} 25 26public 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++){ 57totalCount++; 58String contents = cells[i].getContents(); 59switch(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){106cmd.executeBatch();107 }108 }109 }110 cmd.executeBatch();111 mit();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}11/**122* 得到指定Excel文件中(可以再指定标签)中的行....123* @param filename124* @return125*/126private 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博客园博客，原文链接：/DreamDrive/p/5826997.html，如需转载请自行联系原作者
