在不改动系统代码的情况下如何解决以下问题
- select a from tb_a 变为 select a from tb_a where c=2
- select a,c from tb_a 变为 select a from tb_a
解决思路mybatis拦截器+net.sf.jsqlparser
创建mybatis 拦截器
@Component
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
public MyInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement mappedStatement = (MappedStatement) args[0];
BoundSql boundSql = mappedStatement.getBoundSql(args[1]);
// 获取到原始sql
String sql = boundSql.getSql();
Field sqlField = boundSql.getClass().getDeclaredField("sql");
sqlField.setAccessible(true);
// 改造的sql塞回去 newSql 为改造之后的sql
sqlField.set(boundSql, newSql);
BoundSqlSqlSource boundSqlSqlSource = new BoundSqlSqlSource();
boundSqlSqlSource.setBoundSql(boundSql);
args[0] = copyFromMappedStatement(mappedStatement, boundSqlSqlSource);
return invocation.proceed();
}
/**
* 回塞sql
*
* @param ms MappedStatement
* @param newSqlSource SqlSource
* @return MappedStatement
*/
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
builder.keyProperty(ms.getKeyProperties()[0]);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
}
注意
Executor 里面有三个方法，两个query 一个update 因为拦截的是查询，每个query方法里面的参数不一样，一定要精确到参数，否则会拦截失效，拦截器只要重写intercept方法就行(本章不详细讲解myabtis拦截器，后续单独开一章)
net.sf.jsqlparser包
这个包主要是关于sql语句如何获取语句里面的表和查询的列具体案例如下
public String deleteOrAnd(String sql) throws JSQLParserException {
Select select = (Select) new CCJSqlParserManager().parse(new StringReader(sql));
try {
// 单句sql 如 select a from ta_b
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
} catch (ClassCastException exception) {
// 使用了union 如
// select a from ta_b
// union all
// select b from tb_a
SetOperationList setOperationList = (SetOperationList) select.getSelectBody();
List<SelectBody> selectBodys = setOperationList.getSelects();
List<SelectBody> newSelectBodys = new ArrayList<>();
for (SelectBody selectBody : selectBodys) {
PlainSelect plainSelect = (PlainSelect) selectBody;
PlainSelect newPlainSelect = this.deleteOrAnd(plainSelect, sqlParam);
newSelectBodys.add(newPlainSelect);
}
setOperationList.setBracketsOpsAndSelects(setOperationList.getBrackets(), newSelectBodys, setOperationList.getOperations());
}
}
// 当 sql 为 select * from a，b 且sql中不止一张表 joins 中获取的表为所有的表
List<Join> joins = plainSelect.getJoins();
// 第一个表 当为单表的情况下，joins ==null
FromItem fromItem = plainSelect.getFromItem();
// 当select 为 select * from a join b 那么joins中的table为除第一张表以外的其余所有表
if (joinFromItem instanceof Table) {
// 是个表
}
// 如果获取的表是个子查询
if (joinFromItem instanceof SubSelect) {
PlainSelect joinPlainSelect = (PlainSelect) ((SubSelect)fromItem).getSelectBody();
// 如何在sql后面追加where条件
Expression whereParamExpression = CCJSqlParserUtil.parseCondExpression(“c=1”);
// 原sql的where条件
Expression where = plainSelect.getWhere();
if (where == null) {
plainSelect.setWhere(whereParamExpression);
} else {
AndExpression andExpression = new AndExpression(where, whereParamExpression);
plainSelect.setWhere(andExpression);
}
//如何获取查询字段
List<SelectItem> selectItems = plainSelect.getSelectItems();
Expression expression = ((SelectExpressionItem) selectItem).getExpression();
//查询字段可以是个字段也可以是子查询
if (expression instanceof Column) {
// 查询字段
}
// 子查询
if (expression instanceof SubSelect) {
// 子查询
}
主要是SubSelect，SelectItem，FromItem，Join，PlainSelect ，Table 这些class之间的转换
Java 如何获取系统配置的数据库url中的schema
Connection connection = dataSource.getConnection();
return connection.getCatalog();
