涉及到的Maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.11</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.2</version>
</dependency>
<dependency>
<groupId>net.lingala.zip4j</groupId>
<artifactId>zip4j</artifactId>
<version>2.11.5</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>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<Field> getAllFields(Class<?> clazz) {
List<Field> 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<Field,Column> fileColumnMap ;
protected final Map<Field,Key> keyMap ;
protected final String tableName ;
public FiledInfo(String tableName,Map<Field,Column> fileColumnMap,Map<Field,Key> keyMap){
this.tableName = tableName ;
this.fileColumnMap = fileColumnMap ;
this.keyMap = keyMap ;
}
}
public static final <T> FiledInfo getFiledInfo(Class<T> clazz){
Table table = clazz.getAnnotation(Table.class) ;
if(table == null){
throw new IllegalArgumentException("类型上没有Table注解") ;
}
//获取当前类的所有字段
List<Field> fieldList = getAllFields(clazz) ;
Map<Field,Column> fileColumnMap = new HashMap<>() ;
Map<Field,Key> 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<String> insertSqlList ;
public final List<String> updateSqlList ;
public final List<String> deleteSqlList ;
public SQLResult(List<String> insertSqlList ,List<String> updateSqlList,List<String> deleteSqlList){
this.insertSqlList = insertSqlList ;
this.updateSqlList = updateSqlList ;
this.deleteSqlList = deleteSqlList ;
}
}
public static final <T> SQLResult buildSQL(List<T> dataList,Class<T> clazz){
//获取注解信息
FiledInfo filedInfo = getFiledInfo(clazz) ;
Map<Field,Column> fileColumnMap = filedInfo.fileColumnMap ;
Map<Field,Key> keyMap = filedInfo.keyMap ;
final List<String> insertSqlList = new ArrayList<>() ;
final List<String> updateSqlList = new ArrayList<>() ;
final List<String> deleteSqlList = new ArrayList<>() ;
List<Insert> insertList = new ArrayList<>() ;
List<Update> updateList = new ArrayList<>() ;
List<Delete> 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<net.sf.jsqlparser.schema.Column> columnList = new ArrayList<>() ;
//列所对应的值
//MultiExpressionList valueList = new MultiExpressionList();
ExpressionList valueList = new ExpressionList();
//where条件表达式，可用于更新和删除
List<Expression> 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<Expression> 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<expressionList.size();i++){
AndExpression itemExpression = new AndExpression() ;
itemExpression.withLeftExpression(andExpression).withRightExpression(expressionList.get(i));
andExpression = itemExpression ;
}
return andExpression ;
}
private static Expression getValueExpression(Field field, Column column, Object value){
//字段的类型
String typeName = field.getGenericType().getTypeName() ;
if(value == null){
//如果对象中的值为空，则先判断列是否允许为空
if(!column.allowNull()){
throw new IllegalArgumentException("列"+column.column()+"不允许为空") ;
}
return new NullValue();
}
//数据库中列的值
if(column.type() == DataType.VARCHAR){
return new StringValue(value.toString());
}
if(column.type() == DataType.NUMERIC){
return new LongValue(value.toString()) ;
}
if(column.type() == DataType.DECIMAL){
return new DoubleValue(value.toString()) ;
}
if(column.type() == DataType.DATE){
return getDateExpression(field,column,value) ;
}
if(column.type() == DataType.DATE_TIME){
return getDateTimeExpression(field,column,value) ;
}
throw new RuntimeException("不支持的类型") ;
}
private static Expression getDateExpression(Field field,Column column,Object value){
//字段的类型
String typeName = field.getGenericType().getTypeName() ;
if(StringUtils.equals(typeName,"java.util.Date")){
long t = ((Date)value).getTime() ;
return new DateValue().withValue(new java.sql.Date(t));
}
if(StringUtils.equals(typeName,"java.sql.Date")){
return new DateValue().withValue((java.sql.Date)value);
}
if(StringUtils.equals(typeName,"java.lang.String")){
//格式
String format = column.pattern() ;
Assert.notNull(format,"时间格式化模型不能为空");
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format) ;
TemporalAccessor temporal = formatter.parse(value.toString()) ;
//解析出的时间
LocalDateTime localDateTime = LocalDateTime.from(temporal) ;
return new DateValue()
.withValue(new java.sql.Date(localDateTime.toEpochSecond(ZoneOffset.from(temporal))));
}
throw new RuntimeException("不支持的时间类型") ;
}
private static Expression getDateTimeExpression(Field field,Column column,Object value){
//字段的类型
String typeName = field.getGenericType().getTypeName() ;
if(StringUtils.equals(typeName,"java.util.Date")){
long t = ((Date)value).getTime() ;
return new TimestampValue().withValue(new java.sql.Timestamp(t));
}
if(StringUtils.equals(typeName,"java.sql.Timestamp")){
return new TimestampValue().withValue((java.sql.Timestamp)value);
}
if(StringUtils.equals(typeName,"java.lang.String")){
//格式
String format = column.pattern() ;
Assert.notNull(format,"时间格式化模型不能为空");
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format) ;
TemporalAccessor temporal = formatter.parse(value.toString()) ;
//解析出的时间
LocalDateTime localDateTime = LocalDateTime.from(temporal) ;
return new TimestampValue()
.withValue(new java.sql.Timestamp(localDateTime.toEpochSecond(ZoneOffset.from(temporal))));
}
throw new RuntimeException("不支持的时间类型") ;
}
}8、创建解析Excel的工具
public class ResolverUtil {
//以同步的方式读取指定名称的sheet，并将解析的结果生成指定类型的对象
public static <T> List<T> listModel(File file,String sheetName,Class<T> clazz){
List<T> dataList = EasyExcel.read(file, clazz,new CommEventListener())
.autoCloseStream(false)
.ignoreEmptyRow(true)
.autoTrim(true)
.sheet(sheetName)
.headRowNumber(1)
.doReadSync() ; //同步方式执行
return dataList ;
}
/**
* 解析成LinkedHashMap
*/
public static List<Map<Integer,String>> listMapData(File file){
//解析文件
List<Map<Integer,String>> result = EasyExcel.read(file, new AnalysisEventListener<Map<Integer, String>>() {
//此处为得到一行后的数据转换为实体类
@Override
public void invoke(Map<Integer, String> rowData, AnalysisContext analysisContext) {
}
//全部读取完成之后执行函数
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}).autoCloseStream(true)
.ignoreEmptyRow(true)
.autoTrim(true)
.doReadAllSync(); //同步的方式读取所有sheet
return result ;
}
}9、解析文件并生成SQL
final List<String> insertSqlList = new ArrayList<>() ;
final List<String> updateSqlList = new ArrayList<>() ;
final List<String> deleteSqlList = new ArrayList<>() ;
//解析表格的指定sheet
List<MeasureBaseProductVal> 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) ;
