excel导入mysql bat_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-DX718 *19 */

20 public class ExcelToMysql {21 public static voidmain(String[] args) {22 String excelUrl = "C:\\Users\\CDV-DX7\\Desktop\\节目信息输入数据.xls";23 excel2db(excelUrl);24 }25

26 public static voidexcel2db(String importPath) {27 ListrowList;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

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 filename124 * @return125 */

126 private static ListjxlGetExcelRows(String filename) {127 InputStream is = null;128 jxl.Workbook rwb = null;129 List list = new ArrayList();130 try{131 is = newFileInputStream(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