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上去，到时候会更新博客，提供下载地址的
==============20190107更新 有小伙伴在问我这个包的下载地址，今天就上传到github上了，感谢提醒哈哈哈哈 不然我都快要忘记自己还有这项事情未完成了==start====================
github下载地址： https://github.com/DFX339/odbcJar.git
==============20190107更新 有小伙伴在问我这个包的下载地址，今天就上传到github上了，感谢提醒哈哈哈哈 不然我都快要忘记自己还有这项事情未完成了==end====================
还有一个就是在导入数据的时候需要注意两个值
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);
}
posted on
