import net.sf.jsqlparser.expression.operators.conditional.AndExpression; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.schema.Table; import net.sf.jsqlparser.statement.select.*; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.util.CollectionUtils; import java.util.Arrays; import java.util.Iterator; import java.util.List; public class AppTest { public static void main(String[] args) throws Exception { System.out.println(autoAddFilter(Arrays.asList("t_order","t_order_goods"), "org_id in (1,2,3)")); } /** * 自动向订单表增加过滤条件 * * @throws Exception */ private static String autoAddFilter(List<String> tableNames, String filter) throws Exception { Select select = (Select) CCJSqlParserUtil.parse("select id,name from t_order a right join t_order_goods b on a.id=b.order_id and a.code=b.order_code and a.name=b.order_name where name like 'aaa%' order by id desc limit 10"); SelectBody selectBody = select.getSelectBody(); if (selectBody instanceof PlainSelect) { // 获取 From和Join模块 PlainSelect plainSelect = (PlainSelect) selectBody; // List<OrderByElement> orderByElements= plainSelect.getOrderByElements(); FromItem fromItem = plainSelect.getFromItem(); if (fromItem instanceof Table) { String tableName = ((Table) fromItem).getName(); if (tableNames.contains(tableName)) { String _filter=filter; if (fromItem.getAlias() != null) { _filter = fromItem.getAlias().getName() + "." + _filter; } plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(_filter))); } } List<Join> joins = plainSelect.getJoins(); if (!CollectionUtils.isEmpty(joins)) { for (Join joinItem : joins) { FromItem joinTable = joinItem.getRightItem(); if (joinTable instanceof Table) { String _tableName = ((Table) joinTable).getName(); if (tableNames.contains(_tableName)) { String _filter=filter; if (joinTable.getAlias() != null) { _filter = joinTable.getAlias().getName() + "." + _filter; } // plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(filter))); joinItem.setOnExpression(new AndExpression(joinItem.getOnExpression(),CCJSqlParserUtil.parseCondExpression(_filter))); } } } } return plainSelect.toString(); } else if (selectBody instanceof WithItem) { } else { } return null; } }
输出
SELECT id, name FROM t_order a RIGHT JOIN t_order_goods b ON a.id = b.order_id AND a.code = b.order_code AND a.name = b.order_name AND b.org_id IN (1, 2, 3) WHERE name LIKE 'aaa%' AND a.org_id IN (1, 2, 3) ORDER BY id DESC LIMIT 10
如果要做数据权限自动过滤,则可以在 mybatis的切面里 获取到原 MappedStatement ,再获取 源Sql,经过 jSqlParser 修改后,重新生成新的 MappedStatement 来执行