主要用到的jar包：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
spring配置：
<bean id="sqlLog" class="com.xx.xxx.xx.SqlLog"/>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
......
<property name="plugins" ref="sqlLog"/>
</bean>
SqlLog.java：
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.Properties;
/**
* sql拦截类
* @author qiaojun
*/
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {
MappedStatement.class, Object.class }),
@Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class })
})
@SuppressWarnings({"unchecked", "rawtypes"})
//@Component //如果是在springboot中使用就用该注释
public class SqlLog implements Interceptor
{
private Logger logger = LoggerFactory.getLogger(SqlLog.class);
public static final ThreadPoolExecutor THREAD_POOL = (ThreadPoolExecutor) Executors.newFixedThreadPool(10);
@Override
public Object intercept(Invocation invocation) throws Throwable
{
//记录sql执行开始时间
long startTime = System.currentTimeMillis();
try
{
// 开始执行sql
return invocation.proceed();
}
catch(Exception e)
{
logger.error(e.getMessage(), e);
}finally {
SystemProps.THREAD_POOL.execute(new HandleSqlThread(invocation, System.currentTimeMillis() - startTime));
}
return null;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
DateUtil.java:
import java.text.ParseException;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.joda.time.DateTime;
import org.joda.time.Duration;
import org.joda.time.format.DateTimeFormat;
\/**
\* joda优化后的时间工具类，
\*/
public class DateUtil
{
/**
* yyyyMMdd
*/
public static final String SHORT_DATE = "yyyyMMdd";
/**
* yyyy-MM-dd HH:mm:ss
*/
public static final String FULL_DATE = "yyyy-MM-dd HH:mm:ss";
/**
* HH:mm
*/
public static final String SHORT_TIME = "HH:mm";
/**
* yyyy-MM-dd
*/
public static final String DATE = "yyyy-MM-dd";
/**
* yyyyMMddHHmmss
*/
public static final String DATEFULL = "yyyyMMddHHmmss";
/**
* 检查是否是指定时间格式字符串
*
* @Description:
* @Date:2016-6-1 下午03:27:33
* @author:dinghl
*/
public static boolean isFmtDate(String date, String fmt)
{
boolean isDate = false;
if (StringUtil.isNotNullEmpty(date))
{
try
{
DateTimeFormat.forPattern(fmt).parseDateTime(date);
isDate = true;
}
catch (Exception e)
{
}
}
return isDate;
}
/**
* 正则验证日期格式，yyyymmdd或yyyy-mm-dd或yyyy/mm/dd或yyyy mm dd
* @param dateStr 时间字符串
* @author qiaojun
*/
public static boolean isFmtDate(String dateStr)
{
if (dateStr == null)
{
return false;
}
String rexp = "^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))";
Pattern pat = Pattern.compile(rexp);
Matcher mat = pat.matcher(dateStr);
return mat.matches();
}
/**
* 将指定格式字符串时间转换成DateTime时间类型
*
* @param date 时间字符串数据 例："2015-08-10"
* @param format 转换格式 例："yyyy-MM-dd"
*/
public static DateTime parse2DateTime(String date, String format)
{
return DateTimeFormat.forPattern(format).parseDateTime(date);
}
/**
* 将Date转换成DateTime时间类型
*/
public static DateTime parse2DateTime(Date date)
{
return new DateTime(date);
}
/**
* 将Date类型转换成类型yyyy-MM-dd HH:mm:ss字符串时间
*/
public static String fmt2FullString(Date date)
{
return fmt2String(date, FULL_DATE);
}
/**
* 将Date类型转换成类型HH:mm字符串时间
*/
public static String fmt2String(Date date)
{
return fmt2String(date, SHORT_TIME);
}
/**
* 将Date类型转换成类型yyyy-MM-dd字符串时间
*/
public static String fmt2ShortString(Date date)
{
return fmt2String(date, DATE);
}
/**
* 将Date类型转换成类型自定义format格式字符串时间
* @param date 时间
* @param format 指定格式
* @author qiaojun
*/
public static String fmt2String(Date date, String format)
{
if(null != date)
{
return DateFormatUtils.format(date, format);
}
return null;
}
/**
* 将data字符串转换为date，转换效率比上面的方法要高一些
* @param dateString date字符串
* @param format 指定格式
* @author qiaojun
*/
public static Date parse2Date(String dateString, String format)
{
if(dateString==null || format==null)
{
return null;
}
Date date = null;
try
{
date = DateUtils.parseDate(dateString, new String[] { format });
}
catch (Exception e){}
return date;
}
/**
* 得到当前时间与目标时间相差的天数
* @param dateStr 时间字符串
* @param format 指定时间格式
* @return 返回相差天数，结果向上取整，例：超过1天算两天
* @author qiaojun
*/
public static int getDayNumber(String dateStr, String format)
{
Date date = parse2Date(dateStr, format);
if (null == date)
{
return 0;
}
long ms = (date.getTime());
return (int)Math.ceil(Math.abs((float) ms - System.currentTimeMillis()) / (1000*3600*24));
}
/**
* 比较两个时间的相差的天数
* @param sDate 开始时间
* @param eDate 结束时间
* @return 相差天数
*/
public static int twoTimeDifferenceDays(Date sDate, Date eDate)
{
if(null == sDate || null == eDate)
{
return 0;
}
return (int)(Math.abs((float) sDate.getTime() - eDate.getTime()) / (1000*3600*24));
}
/**
* 获取剩余时间秒数
*/
public static int getLeftTime(Date edate)
{
if (null == edate)
{
return 0;
}
DateTime stime = DateTime.now();
DateTime etime = new DateTime(edate);
Duration d = new Duration(stime, etime);
return (int) d.getStandardSeconds();
}
}
SqlInfo.java:
import java.util.Map;
/**
* 解析后需要的sql信息
* @author qiaojun
* @date 2018-3-19 0019
*/
public class SqlInfo
{
private String tableName;//表名
private String mode;//语句类型
private Map<String,Object> map;//字段及字段值组成的map
public String getMode() {
return mode;
}
public void setMode(String mode) {
this.mode = mode;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Map<String, Object> getMap() {
return map;
}
public void setMap(Map<String, Object> map) {
this.map = map;
}
}
HandleSqlThread.java:
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections.CollectionUtils;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
/**
* 处理sql线程
* @author qiaojun
* @date 2018-3-29 0029
*/
public class HandleSqlThread implements Runnable
{
private Logger logger = LoggerFactory.getLogger(HandleSqlThread.class);
private Invocation invocation; //拦截到的mybatis执行的sql信息等
private long timeConsuming;// sql执行的耗时 单位：毫秒
private int isParse; //0：解析sql 1：不解析sql
@Override
public void run()
{
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
// 获取xml中的一个select/update/insert/delete节点，主要描述的是一条SQL语句
Object parameter = null;
//获取参数，if语句成立，表示sql语句有参数，参数格式是map形式
if (invocation.getArgs().length > 1)
{
parameter = invocation.getArgs()[1];
//logger.info("------parameter = {}", JSONObject.toJSONString(parameter));
}
// 获取到节点的id,即sql语句的id
//logger.info("------mapperMethod = {}", mappedStatement.getId());
// BoundSql就是封装myBatis最终产生的sql类
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
// 获取节点的配置
Configuration configuration = mappedStatement.getConfiguration();
// 获取到最终的sql语句
logger.info("------本此执行sql[耗时:{}ms]", timeConsuming);
SqlInfo sqlInfo = SqlParseStatements.getSqlInfo(showSql(configuration, boundSql), this.isParse);
if(sqlInfo != null && !SqlParseStatements.SELECT.equals(sqlInfo.getMode()))
{
logger.info("------解析后的sql参数：{}",JSONObject.toJSONString(sqlInfo));
.......
}
}
}
/**
* 替换指定下标的字符
*
* @param str 被替换字符串
* @param index 替换下标位置
* @param replaceStr 替换字符
* @param size 上一次替换后的位置标识
* @return 替换后的结果
*/
private static String replaceIndexOf(String str, int index, String replaceStr, Integer size) {
String s1 = str.substring(0, index);
String s2 = str.substring(index + 1);
//更新堆内存中的临时size位置标识
size += replaceStr.length();
return s1 + replaceStr + s2;
}
/**
* 进行‘?’号的替换
*/
private static String showSql(Configuration configuration, BoundSql boundSql) {
// 获取参数
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
// sql语句中多个空格都用一个空格代替
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (null != parameterObject && ListUtil.isNotNull(parameterMappings)) {
// 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换
// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
// MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,
// 主要支持对JavaBean、Collection、Map三种类型对象的操作
MetaObject metaObject = configuration.newMetaObject(parameterObject);
//查询“?”号字符替换的开始位置
Integer size = 0;
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
String value = Matcher.quoteReplacement(getParameterValue(obj));
size = sql.indexOf("?", size);
sql = replaceIndexOf(sql, size, value, size);
} else if (boundSql.hasAdditionalParameter(propertyName)) {
// 该分支是动态sql
Object obj = boundSql.getAdditionalParameter(propertyName);
String value = Matcher.quoteReplacement(getParameterValue(obj));
size = sql.indexOf("?", size);
sql = replaceIndexOf(sql, size, value, size);
} else {
//打印出缺失，提醒该参数缺失并防止错位
size = sql.indexOf("?", size);
sql = replaceIndexOf(sql, size, "缺失", size);
}
}
}
}
return sql;
}
/**
* 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理
*/
private static String getParameterValue(Object obj)
{
String value = null;
if (obj instanceof String)
{
value = "'" + obj.toString() + "'";
}
else if (obj instanceof Date)
{
value = "to_date('" + DateUtil.fmt2String((Date) obj, DateUtil.FULL_DATE) + "','yyyy-MM-dd hh24:mi:ss')";
}
else
{
if (obj != null)
{
value = obj.toString();
}
else
{
value = "''";
}
}
return value;
}
public HandleSqlThread(Invocation invocation, int isParse,long timeConsuming) {
this.invocation = invocation;
this.isParse = isParse;
this.timeConsuming = timeConsuming;
}
public int getIsParse() {
return isParse;
}
public void setIsParse(int isParse) {
this.isParse = isParse;
}
public Invocation getInvocation() {
return invocation;
}
public void setInvocation(Invocation invocation) {
this.invocation = invocation;
}
public String getTaskUrl() {
return taskUrl;
}
public void setTaskUrl(String taskUrl) {
this.taskUrl = taskUrl;
}
}
InsertBean.java
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLInsertStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLUnionQuery;
import java.util.List;
/**
*
* @author qiaojun
* @date 2018-3-16 0016
*/
public class InsertBean
{
private SQLExprTableSource tableSource;
private List<SQLIdentifierExpr> columns;
private SQLInsertStatement.ValuesClause values;
private SQLSelect query;
private List<SQLExpr> myValue;
private SQLSelectQueryBlock queryDetail ;
public SQLSelectQueryBlock getQueryDetail() {
return queryDetail;
}
public void setQueryDetail(SQLSelectQueryBlock queryDetail) {
this.queryDetail = queryDetail;
}
public SQLSelect getQuery() {
return query;
}
public void setQuery(SQLSelect query) {
this.query = query;
if (query != null){
getUnionQuery(query);
}
}
/**
* 封装批量插入时的数据
*/
private void getUnionQuery(SQLSelect select){
if(select.getQuery() instanceof SQLSelectQueryBlock) {
//只有一条数据
this.queryDetail = (SQLSelectQueryBlock) query.getQuery();
}
if(select.getQuery() instanceof SQLUnionQuery){
//多个数据
SQLUnionQuery sqlUnionQuery = (SQLUnionQuery) select.getQuery();
this.queryDetail = (SQLSelectQueryBlock) sqlUnionQuery.getLeft();
}
}
public SQLExprTableSource getTableSource() {
return tableSource;
}
public void setTableSource(SQLExprTableSource tableSource) {
this.tableSource = tableSource;
}
public List<SQLIdentifierExpr> getColumns() {
return columns;
}
public void setColumns(List<SQLIdentifierExpr> columns) {
this.columns = columns;
}
public SQLInsertStatement.ValuesClause getValues() {
return values;
}
public void setValues(SQLInsertStatement.ValuesClause values) {
this.values = values;
if (values != null){
this.myValue = values.getValues();
}
}
public List<SQLExpr> getMyValue() {
return myValue;
}
public void setMyValue(List<SQLExpr> myValue) {
this.myValue = myValue;
}
}
SqlParseStatements.java
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* sql解析类
* @author qiaojun
* @date 2018-3-19 0019
*/
public class SqlParseStatements
{
private static Logger logger = LoggerFactory.getLogger(SqlParseStatements.class);
public static final String SELECT = "select";
/**
* 验证是否纯数字
*/
public static boolean isDigit(String digit)
{
boolean flag = false;
try
{
Pattern webIdPattern = Pattern.compile("^\\d+$");
Matcher matcher = webIdPattern.matcher(digit);
flag = matcher.matches();
}
catch (Exception e)
{
flag = false;
}
return flag;
}
/**
* 解析sql
* @param sql 需要解析的sql语句
* @param isParse 0：解析sql 1：不解析sql
* @return 解析结果
*/
public static SqlInfo getSqlInfo(String sql, int isParse)
{
if(sql==null)
{
return null;
}
String parseSql;
try {
parseSql = SQLUtils.format(sql, JdbcConstants.ORACLE);
}catch (Exception e){
parseSql = sql;
logger.error(e.toString());
}
logger.info("------格式化后的sql如下:\n{}", parseSql);
if(isParse > 0)
{
return null;
}
List<SQLStatement> stmtList ;
try {
stmtList = SQLUtils.parseStatements(sql, JdbcConstants.ORACLE);
}catch (Exception e){
sql = sql.trim().toLowerCase();
if(sql.indexOf(SELECT) == 0)
{
return null;
}
stmtList = subStrSql(sql);
if(stmtList == null)
{
return null;
}
}
//记录需要返回的信息
SqlInfo sqlInfo = null;
for (SQLStatement stmt : stmtList)
{
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
stmt.accept(visitor);
Map<TableStat.Name, TableStat> tabmap = visitor.getTables();
Map<String,Object> map = new HashMap<String,Object>();
String mode; //操作数据库的方法
if(tabmap.size() <= 0)
{
return null;
}
for(TableStat.Name name : tabmap.keySet())
{
mode = tabmap.get(name).toString().toLowerCase();
if (SELECT.equals(mode))
{
//如果是查询语句就跳过，因为该map无序，不保证会按照sql层级获取操作类型
continue;
}
sqlInfo = new SqlInfo();
sqlInfo.setTableName(name.toString().toLowerCase());
sqlInfo.setMode(mode);
if ("insert".equals(mode))
{
InsertBean bean = new InsertBean();
BeanUtils.copyProperties(stmt, bean);
if(null != bean.getQueryDetail())
{
//批量插入处理
if(bean.getColumns().size() != bean.getQueryDetail().getSelectList().size())
{
sqlInfo.setMap(map);
return sqlInfo;
}
for (int i = 0, len = bean.getColumns().size(); i < len; i++)
{
String value = bean.getQueryDetail().getSelectList().get(i).toString();
if (isDigit(value))
{
map.put(String.valueOf(bean.getColumns().get(i)).toLowerCase(), value);
}
}
}
else
{
//单个插入处理
for (int i = 0, len = bean.getColumns().size(); i < len; i++)
{
String value = bean.getMyValue().get(i).toString();
if (isDigit(value))
{
map.put(String.valueOf(bean.getColumns().get(i)).toLowerCase(), value);
}
}
}
sqlInfo.setMap(map);
return sqlInfo;
}
}
if(sqlInfo == null)
{
return null;
}
//是delete或者update语句，就会执行到这里来
for(TableStat.Condition condition:visitor.getConditions())
{
if (condition.getValues().size() == 1 && isDigit(condition.getValues().get(0) + ""))
{
map.put(condition.getColumn().getName().toLowerCase(), condition.getValues().get(0));
}
}
sqlInfo.setMap(map);
}
return sqlInfo;
}
/**
* 将sql中无法解析的条件过滤删掉
*/
private static List<SQLStatement> subStrSql(String sqlLowerCase)
{
String and = " and ";
if(!sqlLowerCase.contains(and))
{
return null;
}
int whereLength = 6;
String where = "where ";
String whereSql = sqlLowerCase.substring(sqlLowerCase.indexOf(where) + whereLength);
String[] ands = whereSql.split(and);
StringBuilder andSql = new StringBuilder(sqlLowerCase.substring(0, sqlLowerCase.indexOf(where) + whereLength));
for(String andStr :ands)
{
andStr.trim();
if(andStr.indexOf("(") == 0 &&andStr.indexOf(")") == (andStr.length() - 1))
{
continue;
}
andSql.append(" ").append(andStr).append(and);
}
andSql = new StringBuilder(andSql.substring(0, andSql.length() - 5));
List<SQLStatement> stmtList;
try {
stmtList = SQLUtils.parseStatements(andSql.toString(), JdbcConstants.ORACLE);
}catch (Exception e){
return null;
}
return stmtList;
}
}
826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
