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
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 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 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
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 pkColumns = Lists.newArrayList();
private Map columnCommentsMap = Maps.newHashMap();
private List changeObjectPojoList = Lists.newArrayList();
private List 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 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;
}