excel动态导入数据库---mysql

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

环境：

    jdk1.8+poi-3.17+mysql-5.6+excel2010(xlsx)

工具类：

    1.excel解析工具类，使用poi sax模式解析excel。生成数据格式为List<LinkedHashMap<String,String>>

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

                String value = null==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对应的数据表

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

package com.fdrx.model;

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

/**
 * Created by shea on 2019-06-05.
 */
@Data
@Entity
@Table(name ="t_fields")
public class ExcelBean implements Serializable {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id;
    @Column(length = 10)
    private String fcode;
    @Column(length = 10)
    private String fname;
    @Column(length = 20)
    private String dataType;
    @Column(length = 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.
 */
@Repository
public interface ExcelBeanDao extends JpaRepository<ExcelBean,Integer> {

}

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.
 */
@Service
@Slf4j
public class ExcelPipeline {
    @Resource
    private ExcelBeanDao excelBeanDao;
    private HashMap<String,String> allFields= new HashMap<>();
    /**
     * 写入数据库
     * @param datas excel解析数据
     * @param table 目标表
     * @return
     */
    public void saveData(List<LinkedHashMap<String, String>> datas,String table){

        //1.获取配置文件
        List<ExcelBean> all = excelBeanDao.findAll();
        //2.根据表 获取对应字段映射关系
        allFields=all.stream().filter(m -> table.equals(m.getFtable()))
                .collect(Collectors.toMap(ExcelBean::getFname,ExcelBean::getFcode,(k1, k2)->k2,LinkedHashMap::new));

        //3.根据数据库配置信息,生成建表sql
        String collect = all.stream().filter(m -> table.equals(m.getFtable()))
                .map(m -> String.format("`%s` %s comment '%s'",m.getFcode(), StringUtils.isEmpty(m.getDataType())?"text":m.getDataType(),m.getFname()))
                .collect(Collectors.joining(","));
        String createSql = String.format("create table IF NOT Exists %s (%s)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", table,collect);

        //4.根据excel标题 获取导入字段
        List<String> title = createTitle(datas.get(0));
        //5.根据导入字段，生成对应的导入数据列表
        List<List<String>> importLists = convert2List(datas,title);
        //6.根据导入字段，生成导入语句
        String inserSql = createSql(title, table);

        //7.开始msql导入
        Connection con=null;
        try {
            con = getConnector();//jdbc链接
            con.setAutoCommit(false);//不自动提交
            //指定建表语句，存在则不创建
            executeUpdate(createSql,new ArrayList<>(),con);
            for (List<String> strings : importLists) {
                try {
                    executeUpdate(inserSql, strings, con);
                } catch (SQLException e) {
                    log.error("{}=》写入失败！",strings.get(0));
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        }finally {
            try {
                if(con!=null){
                    con.setAutoCommit(true);
                    con.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
        log.info("导入完成");

    }

    /**
     * 执行 sql 更新
     * @param sql sql
     * @param params params
     * @param conn conn
     * @return
     * @throws SQLException
     */
    private void executeUpdate(String sql, List<String> params, Connection conn ) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)){
            int paramIdx = 1;
            for(Object obj: params){
                ps.setObject(paramIdx, obj);
                paramIdx ++;
            }
            ps.executeUpdate();
        }
    }

    /**
     * 根据excel数据生成插入语句
     */
    private String createSql( List<String> title,String table){
        Optional<String> sqlField = title.stream()
                .map(str -> String.format("`%s`", allFields.get(str.trim())))
                .filter(s->!"".equals(s)&& null!=s)
                .reduce((a, b) -> String.format("%s,%s", a, b));
        Optional<String> sqlValue=title.stream()
                .map(str -> String.format("`%s`", allFields.get(str.trim())))
                .filter(s->!"".equals(s)&& null!=s)
                .map(str->"?").reduce((a, b) -> String.format("%s,%s", a, b));
        String sql = String.format("replace into `%s`(%s) values(%s)",table, sqlField.orElse(""), sqlValue.orElse(""));
        return sql;
    }

    /**
     * 映射excel标题行 与 数据库配置信息===》确定要导入的数据列
     * @param headers
     * @return
     */
    private List<String> createTitle(LinkedHashMap<String,String> headers){
        return headers.keySet().stream()
                .filter(str -> allFields.get(str.trim())!=null)
                .collect(Collectors.toList());
    }

    /**
     * 转换数据list
     * @param datas excel数据
     * @param title 导入字段列表
     * @return
     */
    private List<List<String>> convert2List(List<LinkedHashMap<String,String>> datas,List<String> title){
        List<List<String>> collect = datas.stream().map(data -> {
            List<String> single = title.stream().map(data::get).collect(Collectors.toList());
            return single;
        }).collect(Collectors.toList());
        return collect;
    }
    /**
     * jdbc
     * @return
     * @throws SQLException
     */
    public  Connection getConnector() throws SQLException {
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/weixin?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String passwd = "root";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = 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.
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)//如果需要注入服务，则要启
@Slf4j
public class TestDemo {

    @Resource
    private JdbcPipeline jdbcPipeline;
    @Resource
    private ExcelPipeline excelPipeline;
    @Test
    public void test1()throws Exception{
        String table ="t_excel2";
        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\excel2.xlsx");
        //调用事件驱动解析excel
        Excel2007Parser parser = new Excel2007Parser(in);
        parser.parse();
        //解析结果
        List<LinkedHashMap<String, String>> datas = parser.getResults();
        excelPipeline.saveData(datas,table);
    }
}

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

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

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

 

 

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

    

#生成插入语句--修改
/**
     * 根据第一行生成插入语句
     */
    private String createSql( List<String> title,String table){
        Optional<String> sqlField = title.stream()
                .map(str -> String.format("`%s`", allFields.get(str.trim())))
                .filter(s->!"".equals(s)&& null!=s)
                .reduce((a, b) -> String.format("%s,%s", a, b));
        String sql = String.format("insert into `%s`(%s) values ",table, sqlField.get());
        return sql;
    }

#生成批量插入
private int batch = 1000;

 String inserSql = createSql(title, table);
        try {

            ArrayList<String> res = new ArrayList<>();
            //使用批量插入---每N条生成一个插入语句
            for (List<String> strings : lists) {

               String s = strings.stream().map(m -> String.format("'%s'", m)).reduce((x,y)->String.join(",",x,y))
                        .map(m -> String.format("(%s)", m)).get();
                res.add(s);
                if(res.size() % batch ==0){
                    String join = String.join(",", res);
                    executeUpdateBatch(inserSql+join, con);
                    res.clear();
                }

            }
            String join = String.join(",", res);
            executeUpdateBatch(inserSql+join, con);

            try {

                if(con!=null){
                    con.setAutoCommit(true);
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

/**
     * 执行 sql批量更新
     * @param sql sql
     * @param conn conn
     * @return
     * @throws SQLException
     */
    private void executeUpdateBatch(String sql, Connection conn ) throws SQLException {
        AtomicInteger count = new AtomicInteger(1);
        PreparedStatement ps = conn.prepareStatement(sql);
       ps.executeUpdate();
       log.info("第{}次提交！",count.getAndIncrement());
    }

 

耗时统计：

 

 

    测试数据：35个字段，共65330条数据

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

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

 

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

前两行为标题行，第三行为fcode，第四行为data_type.

你也可以直接用poi解析，生成 List<ArrayList<String>>的数据即可：第一行为标题fname，第二行为字段fcode，第三行为建表数据格式data_type.

String fieldsTable = "t_fields";//excel字段配置表
        String ftable ="t_excel4";//excel对应的table名称

        //todo Excel2007Parser解析默认第一行为标题好，数据从第二行开始计算的,所以excel中多定义一行标题
        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\t4.xlsx");
        Excel2007Parser parser = new Excel2007Parser(in);
        parser.parse();
        //解析结果
        List<LinkedHashMap<String, String>> datas = parser.getResults();

//直接转换为需要的数据格式
 List<List<String>> values= 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<ArrayList<String>> collect = datas.stream().map(m -> {
            return new ArrayList<>(m.values());
        }).collect(Collectors.toList());

        //实现行转列--生成插入需要的数据结构
        List<List<String>> values = 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 = String.format("insert into %s (`fname`,`fcode`,`data_type`,`ftable`) values (?,?,?,'%s')",fieldsTable,ftable);
        Connection conn = getcon();
        values.forEach(v-> {
            try {
                executeUpdate(sql,v,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });

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

1.引入拼音处理包

<dependency>
    <groupId>com.hankcs</groupId>
    <artifactId>hanlp</artifactId>
    <version>portable-1.7.2</version>
</dependency>

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

@Test
    public void createTable()throws Exception{
        String fieldsTable = "t_fields";//excel字段配置表
        String ftable ="t_excel6";//excel对应的table名称
        //todo Excel2007Parser解析默认第一行为标题好，数据从第二行开始计算的,所以excel中多定义一行标题
        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\t6.xlsx");
        Excel2007Parser parser = new Excel2007Parser(in);
        parser.parse();
        //解析结果
        List<LinkedHashMap<String, String>> datas = parser.getResults();

        LinkedHashMap<String, String> map = new LinkedHashMap<>();
        datas.get(0).keySet().forEach(k->{
            String fcode = HanLP.convertToPinyinFirstCharString(k, "", false);
            if(map.containsKey(fcode)){
                String code = assertLive(map, fcode, 1);
                map.put(code,k);
            }else {
                map.put(fcode,k);
            }
        });

        List<List<String>> values = map.entrySet().stream()
                .map(m -> Stream.of(m.getKey(), m.getValue(), "text").collect(Collectors.toList()))
                .collect(Collectors.toList());

        String sql = String.format("insert into %s (`fcode`,`fname`,`data_type`,`ftable`) values (?,?,?,'%s')",fieldsTable,ftable);

        Connection conn = getcon();
        values.forEach(v-> {
            try {
                executeUpdate(sql,v,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
        conn.close();


    }

    //递归查询，避免重复字段
    private String assertLive(Map<String,String> map,String code,int index){
        String suffix = String.valueOf(index);
        if(map.containsKey(code.concat(suffix))){
            index++;
            return assertLive(map,code,index);
        }
        return code.concat(suffix);

    }

结果：