Excel2Table框架搭建 - Excel文件与数据库相互转换的工具

news/2024/7/21 6:50:53 标签: excel, 数据库, java

Excel2Table框架搭建

毕设某模块，源码暂不公开。但总体实现，在实现原理一节基本叙述完毕。
本文仅是初期实现的记录分享。

1. 基本特性：

对于简单的Excel文件（第一行为列名，后续为数据）

  • 支持根据excel文件动态建表
  • 支持根据表数据生成excel文件
  • 实现一一对应：Java对象实体 - 数据库表 - Excel文件
  • 实现运营人员与开发工具相隔离，让医生更专注在工作上，让开发人员用最少的精力去维护系统。
  • Android端、Java后端代码通用

2. 基本思路：

请添加图片描述

3.基本使用：

  1. 引入初版实现 excel2table 框架
  2. 与 EasyExcel 等 Excel 解析工具 配合使用

3.1 场景说明

目前需要上传的Excel为（Excel文件截图）：

请添加图片描述

JavaBean（将要生成字节码用）为：

java">@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("dian_temp_excel_vo")
public class TempExcelVo {

    @TableColumn(name="col1")
    private String x;

    @TableColumn(name = "col2")
    private String y;

    @TableColumn(name = "col3")
    private String z;

}

其中变量名被混淆了（这也是为什么要用注解的原因）。将该Java文件编译成为字节码文件，上传到服务器的某个文件存储位置。

3.2 上传Excel文件，并解析

这里模拟直接获取到了字节码所在的路径，客户端上传了一个excel文件，需要进行解析处理：

java">@PostMapping("upload1")
public void upload1(@RequestParam("excel") MultipartFile file){
    //path为字节码所在的父路径
    String path = "D:/JavaProjects/clinic/target/classes";//父路径
    //name为字节码的全类名
    String name = "com.dian.clinic.entity.vo.TempExcelVo";//全类名
    //ExcelTableGenerator是上传入口
    ExcelTableGenerator generator = new ExcelTableGenerator(clazzTableService);
    //加载字节码，顺带建表
    generator.loadClass(path, name, new ExcelTableGenerator.IClzTableService.TableCreateCallback() {
        @Override
        public void finish(Class<?> clz, String tableName) {
            //回调 - 建表完成
            //建表完成之后，通过EasyExcel解析excel，将解析后的数据写入表中
            try {
                //文件输入流
                InputStream is = file.getInputStream();
                //不带id的读取，和excel头保持一致
                EasyExcel.read(is, clz, new ExcelUploadListener(clz)).sheet().doRead();
            }catch (Exception e){
                //                    e.printStackTrace();
                System.out.println("上传文件出错");
            }
            System.out.println("上传excel解析完成");;
        }

        @Override
        public void error() {
            System.out.println("上传文件出错");
        }
    });
}

执行会做两件事：

1.如果 @TableName 注解中的表名不存在，将会建表，同时通知需要记录 class-table 数据的表更新信息（Navicat界面截图）：

请添加图片描述

2.将Excel数据解析，并填入表中（Navicat界面截图）

请添加图片描述

3.3 下载Excel文件

通过Excel2Table工具读取表数据，再通过EasyExcel生成Excel文件

java">@PostMapping("download1")
public void download1(HttpServletResponse response){
    try{
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("TempExcel", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        TableManager tableManager = TableManager.getInstance();
        //传入一个Class<>对象去解析head
        List<List<String>> head = tableManager.getHead(TempExcelVo.class);
        EasyExcel.write(response.getOutputStream())
            .head(head)
            .sheet("模板")
            .doWrite(tableManager.search(TempExcelVo.class));//list
    }catch (Exception e){
        e.printStackTrace();
    }
}

EasyExcel生成Excel文件需要head，这可以是List<List<String>> 类型的，对应的写入的数据 data 需要是 List 类型的。本场景下：

  • head为：[[col3], [col2], [col1]
  • data 为: [[内容3,内容2,内容1],[内容6,内容5,内容4],[内容9,内容8,内容7]]

解析后，生成文件：

请添加图片描述

4. 实现原理

4.1 注解与反射

希望能有一个模板来建表，我们可以通过反射成员变量的名字来建表：

java">//直接使用模板的成员变量名来建表
public int createTableFromClazz(Class<?> clazz,String tableName){
        //为了简单起见，类变量的名字直接转成column名，可以在这里插入驼峰转换的工具
        Map<String,Class<?>> map = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        Field cur;
        StringBuilder sb = new StringBuilder();//默认初始值，不希望提前占过多内存
        //可能要拼接 if exists,这里暂时不拼接
        sb.append("create table ")
                .append(tableName)
                .append("(\n");
        //给个默认升序id
        sb.append("id int not null auto_increment primary key,");
        for (int i = 0; i < fields.length; i++) {
            cur = fields[i];
            cur.setAccessible(true);
            //拿到名字
            String colName = cur.getName();
            //拿到类型,简便起见全都转为String类型
            //Class<?> type = declaredField.getType();
            sb.append(colName)
                    .append(" ")
                    .append(sqlMap.get(String.class));
            if (i <fields.length-1){
                sb.append(",\n");
            }else{
                sb.append(");");
            }
        }
        //打印建表语句
        System.out.println(sb.toString());
        //建表语句拼接完成,开始建表
        String sqlStatement = sb.toString();
        int createTableResult = CRUDTemplate.executeUpdate(new SqlParam(sqlStatement));
        if (createTableResult==0){
            //建表失败
//            System.out.println("建表失败");
        }
        return createTableResult;
    }

但是这样就带来一个坏处，一方面模板可能会经过代码混淆，字节码中变量名被修改。另一方面，模板类无法再引入其他变量。为了拓展性考虑，使用注解进行标注信息：

  • @TableName : 模板类对应的表名
  • @TableColumn: 某个列名，机器对应的表属性
java">//TableName
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableName {
    String value();//tableName
}

java">//TableColumn
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableColumn {
    //表名
    String name();
    //类型,默认是字符串类型
    String type() default Type.STR;
    //长度，默认100
    int len() default 100;
    //如果需要，可以增加主键，excel暂时不需要
}

使用注解还有一个好处，易拓展，可以在原有基础上，增加可以表示的表信息。（如果直接在TableColumn上做修改的话，就违反了开闭原则，这里的“拓展性”只体现在开发框架的时候，并不体现在程序运行中）

有了这些注解信息，我们就可以通过反射来根据表名、列信息来建表。在此之前，我们还需要将字节码文件加载入系统，所以我们需要自定义类加载器：

java">public class ExcelClassLoader extends ClassLoader {
    
    /**
     * 加载二进制数据
     * @param path 文件所在父路径（注意是全类名之上的父路径）
     * @param name 全类名转为文件名  com.company.User->com/company/User 
     * @return
     */
    private byte[] loadByte(String path,String name) throws Exception{
        name = name.replaceAll("\\.","/");
        //ava.io.FileNotFoundException: D:\JavaProjects\clinic\target\classes\com\dian\clinic\entity\vo\TempExcelVoBeanInfo.class (系统找不到指定的文件。)
        FileInputStream fis = new FileInputStream(path+"/"+name+".class");
        int len = fis.available();
        byte[] data = new byte[len];
        fis.read(data);
        fis.close();
        return data;
    }


    //生成Class<>对象
    public Class<?> findClass(String path,String name) throws ClassNotFoundException{
        try{
            byte[] data = loadByte(path,name);
            return defineClass(name,data,0,data.length);
        }catch (Exception e){
            //e.printStackTrace();
            throw new ClassNotFoundException();
        }
    }
    
}

原理很简单，通过文件输入流，直接读取字节码的二进制串，通过defineClass生成一个Class<>对象，如果需要拓展，可以增加网络输入流等方式，增加字节码传输方式。

类加载完成后，就可以进行反射解析注解，并且建表了，这部分逻辑和直接反射成员变量拼接sql语句是几乎类似的。经过实验是可行的，但是问题来了，反射是非常消耗性能的，如何解决这个问题？在文章 Retrofit中的反射耗时，罪魁祸首并不在动态代理，而在反射注解 中，讨论了反射优化。我们通过添加缓存，将那些统一的反射逻辑封装到一个模板中，未来就根据这个模板来创建实例。

4.2 反射优化

我们建立一个Template作为可以用来缓存的模板：

java">//模板类，用于缓存
public class Template {
    //同时也缓存了所有Template
    //LRU，便于FullGC释放元空间的Class对象
    private static final Map<Class<?>, Template> cache = new LruMap(50);

    private String tableName;

    //tableName - field - 用来反向构建一个对象
    private final Map<String, Field> mTableNameFieldMap;
    //field  - annotation - 用于拼接sql语句
    private final Map<Field, TableColumn> mAnnotationMap;



    private Template() {
        //外部自己维持好并发问题
        this.mTableNameFieldMap = new HashMap<>();
        this.mAnnotationMap = new HashMap<>();
    }

    //假单例
    //开始解析
    public static Template parse(Class<?> clz) {

        //先看有没有缓存，优先使用缓存
        //考虑并发安全
        if (cache.containsKey(clz)) {
            return cache.get(clz);
        } else {
            //DCL，不用加volatile了，已经final可见性了
            synchronized (Template.class){
                if (!cache.containsKey(clz)) {
                    //反射
                    Template t = parse0(clz);
                    //计入缓存
                    cache.put(clz, t);
                    return t;
                }else{
                    return cache.get(clz);
                }
            }
        }
    }

    
    private static Template parse0(Class<?> clz) {
        //必须要有表名
        TableName tableNameAnnotation = clz.getAnnotation(TableName.class);
        if (tableNameAnnotation==null){
            throw new RuntimeException("模板必须要有tableName");
        }
        Template t = new Template();
        //设置表名
        t.tableName = tableNameAnnotation.value();
        //反射
        Field[] fields = clz.getDeclaredFields();
        //遍历，查看其注解
        for (Field field : fields) {
            //置为可访问
            field.setAccessible(true);
            TableColumn annotation = field.getAnnotation(TableColumn.class);
            //如果没有这个注解，跳过，不处理
            if (annotation == null) continue;
            //如果有，获取表名
            String tableName = annotation.name();
            t.mTableNameFieldMap.put(tableName, field);
            t.mAnnotationMap.put(field,annotation);
        }
        //解析完毕
        return t;
    }

    public Map<String, Field> getmTableNameFieldMap() {
        return mTableNameFieldMap;
    }

    public Map<Field, TableColumn> getmAnnotationMap() {
        return mAnnotationMap;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    private static class LruMap extends LinkedHashMap<Class<?>, Template> {
        int capacity;

        public LruMap(int initialCapacity) {
            super(initialCapacity);
            this.capacity = initialCapacity;
        }

        //超过了就删掉最少访问的
        @Override
        protected boolean removeEldestEntry(Map.Entry<Class<?>, Template> eldest) {
            return size()>capacity;
        }

        @Override
        public Template remove(Object key) {
            //截获删除的节点，从其他map中把它也删掉
            //其实也不用截获，它本身被删除之后就没有引用了
            //如果还有引用，在这里要做后续断掉引用的操作，避免内存泄漏
            Template removed = super.remove(key);
            return removed;
        }
    }
}

这里我使用LinkedHashMap的LRU实现，来进行缓存处理，因为我们知道，类的加载过程中，类信息、Class对象都会存在本地内存（元空间）中，程序运行地越久，加载的类越多，当类达到一定量的时候，就会触发 FullGC，此时可能会清理本地内存（元空间）中的数据，如果这些数据还被持有强引用，将不会被清理，这是非常危险的。此外，LinkedHashMap缓存将会跑在内存中，如果体积太大，也是不好的。所以我们给它限制了 50 的大小。通过LRU（最近最少使用删除调度）来控制缓存大小。

Template类中的LinkedHashMap是全局缓存，存了所有的Template。

每个Template实体，作为模板，保存了反射到的信息，例如表名、注解实体。后续可以通过引用实例的方式，而不是反射的方式进行这些信息的访问，提高效率。

4.3 建表

有了Template，我们就可以进行建表了，将这部分逻辑解耦到两处：

  • TableManager 负责上层调度，发起建表、填充数据、数据查询
  • CRUDTemplate 进行实际的数据库操作逻辑

CRUDTemplate很简单了，就是通过JDBC访问数据库，通过sql语句来执行数据库操作。我们直接看到TableManager中是如何通过Template进行建表的：

java">//TableManager.java
/**
  * 根据解析出来的Template进行建表
  */
public Template createTableFromTemplate(Class<?> clz){
    //解析clz，可能会复用之前的template
    Template template = Template.parse(clz);
    //开始建表
    Map<Field, TableColumn> map = template.getmAnnotationMap();
    StringBuilder sb = new StringBuilder();//默认初始值，不希望提前占用过多内存
    sb.append("create table ")
        .append(template.getTableName())
        .append("(\n");
    //给个默认升序id,这里可以根据业务逻辑修改，或者通过某种设计模式将此逻辑抛出去给上层动态设置。更适配开闭原则
    sb.append("id int not null auto_increment primary key,\n");
    //开始拼接建表语句
    Iterator<Map.Entry<Field, TableColumn>> iterator = map.entrySet().iterator();
    Map.Entry<Field, TableColumn> cur;//写在外面避免内存抖动
    while (iterator.hasNext()){
        cur = iterator.next();
        TableColumn anno = cur.getValue();
        //例如"phone varchar(20)"
        sb.append(anno.name())//这个是必须由的值
            .append(" ")
            .append(anno.type())//这个如果没有设置，有默认值varchar
            .append("(")
            .append(anno.len())//这个如果没有设置，有默认值100
            .append(")");
        if (iterator.hasNext()){
            sb.append(",\n");
        }else{
            sb.append(");\n");
        }
    }
    //打印建表语句
    System.out.println(sb.toString());

    //拼接完成，开始建表
    String sqlStatement = sb.toString();
    int i = CRUDTemplate.executeUpdate(new SqlParam(sqlStatement));

    System.out.println(i);

    return template;
}

4.4 查表

我们只需要根据模板来判断生成一个Excel文件需要哪几列数据，拼接出sql语句，剩下的就交给CRUDTemplate工具了：

java">public List<List<Object>> search(Class<?> clz){
    Template template = Template.parse(clz);
    String tableName = template.getTableName();

    StringBuilder sb = new StringBuilder();
    //先从缓存中拿sql语句
    if (searchSqlCache.containsKey(clz) && searchColumnCache.containsKey(clz)){
        sb.append(searchSqlCache.get(clz));
    }else {
        //拿不到才拼接，并添加到缓存中
        Map<Field, TableColumn> map = template.getmAnnotationMap();
        sb.append("select ");
        Iterator<Map.Entry<Field, TableColumn>> iterator = map.entrySet().iterator();
        ArrayList<String> columnName = new ArrayList<>();
        while (iterator.hasNext()) {
            Map.Entry<Field, TableColumn> next = iterator.next();
            String name = next.getValue().name();
            columnName.add(name);
            sb.append(name);
            if (iterator.hasNext()) {
                sb.append(",");
            } else {
                sb.append(" ");
            }
        }
        sb.append("from ");
        sb.append(tableName);
        searchSqlCache.put(clz,sb.toString());
        searchColumnCache.put(clz,columnName);
    }

    List<List<Object>> result = CRUDTemplate.executeQuery(new SqlParam(sb.toString()), new IResultSetHandler() {
        @Override
        public List handle(ResultSet... resultSets) throws Exception {
            ResultSet res = resultSets[0];
            List<List<Object>> list = new ArrayList<>();
            while (res.next()){
                ArrayList<Object> arr = new ArrayList<>();

                List<String> columnName = searchColumnCache.get(clz);

                for (int i = 0; i < columnName.size(); i++) {
                    arr.add(res.getObject(columnName.get(i)));
                }
                list.add(arr);
            }
            return list;
        }
    });
    return result;
}

4.5 TableManager优化

虽然有了Template缓存优化，但是发现每次都需要拼接sql语句，如果某个Template用的很频繁，将会出现重复拼接之前已经拼接过的sql语句，这是无用功，我们一样可以通过缓存复用之前拼接过的sql语句：

java">public class TableManager {
    //sql语句缓存
    //insert
    Map<Class<?>,String> insertSqlCache = new LruMap<>(50);
	//query
    Map<Class<?>,String> searchSqlCache = new LruMap<>(50);
    Map<Class<?>,List<String>> searchColumnCache = new LruMap<>(50);
    private TableManager(){}
    
    private static class LruMap<K,V> extends LinkedHashMap<K,V>{
        int capacity;

        public LruMap(int initialCapacity) {
            super(initialCapacity);
            this.capacity = initialCapacity;
        }

        //超过了就删掉最少访问的
        @Override
        protected boolean removeEldestEntry(Map.Entry<K,V> eldest) {
            return size()>capacity;
        }

        @Override
        public V remove(Object key) {
            //截获删除的节点，从其他map中把它也删掉
            //其实也不用截获，它本身被删除之后就没有引用了
            //如果还有引用，在这里要做后续断掉引用的操作，避免内存泄漏
            V removed = super.remove(key);
            return removed;
        }
    }
}

5. 待优化

  • 架构模块分工并不清晰
  • 查表逻辑不够开放，需要改进
  • 性能与使用复杂数据库查询条件实现同样效果来说有一定差距。

http://www.niftyadmin.cn/n/143057.html

相关文章

cmake简明教程：关键语法分析

cmake简明教程：关键语法分析

CMake是一个跨平台的安装/编译工具&#xff0c;它能用简单的语句描述所有平台的安装/编译过程。通常在Linux环境下&#xff0c;CMake根据CMakeLists.txt生成Makefile&#xff0c;在Makefile中定义了具体的编译过程。一个简单的CMakeLists.txt文件如下所示&#xff1a;cmake_min…
阅读更多...
APP自动化基础知识

APP自动化基础知识

一、adb常用命令 及 Appium的元素定位工具 以下命令均在cmd中进行操作 连接夜神模拟器&#xff1a; adb connect 127.0.0.1:62001查看连接的设备&#xff1a; adb devices查看被测app的包名以及界面名 adb shell dumpsys window windows | findstr mFocusedApp元素常见的几个…
阅读更多...
从GPT到GPT-3：自然语言处理领域的prompt方法

从GPT到GPT-3：自然语言处理领域的prompt方法

❤️觉得内容不错的话&#xff0c;欢迎点赞收藏加关注&#x1f60a;&#x1f60a;&#x1f60a;&#xff0c;后续会继续输入更多优质内容❤️&#x1f449;有问题欢迎大家加关注私戳或者评论&#xff08;包括但不限于NLP算法相关&#xff0c;linux学习相关&#xff0c;读研读博…
阅读更多...
强网杯2022 pwn 赛题解析——UserManager

强网杯2022 pwn 赛题解析——UserManager

刚刚结束的2022年强网杯中有一道题用到了musl libc&#xff0c;但是之前没有接触过&#xff0c;只能遗憾跳过。本文根据musl libc 1.2.2的源码&#xff0c;和赛题本身&#xff0c;学习一下musl libc的利用方式。 musl libc 是一种轻量级的libc&#xff0c;可以用于嵌入式设备等…
阅读更多...
QT学习记录（二）QT基本功能

QT学习记录（二）QT基本功能

按照下面两个教程学习 QT学习教程&#xff08;全面&#xff09;_Strive--顾的博客-CSDN博客_qt学习 天山老妖S的博客_QT开发(3)_51CTO博客 1、pdb文件 VS2017配置QT环境(详细版)_Wi~的博客-CSDN博客_vs2017配置qt 解决 VS 配置 Qt 报错&#xff1a;未加载XXX.pdb 以及 找不到…
阅读更多...
GZ80几个会员表说明

GZ80几个会员表说明

MemberSettlement&#xff08;会员充值表&#xff09; 上表中会员会员充值了2笔&#xff0c;共1000元。 Retail&#xff08;零售表&#xff09; PayKindID结算方式&#xff1a; 0不区分 1现金 2支票 3会员卡余额结算&#xff08;PayKindID3且PayByBalanceFlag1时&#xff0…
阅读更多...
企业管理培训班哪家好？

企业管理培训班哪家好？

对于企业管理培训班哪个好这个问题&#xff0c;我想我还是比较有发言权的。毕竟之前一直在公司人力资源部门做培训规划工作&#xff0c;大大小小各形各色的企业管理培训班接触了少。然后我本人也亲身参加过不少管理培训课程。我个人比较推荐看一些类似《经理人参阅&#xff1a;…
阅读更多...
伪分布式集群的环境搭建

伪分布式集群的环境搭建

本作者只是在这里做一个学习记录&#xff0c;也希望大家在安装伪分布式的时候能够帮助到大家。1.首先JAVA环境变量配置将JavaJDK传到虚拟机的/home/hadoop/software/目录下面&#xff08;这里用的是MobaXterm软件可以直接拖拉传文件&#xff0c;不要用WinSCP软件传Windows的文件…
阅读更多...
最新文章

Copyright @ 2022~2023