JSqlParser 简介
JSqlParser是一个SQL语句解析器，支持常见数据库包含Oracle、SqlServer、MySQL、PostgreSQL，可以直接操作由语句生成的对象并生成新的sql，也可以使用访问者模式访问sql转换而来的对象
使用
String sqlStr = "SELECT\n" +
"\tsu.dept_id `deptId`,\n" +
"\tsu.user_id,\n" +
"\tsr.role_id,\n" +
"\tsu.user_name,\n" +
"\tsd.dept_name,\n" +
"\tsr.role_name\n" +
"FROM\n" +
"\tsys_user AS su\n" +
"JOIN sys_dept sd ON su.dept_id = sd.dept_id\n" +
"JOIN sys_user_role sur ON sur.user_id = su.user_id\n" +
"JOIN sys_role sr ON sur.role_id = sr.role_id\n" +
"WHERE\n" +
"\tsd.dept_name = '研发部门'\n" +
"\tand su.user_name = 'admin'\n" +
"\tand su.dept_id = 103\n" +
"\tor sr.role_name = '超级管理员'\n" +
"ORDER BY\n" +
"\tsd.create_time DESC";
Select querySql = (Select)CCJSqlParserUtil.parse(sqlStr);
querySql.getSelectBody().accept(new SelectVisitorAdapter () {
@Override
public void visit(PlainSelect plainSelect) {
log.info("--------------查询列名----------------------------------------");
plainSelect.getSelectItems().stream().forEach(selectItem -> {
selectItem.accept(new SelectItemVisitorAdapter() {
@Override
public void visit(SelectExpressionItem selectExpressionItem) {
log.info(selectExpressionItem.getExpression().toString());
if (selectExpressionItem.getAlias()!=null) {
log.info("列别名 {}",selectExpressionItem.getAlias().getName());
}
}
});
});
log.info("--------------From Table Info----------------------------------------");
log.info(plainSelect.getFromItem().toString());
if (plainSelect.getFromItem().getAlias()!=null) {
log.info("表别名"+plainSelect.getFromItem().getAlias().getName());
}
log.info("--------------Join Table Info----------------------------------------");
plainSelect.getJoins().stream().forEach(join -> {
log.info(join.toString());
log.info("关联表：{} ",join.getRightItem());
if (join.getRightItem().getAlias()!=null) {
log.info("关联表别名：{}",join.getRightItem().getAlias().getName());
}
log.info("关联条件：{}",join.getOnExpression().toString());
});
log.info("--------------Where Info----------------------------------------");
plainSelect.getWhere().accept(new ExpressionVisitorAdapter() {
@Override
public void visitBinaryExpression(BinaryExpression expr) {
log.info("表达式：{}",expr.toString());
log.info("表达式左侧：{}",expr.getLeftExpression().toString());
log.info("表达式右侧：{}",expr.getRightExpression().toString());
}
});
log.info("--------------增加查询条件----------------------------------------");
try {
plainSelect.setWhere(new AndExpression(CCJSqlParserUtil.parseCondExpression("1=1"),plainSelect.getWhere()));
} catch (JSQLParserException e) {
throw new RuntimeException(e);
}
}
});
log.info("语句：{}",querySql.toString());