首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

excel动态导入数据库mysql

作者：好人森森_195 | 来源：互联网 | 2023-06-02 17:36
2019独角兽企业重金招聘Python工程师标准环境：jdk1.8poi-3.17mysql-5.6excel2010(xlsx)工具类：1.ex



2019独角兽企业重金招聘Python工程师标准>>> hot3.png



环境&＃xff1a;


    jdk1.8&＃43;poi-3.17&＃43;mysql-5.6&＃43;excel2010(xlsx)


工具类&＃xff1a;


    1.excel解析工具类&＃xff0c;使用poi sax模式解析excel。生成数据格式为List>


private List> results&＃61;new ArrayList<>();
public void process(LinkedHashMap dataMap, int curRow) {
if(headerMap.isEmpty()){
for(Map.Entry e: dataMap.entrySet()){
if(null !&＃61; e.getKey() && null !&＃61; e.getValue()){
headerMap.put(e.getKey(), e.getValue().toLowerCase());
}
}
}else{
LinkedHashMap data &＃61; new LinkedHashMap<>();
for (Map.Entry e : headerMap.entrySet()) {
String key &＃61; e.getValue();
String value &＃61; null&＃61;&＃61;dataMap.get(e.getKey())?"":dataMap.get(e.getKey());
data.put(key, value);
}
count.getAndIncrement();
results.add(data);
}
}

2.数据库的excel的配置信息t_fields--可以配置多个excel


fname--excel标题字段


fcode--标题字段对应的数据库字段


data_type--建表字段信息


ftable--excel对应的数据表


1828bfb736b4af600ef125709123bcd8483.jpg


3.excel配置表实体类---jpa


package com.fdrx.model;
import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;
/**
* Created by shea on 2019-06-05.
*/
&＃64;Data
&＃64;Entity
&＃64;Table(name &＃61;"t_fields")
public class ExcelBean implements Serializable {
&＃64;Id
&＃64;GeneratedValue(strategy&＃61; GenerationType.IDENTITY)
private Integer id;
&＃64;Column(length &＃61; 10)
private String fcode;
&＃64;Column(length &＃61; 10)
private String fname;
&＃64;Column(length &＃61; 20)
private String dataType;
&＃64;Column(length &＃61; 10)
private String ftable;
}
package com.fdrx.dao;
import com.fdrx.model.ExcelBean;
import org.springframework.data.jpa.repository.JpaRepository;
/**
* Created by shea on 2019-06-05.
*/
&＃64;Repository
public interface ExcelBeanDao extends JpaRepository {
}

4.excel工具导入服务类


package com.fdrx.service;
import com.fdrx.dao.ExcelBeanDao;
import com.fdrx.model.ExcelBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
/**
* Created by shea on 2019-06-06.
*/
&＃64;Service
&＃64;Slf4j
public class ExcelPipeline {
&＃64;Resource
private ExcelBeanDao excelBeanDao;
private HashMap allFields&＃61; new HashMap<>();
/**
* 写入数据库
* &＃64;param datas excel解析数据
* &＃64;param table 目标表
* &＃64;return
*/
public void saveData(List> datas,String table){
//1.获取配置文件
List all &＃61; excelBeanDao.findAll();
//2.根据表 获取对应字段映射关系
allFields&＃61;all.stream().filter(m -> table.equals(m.getFtable()))
.collect(Collectors.toMap(ExcelBean::getFname,ExcelBean::getFcode,(k1, k2)->k2,LinkedHashMap::new));
//3.根据数据库配置信息,生成建表sql
String collect &＃61; all.stream().filter(m -> table.equals(m.getFtable()))
.map(m -> String.format("&＃96;%s&＃96; %s comment &＃39;%s&＃39;",m.getFcode(), StringUtils.isEmpty(m.getDataType())?"text":m.getDataType(),m.getFname()))
.collect(Collectors.joining(","));
String createSql &＃61; String.format("create table IF NOT Exists %s (%s)ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8mb4", table,collect);
//4.根据excel标题 获取导入字段
List title &＃61; createTitle(datas.get(0));
//5.根据导入字段&＃xff0c;生成对应的导入数据列表
List> importLists &＃61; convert2List(datas,title);
//6.根据导入字段&＃xff0c;生成导入语句
String inserSql &＃61; createSql(title, table);
//7.开始msql导入
Connection con&＃61;null;
try {
con &＃61; getConnector();//jdbc链接
con.setAutoCommit(false);//不自动提交
//指定建表语句&＃xff0c;存在则不创建
executeUpdate(createSql,new ArrayList<>(),con);
for (List strings : importLists) {
try {
executeUpdate(inserSql, strings, con);
} catch (SQLException e) {
log.error("{}&＃61;》写入失败&＃xff01;",strings.get(0));
}
}
} catch (Exception e) {
log.error(e.getMessage());
}finally {
try {
if(con!&＃61;null){
con.setAutoCommit(true);
con.close();
}
} catch (SQLException e) {
log.error(e.getMessage());
}
}
log.info("导入完成");
}
/**
* 执行 sql 更新
* &＃64;param sql sql
* &＃64;param params params
* &＃64;param conn conn
* &＃64;return
* &＃64;throws SQLException
*/
private void executeUpdate(String sql, List params, Connection conn ) throws SQLException {
try (PreparedStatement ps &＃61; conn.prepareStatement(sql)){
int paramIdx &＃61; 1;
for(Object obj: params){
ps.setObject(paramIdx, obj);
paramIdx &＃43;&＃43;;
}
ps.executeUpdate();
}
}
/**
* 根据excel数据生成插入语句
*/
private String createSql( List title,String table){
Optional sqlField &＃61; title.stream()
.map(str -> String.format("&＃96;%s&＃96;", allFields.get(str.trim())))
.filter(s->!"".equals(s)&& null!&＃61;s)
.reduce((a, b) -> String.format("%s,%s", a, b));
Optional sqlValue&＃61;title.stream()
.map(str -> String.format("&＃96;%s&＃96;", allFields.get(str.trim())))
.filter(s->!"".equals(s)&& null!&＃61;s)
.map(str->"?").reduce((a, b) -> String.format("%s,%s", a, b));
String sql &＃61; String.format("replace into &＃96;%s&＃96;(%s) values(%s)",table, sqlField.orElse(""), sqlValue.orElse(""));
return sql;
}
/**
* 映射excel标题行 与 数据库配置信息&＃61;&＃61;&＃61;》确定要导入的数据列
* &＃64;param headers
* &＃64;return
*/
private List createTitle(LinkedHashMap headers){
return headers.keySet().stream()
.filter(str -> allFields.get(str.trim())!&＃61;null)
.collect(Collectors.toList());
}
/**
* 转换数据list
* &＃64;param datas excel数据
* &＃64;param title 导入字段列表
* &＃64;return
*/
private List> convert2List(List> datas,List title){
List> collect &＃61; datas.stream().map(data -> {
List single &＃61; title.stream().map(data::get).collect(Collectors.toList());
return single;
}).collect(Collectors.toList());
return collect;
}
/**
* jdbc
* &＃64;return
* &＃64;throws SQLException
*/
public Connection getConnector() throws SQLException {
Connection conn &＃61; null;
String url &＃61; "jdbc:mysql://localhost:3306/weixin?useUnicode&＃61;true&characterEncoding&＃61;utf-8";
String user &＃61; "root";
String passwd &＃61; "root";
try {
Class.forName("com.mysql.jdbc.Driver");
conn &＃61; DriverManager.getConnection(url, user, passwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
}

5.测试


package com.fdrx.demo;
import com.fdrx.Application;
import com.fdrx.service.ExcelPipeline;
import com.fdrx.service.JdbcPipeline;
import com.fdrx.util.excel.Excel2007Parser;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.List;
/**
* Created by shea on 2019-06-05.
*/
&＃64;RunWith(SpringRunner.class)
&＃64;SpringBootTest(classes &＃61; Application.class)//如果需要注入服务&＃xff0c;则要启
&＃64;Slf4j
public class TestDemo {
&＃64;Resource
private JdbcPipeline jdbcPipeline;
&＃64;Resource
private ExcelPipeline excelPipeline;
&＃64;Test
public void test1()throws Exception{
String table &＃61;"t_excel2";
InputStream in &＃61; new FileInputStream("C:\\Users\\shea\\Desktop\\excel2.xlsx");
//调用事件驱动解析excel
Excel2007Parser parser &＃61; new Excel2007Parser(in);
parser.parse();
//解析结果
List> datas &＃61; parser.getResults();
excelPipeline.saveData(datas,table);
}
}

excel导入都可以通过只配置mysql数据表中的excel配置表t_fields即可。


再导入excel的服务类中指定导入excel对应的ftable 即可&＃xff0c;程序会自动创建对应的excel表&＃xff0c;excel导入就可以实现快捷导入。


当导入的excel模板发生变化的时候&＃xff0c;也能通过t_fields配置&＃xff0c;实现标题的动态配置。


 


 


修改为批量插入&＃xff1a;不能使用replace into 进行主键覆盖策略&＃xff0c;所有需要根据自己业务情况选择合适的插入方法


    


#生成插入语句--修改
/**
* 根据第一行生成插入语句
*/
private String createSql( List title,String table){
Optional sqlField &＃61; title.stream()
.map(str -> String.format("&＃96;%s&＃96;", allFields.get(str.trim())))
.filter(s->!"".equals(s)&& null!&＃61;s)
.reduce((a, b) -> String.format("%s,%s", a, b));
String sql &＃61; String.format("insert into &＃96;%s&＃96;(%s) values ",table, sqlField.get());
return sql;
}
#生成批量插入
private int batch &＃61; 1000;
String inserSql &＃61; createSql(title, table);
try {
ArrayList res &＃61; new ArrayList<>();
//使用批量插入---每N条生成一个插入语句
for (List strings : lists) {
String s &＃61; strings.stream().map(m -> String.format("&＃39;%s&＃39;", m)).reduce((x,y)->String.join(",",x,y))
.map(m -> String.format("(%s)", m)).get();
res.add(s);
if(res.size() % batch &＃61;&＃61;0){
String join &＃61; String.join(",", res);
executeUpdateBatch(inserSql&＃43;join, con);
res.clear();
}
}
String join &＃61; String.join(",", res);
executeUpdateBatch(inserSql&＃43;join, con);
try {
if(con!&＃61;null){
con.setAutoCommit(true);
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
/**
* 执行 sql批量更新
* &＃64;param sql sql
* &＃64;param conn conn
* &＃64;return
* &＃64;throws SQLException
*/
private void executeUpdateBatch(String sql, Connection conn ) throws SQLException {
AtomicInteger count &＃61; new AtomicInteger(1);
PreparedStatement ps &＃61; conn.prepareStatement(sql);
ps.executeUpdate();
log.info("第{}次提交&＃xff01;",count.getAndIncrement());
}

 


耗时统计&＃xff1a;


 


 


c1a3f6e1dfcbaf000d02e0b070e62e7e1ce.jpg


    测试数据&＃xff1a;35个字段&＃xff0c;共65330条数据


    1.使用单条导入耗时&＃xff1a;导入完成,共导入65330条数据,一共耗时24.816秒。


    2.使用批量导入耗时&＃xff1a;导入完成,共导入65330条数据,一共耗时7.359秒。


 


新增excel快速导入配置信息到t_fields:


341df83990dfd417fa45c0ed8a99d836526.jpg


前两行为标题行&＃xff0c;第三行为fcode&＃xff0c;第四行为data_type.


你也可以直接用poi解析&＃xff0c;生成 List>的数据即可&＃xff1a;第一行为标题fname&＃xff0c;第二行为字段fcode&＃xff0c;第三行为建表数据格式data_type.


String fieldsTable &＃61; "t_fields";//excel字段配置表
String ftable &＃61;"t_excel4";//excel对应的table名称
//todo Excel2007Parser解析默认第一行为标题好&＃xff0c;数据从第二行开始计算的,所以excel中多定义一行标题
InputStream in &＃61; new FileInputStream("C:\\Users\\shea\\Desktop\\t4.xlsx");
Excel2007Parser parser &＃61; new Excel2007Parser(in);
parser.parse();
//解析结果
List> datas &＃61; parser.getResults();
//直接转换为需要的数据格式
List> values&＃61; datas.get(0).keySet().stream().map(k -> {
return IntStream.range(0, 3).boxed().map(i -> datas.get(i).get(k)).collect(Collectors.toList());
}).collect(Collectors.toList());
/**
//获取解析结果转为list
List> collect &＃61; datas.stream().map(m -> {
return new ArrayList<>(m.values());
}).collect(Collectors.toList());
//实现行转列--生成插入需要的数据结构
List> values &＃61; IntStream.range(0, collect.get(0).size()).boxed().map(i -> {
return IntStream.range(0, 3).boxed().map(index -> collect.get(index).get(i)).collect(Collectors.toList());
}).collect(Collectors.toList());
*/
String sql &＃61; String.format("insert into %s (&＃96;fname&＃96;,&＃96;fcode&＃96;,&＃96;data_type&＃96;,&＃96;ftable&＃96;) values (?,?,?,&＃39;%s&＃39;)",fieldsTable,ftable);
Connection conn &＃61; getcon();
values.forEach(v-> {
try {
executeUpdate(sql,v,conn);
} catch (SQLException e) {
e.printStackTrace();
}
});

6b2f340261dbea0a3ae16b618726b76af1f.jpg


根据标题自动生成slq字段---中文缩写


4667dcd8c54ae6f89a5621e5cf1824cef54.jpg


1.引入拼音处理包



com.hankcs
hanlp
portable-1.7.2

2.根据中文标题拼音首字母缩写生成字段


&＃64;Test
public void createTable()throws Exception{
String fieldsTable &＃61; "t_fields";//excel字段配置表
String ftable &＃61;"t_excel6";//excel对应的table名称
//todo Excel2007Parser解析默认第一行为标题好&＃xff0c;数据从第二行开始计算的,所以excel中多定义一行标题
InputStream in &＃61; new FileInputStream("C:\\Users\\shea\\Desktop\\t6.xlsx");
Excel2007Parser parser &＃61; new Excel2007Parser(in);
parser.parse();
//解析结果
List> datas &＃61; parser.getResults();
LinkedHashMap map &＃61; new LinkedHashMap<>();
datas.get(0).keySet().forEach(k->{
String fcode &＃61; HanLP.convertToPinyinFirstCharString(k, "", false);
if(map.containsKey(fcode)){
String code &＃61; assertLive(map, fcode, 1);
map.put(code,k);
}else {
map.put(fcode,k);
}
});
List> values &＃61; map.entrySet().stream()
.map(m -> Stream.of(m.getKey(), m.getValue(), "text").collect(Collectors.toList()))
.collect(Collectors.toList());
String sql &＃61; String.format("insert into %s (&＃96;fcode&＃96;,&＃96;fname&＃96;,&＃96;data_type&＃96;,&＃96;ftable&＃96;) values (?,?,?,&＃39;%s&＃39;)",fieldsTable,ftable);
Connection conn &＃61; getcon();
values.forEach(v-> {
try {
executeUpdate(sql,v,conn);
} catch (SQLException e) {
e.printStackTrace();
}
});
conn.close();
}
//递归查询&＃xff0c;避免重复字段
private String assertLive(Map map,String code,int index){
String suffix &＃61; String.valueOf(index);
if(map.containsKey(code.concat(suffix))){
index&＃43;&＃43;;
return assertLive(map,code,index);
}
return code.concat(suffix);
}

结果&＃xff1a;


603cbb4b95ccd38ba35e69dd6376dff0f98.jpg







转载于:https://my.oschina.net/shea1992/blog/3059369



推荐阅读
  • spring

    Spring特性实现接口多类的动态调用详解

    Spring特性实现接口多类的动态调用详解
    本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]
  • format

    [大整数乘法] java代码实现

    [大整数乘法] java代码实现
    本文介绍了使用java代码实现大整数乘法的过程，同时也涉及到大整数加法和大整数减法的计算方法。通过分治算法来提高计算效率，并对算法的时间复杂度进行了研究。详细代码实现请参考文章链接。 ... [详细]
  • const

    Java太阳系小游戏分析和源码详解

    Java太阳系小游戏分析和源码详解
    本文介绍了一个基于Java的太阳系小游戏的分析和源码详解。通过对面向对象的知识的学习和实践，作者实现了太阳系各行星绕太阳转的效果。文章详细介绍了游戏的设计思路和源码结构，包括工具类、常量、图片加载、面板等。通过这个小游戏的制作，读者可以巩固和应用所学的知识，如类的继承、方法的重载与重写、多态和封装等。 ... [详细]
  • future

    Netty拆包粘包问题解决 —— 特殊结束符

    本文介绍了解决Netty拆包粘包问题的一种方法——使用特殊结束符。在通讯过程中，客户端和服务器协商定义一个特殊的分隔符号，只要没有发送分隔符号，就代表一条数据没有结束。文章还提供了服务端的示例代码。 ... [详细]
  • const

    在类中定义数组时出错 - Error on defining arrays in class

    Iamtryingtomakeaclassthatwillreadatextfileofnamesintoanarray,thenreturnthatarra ... [详细]
  • import

    开发笔记:加密&json&StringIO模块&BytesIO模块

    开发笔记:加密&json&StringIO模块&BytesIO模块
    篇首语：本文由编程笔记#小编为大家整理，主要介绍了加密&json&StringIO模块&BytesIO模块相关的知识，希望对你有一定的参考价值。一、加密加密 ... [详细]
  • hash

    Java工具类库Hutool介绍及功能概述

    本文介绍了Java工具类库Hutool，该工具包封装了对文件、流、加密解密、转码、正则、线程、XML等JDK方法的封装，并提供了各种Util工具类。同时，还介绍了Hutool的组件，包括动态代理、布隆过滤、缓存、定时任务等功能。该工具包可以简化Java代码，提高开发效率。 ... [详细]
  • hash

    Mac OS 升级到11.2.2 Eclipse打不开了，报错Failed to create the Java Virtual Machine

    Mac OS 升级到11.2.2 Eclipse打不开了，报错Failed to create the Java Virtual Machine
    本文介绍了在Mac OS升级到11.2.2版本后，使用Eclipse打开时出现报错Failed to create the Java Virtual Machine的问题，并提供了解决方法。 ... [详细]
  • hash

    java 线程死锁模拟

    1，关于死锁的理解死锁，我们可以简单的理解为是两个线程同时使用同一资源，两个线程又得不到相应的资源而造成永无相互等待的情况。 2，模拟死锁背景介绍：我们创建一个朋友 ... [详细]
  • import

    java 模拟get post请求_Java后台模拟发送http的get和post请求，并测试

    个人学习使用：谨慎参考1Client类importcom.thoughtworks.gauge.Step;importcom.thoughtworks.gauge.T ... [详细]
  • runtime

    Android源码深入理解JNI技术的概述和应用

    Android源码深入理解JNI技术的概述和应用
    本文介绍了Android源码中的JNI技术，包括概述和应用。JNI是Java Native Interface的缩写，是一种技术，可以实现Java程序调用Native语言写的函数，以及Native程序调用Java层的函数。在Android平台上，JNI充当了连接Java世界和Native世界的桥梁。本文通过分析Android源码中的相关文件和位置，深入探讨了JNI技术在Android开发中的重要性和应用场景。 ... [详细]
  • runtime

    C#导入、导出功能

    导出功能protectedvoidbtnExport(objectsender,EventArgse){用来打开下载窗口stringfileName中 ... [详细]
  • import

    （三）多表代码生成的实现方法

    （三）多表代码生成的实现方法
    本文介绍了一种实现多表代码生成的方法，使用了java代码和org.jeecg框架中的相关类和接口。通过设置主表配置，可以生成父子表的数据模型。 ... [详细]
  • hash

    GreenDAO快速入门

    GreenDAO快速入门
    前言之前在自己做项目的时候，用到了GreenDAO数据库，其实对于数据库辅助工具库从OrmLite，到litePal再到GreenDAO，总是在不停的切换，但是没有真正去了解他们的 ... [详细]
  • import

    Java中的Hashtable clear()方法详解

    本文介绍了Java中Hashtable的clear()方法，该方法用于清除和移除指定Hashtable中的所有键。通过示例程序演示了clear()方法的使用。 ... [详细]
author-avatar
好人森森_195
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有