package com.artfess.sysConfig.config; import java.sql.Connection; import java.util.*; import com.alibaba.fastjson.JSONObject; import com.artfess.base.exception.ApplicationException; import com.artfess.base.util.AppUtil; import com.artfess.sysConfig.model.TableField; import com.baomidou.mybatisplus.core.enums.IEnum; import com.baomidou.mybatisplus.core.toolkit.StringUtils; import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.*; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.reflection.DefaultReflectorFactory; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.type.TypeHandlerRegistry; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.jdbc.support.rowset.SqlRowSetMetaData; import org.springframework.stereotype.Component; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * 数据权限过滤 加入会DataPermissionInterceptor冲突报错，弃用 * 这里处理update 和 delete 语句 select 语句放在切面处理 可以改为拦截 Executor.update 方法 * @author wh */ @Slf4j //@Component //@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}) public class MySqlInterceptor extends AbstractSqlParserHandler implements Interceptor{ private Logger logger = LoggerFactory.getLogger(MySqlInterceptor.class); @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); //通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、 //优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。 MetaObject metaObject = MetaObject .forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory()); //先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement //MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; //id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser String namespace = mappedStatement.getId(); //sql语句类型 select、delete、insert、update SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType(); //数据库连接信息 // Configuration configuration = mappedStatement.getConfiguration(); // ComboPooledDataSource dataSource = (ComboPooledDataSource)configuration.getEnvironment().getDataSource(); // dataSource.getJdbcUrl(); ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes(); HttpServletRequest request = attributes.getRequest(); String export=request.getParameter("export"); if("field".equals(export)) { HttpServletResponse response = attributes.getResponse(); //BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql"); //0.sql参数获取 Object parameter1 = null; if (invocation.getArgs().length > 1) { parameter1 = invocation.getArgs()[1]; } BoundSql boundSql = mappedStatement.getBoundSql(parameter1); String sql = boundSql.getSql().replaceAll("\\s+", " ").toLowerCase(); List parameterMappings = new ArrayList<>(boundSql.getParameterMappings()); Object parameterObject = boundSql.getParameterObject(); Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); try { this.sqlParser(metaObject); String parameter = "null"; MetaObject newMetaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { if (parameterMapping.getMode() == ParameterMode.OUT) { continue; } String propertyName = parameterMapping.getProperty(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { parameter = getParameterValue(parameterObject); } else if (newMetaObject.hasGetter(propertyName)) { parameter = getParameterValue(newMetaObject.getValue(propertyName)); } else if (boundSql.hasAdditionalParameter(propertyName)) { parameter = getParameterValue(boundSql.getAdditionalParameter(propertyName)); } //此处不严谨，若sql语句中有❓，则替换错位。?️ sql = sql.replaceFirst("\\?", parameter); } //最后要执行的sql 语句 //System.out.println(sql); } catch (Exception e) { log.error(String.format("intercept sql error: [%s]", sql), e); } JdbcTemplate jdbcTemplate = AppUtil.getBean(JdbcTemplate.class); List fieldList=getSqlFields(jdbcTemplate,sql); //System.out.println(fieldList); ServletOutputStream output = response.getOutputStream(); JSONObject map=new JSONObject(); map.put("sql",fieldList); output.write(map.toString().getBytes()); output.flush(); return null; } Object result = invocation.proceed(); return result; } public ResultSetExtractor getResultSetExtractor() { return new SQLRowSetOracleResultSetExtractor(); } //返回查询的sql的字段名 public List getSqlFields(JdbcTemplate jdbcTemplate, String sql){ //判断 如果语句中包含了update、delete 就返回空,不是以select开头 if(sql.indexOf("update")!=-1 || sql.indexOf("delete")!=-1 || sql.indexOf("truncate")!=-1|| sql.indexOf("create")!=-1 || sql.indexOf("select")!=0){ throw new ApplicationException("SQL语句只能是查询语句。"); } SqlRowSet srs = (SqlRowSet) jdbcTemplate.query(sql, getResultSetExtractor()); List columnList = new ArrayList(); String queryClumn="select column_comment from information_schema.columns where table_schema =? and table_name=? and column_name=?"; try { SqlRowSetMetaData rsmd = srs.getMetaData(); for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { String fieldName = rsmd.getColumnName(i).toLowerCase(); String tableName = rsmd.getTableName(i).toLowerCase(); String dataType = rsmd.getColumnTypeName(i).toLowerCase(); String dataBase = rsmd.getCatalogName(i).toLowerCase(); String fieldDesc = ""; try { fieldDesc=jdbcTemplate.queryForObject(queryClumn, new Object[]{dataBase, tableName, fieldName}, String.class); }catch (EmptyResultDataAccessException e){ //sql里面有拼接字段取别名会出错 System.out.println("**********"+fieldName); } TableField field=new TableField(); field.setFieldDesc(fieldDesc); field.setTableName(tableName); field.setDataType(dataType); field.setFieldName(fieldName); columnList.add(field); } }catch (Exception e){ throw new ApplicationException("解析SQL出错"+e.getMessage()); } return columnList; } /** * 获取参数 * * @param param Object类型参数 * @return 转换之后的参数 */ private static String getParameterValue(Object param) { if (param == null) { return "null"; } if (param instanceof Number) { return param.toString(); } String value = null; if (param instanceof String) { value = param.toString(); } else if (param instanceof Date) { //TODO 引入依赖 //DateUtil.format((Date) param, "yyyy-MM-dd HH:mm:ss"); } else if (param instanceof IEnum) { value = String.valueOf(((IEnum) param).getValue()); } else { value = param.toString(); } return StringUtils.quotaMark(value); } @Override public Object plugin(Object o) { if (o instanceof StatementHandler) { return Plugin.wrap(o, this); } return o; } @Override public void setProperties(Properties properties) { } }