Java实现Excel数据批量导入数据库
概述:
这个小工具类是工作中的一个小插曲哦,因为提数的时候需要跨数据库导数... 有的是需要从oracle导入mysql ,有的是从mysql导入oracle
实践之后我们会发现 使用navicat工具连接mysql数据库, 从oracle数据库中导出数据为csv格式,再导入mysql是很快的,大概是分分钟的问题吧
但是当我们使用plsql连接oracle数据库,然后在mysql数据库中导出sql文件形式的数据想要插入oracle的时候,就很慢了..... 我一般是5万条的数据 6个字段,在开启命令窗口执行需要执行差不多2小时~ 这个真的太可怕了 而且有时候工具有问题,还*中断.... 又要重新开始... 效率太低了
所以有了以下导数工具类.
新建maven项目,项目名称可自定义哦
ExecuteDataExcelToOracle.java 类,使用的时候直接执行main方法即可
需要替换你的Excel文件
需要替换你的数据库连接信息
需要替换你的表信息,以及字段 (这里字段还与内部了 Data相关哦,如果你需要导入的字段大于10个请自己继续添加哈~)
主要做了以下几件事:
1.解析Excel文件,将Excel文件中的数据逐条的放入对象中
2. 连接数据库,将数据插入数据库
通过此工具类导入数据,5万条数据大概只要2分钟吧,而且大部分时间都是花在解析excel上~,效率简直不是一般的高哈哈哈哈~
package com.imodule.dataImport.dataImport.main; import java.io.File; import java.io.FileInputStream; import java.io.Serializable; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import java.util.Date; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class ExecuteDataExcelToOracle { public static void main(String[] args) { try { List<Data> datas = loadExcel("C:/Users/xxxx/Desktop/aa.xlsx");//需要替换 batchInsert(datas); } catch (Exception e) { e.printStackTrace(); } } /** * 获取excel中的数据 * @param filepath * @return * @throws Exception */ public static List<Data> loadExcel(String filepath) throws Exception{ File file = new File(filepath); Workbook wb = WorkbookFactory.create(new FileInputStream(file)); Sheet sheet = wb.getSheetAt(0); List<Data> datas = new ArrayList<>();//将数据添加到数据一行一行的添加到集合中,作为插入数据的入参 Row row = null; for (int i = 1; i <= sheet.getLastRowNum(); i++) { System.out.println("i="+i); row = sheet.getRow(i); if(row == null || StringUtils.isEmpty(row.getCell(0).getStringCellValue())){ continue; } Data data = new ExecuteDataExcelToOracle().new Data(); data.setStr1(row.getCell(0) == null? "":row.getCell(0).getStringCellValue()); data.setStr2(row.getCell(1) == null? "":row.getCell(1).getStringCellValue()); data.setStr3(row.getCell(2) == null? "":row.getCell(2).getStringCellValue()); data.setStr4(row.getCell(3) == null? "":row.getCell(3).getStringCellValue()); data.setStr5(row.getCell(4) == null? "":row.getCell(4).getStringCellValue()); data.setStr6(row.getCell(5) == null? "":row.getCell(5).getStringCellValue()); // row.getCell(5).setCellType(CellType.STRING); if(row.getCell(6) == null){ data.setStr7(""); }else{ Date d = (Date) row.getCell(6).getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String date = formater.format(d); data.setStr7(date); } data.setStr8(row.getCell(7) == null? "":row.getCell(7).getStringCellValue()); data.setStr9(row.getCell(8) == null? "":row.getCell(8).getStringCellValue()); data.setStr10(row.getCell(9) == null? "":row.getCell(9).getStringCellValue()); datas.add(data); } return datas; } /** * 批量执行插入数据 * @param datas */ public static void batchInsert(List<Data> datas){ long startTime = System.currentTimeMillis(); Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); //如果是oracle就不需要替换,如果是mysql就需要替换 conn=DriverManager.getConnection("jdbc:oracle:thin:@10.11.22.33:1521:demo", "root","111"); //需要替换 conn.setAutoCommit(false); PreparedStatement stmt = conn.prepareStatement("INSERT INTO temp_user_info VALUES (?,?,?,?,?,?,?,?,?,?)"); //需要替换 //System.out.println("数据大小:" + datas.size()); int num = 0; for (Data v : datas) { num++; stmt.setString(1, v.getStr1()); stmt.setString(2, v.getStr2()); stmt.setString(3, v.getStr3()); stmt.setString(4, v.getStr4()); stmt.setString(5, v.getStr5()); // stmt.setString(6, v.getStr6()); stmt.setString(6, v.getStr6()); stmt.setString(7, v.getStr7()); stmt.setString(8, v.getStr8()); stmt.setString(9, v.getStr9()); stmt.setString(10, v.getStr10()); stmt.addBatch(); // 每5万,提交一次 if (num > 50000) { stmt.executeBatch(); conn.commit(); num = 0; } } stmt.executeBatch(); conn.commit(); } catch (Exception e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } long endTime = System.currentTimeMillis(); System.out.println("方法执行时间:" + (endTime - startTime) + "ms"); } } class Data implements Serializable{ private static final long serialVersionUID = 1L; private String str1; private String str2; private String str3; private String str4; private String str5; private String str6; private String str7; private String str8; private String str9; private String str10; public String getStr7() { return str7; } public void setStr7(String str7) { this.str7 = str7; } public String getStr8() { return str8; } public void setStr8(String str8) { this.str8 = str8; } public String getStr9() { return str9; } public void setStr9(String str9) { this.str9 = str9; } public String getStr10() { return str10; } public void setStr10(String str10) { this.str10 = str10; } public String getStr1() { return str1; } public void setStr1(String str1) { this.str1 = str1; } public String getStr2() { return str2; } public void setStr2(String str2) { this.str2 = str2; } public String getStr3() { return str3; } public void setStr3(String str3) { this.str3 = str3; } public String getStr4() { return str4; } public void setStr4(String str4) { this.str4 = str4; } public String getStr5() { return str5; } public void setStr5(String str5) { this.str5 = str5; } public String getStr6() { return str6; } public void setStr6(String str6) { this.str6 = str6; } } }
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.imodule.dataImport</groupId> <artifactId>dataImport</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>dataImport</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <!-- 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> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-io/commons-io --> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-lang/commons-lang --> <dependency> <groupId>commons-lang</groupId> <artifactId>commons-lang</artifactId> <version>2.4</version> </dependency> <!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 --> <dependency> <groupId>ojdbc</groupId> <artifactId>ojdbc</artifactId> <version>6</version> </dependency> </dependencies> </project>
这个pom里面需要主要这个包好像比较难拿到
<dependency> <groupId>ojdbc</groupId> <artifactId>ojdbc</artifactId> <version>6</version> </dependency>
这个我看看啥时候有时间就把这个包传到我github上去,到时候会更新博客,提供下载地址的
还有一个就是在导入数据的时候需要注意两个值
1.null
2.日期类型的值
关于null,我的处理方案是先判断是否为null,如果为null就赋值""
data.setStr1(row.getCell(0) == null? "":row.getCell(0).getStringCellValue());
关于日期,我的处理方案是手动转换 (这里不能直接用数据类型,会把日期自动计算为 49555.4类似的数据,到时候存到数据库也是这样的数据,所以我们还是用这个字符串吧~)
if(row.getCell(6) == null){ data.setStr7(""); }else{ Date d = (Date) row.getCell(6).getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String date = formater.format(d); data.setStr7(date); }