Mybatis interceptor

package com.pab.bloan.capv.server.common.changemonitor.enumerate;


public enum DBActionTypeEnum {
    
    UPDATE("UPDATE","更新"),
    INSERT("INSERT","新增"),
    DELETE("DELETE","删除");
    
    private String key;
    private String value;
    
    private DBActionTypeEnum(String key, String value) {
        this.key = key;
        this.value = value;
    }
    
    public String getKey() {
        return key;
    }

    public String getValue() {
        return value;
    }
    
}

package com.pab.bloan.capv.server.common.changemonitor.interceptor;

import com.alibaba.druid.pool.DruidPooledPreparedStatement;
import com.alibaba.druid.proxy.jdbc.JdbcParameter;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.sql.SQLUtils;
import com.pab.bloan.capv.server.common.changemonitor.listener.DBObjectChangeEventListener;
import com.pab.bloan.capv.server.common.changemonitor.model.ChangeMonitorBo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.jdbc.PreparedStatementLogger;
import org.apache.ibatis.plugin.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ReflectionUtils;

import java.lang.reflect.Field;
import java.sql.Statement;
import java.util.*;

@Slf4j
@Component
@Intercepts({
    @Signature(type = StatementHandler.class, method = "update", args = {Statement.class})
})
public class ChangeMinitorInterceptor implements Interceptor {

    @Autowired
    private DBObjectChangeEventListener changeEventListener;
    @Value("${capv.minitor.plugin.enable:true}")
    private Boolean changeMinitorSwitch;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object proceedResult = null;

        // 解析拦截到的sql 并进行更新前处理
        ChangeMonitorBo changeMonitorBo = null;
        try {
            String realSql = parseRealSql((Statement) invocation.getArgs()[0]);
            log.info("解析后的sql为: {}", realSql);
            changeMonitorBo = new ChangeMonitorBo(realSql);

            // 更新前处理
            changeEventListener.beforeChange(changeMonitorBo);
        }catch (Throwable err){
            log.error(err.getMessage());
        }

        // 执行拦截的方法
        proceedResult = invocation.proceed();

        // 更新后处理
        try {
            if(changeMonitorBo != null) {
                changeEventListener.afterChange(changeMonitorBo);
            }
        }catch (Throwable err){
            log.error(err.getMessage());
        }

        // 返回执行结果
        return proceedResult;
    }

    @Override
    public Object plugin(Object target) {
        if(changeMinitorSwitch) {
            if (target instanceof StatementHandler) {
                return Plugin.wrap(target, this);
            }
        }
        return target;
    }

    @Override
    public void setProperties(Properties arg0) {
    }

    private Object getField(Object obj, String fieldName) {
        Field field = ReflectionUtils.findField(obj.getClass(),fieldName);
        ReflectionUtils.makeAccessible(field);
        return ReflectionUtils.getField(field,obj);
    }

    public String parseRealSql(Statement statement) {
        DruidPooledPreparedStatement druidPooledPreparedStatement = null;
        if(statement instanceof DruidPooledPreparedStatement) {
            druidPooledPreparedStatement = (DruidPooledPreparedStatement) statement;
        }else{
            PreparedStatementLogger preparedStatementLogger = (PreparedStatementLogger) getField(statement, "h");
            druidPooledPreparedStatement = (DruidPooledPreparedStatement) preparedStatementLogger.getPreparedStatement();
        }
        PreparedStatementProxy preparedStatementProxy = (PreparedStatementProxy)druidPooledPreparedStatement.getStatement();

        int parametersSize = preparedStatementProxy.getParametersSize();
        List<Object> parameters = new ArrayList(parametersSize);

        for(int i = 0; i < parametersSize; ++i) {
            JdbcParameter jdbcParam = preparedStatementProxy.getParameter(i);
            parameters.add(jdbcParam != null ? jdbcParam.getValue() : null);
        }

        String dbType = preparedStatementProxy.getConnectionProxy().getDirectDataSource().getDbType();
        return SQLUtils.format(preparedStatementProxy.getSql(), dbType, parameters);
    }

}

package com.pab.bloan.capv.server.common.changemonitor.listener;

import com.pab.bloan.capv.server.common.changemonitor.model.ChangeMonitorBo;
import net.sf.jsqlparser.JSQLParserException;

public interface DBObjectChangeEventListener {
    void beforeChange(ChangeMonitorBo changeMonitorBo) throws JSQLParserException;
    void afterChange(ChangeMonitorBo changeMonitorBo);
}

package com.pab.bloan.capv.server.common.changemonitor.listener;

import com.alibaba.fastjson.JSON;
import com.ctrip.framework.apollo.Config;
import com.ctrip.framework.apollo.model.ConfigChangeEvent;
import com.ctrip.framework.apollo.spring.annotation.ApolloConfig;
import com.ctrip.framework.apollo.spring.annotation.ApolloConfigChangeListener;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.pab.bloan.capv.server.common.changemonitor.enumerate.DBActionTypeEnum;
import com.pab.bloan.capv.server.common.changemonitor.model.ChangeMonitorBo;
import com.pab.bloan.capv.server.common.util.LoginUtils;
import com.pab.bloan.capv.server.model.pojo.rcpmdata.DataObjectLogPojo;
import com.pab.bloan.capv.server.model.pojo.rcpmlog.ChangeObjectDetailPojo;
import com.pab.bloan.capv.server.model.pojo.rcpmlog.ChangeObjectPojo;
import com.pab.framework.google.common.collect.Lists;
import com.pab.halo.component.idgenerator.KeyGenerator;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.annotation.PostConstruct;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

@Slf4j
@Component
public class DefaultDBObjectChangeEventListenerImpl implements DBObjectChangeEventListener {
    private static final String MONITOR_CHANGE_STORE_TABLE = "CHANGE_OBJECT";
    private static final String MONITOR_CHANGE_STORE_TABLE_DETAIL = "CHANGE_OBJECT_DETAIL";
    private Set<String> needMonitorTables = Sets.newHashSet();

    @Value("${capv.monitor.need-monitor-tables:}")
    private String monitorTables;
    @Autowired
    @Qualifier("defaultJdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Autowired
    @Qualifier("logJdbcTemplate")
    private JdbcTemplate logJdbcTemplate;
    @Autowired
    private KeyGenerator keyGenerator;
//    @Autowired
//    private ChangeObjectAndDetailService changeObjectAndDetailService; //这里不能间接注入mybatis相关的Mapper对象，执行openSession直接报错
//    @Autowired
//    private SqlSessionFactory sqlSessionFactory; //这里不能注入mybatis相关的对象，执行openSession直接报错

    @ApolloConfig
    private Config config;

    @ApolloConfigChangeListener
    private void configChangeListter(ConfigChangeEvent changeEvent) {
        Set<String> keyNames = config.getPropertyNames();
        for (String key : keyNames) {
            if (key.equals("capv.monitor.need-monitor-tables")) {
                String strMonitorTables = config.getProperty(key, "");
                this.monitorTables = strMonitorTables;
                log.info("{}:{}", key, strMonitorTables);
                initNeedMonitorTables();
                break;
            }
        }
    }

    @PostConstruct
    public void initNeedMonitorTables() {
        String[] tables = monitorTables.toUpperCase().split(",");
        log.info("当前拦截的表清单: {}", JSON.toJSONString(this.needMonitorTables));
        synchronized (needMonitorTables) {
            needMonitorTables.clear();
            needMonitorTables.addAll(Arrays.asList(tables));
        }
        log.info("刷新后拦截的表清单: {}", JSON.toJSONString(this.needMonitorTables));
    }

    public void fillSql(ChangeMonitorBo changeMonitorBo) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(changeMonitorBo.getMonitorSql());
        changeMonitorBo.setStatement(statement);
        //初始化表信息
        prepareTable(changeMonitorBo);
        if(!changeMonitorBo.isNeedMonitor()){
            return;
        }
        // 初始化主键信息
        fetchTablePkColumns(changeMonitorBo);
        // 初始化字段注释
        fetchTableColumnComments(changeMonitorBo);
        // 拼装查询sql
        prepareSelectSql(changeMonitorBo);
    }

    @Override
    public void beforeChange(ChangeMonitorBo changeMonitorBo) throws JSQLParserException {
        fillSql(changeMonitorBo);
        if(!changeMonitorBo.isNeedMonitor()
                || null == changeMonitorBo.getSelectSql() ){
            return;
        }
        if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.UPDATE)) {
            Date curDateTime = new Date();
            List<Map<String, Object>> lastData = jdbcTemplate.queryForList(changeMonitorBo.getSelectSql());

            //保存修改前数据串
            changeMonitorBo.setBeforeJsonString(JSON.toJSONString(lastData));

            for(Map<String, Object> map : lastData){
                ChangeObjectPojo changeObject = new ChangeObjectPojo();
//                changeObject.setPkColumnName(StringUtils.join(changeMonitorBo.getPkColumns().toArray(), ","));
                changeObject.setUserUm(getLoginUserUm());
                changeObject.setTableName(changeMonitorBo.getTable().getName());
                changeObject.setCreateTime(curDateTime);
                changeObject.setChangeType(changeMonitorBo.getChageType().getValue());
                changeObject.setSerialNo(String.valueOf(keyGenerator.generateKey()));

                StringBuilder sbPkComments = new StringBuilder();
                changeMonitorBo.getPkColumns().forEach(pkColumn->{
                    sbPkComments.append(changeMonitorBo.getColumnCommentsMap().get(pkColumn));
                });
                changeObject.setPkColumnComments(sbPkComments.toString());

                log.info("threadNo={},oldValue={}",Thread.currentThread().getId(), map.toString());
                Map<String,Object> pkValueMap = Maps.newHashMap();
                map.forEach((key, value) -> {
                    ChangeObjectDetailPojo detail = new ChangeObjectDetailPojo();
                    detail.setSerialNo(String.valueOf(keyGenerator.generateKey()));
                    detail.setObjSerialNo(changeObject.getSerialNo());
                    detail.setColumnName(key);
                    detail.setColumnComments(changeMonitorBo.getColumnCommentsMap().get(key));
                    detail.setLastValue(value==null?null:String.valueOf(value));
                    detail.setCreateTime(curDateTime);
                    changeMonitorBo.getChangeObjectDetailPojoList().add(detail);

                    // 判断当前处理字段是否主键字段
                    if(changeMonitorBo.getPkColumns().contains(key)){
                        pkValueMap.put(key,value);
                    }
                });
                //解决主键顺序不一致问题
                StringBuilder sbPkColumn = new StringBuilder();
                StringBuilder sbPkValue = new StringBuilder();
                pkValueMap.forEach((key,value)->{
                    if(StringUtils.isBlank(sbPkColumn.toString())) {
                        sbPkColumn.append(key);
                        sbPkValue.append(value);
                    }else{
                        sbPkColumn.append(",").append(key);
                        sbPkValue.append(",").append(value);
                    }
                });
                changeObject.setPkColumnName(sbPkColumn.toString());
                changeObject.setPkValue(sbPkValue.toString());
                changeMonitorBo.getChangeObjectPojoList().add(changeObject);
            }
        }else if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.INSERT)) {

        }else if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.DELETE)) {
            Date curDateTime = new Date();
            List<Map<String, Object>> lastData = jdbcTemplate.queryForList(changeMonitorBo.getSelectSql());
            for(Map<String, Object> map : lastData){
                ChangeObjectPojo changeObject = new ChangeObjectPojo();
//                changeObject.setPkColumnName(StringUtils.join(changeMonitorBo.getPkColumns().toArray(), ","));
                changeObject.setUserUm(getLoginUserUm());
                changeObject.setTableName(changeMonitorBo.getTable().getName());
                changeObject.setCreateTime(curDateTime);
                changeObject.setChangeType(changeMonitorBo.getChageType().getValue());
                changeObject.setSerialNo(String.valueOf(keyGenerator.generateKey()));

                StringBuilder sbPkComments = new StringBuilder();
                changeMonitorBo.getPkColumns().forEach(pkColumn->{
                    sbPkComments.append(changeMonitorBo.getColumnCommentsMap().get(pkColumn));
                });
                changeObject.setPkColumnComments(sbPkComments.toString());

                log.info("threadNo={},oldValue={}",Thread.currentThread().getId(), map.toString());
                Map<String,Object> pkValueMap = Maps.newHashMap();
                map.forEach((key, value) -> {
                    ChangeObjectDetailPojo detail = new ChangeObjectDetailPojo();
                    detail.setSerialNo(String.valueOf(keyGenerator.generateKey()));
                    detail.setObjSerialNo(changeObject.getSerialNo());
                    detail.setColumnName(key);
                    detail.setColumnComments(changeMonitorBo.getColumnCommentsMap().get(key));
                    detail.setLastValue(value==null?null:String.valueOf(value));
                    detail.setCurValue(null);
                    detail.setCreateTime(curDateTime);
                    changeMonitorBo.getChangeObjectDetailPojoList().add(detail);

                    // 判断当前处理字段是否主键字段
                    if(changeMonitorBo.getPkColumns().contains(key)){
                        pkValueMap.put(key,value);
                    }
                });
                //解决主键顺序不一致问题
                StringBuilder sbPkColumn = new StringBuilder();
                StringBuilder sbPkValue = new StringBuilder();
                pkValueMap.forEach((key,value)->{
                    if(StringUtils.isBlank(sbPkColumn.toString())) {
                        sbPkColumn.append(key);
                        sbPkValue.append(value);
                    }else{
                        sbPkColumn.append(",").append(key);
                        sbPkValue.append(",").append(value);
                    }
                });
                changeObject.setPkColumnName(sbPkColumn.toString());
                changeObject.setPkValue(sbPkValue.toString());
                changeMonitorBo.getChangeObjectPojoList().add(changeObject);
            }
        }
    }

    @Override
    public void afterChange(ChangeMonitorBo changeMonitorBo) {
        if(!changeMonitorBo.isNeedMonitor()
                || null == changeMonitorBo.getSelectSql()){
            return;
        }
        if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.UPDATE)) {
            List<Map<String, Object>> curData = jdbcTemplate.queryForList(changeMonitorBo.getSelectSql());

            //变更前后数据完全相等
            if(changeMonitorBo.getBeforeJsonString().equals(JSON.toJSONString(curData))){
                return;
            }

            for(Map<String, Object> map : curData){
                for(ChangeObjectPojo changeObject : changeMonitorBo.getChangeObjectPojoList()) {
                    String strPkColumnName = changeObject.getPkColumnName();
                    StringBuilder sbPkValue = new StringBuilder();
                    //可能是联合索引
                    if(StringUtils.isNotBlank(strPkColumnName)){
                        String[] arrPkColumnName = strPkColumnName.split(",");
                        Arrays.asList(arrPkColumnName).forEach(column->{
                            if(StringUtils.isBlank(sbPkValue.toString())) {
                                sbPkValue.append(map.get(column));
                            }else{
                                sbPkValue.append(",").append(map.get(column));
                            }
                        });
                    }
                    if(null != changeObject.getPkValue() && changeObject.getPkValue().equals(sbPkValue.toString())) {
                        log.info("threadNo={},newValue={}", Thread.currentThread().getId(), map.toString());
                        changeMonitorBo.getChangeObjectDetailPojoList().forEach(detail -> {
                            if(detail.getObjSerialNo().equals(changeObject.getSerialNo())){
                                Object curValue = map.get(detail.getColumnName());
                                detail.setCurValue(curValue==null?null:String.valueOf(curValue));
                            }
                        });
                    }
                }
            }
        }else if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.INSERT)) {
            Date curDateTime = new Date();
            List<Map<String, Object>> curData = jdbcTemplate.queryForList(changeMonitorBo.getSelectSql());
            for(Map<String, Object> map : curData){
                ChangeObjectPojo changeObject = new ChangeObjectPojo();
//                changeObject.setPkColumnName(StringUtils.join(changeMonitorBo.getPkColumns().toArray(), ","));
                changeObject.setUserUm(getLoginUserUm());
                changeObject.setTableName(changeMonitorBo.getTable().getName());
                changeObject.setCreateTime(curDateTime);
                changeObject.setChangeType(changeMonitorBo.getChageType().getValue());
                changeObject.setSerialNo(String.valueOf(keyGenerator.generateKey()));

                StringBuilder sbPkComments = new StringBuilder();
                changeMonitorBo.getPkColumns().forEach(pkColumn->{
                    sbPkComments.append(changeMonitorBo.getColumnCommentsMap().get(pkColumn));
                });
                changeObject.setPkColumnComments(sbPkComments.toString());

                log.info("threadNo={},oldValue={}",Thread.currentThread().getId(), map.toString());
                Map<String,Object> pkValueMap = Maps.newHashMap();
                map.forEach((key, value) -> {
                    ChangeObjectDetailPojo detail = new ChangeObjectDetailPojo();
                    detail.setSerialNo(String.valueOf(keyGenerator.generateKey()));
                    detail.setObjSerialNo(changeObject.getSerialNo());
                    detail.setColumnName(key);
                    detail.setColumnComments(changeMonitorBo.getColumnCommentsMap().get(key));
                    detail.setLastValue(null);
                    detail.setCurValue(value==null?null:String.valueOf(value));
                    detail.setCreateTime(curDateTime);
                    changeMonitorBo.getChangeObjectDetailPojoList().add(detail);

                    // 判断当前处理字段是否主键字段
                    if(changeMonitorBo.getPkColumns().contains(key)){
                        pkValueMap.put(key,value);
                    }
                });
                //解决主键顺序不一致问题
                StringBuilder sbPkColumn = new StringBuilder();
                StringBuilder sbPkValue = new StringBuilder();
                pkValueMap.forEach((key,value)->{
                    if(StringUtils.isBlank(sbPkColumn.toString())) {
                        sbPkColumn.append(key);
                        sbPkValue.append(value);
                    }else{
                        sbPkColumn.append(",").append(key);
                        sbPkValue.append(",").append(value);
                    }
                });
                changeObject.setPkColumnName(sbPkColumn.toString());
                changeObject.setPkValue(sbPkValue.toString());
                changeMonitorBo.getChangeObjectPojoList().add(changeObject);
            }
        }else if(changeMonitorBo.getChageType().equals(DBActionTypeEnum.DELETE)) {

        }

        log.info("threadNo={},changeObject={}", Thread.currentThread().getId(), changeMonitorBo);

        // 保存数据
        saveChangeObject(changeMonitorBo.getChangeObjectPojoList());
        saveChangeObjectDetail(changeMonitorBo.getChangeObjectDetailPojoList());

        // 兼容老个贷 DATAOBJECT_LOG 表保存数据
        saveDataobjectLog(changeMonitorBo);

    }

    private String getLoginUserUm() {
        try{
            return LoginUtils.getLoginUserUM();
        }catch (RuntimeException e){
        }
        return null;
    }
    private String getLoginUserBelongOrgId() {
        try{
            return LoginUtils.getLoginUserBelongOrgId();
        }catch (RuntimeException e){
        }
        return null;
    }

    private void saveDataobjectLog(ChangeMonitorBo changeMonitorBo) {
        List<DataObjectLogPojo> list = Lists.newArrayList();
        changeMonitorBo.getChangeObjectPojoList().forEach(item->{
            DataObjectLogPojo pojo = new DataObjectLogPojo();
            pojo.setOpTime(DateFormatUtils.format(item.getCreateTime(),"yyyy/MM/dd HH:mm:ss:SSS"));
            pojo.setOpTable(item.getTableName());
            pojo.setOpKeys(item.getPkColumnName());
            pojo.setOpValues(item.getPkValue());
            pojo.setOpActions(item.getChangeType());
            pojo.setOrgId(getLoginUserBelongOrgId());
            pojo.setUserId(item.getUserUm());

            StringBuilder sb = new StringBuilder();
            changeMonitorBo.getChangeObjectDetailPojoList().forEach(detail->{
                if(detail.getObjSerialNo().equals(item.getSerialNo())){
                    if( ( StringUtils.isBlank(detail.getLastValue()) && StringUtils.isBlank(detail.getCurValue()) )
                        || (detail.getLastValue() != null && detail.getLastValue().equals(detail.getCurValue()) )
                            ) {

                    }else {
                        sb.append(detail.getColumnComments()).append("(").append(detail.getColumnName()).append(")")
                                .append(":").append("原值=").append(detail.getLastValue())
                                .append(",").append("新值=").append(detail.getCurValue()).append("。\n");
                    }
                }
            });
            String strAttr1 = sb.toString();
            if(strAttr1.length()>2000){
                strAttr1 = strAttr1.substring(0, 2000);
            }
            pojo.setAttribute1(strAttr1);

            if(StringUtils.isNotBlank(strAttr1)) {
                list.add(pojo);
            }
        });
        if(CollectionUtils.isEmpty(list)){
            return;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("insert into DATAOBJECT_LOG (OPTIME, OPTABLE, OPKEYS, OPVALUES, OPACTIONS, ORGID, ORGNAME,")
                .append(" USERID, USERNAME, ATTRIBUTE1)")
                .append(" values (?,?,?,?, ?,?,?,?, ?,?)");

        final List<DataObjectLogPojo> tmpList = list;
        jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, tmpList.get(i).getOpTime());
                ps.setString(2, tmpList.get(i).getOpTable());
                ps.setString(3, tmpList.get(i).getOpKeys());
                ps.setString(4, tmpList.get(i).getOpValues());
                ps.setString(5, tmpList.get(i).getOpActions());
                ps.setString(6, tmpList.get(i).getOrgId());
                ps.setString(7, tmpList.get(i).getOrgName());
                ps.setString(8, tmpList.get(i).getUserId());
                ps.setString(9, tmpList.get(i).getUserName());
                ps.setString(10, tmpList.get(i).getAttribute1());
            }

            @Override
            public int getBatchSize() {
                return tmpList.size();
            }
        });
    }

    private void saveChangeObject(List<ChangeObjectPojo> list) {
        if(CollectionUtils.isEmpty(list)){
            return;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("insert into CHANGE_OBJECT (SERIALNO, TABLENAME, PKCOLUMNNAME,")
                .append(" PKCOLUMNCOMMENTS, PKVALUE, CHANGETYPE, CREATETIME, USERUM)")
                .append(" values (?,?,?,?,?,?,?,?)");

        final List<ChangeObjectPojo> tmpList = list;
        logJdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, tmpList.get(i).getSerialNo());
                ps.setString(2, tmpList.get(i).getTableName());
                ps.setString(3, tmpList.get(i).getPkColumnName());
                ps.setString(4, tmpList.get(i).getPkColumnComments());
                ps.setString(5, tmpList.get(i).getPkValue());
                ps.setString(6, tmpList.get(i).getChangeType());
                ps.setTimestamp(7, new java.sql.Timestamp(tmpList.get(i).getCreateTime().getTime()));
                ps.setString(8, tmpList.get(i).getUserUm());
            }

            @Override
            public int getBatchSize() {
                return tmpList.size();
            }
        });

    }

    private void saveChangeObjectDetail(List<ChangeObjectDetailPojo> list) {
        if(CollectionUtils.isEmpty(list)){
            return;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("insert into CHANGE_OBJECT_DETAIL (SERIALNO, OBJSERIALNO, COLUMNNAME, COLUMNCOMMENTS, LASTVALUE, CURVALUE, CREATETIME)")
                .append(" values (?,?,?,?,?,?,?)");

        final List<ChangeObjectDetailPojo> tmpList = list;
        logJdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, tmpList.get(i).getSerialNo());
                ps.setString(2, tmpList.get(i).getObjSerialNo());
                ps.setString(3, tmpList.get(i).getColumnName());
                ps.setString(4, tmpList.get(i).getColumnComments());
                ps.setString(5, tmpList.get(i).getLastValue());
                ps.setString(6, tmpList.get(i).getCurValue());
                ps.setTimestamp(7, new java.sql.Timestamp(tmpList.get(i).getCreateTime().getTime()));
            }

            @Override
            public int getBatchSize() {
                return tmpList.size();
            }
        });
    }

    /**
     * 根据本次更新的sql拼装查询本次更新前的数据的sql
     * @param
     * @param changeMonitorBo
     * @return
     * @throws JSQLParserException
     */
    private void prepareSelectSql(ChangeMonitorBo changeMonitorBo) {
        if(changeMonitorBo.getStatement() instanceof Update) {
            prepareSelectSqlFromUpdateSql((Update) changeMonitorBo.getStatement(), changeMonitorBo);
        }else if(changeMonitorBo.getStatement() instanceof Insert){
            prepareSelectSqlFromInsertSql((Insert) changeMonitorBo.getStatement(), changeMonitorBo);
        }else if(changeMonitorBo.getStatement() instanceof Delete) {
            prepareSelectSqlFromDeleteSql((Delete) changeMonitorBo.getStatement(), changeMonitorBo);
        }
    }

    private void prepareTable(ChangeMonitorBo changeMonitorBo) {
        if(changeMonitorBo.getStatement() instanceof Update) {
            changeMonitorBo.setTable(((Update)changeMonitorBo.getStatement()).getTables().get(0));
            changeMonitorBo.setChageType(DBActionTypeEnum.UPDATE);
        }else if(changeMonitorBo.getStatement() instanceof Insert){
            changeMonitorBo.setTable(((Insert)changeMonitorBo.getStatement()).getTable());
            changeMonitorBo.setChageType(DBActionTypeEnum.INSERT);
        }else if(changeMonitorBo.getStatement() instanceof Delete) {
            changeMonitorBo.setTable(((Delete)changeMonitorBo.getStatement()).getTable());
            changeMonitorBo.setChageType(DBActionTypeEnum.DELETE);
        }

        // 剔除本监控插入的两张表，避免死循环
        if(changeMonitorBo.getTable().getName().equals(MONITOR_CHANGE_STORE_TABLE)
                || changeMonitorBo.getTable().getName().equals(MONITOR_CHANGE_STORE_TABLE_DETAIL)){
            changeMonitorBo.setNeedMonitor(false);
        }else if(needMonitorTables.contains(changeMonitorBo.getTable().getName())
                || needMonitorTables.contains("**")){
            changeMonitorBo.setNeedMonitor(true);
        }
    }

    private void prepareSelectSqlFromUpdateSql(Update statement, ChangeMonitorBo changeMonitorBo) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        Iterator<Column> iterator = statement.getColumns().iterator();
        boolean bFirst = true;
        while (iterator.hasNext()) {
            if (!bFirst) {
                sb.append(",");
            } else {
                bFirst = false;
            }
            sb.append(iterator.next().getColumnName());
        }
        //增加主键字段
        changeMonitorBo.getPkColumns().forEach(pkColumn -> {
            if(!sb.toString().contains(pkColumn)){
                sb.append(",").append(pkColumn);
            }
        });

        sb.append(" from ").append(statement.getTables().get(0));
        sb.append(" where ").append(statement.getWhere());
        log.info("查询语句为: {}", sb.toString());
        changeMonitorBo.setSelectSql(sb.toString());
    }

    private void prepareSelectSqlFromInsertSql(Insert statement, ChangeMonitorBo changeMonitorBo) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(statement.getTable());
        Iterator<Column> iterator = statement.getColumns().iterator();
        InsertItemsListVisitor visitor = new InsertItemsListVisitor();
        statement.getItemsList().accept(visitor);
        int index = 0;
        while (iterator.hasNext()) {
            String column = iterator.next().getColumnName();
            // 判断当前处理字段是否主键字段
            if(changeMonitorBo.getPkColumns().contains(column)){
                if(index == 0){
                    sb.append(" where ");
                }else{
                    sb.append(" and ");
                }
                String value = visitor.getColumnValue(index);
                sb.append(column).append(" = ").append(value);
            }
            index++;
        }
        log.info("查询语句为: {}", sb.toString());
        changeMonitorBo.setSelectSql(sb.toString());
    }

    private void prepareSelectSqlFromDeleteSql(Delete statement, ChangeMonitorBo changeMonitorBo) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(statement.getTable());
        sb.append(" where ").append(statement.getWhere());
        log.info("查询语句为: {}", sb.toString());
        changeMonitorBo.setSelectSql(sb.toString());
    }

    private void fetchTablePkColumns(ChangeMonitorBo changeMonitorBo) {
        StringBuilder sbPkSql = new StringBuilder();
        sbPkSql.append("select col.COLUMN_NAME ")
                .append( "  from all_constraints con, all_cons_columns col ")
                .append( " where con.constraint_name = col.constraint_name ")
                .append( "   and con.owner='RCPMDATA' ")
                .append( "   and con.owner=col.owner ")
                .append( "   and con.constraint_type = 'P' ")
                .append( "   and col.table_name = upper( ? ) ")
                .append( " order by position");
        List<Map<String, Object>> pkData = jdbcTemplate.queryForList(sbPkSql.toString(), new Object[]{changeMonitorBo.getTable().getName()});
        pkData.forEach(data->{
            changeMonitorBo.getPkColumns().add(String.valueOf(data.get("COLUMN_NAME")));
        });
    }

    private void fetchTableColumnComments(ChangeMonitorBo changeMonitorBo) {
        String commentsSql = "SELECT COLUMN_NAME, COMMENTS FROM all_col_comments WHERE table_name=upper( ? ) and owner='RCPMDATA' ";
        List<Map<String, Object>> commentsData = jdbcTemplate.queryForList(commentsSql, new Object[]{changeMonitorBo.getTable().getName()});
        commentsData.forEach(data->{
            changeMonitorBo.getColumnCommentsMap().put(String.valueOf(data.get("COLUMN_NAME")),String.valueOf(data.get("COMMENTS")));
        });
    }

}

package com.pab.bloan.capv.server.common.changemonitor.listener;

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.statement.select.SubSelect;

@Slf4j
public class InsertItemsListVisitor implements ItemsListVisitor {
    private ExpressionList expressionList;
    @Override
    public void visit(SubSelect subSelect) {

    }

    @Override
    public void visit(ExpressionList expressionList) {
        this.expressionList = expressionList;
    }

    @Override
    public void visit(MultiExpressionList multiExpressionList) {

    }

    public String getColumnValue(int index) {
        return expressionList.getExpressions().get(index).toString();
    }
}

package com.pab.bloan.capv.server.common.changemonitor.model;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.pab.bloan.capv.server.common.changemonitor.enumerate.DBActionTypeEnum;
import com.pab.bloan.capv.server.model.pojo.rcpmlog.ChangeObjectDetailPojo;
import com.pab.bloan.capv.server.model.pojo.rcpmlog.ChangeObjectPojo;
import com.pab.framework.google.common.collect.Maps;
import lombok.Data;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;

import java.util.List;
import java.util.Map;

@Data
public class ChangeMonitorBo {
    private String monitorSql;
    private boolean needMonitor = false;
    private String beforeJsonString;

    private Statement statement;
    private String selectSql;
    private DBActionTypeEnum chageType;
    private Table table;
    private List<String> pkColumns = Lists.newArrayList();
    private Map<String,String> columnCommentsMap = Maps.newHashMap();
    private List<ChangeObjectPojo> changeObjectPojoList = Lists.newArrayList();
    private List<ChangeObjectDetailPojo> changeObjectDetailPojoList = Lists.newArrayList();

    public ChangeMonitorBo(String sql){
        this.monitorSql = sql;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(JSON.toJSONString(this.changeObjectPojoList,true))
                .append("\n")
                .append(JSON.toJSONString(this.changeObjectDetailPojoList, true));
        return sb.toString();
    }
}

package com.pab.bloan.capv.server.common.configuration;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.github.pagehelper.PageHelper;
import com.pab.bloan.capv.server.common.changemonitor.interceptor.ChangeMinitorInterceptor;
import com.pab.bloan.capv.server.common.exception.BusinessException;
import com.pab.common.utils.PasswordCodeUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 
 */
@Slf4j
public abstract class AbstractMyBatisConfig {

    @Autowired
    ChangeMinitorInterceptor changeMinitorInterceptor;

    abstract DruidConfig druidConfig();

    protected DruidDataSource getDataSource() {
        DruidConfig config = druidConfig();
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(config.getDriverClassName());
        dataSource.setUrl(config.getUrl());
        dataSource.setUsername(config.getUsername());
        try {
            dataSource.setPassword(PasswordCodeUtils.decrypt(config.getPassword()));
        }catch (Exception ex){
            log.error("decrypt", ex);
            dataSource.setPassword(config.getPassword());
        }
        if(config.getInitialSize() != null) {
            dataSource.setInitialSize(config.getInitialSize());
        }
        if(config.getMaxActive() != null) {
            dataSource.setMaxActive(config.getMaxActive());
        }
        if(config.getMinIdle() != null) {
            dataSource.setMinIdle(config.getMinIdle());
        }
        if(config.getMaxWaite() != null){
            dataSource.setMaxWait(config.getMaxWaite());
        }
        dataSource.setTestOnBorrow(config.isTestOnBorrow());
        dataSource.setTestWhileIdle(config.isTestWhileIdle());

        List<Filter> filters = new ArrayList<>();
        filters.add(statFilter());
        filters.add(wallFilter());
        dataSource.setProxyFilters(filters);

        return dataSource;
    }

    protected SqlSessionFactory getSqlSessionFactory(
            final DataSource dataSource,
            final String mapperFolder,
            final String basePackage) {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage(basePackage);
        bean.setPlugins(new Interceptor[]{pagePlugin(), changeMinitorInterceptor});
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:mapping/" + mapperFolder + "/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            log.error("setMapperLocations", e);
            throw new BusinessException(e.getMessage(), e);
        }
    }

    protected StatFilter statFilter(){
        StatFilter statFilter = new StatFilter();
        statFilter.setLogSlowSql(true);
        statFilter.setMergeSql(true);
        statFilter.setSlowSqlMillis(1000);

        return statFilter;
    }

    protected WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();

        //允许执行多条SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);

        return wallFilter;
    }

    public static PageHelper pagePlugin() {
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("dialect", "Oracle");
        properties.setProperty("reasonable", "true");
        properties.setProperty("rowBoundsWithCount", "true");
        pageHelper.setProperties(properties);
        return pageHelper;
    }
}

package com.pab.bloan.capv.server.common.configuration;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**
 * 
 */
@Configuration
@MapperScan(basePackages = {"com.pab.bloan.capv.server.dao.rcpmdata"},
        sqlSessionTemplateRef = "defaultSqlSessionTemplate")
@EnableTransactionManagement
@Slf4j
public class RcpmMyBatisConfig extends AbstractMyBatisConfig {

    @Bean("defaultConfig")
    @ConfigurationProperties(prefix = "jdbc.default")
    @Override
    public DruidConfig druidConfig() {
        return new DruidConfig();
    }

    @Primary
    @Bean(name = "defaultDataSource")
    public DataSource defaultDataSource() {
        return this.getDataSource();
    }

    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", "capv");
        reg.addInitParameter("loginPassword", "20083");
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @Primary
    @Bean(name = "defaultSqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("defaultDataSource") DataSource dataSource) {
        return this.getSqlSessionFactory(dataSource,
                "rcpmdata", "com.pab.bloan.capv.server.model.pojo.rcpmdata");
    }

    @Bean(name = "defaultSqlSessionTemplate")
    public SqlSessionTemplate defaultSqlSessionTemplate(
            @Qualifier("defaultSqlSessionFactory") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

    @Bean(name = "defaultJdbcTemplate")
    public JdbcTemplate defaultJdbcTemplate(@Qualifier("defaultDataSource")DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

}

 

package com.pab.bloan.capv.server.model.pojo.rcpmlog;

import lombok.Data;

import java.util.Date;

@Data
public class ChangeObjectPojo {
    private String serialNo;

    private String tableName;

    private String pkColumnName;

    private String pkColumnComments;

    private String pkValue;

    private String changeType;

    private Date createTime;

    private String userUm;
}

 

package com.pab.bloan.capv.server.model.pojo.rcpmlog;

import lombok.Data;

import java.util.Date;

@Data
public class ChangeObjectDetailPojo {
    private String serialNo;
    private String objSerialNo;
    private String columnName;
    private String columnComments;
    private String lastValue;
    private String curValue;
    private Date createTime;
}