读取大数据量excel并执行批量更新数据库操作
来源：互联网 发布：firefox知乎 编辑：程序博客网 时间：2024/05/21 20:52
import java.io.IOException; import java.io.InputStream; import java.io.PrintStream; import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.text.DateFormat;import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.xml.parsers.ParserConfigurationException; import javax.xml.parsers.SAXParser; import javax.xml.parsers.SAXParserFactory; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.openxml4j.opc.PackageAccess; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler;public class ImportMutation { /** * 执行程序入口方法 * readerExcel方法参数如下： *文件路径 ，sheet名称，列总数 * @author: wanght * 2017-6-28 上午10:19:14 * @param args * @throws Exception * void * */ public static void main(String[] args) throws Exception { //每次执行的条数，可自行修改 int total = 200; DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); List<String[]> list = ImportMutation.readerMutationExcel("F:\\import\\info.xlsx","info", 23); Date date=new Date(); String time=format.format(date); System.out.println("开始时间："+time); updateMutation(list,total); Date date1=new Date(); String time1=format.format(date1); System.out.println("结束时间："+time1); System.out.println("执行完毕，数据操作完成！"); } /** * 链接数据库 * @author: wanght * 2017-6-26 下午2:30:04 * void * */ public static Connection getCon(){ Connection connection = getConnection(); for (int i = 0; i < 5; i++) { if(connection == null){ try { Thread.sleep(1000); connection = getConnection(); if(i==4){ throw new Exception("获取链接失败"); } } catch (Exception e) { e.printStackTrace(); } }else{ break; } } return connection; } public static Connection getConnection(){ Connection conn = null; try { // 加载mysql驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 连接localhost上的mysql,并指定使用的数据库，用户名为***,密码为*** conn = DriverManager.getConnection("jdbc:mysql://数据库地址:3306/酷名称?characterEncoding=UTF-8","数据库用户名","数据库密码"); return conn; } catch (Exception e) { e.printStackTrace(); return null; } } /** 判断数据库是否支持批处理 */ public static boolean supportBatch(Connection con) { try { // 得到数据库的元数据 DatabaseMetaData md = con.getMetaData(); return md.supportsBatchUpdates(); } catch (SQLException e) { e.printStackTrace(); } return false; } /** 执行一批SQL语句 */ public static int[] goBatch(Connection con, String[] sqls) throws Exception { if (sqls == null) { return null; } Statement sm = null; try { sm = con.createStatement(); for (int i = 0; i < sqls.length; i++) { sm.addBatch(sqls[i]);// 将所有的SQL语句添加到Statement中 } // 一次执行多条SQL语句 return sm.executeBatch(); } catch (SQLException e) { e.printStackTrace(); } finally { sm.close(); } return null; } /** * 执行更新操作 * @author: wanght * 2017-6-27 上午11:17:28 * @param total * @param str * void * */ public static void updateMutation(List<String[]> list, int total){ Connection conn = getCon(); try{ if (!conn.isClosed()) { boolean supportBatch = supportBatch(conn); // 判断是否支持批处理 if(supportBatch){ update(conn,list,total); } } } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { //关闭连接对象 conn.close(); conn = null; } catch (Exception e) { e.printStackTrace(); } } } } public static void update(Connection conn,List<String[]> list, int total){ List<String> sqls = new ArrayList<String>(); String [] sqlArrayStrings = new String[total]; for (int i = 0; i < list.size(); i++) { if(i==0){ continue; } String[] strings = list.get(i); sqls.add("update mm_mutation set chrom_name = "+strings[1]+"" + ",start_location = "+strings[2]+",end_location = "+strings[3]+",chrombase_change = "+strings[4]+"" + ",rs_no = "+strings[5]+",mutation_area = "+strings[6]+",maf = "+strings[7]+"" + ",cn_rate = "+strings[8]+",cn_rate_south = "+strings[9]+",cn_rate_north = "+strings[10]+"" + ",deyidf_rate = "+strings[11]+",deyidf_rate_patient = "+strings[12]+",exac_easia = "+strings[13]+"" + ",exac_sasia = "+strings[14]+",exac_max = "+strings[15]+",esp_rate = "+strings[16]+"" + ",sift_forecast = "+strings[17]+",poly_hdiv_forecast = "+strings[18]+",poly_hvar_forecast = "+strings[19]+"" + ",mutationtaster_forecast = "+strings[20]+",mcap_forecast = "+strings[21]+",revel_forecast = "+strings[22]+"" + " where mutation_no =" +strings[0]); try { if(i%(total)==0){ int[] results = goBatch(conn, sqls.toArray(sqlArrayStrings));// 执行一批SQL语句 System.out.println("以下执行范围是第"+ ((i+2)-200)+ "行至第"+(i+1)+"行"); // 分析执行的结果 for (int k = 0; k < sqls.size(); k++) { if(results[k] > 0){ System.out.println("编号为:" + sqls.get(k).substring(sqls.get(k).indexOf("mutation_no")) + " 执行成功，影响了" + results[k] + "行数据!"); }else if (results[k] == 0) { //影响行数为0的数据说明数据库不存在该突变信息对应的信息 System.out.println("编号为:" + sqls.get(k).substring(sqls.get(k).indexOf("mutation_no"))+ "对应的数据在库中不存在！"); }else{ System.out.println("执行失败！"); } } sqls.clear(); }else if(i == list.size()-1){ int[] results = goBatch(conn, sqls.toArray(sqlArrayStrings));// 执行一批SQL语句 // 分析执行的结果 for (int k = 0; k < sqls.size(); k++) { if(results[k] > 0){ System.out.println("编号为:" + sqls.get(k).substring(sqls.get(k).indexOf("mutation_no")) + " 执行成功，影响了" + results[k] + "行数据!"); }else if (results[k] == 0) { //影响行数为0的数据说明数据库不存在该突变信息对应的信息 System.out.println("编号为:" + sqls.get(k).substring(sqls.get(k).indexOf("mutation_no"))+ "的数据在库中不存在！"); }else{ System.out.println("执行失败！"); } } sqls.clear(); } } catch (Exception e) { e.printStackTrace(); } } }==================以下代码转载为http://blog.csdn.net/lishengbo/article/details/40711769博客，非原创=========
/** * The type of the data value is indicated by an attribute on the cell. The * value is usually in a "v" element within the cell. */ enum xssfDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, } /** * 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api * <p/> * Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at * http://www.ecma-international.org/publications/standards/Ecma-376.htm * <p/> * A web-friendly version is http://openiso.org/Ecma/376/Part4 */ class MyXSSFSheetHandler extends DefaultHandler { /** * Table with styles */ private StylesTable stylesTable; /** * Table with unique strings */ private ReadOnlySharedStringsTable sharedStringsTable; /** * Destination for data */ private final PrintStream output; /** * Number of columns to read starting with leftmost */ private final int minColumnCount; // Set when V start element is seen private boolean vIsOpen; // Set when cell start element is seen; // used when cell close element is seen. private xssfDataType nextDataType; // Used to format numeric cell values. private short formatIndex; private String formatString; private final DataFormatter formatter; private int thisColumn = -1; // The last column printed to the output stream private int lastColumnNumber = -1; // Gathers characters as they are seen. private StringBuffer value; private String[] record; private List<String[]> rows = new ArrayList<String[]>(); private boolean isCellNull = false; /** * Accepts objects needed while parsing. * * @param styles * Table of styles * @param strings * Table of shared strings * @param cols * Minimum number of columns to show * @param target * Sink for output */ public MyXSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) { this.stylesTable = styles; this.sharedStringsTable = strings; this.minColumnCount = cols; this.output = target; this.value = new StringBuffer(); this.nextDataType = xssfDataType.NUMBER; this.formatter = new DataFormatter(); record = new String[this.minColumnCount]; rows.clear();// 每次读取都清空行集合 } /* * (non-Javadoc) * * @see * org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, * java.lang.String, java.lang.String, org.xml.sax.Attributes) */ public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { if ("inlineStr".equals(name) || "v".equals(name)) { vIsOpen = true; // Clear contents cache value.setLength(0); } // c => cell else if ("c".equals(name)) { // Get the cell reference String r = attributes.getValue("r"); int firstDigit = -1; for (int c = 0; c < r.length(); ++c) { if (Character.isDigit(r.charAt(c))) { firstDigit = c; break; } } thisColumn = nameToColumn(r.substring(0, firstDigit)); // Set up defaults. this.nextDataType = xssfDataType.NUMBER; this.formatIndex = -1; this.formatString = null; String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); if ("b".equals(cellType)) nextDataType = xssfDataType.BOOL; else if ("e".equals(cellType)) nextDataType = xssfDataType.ERROR; else if ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINESTR; else if ("s".equals(cellType)) nextDataType = xssfDataType.SSTINDEX; else if ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA; else if (cellStyleStr != null) { // It's a number, but almost certainly one // with a special style or format int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); this.formatIndex = style.getDataFormat(); this.formatString = style.getDataFormatString(); if (this.formatString == null) this.formatString = BuiltinFormats .getBuiltinFormat(this.formatIndex); } } } /* * (non-Javadoc) * * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, * java.lang.String, java.lang.String) */ public void endElement(String uri, String localName, String name) throws SAXException { String thisStr = null; // v => contents of a cell if ("v".equals(name)) { // Process the value contents as required. // Do now, as characters() may be called more than once switch (nextDataType) { case BOOL: char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break; case ERROR: thisStr = "\"ERROR:" + value.toString() + '"'; break; case FORMULA: thisStr = '"' + value.toString() + '"'; break; case INLINESTR: XSSFRichTextString rtsi = new XSSFRichTextString( value.toString()); thisStr = '"' + rtsi.toString() + '"'; break; case SSTINDEX: String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString( sharedStringsTable.getEntryAt(idx)); thisStr = '"' + rtss.toString() + '"'; } catch (NumberFormatException ex) { output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); } break; case NUMBER: String n = value.toString(); // 判断是否是日期格式 if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) { Double d = Double.parseDouble(n); Date date=HSSFDateUtil.getJavaDate(d); thisStr=formateDateToString(date); } else if (this.formatString != null) thisStr = formatter.formatRawCellContents( Double.parseDouble(n), this.formatIndex, this.formatString); else thisStr = n; break; default: thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; break; } // Output after we've seen the string contents // Emit commas for any fields that were missing on this row if (lastColumnNumber == -1) { lastColumnNumber = 0; } //判断单元格的值是否为空 if (thisStr == null || "".equals(isCellNull)) { isCellNull = true;// 设置单元格是否为空值 } record[thisColumn] = thisStr; // Update column if (thisColumn > -1) lastColumnNumber = thisColumn; } else if ("row".equals(name)) { // Print out any missing commas if needed if (minColumns > 0) { // Columns are 0 based if (lastColumnNumber == -1) { lastColumnNumber = 0; } if (isCellNull == false && record[0] != null && record[1] != null)// 判断是否空行 { rows.add(record.clone()); isCellNull = false; for (int i = 0; i < record.length; i++) { record[i] = null; } } } lastColumnNumber = -1; } } public List<String[]> getRows() { return rows; } public void setRows(List<String[]> rows) { this.rows = rows; } /** * Captures characters only if a suitable element is open. Originally * was just "v"; extended for inlineStr also. */ public void characters(char[] ch, int start, int length) throws SAXException { if (vIsOpen) value.append(ch, start, length); } /** * Converts an Excel column name like "C" to a zero-based index. * * @param name * @return Index corresponding to the specified name */ private int nameToColumn(String name) { int column = -1; for (int i = 0; i < name.length(); ++i) { int c = name.charAt(i); column = (column + 1) * 26 + c - 'A'; } return column; } private String formateDateToString(Date date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期 return sdf.format(date); } } private OPCPackage xlsxPackage; private int minColumns; private PrintStream output; private String sheetName; /** * Creates a new XLSX -> CSV converter * * @param pkg * The XLSX package to process * @param output * The PrintStream to output the CSV to * @param minColumns * The minimum number of columns to output, or -1 for no minimum */ public ImportMutation(OPCPackage pkg, PrintStream output, String sheetName, int minColumns) { this.xlsxPackage = pkg; this.output = output; this.minColumns = minColumns; this.sheetName = sheetName; } /** * * * @param styles * @param strings * @param sheetInputStream */ public List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException { InputSource sheetSource = new InputSource(sheetInputStream); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns, this.output); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); return handler.getRows(); } /** * 初始化这个处理程序 将 * * @throws IOException * @throws OpenXML4JException * @throws ParserConfigurationException * @throws SAXException */ @SuppressWarnings("unused") public List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable( this.xlsxPackage); XSSFReader xssfReader = new XSSFReader(this.xlsxPackage); List<String[]> list = null; StylesTable styles = xssfReader.getStylesTable(); XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader .getSheetsData(); int index = 0; while (iter.hasNext()) { InputStream stream = iter.next(); String sheetNameTemp = iter.getSheetName(); if (this.sheetName.equals(sheetNameTemp)) { list = processSheet(styles, strings, stream); stream.close(); ++index; } } return list; } /** * 读取Excel * * @param path * 文件路径 * @param sheetName * sheet名称 * @param minColumns * 列总数 * @return * @throws SAXException * @throws ParserConfigurationException * @throws OpenXML4JException * @throws IOException */ public static List<String[]> readerMutationExcel(String path, String sheetName, int minColumns) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { OPCPackage p = OPCPackage.open(path, PackageAccess.READ); ImportMutationInfo xlsx2csv = new ImportMutationInfo(p, System.out, sheetName, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; }} 所需jar包如下：
阅读全文
0 0
- 读取大数据量excel并执行批量更新数据库操作
- 批量导出大数据量到EXCEL
- 预览文章: 读取大数据量excel
- 数据库大数据量的操作
- 大数据量 批量导入到数据库
- 利用Ibatis执行批量插入更新数据库操作
- 利用poi将excel表中数据读取存入mysql数据库（数据量比较大）
- 执行数据库更新操作
- 使用JDBC读取本地的Excel文件并批量更新数据
- 通过Loadrunner读取excel表数据并批量更新到Oracle中
- 如何优化操作大数据量数据库
- springmvc 针对大数据量操作数据库 优化
- Java读取大数据量07Excel的方法（POI）
- 大数据量的excel读取poi实际应用
- Java读取大数据量07Excel的方法（POI）
- Java读取大数据量07Excel的方法（POI）
- Java读取大数据量Excel的方法（POI）
- mysql数据库批量更新操作
- Linux 内核的同步机制（1）
- WTL中文菜单显示乱码解决方法
- 移植无线网卡驱动至开发板（PXA16x）
- 静态国际化
- 文本框输入值后延迟触发
- 读取大数据量excel并执行批量更新数据库操作
- 译-Spring-理解AOP代理
- LeetCode 66.Plus One
- testNG
- jmeter数据库连接方法
- Shiro 工作流程及其Spring集成配置分享
- 开放数据集集合
- 簧片检测
- 【C++】C++ 基础知识
