解析Excel数据并生成SQL语句

Posted on 2023年5月6日 by hackdl

涉及到的Maven依赖

		
			com.alibaba
			easyexcel
			2.2.11
		
    
			com.github.jsqlparser
			jsqlparser
			4.2
		
    
			net.lingala.zip4j
			zip4j
			2.11.5
		
    
			commons-io
			commons-io
			2.6

1、定义数据类型枚举

public enum DataType {
    VARCHAR,NUMERIC,DECIMAL,DATE,DATE_TIME;
}

2、添加申明的表格

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Table {

    String value() ;
}

3、添加声明数据库列的注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface Column {

    /**
     * 表里的列名
     * @return
     */
    String column() ;
    /**
     * 列的数据类型
     */
    DataType type() default DataType.VARCHAR ;
    /**
     * 是否允许为空
     */
    boolean allowNull() default false ;
    /**
     * 时间的格式化
     */
    String pattern() default "yyyy-MM-dd HH:mm:ss" ;
}

4、添加关键字

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface Key {

}

5、定义抽象的基础实体

public abstract class BaseModel {

    //年度
    @ExcelProperty("年度编码(year_code)")
    @Column(column = "year_code",type = DataType.NUMERIC,allowNull = false)
    @Key
    private String yearCode ;

    //数字产品码(product_code)
    @ExcelProperty("数字产品码(product_code)")
    @Column(column = "product_code",type = DataType.VARCHAR,allowNull = false)
    @Key
    private String productCode ;

    //价值编码(value_code) . 作为一条数据的唯一标识
    @ExcelProperty("价值编码(value_code)")
    @Column(column = "value_code",type = DataType.VARCHAR,allowNull = false)
    @Key
    private String valueCode ;
    
    //忽略getter和setter方法
}

6、添加一个实体类

@Table("mcp_measure_base_improve")
public class MeasureBaseImprove extends BaseModel   {

    //提效科目编码(subject_code)
    @ExcelProperty("提效科目编码(subject_code)")
    @Column(column = "subject_code",type = DataType.VARCHAR)
    private String subjectCode ;

    //提效科目(subject)
    @ExcelProperty("提效科目(subject)")
    @Column(column = "subject",type = DataType.VARCHAR)
    private String subject ;

    //无数字化工时(no_digit_manhour)
    @ExcelProperty("无数字化工时(no_digit_manhour)")
    @Column(column = "no_digit_manhour",type = DataType.DECIMAL)
    private String noDigitManhour ;

    //有数字化工时(digit_manhour)
    @ExcelProperty("有数字化工时(digit_manhour)")
    @Column(column = "digit_manhour",type = DataType.DECIMAL)
    private String digitManhour ;

    //预估提效单量(estimate_improve_num)
    @ExcelProperty("预估提效单量(estimate_improve_num)")
    @Column(column = "estimate_improve_num",type = DataType.DECIMAL)
    private String estimateImproveNum ;

    //提效目标(improve_target)
    @ExcelProperty("提效目标(improve_target)")
    @Column(column = "improve_target",type = DataType.DECIMAL)
    private String improveTarget ;
    
    //忽略getter和setter方法

}

7、创建生成SQL的工具

public class SqlBuilder {

    /**
     * 获取本类及其父类的字段属性
     * @param clazz 当前类对象
     * @return 字段数组
     */
    public static List getAllFields(Class> clazz) {
        List fieldList = new ArrayList();
        while (clazz != null){
            clazz = clazz.getSuperclass();
            fieldList.addAll(new ArrayList(Arrays.asList(clazz.getDeclaredFields())));
        }
        return fieldList ;
    }

    public static class FiledInfo{
        protected final Map fileColumnMap ;
        protected final Map keyMap ;

        protected final String tableName ;

        public FiledInfo(String tableName,Map fileColumnMap,Map keyMap){
            this.tableName = tableName ;
            this.fileColumnMap = fileColumnMap ;
            this.keyMap = keyMap ;
        }
    }

    public static final  FiledInfo getFiledInfo(Class clazz){
        Table table = clazz.getAnnotation(Table.class) ;
        if(table == null){
            throw new IllegalArgumentException("类型上没有Table注解") ;
        }
        //获取当前类的所有字段
        List fieldList = getAllFields(clazz) ;
        Map fileColumnMap = new HashMap() ;
        Map keyMap = new HashMap() ;
        //遍历每个字段上的注解
        fieldList.forEach(field -> {
            Column column = field.getAnnotation(Column.class) ;
            if(column == null){
                return;
            }
            if(StringUtils.isBlank(column.column())){
                throw new IllegalArgumentException("注解属性有误") ;
            }
            field.setAccessible(true);
            fileColumnMap.put(field,column) ;

            Key key = field.getAnnotation(Key.class) ;
            if(key != null){
                keyMap.put(field,key) ;
            }
        });
        return new FiledInfo(table.value(),fileColumnMap,keyMap) ;
    }

    public static class SQLResult{
        public final List insertSqlList  ;
        public final List updateSqlList ;
        public final List deleteSqlList  ;


        public SQLResult(List insertSqlList ,List updateSqlList,List deleteSqlList){
            this.insertSqlList = insertSqlList ;
            this.updateSqlList = updateSqlList ;
            this.deleteSqlList = deleteSqlList ;
        }
    }

    public static final   SQLResult buildSQL(List dataList,Class clazz){
        //获取注解信息
        FiledInfo filedInfo = getFiledInfo(clazz) ;
        Map fileColumnMap = filedInfo.fileColumnMap ;
        Map keyMap = filedInfo.keyMap ;

        final List insertSqlList = new ArrayList() ;
        final List updateSqlList = new ArrayList() ;
        final List deleteSqlList = new ArrayList() ;

        List insertList = new ArrayList() ;
        List updateList = new ArrayList() ;
        List deleteList = new ArrayList() ;
        dataList.forEach(item->{
            Insert insert = new Insert().withTable(new net.sf.jsqlparser.schema.Table(filedInfo.tableName)) ;
            Update update = new Update().withTable(new net.sf.jsqlparser.schema.Table(filedInfo.tableName)) ;
            Delete delete = new Delete().withTable(new net.sf.jsqlparser.schema.Table(filedInfo.tableName)) ;
            //列
            List columnList = new ArrayList() ;
            //列所对应的值
            //MultiExpressionList valueList = new MultiExpressionList();
            ExpressionList valueList = new ExpressionList();
            //where条件表达式，可用于更新和删除
            List whereList = new ArrayList() ;

            fileColumnMap.forEach((field,column)->{
                //数据的值
                Object value = null ;
                try {
                    value = field.get(item) ;
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
                Expression valueExpression = getValueExpression(field,column,value) ;
                //////////////////更新数据的SQL//////////////
                Key key = keyMap.get(field) ;
                if(key != null) {
                    EqualsTo equalsTo = new EqualsTo(); // 等于表达式
                    equalsTo.setLeftExpression(new net.sf.jsqlparser.schema.Column(column.column()));
                    equalsTo.setRightExpression(valueExpression);
                    whereList.add(equalsTo) ;
                }else{
                    update.addUpdateSet(new net.sf.jsqlparser.schema.Column(column.column()),valueExpression);
                }
                ///////////新增数据的SQL////////
                columnList.add(new net.sf.jsqlparser.schema.Column().withColumnName(column.column())) ;
                valueList.addExpressions(getValueExpression(field,column,value)) ;
            });
            if(whereList.size() > 0) {
                updateList.add(update.withWhere(buildAndExpression(whereList)));
                deleteList.add(delete.withWhere(buildAndExpression(whereList)));
            }else{
                updateList.add(update);
                deleteList.add(delete);
            }
            insertList.add(insert.withColumns(columnList).withItemsList(valueList)) ;
        });

        insertList.forEach(insert -> {
            insertSqlList.add(insert.toString()+";") ;
        });
        updateList.forEach(insert -> {
            updateSqlList.add(insert.toString()+";") ;
        });
        deleteList.forEach(insert -> {
            deleteSqlList.add(insert.toString()+";") ;
        });
        return new SQLResult(insertSqlList,updateSqlList,deleteSqlList) ;
    }


    public static Expression buildAndExpression(List expressionList){
        Assert.notEmpty(expressionList,"创建AND标识的是子表达式不能为空");
        if(expressionList.size() == 1){
            return expressionList.get(0) ;
        }
        AndExpression andExpression = new AndExpression() ;
        andExpression.withLeftExpression(expressionList.get(0)).withRightExpression(expressionList.get(1)) ;
        for(int i=2;i

8、创建解析Excel的工具

public class ResolverUtil {
    
    //以同步的方式读取指定名称的sheet，并将解析的结果生成指定类型的对象
    public static  List listModel(File file,String sheetName,Class clazz){
        List dataList = EasyExcel.read(file, clazz,new CommEventListener())
                .autoCloseStream(false)
                .ignoreEmptyRow(true)
                .autoTrim(true)
                .sheet(sheetName)
                .headRowNumber(1)
                .doReadSync() ;  //同步方式执行
        return  dataList ;
    }


    /**
     * 解析成LinkedHashMap
     */
    public static List> listMapData(File file){
        //解析文件
        List> result = EasyExcel.read(file, new AnalysisEventListener>() {
            //此处为得到一行后的数据转换为实体类
            @Override
            public void invoke(Map rowData, AnalysisContext analysisContext) {
            }
            //全部读取完成之后执行函数
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
        }).autoCloseStream(true)
            .ignoreEmptyRow(true)
            .autoTrim(true)
            .doReadAllSync(); //同步的方式读取所有sheet
        return result ;
    }
}

9、解析文件并生成SQL

final List insertSqlList = new ArrayList() ;
final List updateSqlList = new ArrayList() ;
final List deleteSqlList = new ArrayList() ;

//解析表格的指定sheet
List productValList = ResolverUtil.listModel(targetFile,"04-数字化产品价值",MeasureBaseProductVal.class)
                .stream().filter(item->StringUtils.isNotBlank(item.getYearCode())).collect(Collectors.toList()) ; 

//生成SQL语句
SqlBuilder.SQLResult sqlResult = SqlBuilder.buildSQL(productValList,MeasureBaseProductVal.class) ;
insertSqlList.addAll(Optional.ofNullable(sqlResult.insertSqlList).orElse(new ArrayList())) ;
updateSqlList.addAll(Optional.ofNullable(sqlResult.updateSqlList).orElse(new ArrayList())) ;
deleteSqlList.addAll(Optional.ofNullable(sqlResult.deleteSqlList).orElse(new ArrayList())) ;

//写入文件
////新增语句////
File insertSqlFile=new File(dirFile,"dml-insert.sql");
FileUtils.writeLines(insertSqlFile,"UTF-8",insertSqlList);

////更新语句////
File updateSqlFile=new File(dirFile,"dml-update.sql");
FileUtils.writeLines(updateSqlFile,"UTF-8",updateSqlList);

////删除语句////
File deleteSqlFile=new File(dirFile,"dml-delete.sql");
FileUtils.writeLines(deleteSqlFile,"UTF-8",deleteSqlList);

///添加到压缩文件
ZipParameters zipParameters = new ZipParameters();
// 压缩方式
zipParameters.setCompressionMethod(CompressionMethod.DEFLATE);
// 压缩级别
zipParameters.setCompressionLevel(CompressionLevel.NORMAL);
//压缩文件
ZipFile zipFile = new ZipFile(dirFile + File.separator + ZIP_FILE_NAME ) ;
zipFile.addFiles(Arrays.asList(insertSqlFile,updateSqlFile,deleteSqlFile),zipParameters) ;

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.e1idc.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇: 交换机和路由器到底有什么区别？？？
下一篇: Java中顺序表详解