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 mons.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 datas = loadExcel("C:/Users/xxxx/Desktop/aa.xlsx");//需要替换
batchInsert(datas);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取excel中的数据
* @param filepath
* @return
* @throws Exception
*/
public static List loadExcel(String filepath) throws Exception{
File file = new File(filepath);
Workbook wb = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = wb.getSheetAt(0);
List 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 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();
mit();
num = 0;
}
}
stmt.executeBatch();
mit();
} 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
xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd">
4.0.0
com.imodule.dataImport
dataImport
0.0.1-SNAPSHOT
jar
dataImport
UTF-8
junit
junit
3.8.1
test
org.apache.poi
poi
4.0.1
org.apache.poi
poi-ooxml
4.0.1
org.apache.poi
poi-ooxml-schemas
4.0.1
commons-io
commons-io
2.4
commons-lang
commons-lang
2.4
ojdbc
ojdbc
6
这个pom里面需要主要这个包好像比较难拿到
ojdbc
ojdbc
6
这个我看看啥时候有时间就把这个包传到我github上去，到时候会更新博客，提供下载地址的
==============0107更新 有小伙伴在问我这个包的下载地址，今天就上传到github上了，感谢提醒哈哈哈哈 不然我都快要忘记自己还有这项事情未完成了==start====================
==============0107更新 有小伙伴在问我这个包的下载地址，今天就上传到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);
}
