Java导入百万级数据Excel

Z时代
2024-01-10
分类：综合

在工作中有时候会涉及到一些百万甚至千万级的数据导入到数据，这个时候普通的POI导入方式根本不能满足需求，这个时候我们就需要使用 POI + SAX + DAO 层批处理的方式导入数据

一、下面我们先用普通POI导入数据

添加POI依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>

下面是普通Excel的工具类

public class ExcelUtils {
    public Workbook getFile(String path) {
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            Workbook workbook = null;
            if (path.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else if (path.endsWith(".xls") || path.endsWith(".et")) {
                workbook = new HSSFWorkbook(is);
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != is) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    public List getUserList(Workbook workbook){
        //获取Excel表单
        Sheet sheet = workbook.getSheetAt(0);
        Row row = null;
        List<User> users = new ArrayList<User>();
        for(int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            //获取一行
            row = sheet.getRow(rowNum);
            User user = new User();
            user.setName(getStringValue(row.getCell(0)));
            user.setAge(Integer.valueOf(getStringValue(row.getCell(1))));
            user.setGender(getStringValue(row.getCell(2)));
            user.setIdcard(getStringValue(row.getCell(3)));
            user.setNumber(getStringValue(row.getCell(4)));
            users.add(user);
        }
        return users;
    }
    /**
     * 获取单元格的值的字符串
     * @param cell 单元格对象
     * @return cell单元格的值的字符串
     */
    private static String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                double value = cell.getNumericCellValue();
                return String.valueOf(Math.round(value));
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }}

User.java，这个是我保存到MySql数据库时候用到的映射对象

@Table("user")
public class User {
@Column(value = "id", isKey = true)
    private Long id;
@Column("name")
    private String name;
@Column("gender")
    private String gender;
@Column("age")
    private Integer age;
@Column("idcard")
    private String idcard;
@Column("number")
    private String number;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getIdcard() {
        return idcard;
    }
    public void setIdcard(String idcard) {
        this.idcard = idcard;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
}

下面是测试函数，saveBatch()函数是我自己封装了Spring提供的JdbcTemplate 工具类这里有介绍：

public class Test {
    public static void main(String[] args) {
        Long time = System.currentTimeMillis();
        String path = "C:\Users\Administrator\Desktop\user.xlsx";
        ExcelUtils utils = new ExcelUtils();
        Workbook file = utils.getFile(path);
        List list = utils.getUserList(file);
        System.out.println("读取时间：" + (System.currentTimeMillis() - time));
        System.out.println(list.size());
saveBatch(list);
        System.out.println("保存时间：" + (System.currentTimeMillis() - time));
    }
    public static void saveBatch(List<User> list) {
        try {
            BaseDao<User> baseDao = new BaseDao<User>();
            JdbcTemplate template = new JdbcTemplate(JdbcTemplateUtils.getDataSource());
            baseDao.setJdbcTemplate(template);
            baseDao.batchSave(list);
            template.getDataSource().getConnection().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

我用上面这段测试了五万数据与十万数据，五万数据还是能保存，但是即使能保存效率也极低。而导入十万甚至百万数据的时候，别说持久化到数据库，在我的程序里面读也没有读取成功，读取速度超级慢，读了几分钟之后抛出了个java.lang.OutOfMemoryError异常，也就是内存溢出（OOM）

二、这个时候我们就需要用到SAX

SAX就是把我们的Excel文件转成XML文件，然后逐行扫描并且解析，并且SAX解析XML要比DOM解析XML更快。SAX与DOM操作不同的是不需要把文件数据保存到我们的内存，不需要构建一棵庞大的数据树，这样能减少内存消耗提升数据读取速度，避免抛出OOM

下面是POI + SAX方式批量插入数据

添加SAX依赖

<dependency>
<groupId>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>2.11.0</version>
</dependency>

下面是 POI +SAX 实现导入的代码

public class SaxUtils {
// 储存整个列表
public List<User> dataList = new ArrayList<User>();
// 储存每行对象
private User user;
// 记录当前遍历到第几列
private int cellIndex = 0;
// 读取的文件
private final String filename;
    public SaxUtils(String filename) throws Exception{
        if(filename == null || "".equals(filename)) throw new Exception("文件名不能空");
        this.filename = filename;
        processFirstSheet();
    }
/**
     * 指定获取第一个sheet
     * @throws Exception
     */
private void processFirstSheet() throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
// 读取第一个 sheet
InputStream sheet1 = r.getSheet("rId1");
        InputSource sheetSource = new InputSource(sheet1);
// 利用XML解析 sheet
parser.parse(sheetSource);
        sheet1.close();
        sst.close();
        pkg.close();
    }
/**
     * 加载XML 解析器
* @param sst
* @return
* @throws SAXException
     */
private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
                XMLReaderFactory.createXMLReader(
                        "org.apache.xerces.parsers.SAXParser"
                );
        ContentHandler handler = new ReadHandler(sst);
        parser.setContentHandler(handler);
        return parser;
    }
/**
     * 实现DefaultHandler 重写
*  startElement(String uri, String localName, String name, Attributes attributes)
     *  endElement(String uri, String localName, String name)
     *  characters(char[] ch, int start, int length)
     *  endDocument()
     */
private  class ReadHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean isSstIndex;
        private ReadHandler(SharedStringsTable sst) {
            this.sst = sst;
        }
/**
         * 每个单元格开始时的处理
*/
@Override
public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// 获取元素坐标
String index = attributes.getValue("r");
// 判断是否新行
if(Pattern.compile("^A[0-9]+$").matcher(index).find()){
//存保存上一行数据
if(user!=null && !StringUtils.isEmpty(user.getName())){
                        dataList.add(user);
                    }
// 新行要先清除上一行的数据
user = new User();
// 重置当前列
cellIndex = 0;
                }
// 判断该值是否是SST中的一个索引
String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    isSstIndex = true;
                } else {
                    isSstIndex = false;
                }
            }
// Clear contents cache
lastContents = "";
        }
/**
         * 每个单元格结束时的处理
*/
@Override
public void endElement(String uri, String localName, String name)
                throws SAXException {
// Process the last contents as required.
            // Do now, as characters() may be called more than once
if(isSstIndex) {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                isSstIndex = false;
            }
// v 是每个单元格 cell的值
// 如果当前属性为 v 则保存
if(name.equals("v")) {
                if(cellIndex == 0) {
                    user.setName(lastContents);
                } else if (cellIndex == 1) {
                    user.setAge(Integer.parseInt(lastContents));
                } else if (cellIndex == 2) {
                    user.setGender(lastContents);
                } else if (cellIndex == 3) {
                    user.setIdcard(lastContents);
                } else if (cellIndex == 4) {
                    user.setNumber(lastContents);
                }
                cellIndex++;
            }
        }
@Override
public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
/**
         * 如果文档结束后，发现读取的末尾行正处在当前行中，存储下这行
* （存在这样一种情况，当待读取的末尾行正好是文档最后一行时，最后一行无法存到集合中，
* 因为最后一行没有下一行了，所以不为启动starElement()方法，
* 当然我们可以通过指定最大列来处理，但不想那么做，扩展性不好）
*/
@Override
public void endDocument() throws SAXException {
            if(user!=null && !StringUtils.isEmpty(user.getName())){
                dataList.add(user);
            }
        }
    }
}

测试类：

public class Test {
    public static void main(String[] args) throws Exception {
        long time = System.currentTimeMillis();
        SaxUtils reader = new SaxUtils("C:\Users\Administrator\Desktop\user.xlsx");
        System.out.println("数据量：" + reader.dataList.size());
        System.out.println("读取数据时间：" + ((System.currentTimeMillis() - time) / 1000.0));
        long time2 = System.currentTimeMillis();
saveBatch(reader.dataList);
        System.out.println("保存时间：" + ((System.currentTimeMillis() - time2) / 1000.0));
    }
    public static void saveBatch(List<User> list) {
        try {
            BaseDao<User> baseDao = new BaseDao<User>();
            JdbcTemplate template = new JdbcTemplate(JdbcTemplateUtils.getDataSource());
            baseDao.setJdbcTemplate(template);
            baseDao.batchSave(list);
            template.getDataSource().getConnection().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

执行结果：

如果我们不修改mysql配置直接插入会出现上面的错误提示，并且会提示我们配置 max_allowed_packet 参数，原因是插入的数据太大。除了这个配置，我们再加一个配置 innodb_buffer_pool_size（innodb_buffer_pool_size 百万级数据配不配都可以，工作中根据实际情况配置，默认是128M）配置了这个缓存区我们批量插入操作的时候可以更快，并且把一百万数据拆分成每次保存十万条数据

接下来我们需要修改my.cnf文件，把插入的内存调大点

然后再执行，持久化时间是25秒数据也成功插入了

三、使用原生JDBC做批量持久化操作

上面的效率还可以继续优化，因为各种ORM框架封装的细节我们很难把控，这个时候我们换成原生的JDBC批处理再测试下

public class JdbcTest {
    private String url = "jdbc:mysql://localhost:3306/winfongs?characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "123456";
    public void batchSave(List<User> users){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
// 加载JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
// 设置批处理sql
String sql = "INSERT INTO user(name,gender,age,idcard,number) VALUES(?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
// 禁止自动提交事务
conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            System.out.println("开始执行持久化！！");
// 遍历数据设置插入的值
for (int j = 0,i = 0; j < users.size(); j++) {
                User user = users.get(j);
                pstm.setString(1, user.getName());
                pstm.setString(2, user.getGender());
                pstm.setInt(3, user.getAge());
                pstm.setString(4, user.getIdcard());
                pstm.setString(5, user.getNumber());
                pstm.addBatch();
                if( (i == 100000) ||  (j == users.size() - 1) ) {
// 提交事务
pstm.executeBatch();
                    conn.commit();
                    i = 0;
                }
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

public static void main(String[] args) {
    try {
        long time = System.currentTimeMillis();
        SaxUtils reader = new SaxUtils("C:\Users\Administrator\Desktop\user.xlsx");
        System.out.println("数据量：" + reader.dataList.size());
        System.out.println("读取数据时间：" + ((System.currentTimeMillis() - time) / 1000.0));
        long time2 = System.currentTimeMillis();
        new JdbcTest().batchSave(reader.dataList);
        System.out.println("保存时间：" + ((System.currentTimeMillis() - time2) / 1000.0));
    } catch (Exception e) {
        e.printStackTrace();
    }
}