自定义注解+泛型+反射实现SQL拼接

简介:
生产需求,代码框架没有用到mybatis,采用SQL直接执行方式。因为需要对不同的表进行插入数据操作,这里通过自定义注解+泛型+反射工具类,实现智能化SQL拼接,代码封装思路挺好的,值得记录学习。

1.方法调用入口示例

    AtomicInteger failNum = new AtomicInteger();  if (dataList.size()>0){
        Lists.partition(dataList,100).parallelStream().forEach(l->{ try {
                SqlDataUtil<FinancingRequirement> util = new SqlDataUtil<>(FinancingRequirement.class);  String insertSql = util.getInsertSql(l, "XYD_UPLOAD_FINANCING_REQ");  Db.use(dataSource).execute(insertSql);  log.info("当前执行SQL-{}",insertSql);  }catch (Exception e){ failNum.set(failNum.get() + l.size());  log.error("当前报错数据如下-{}",l);  e.printStackTrace();  }
        });  } return "总数据" + dataList.size() + "条，入库成功" + (dataList.size()-failNum.get()) + "条!"; }

2.核心封装工具类

package com.ahzx.xed.util;

import cn.hutool.core.util.ReflectUtil;
import com.ahzx.system.annotations.SqlDeal;
import com.alibaba.fastjson.JSONObject;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Stream;

/**
 * SqlDataUtil
 *
 * @author jiangnan
 * @date 2022/6/1
 */
public class SqlDataUtil<T> {
    /**
     * 实体对象
     */
    public Class<T> clazz;

    /**
     * 表名称
     */
    private String tableName;

    /**
     * 数据列表
     */
    private List<T> list;

    public SqlDataUtil(Class<T> clazz) {
        this.clazz = clazz;
    }
    private void init(List<T> list, String tableName) {
        if (list == null) {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.tableName = tableName;
    }

    private String insertDeal() {
        StringBuilder insertBuffer = new StringBuilder("insert into ");
        StringBuilder keyBuffer = new StringBuilder("(");
        StringBuilder valueBuffer = new StringBuilder();
        Field[] fields = ReflectUtil.getFields(clazz);
        for (Field field : fields) {
            SqlDeal sqlDeal = field.getAnnotation(SqlDeal.class);
            if (sqlDeal!=null){
                keyBuffer.append("`").append(sqlDeal.column()).append("`").append(",");
            }
//            valueBuffer.append(field.getName()).append(",");
        }
        keyBuffer.replace(keyBuffer.length()-1, keyBuffer.length(), ")");
        for (T vo : list) {
            valueBuffer.append("(");
            for (Field field : fields) {
                if (field.getAnnotation(SqlDeal.class)!=null){
                    Object fieldValue = ReflectUtil.getFieldValue(vo, field);
                    valueBuffer.append(objectDeal(fieldValue)).append(",");
                }
            }
            valueBuffer.replace(valueBuffer.length()-1, valueBuffer.length(), "),");
        }
        valueBuffer.replace(valueBuffer.length()-1,valueBuffer.length(),"");
        insertBuffer.append(" ").append(tableName);
        insertBuffer.append(" ").append(keyBuffer);
        insertBuffer.append(" ").append("values");
        insertBuffer.append(" ").append(valueBuffer);
        return insertBuffer.toString();
    }
    public String getInsertSql(List<T> list, String tableName) {
        this.init(list, tableName);
        return insertDeal();
    }

    /**
     * 数据格式转换+脏数据过滤
     */
    private String objectDeal(Object fieldValue){
        if (fieldValue==null){
            return "null";
        } else {
            return "'"+fieldValue.toString().replaceAll("'","")+"'";
        }
    }

}