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.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("。
");
}
}
});
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.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("
")
.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);
}
}