druid分析SQL
2019-08-07 本文已影响0人
anyly
sql解析工具 druid
1.优点:因为本身做连接池,sql解析比jsqlparse要彻底
2.操作sql方便,直接操作对象
需求:在所有的条件后面拼接
is_delete =0 or name IN ('lisr', 'gqq') // 如果表有别名自动获取,否则用表全名
代码->支持复杂的连表查询：
@Test
public void testDruidParserSql() {
String sql = SqlConfig.IN1;
//将string类型转换为sql对象
SQLExpr exp = SQLUtils.toSQLExpr(sql);
sqlExpr(exp);
System.out.println(" sql print :->>>>>>>>>>>>>>>> \r\n" + exp.toString());
}
/**
* @param exp
*/
public void sqlExpr(SQLExpr exp) {
if (exp != null && exp instanceof SQLQueryExpr) {
sqlSelect(((SQLQueryExpr) exp).getSubQuery());
} else if (exp instanceof SQLInSubQueryExpr) {
sqlSelect(((SQLInSubQueryExpr) exp).getSubQuery());
} else if (exp instanceof SQLBinaryOpExpr) {
sqlExpr(((SQLBinaryOpExpr) exp).getRight());
sqlExpr(((SQLBinaryOpExpr) exp).getRight());
} else if (exp instanceof SQLInListExpr
|| exp instanceof SQLIntegerExpr
|| exp instanceof SQLCharExpr
|| exp instanceof SQLPropertyExpr
) {
return;
} else {
System.out.println(exp.getClass() + ":遇到无法解析的对象:" + Thread.currentThread().getStackTrace()[1].getMethodName());
}
}
/**
* sql select
*
* @param sqlSelect
*/
private void sqlSelect(SQLSelect sqlSelect) {
sqlSelectQuery(sqlSelect.getQuery());
}
/**
* @param sqlSelectQuery
*/
private void sqlSelectQuery(SQLSelectQuery sqlSelectQuery) {
if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
if (((SQLSelectQueryBlock) sqlSelectQuery).getSelectList().size() > 1) {
List<SQLSelectItem> sqlSelectItems = ((SQLSelectQueryBlock) sqlSelectQuery).getSelectList();
sqlSelectItems.forEach(sqlSelectItem -> sqlExpr(sqlSelectItem.getExpr()));
}
sqlTableSource(((SQLSelectQueryBlock) sqlSelectQuery).getFrom());
} else if (sqlSelectQuery instanceof SQLUnionQuery) {
sqlSelectQuery(((SQLUnionQuery) sqlSelectQuery).getLeft());
sqlSelectQuery(((SQLUnionQuery) sqlSelectQuery).getRight());
} else {
System.out.println(sqlSelectQuery.getClass() + ":遇到无法解析的对象:" + Thread.currentThread().getStackTrace()[1].getMethodName());
}
}
/**
* @param sqlTableSource
*/
private void sqlTableSource(SQLTableSource sqlTableSource) {
// 右边是连接查询
if (sqlTableSource instanceof SQLJoinTableSource) {
sqlTableSource(((SQLJoinTableSource) sqlTableSource).getRight());
sqlTableSource(((SQLJoinTableSource) sqlTableSource).getLeft());
// 右边是子查询
} else if (sqlTableSource instanceof SQLSubqueryTableSource) {
sqlSelect(((SQLSubqueryTableSource) sqlTableSource).getSelect());
// 右边是 union
} else if (sqlTableSource instanceof SQLUnionQueryTableSource) {
sqlSelectQuery(((SQLUnionQueryTableSource) sqlTableSource).getUnion());
// 右边是table
} else if (sqlTableSource instanceof SQLExprTableSource) { // 当tablesource 为 SQLExprTableSource 时候即可拼接sql
String alias = getAlias(sqlTableSource);
System.out.println("table:" + sqlTableSource + "> alias:" + alias);
SQLSelectQueryBlock sqlSelectQueryBlock = getSelectQueryBlock(sqlTableSource);
if (sqlSelectQueryBlock != null) {
sqlSelectQueryBlock(sqlSelectQueryBlock, alias);
}
} else {
System.out.println(sqlTableSource.getClass() + ":遇到无法解析的对象:" + Thread.currentThread().getStackTrace()[1].getMethodName());
}
}
/**
* 通过迭代获取 MySqlSelectQueryBlock
*
* @param sqlObject
* @return
*/
private SQLSelectQueryBlock getSelectQueryBlock(SQLObject sqlObject) {
if (sqlObject.getParent() instanceof SQLSelectQueryBlock) {
return (SQLSelectQueryBlock) sqlObject.getParent();
} else {
return getSelectQueryBlock(sqlObject.getParent());
}
}
/**
* sql 拼接
*
* @param sqlSelectQueryBlock
* @param alias
*/
private void sqlSelectQueryBlock(SQLSelectQueryBlock sqlSelectQueryBlock, String alias) {
if (sqlSelectQueryBlock.getWhere() != null) {
sqlExpr(sqlSelectQueryBlock.getWhere());
}
SQLBinaryOpExpr sqlBinaryOpExpr = new SQLBinaryOpExpr(new SQLPropertyExpr(alias, "is_delete"),
new SQLIntegerExpr(0), SQLBinaryOperator.Equality);
// 第一个条件
// whereAdd(sqlSelectQueryBlock, sqlBinaryOpExpr);
SQLInListExpr sqlInListExpr = new SQLInListExpr();
sqlInListExpr.setExpr(new SQLPropertyExpr(alias, "name"));
List<SQLExpr> list = new ArrayList();
list.add(new SQLCharExpr("lisr"));
list.add(new SQLCharExpr("gqq"));
sqlInListExpr.setTargetList(list);
// 第二个条件
// whereAdd(sqlSelectQueryBlock, sqlInListExpr);
SQLBinaryOpExpr whereExpr = new SQLBinaryOpExpr(sqlBinaryOpExpr, SQLBinaryOperator.BooleanOr, sqlInListExpr);
whereAdd(sqlSelectQueryBlock, whereExpr);
}
public void whereAdd(SQLSelectQueryBlock sqlSelectQueryBlock, SQLExpr sqlExpr) {
if (sqlSelectQueryBlock.getWhere() != null) {
SQLExpr sqlExprWhere = sqlSelectQueryBlock.getWhere();
List<SQLObject> sqlList = sqlExprWhere.getChildren();
List<SQLObject> sqlObjectList = sqlExpr.getChildren();
if (sqlList != null && sqlList.size() > 1) {
// 原有的where 如果是or 则不拆
if (sqlExprWhere instanceof SQLBinaryOpExpr) {
if (!((SQLBinaryOpExpr) sqlExprWhere).getOperator().equals(SQLBinaryOperator.BooleanOr)) {
// 原有where 和需要拼接的where
if (sqlObjectList != null && sqlObjectList.size() > 1) {
if (!((SQLBinaryOpExpr) sqlExpr).getOperator().equals(SQLBinaryOperator.BooleanOr)) {
sqlObjectList.forEach(sqlObject -> {
if (!sqlList.contains(sqlObject)) {
sqlSelectQueryBlock.addWhere((SQLExpr) sqlObject);
}
});
} else {
if (!sqlList.contains(sqlExpr)) {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
}
} else {
if (!sqlList.contains(sqlExpr)) {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
}
} else {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
} else if (sqlExprWhere instanceof SQLInSubQueryExpr) {
sqlSelectQueryBlock.addWhere(sqlExpr);
} else {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
} else {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
} else {
sqlSelectQueryBlock.addWhere(sqlExpr);
}
}
/**
* 获取别名
*
* @param tableSource
* @return
*/
private String getAlias(SQLTableSource tableSource) {
if (StringUtils.isEmpty(tableSource.getAlias())) {
if (((SQLExprTableSource) tableSource).getExpr() instanceof SQLIdentifierExpr) {
return ((SQLIdentifierExpr) ((SQLExprTableSource) tableSource).getExpr()).getName();
}
}
return tableSource.getAlias();
}
- 下面看sql例子
example1 自带in
SELECT *
FROM t_sys_user tsu
WHERE tsu.name IN (
SELECT tsc.id
FROM t_sys_config tsc
WHERE tsc.id = 1
)
example1执行后结果
SELECT *
FROM t_sys_user tsu
WHERE tsu.name IN (
SELECT tsc.id
FROM t_sys_config tsc
WHERE tsc.id = 1
AND (tsc.is_delete = 0
OR tsc.name IN ('lisr', 'gqq'))
)
AND (tsu.is_delete = 0
OR tsu.name IN ('lisr', 'gqq'))
example2 带左连接
SELECT temp.*
FROM (
SELECT tsc.*
FROM t_sys_config tsc
LEFT JOIN t_sys_user tsu ON tsc.id = tsu.id
WHERE tsc.id = 1
ORDER BY tsc.id
) temp
WHERE temp.id = 1
AND temp.id = 2
example2执行后结果
SELECT temp.*
FROM (
SELECT tsc.*
FROM t_sys_config tsc
LEFT JOIN t_sys_user tsu ON tsc.id = tsu.id
WHERE tsc.id = 1
AND (tsu.is_delete = 0
OR tsu.name IN ('lisr', 'gqq'))
AND (tsc.is_delete = 0
OR tsc.name IN ('lisr', 'gqq'))
ORDER BY tsc.id
) temp
WHERE temp.id = 1
AND temp.id = 2
example3 连接子查询
SELECT tsu.*
FROM t_sys_user tsu
RIGHT JOIN (
SELECT *
FROM t_sys_config
) tsc
ON tsc.id = tsu.id
example3执行后结果
SELECT tsu.*
FROM t_sys_user tsu
RIGHT JOIN (
SELECT *
FROM t_sys_config
WHERE t_sys_config.is_delete = 0
OR t_sys_config.name IN ('lisr', 'gqq')
) tsc
ON tsc.id = tsu.id
WHERE tsu.is_delete = 0
OR tsu.name IN ('lisr', 'gqq')
example4 用union
SELECT *
FROM t_sys_user tsu
WHERE tsu.id = 1
AND tsu.is_delete = 0
UNION ALL
SELECT *
FROM t_sys_config tsc
WHERE tsc.id = 2
AND tsc.is_delete = 0
example4执行后结果
SELECT *
FROM t_sys_user tsu
WHERE tsu.id = 1
AND tsu.is_delete = 0
AND (tsu.is_delete = 0
OR tsu.name IN ('lisr', 'gqq'))
UNION ALL
SELECT *
FROM t_sys_config tsc
WHERE tsc.id = 2
AND tsc.is_delete = 0
AND (tsc.is_delete = 0
OR tsc.name IN ('lisr', 'gqq'))
example5 普通sql
SELECT *
FROM t_sys_user tsu
LEFT JOIN t_config tc
ON tc.id = tsu.id
AND tsu.name = 'test'
example5执行后结果
SELECT *
FROM t_sys_user tsu
LEFT JOIN t_config tc
ON tc.id = tsu.id
AND tsu.name = 'test'
WHERE (tc.is_delete = 0
OR tc.name IN ('lisr', 'gqq'))
AND (tsu.is_delete = 0
OR tsu.name IN ('lisr', 'gqq'))
