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

 

这个demo是有一个Excel中的数据,我需要读取其中的数据然后导入到关系型数据库中,但是为了向数据库中插入更多的数据,循环N次Excel中的结果.

关于JDBC的批处理还可以参考我总结的如下博文:

http://www.cnblogs.com/DreamDrive/p/5757693.html

此外用到了读取Excel文件的jxl.jar包

下载地址:http://files.cnblogs.com/files/DreamDrive/jxl.rar

附代码:

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Workbook;

/**
 * Excel模板中的数据分析导入到Mysql中.
 * @author CDV-DX7
 *
 */
public class ExcelToMysql {
    public static void main(String[] args) {
        String excelUrl = "C:\\Users\\CDV-DX7\\Desktop\\节目信息输入数据.xls";
        excel2db(excelUrl);
    }

    public static void excel2db(String importPath) {
        List<Cell[]> rowList;
        rowList = jxlGetExcelRows(importPath);
        try {
            long start = System.currentTimeMillis();
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/50million?rewriteBatchedStatements=true","root","root");
            connection.setAutoCommit(false);
            PreparedStatement cmd = connection.prepareStatement("insert into asset (assetname,director,screenwriter,actor,programtype,region,language,releasetime,duration,alias) "
                    + "values(?,?,?,?,?,?,?,?,?,?)");
            
            String assetname = null;
            String director = null;
            String screenwriter = null;
            String actor = null;
            String programtype = null;
            String region = null;
            String language = null;
            String releasetime = null;
            String duration = null;
            String alias = null;
            int totalCount = 0;
            for (int k=1;k<442478;k++){
                //50万对应.........................redis是0号数据库
                //100万对应循环次数8851---用时65680---redis是1号数据库
                //500万对应循环次数44248---用时469947---redis是2号数据库
                //1000万对应循环次数88496---用时1385016---redis是3号数据库
                //5000万对应循环次数442478---用时C盘空间不够了....暂时不做数据了..
                for (int m=1; m<rowList.size(); m++) {
                    Cell[] cells = rowList.get(m);
                    for(int i=0; i<cells.length-1; i++){
                        totalCount++;
                        String contents = cells[i].getContents(); 
                        switch(i){
                            case 0:
                                assetname = contents;
                                break;
                            case 1:
                                director = contents;
                                break;
                            case 2:
                                screenwriter = contents;
                                break;
                            case 3:
                                actor = contents;
                                break;
                            case 4:
                                programtype = contents;
                                break;
                            case 5:
                                region = contents;
                                break;
                            case 6:
                                language = contents;
                                break;
                            case 7:
                                releasetime = contents;
                                break;
                            case 8:
                                duration = contents;
                                break;
                            case 9:
                                alias = contents;
                                break;

                        }
                    }
                    cmd.setString(1,assetname);
                    cmd.setString(2,director);
                    cmd.setString(3,screenwriter);
                    cmd.setString(4,actor);
                    cmd.setString(5,programtype);
                    cmd.setString(6,region);
                    cmd.setString(7,language);
                    cmd.setString(8,releasetime);
                    cmd.setString(9,duration);
                    cmd.setString(10,alias);
                    
                    cmd.addBatch();
                    if(totalCount%10000==0){
                        cmd.executeBatch();
                    }
                }
            }
            cmd.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();//
            System.out.println("批量插入需要时间:"+(end - start));
            cmd.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }
    
    /**
     * 得到指定Excel文件中(可以再指定标签)中的行....
     * @param filename
     * @return
     */
    private static List<Cell[]> jxlGetExcelRows(String filename) {
        InputStream is = null;
        jxl.Workbook rwb = null;
        List<Cell[]> list = new ArrayList<Cell[]>();
        try {
            is = new FileInputStream(filename);
            rwb = Workbook.getWorkbook(is);
            // Sheet[] sheets = rwb.getSheets();
            // int sheetLen = sheets.length;
            jxl.Sheet rs = rwb.getSheet(0); // 默认先读取第一个工作表的数据
            
            //getRows() 获取总共多少行...getRow(n)获取第n行...
            for(int i=0; i<rs.getRows(); i++ ){
                list.add(rs.getRow(i));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            rwb.close();
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}