基础篇
JSqlParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。
支持Oracle，SqlServer，MySQL，PostgreSQL等常用数据库。但各种数据库系统的SQL语法都在动态变化，可以解析某些（不是全部）。
JSqlParser就是一个把SQL转换为JAVA对象操作的工具包，但是发现此类文章较少，文档也不太详细，所以写个博客供参考。
入门
添加POM
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>3.2</version>
</dependency>
编写简单的测试类
public void testSelect() throws JSQLParserException {
// 使用工具类把SQL转换为Select对象
Select select = (Select) CCJSqlParserUtil.parse("SELECT username,age,sex FROM user");
SelectBody selectBody = select.getSelectBody();
System.err.println(selectBody);
}
源码结构
expression：SQL构建相关类，比如EqualsTo、InExpression等表达式用于构建SQL。
parser： SQL解析相关类，比如CCJSqlParserUtil。
schema：主要存放数据库schema相关的类 ，比如表、列等。
statement：封装了数据库操作对象，create、insert、delete、select等
util： 各种工具类、不同DB版本、SQL标准等处理类，如SelectUtils、DatabaseType等。
JSqlParser支持WHERE及多表操作SQL构建
单表WHERE
/**
* 单表SQL查询
*
* @throws JSQLParserException
*/
public void testSelectOneTable() throws JSQLParserException {
// 单表全量
Table table = new Table("test");
Select select = SelectUtils.buildSelectFromTable(table);
System.err.println(select); // SELECT * FROM test
// 指定列查询
Select buildSelectFromTableAndExpressions = SelectUtils.buildSelectFromTableAndExpressions(new Table("test"), new Column("col1"), new Column("col2"));
System.err.println(buildSelectFromTableAndExpressions); // SELECT col1, col2 FROM test
// WHERE =
EqualsTo equalsTo = new EqualsTo(); // 等于表达式
equalsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
equalsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 转换为更细化的Select对象
plainSelect.setWhere(equalsTo);
System.err.println(plainSelect);// SELECT * FROM test WHERE test.user_id = '123456'
// WHERE != <>
NotEqualsTo notEqualsTo = new NotEqualsTo();
notEqualsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
notEqualsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody();
plainSelectNot.setWhere(notEqualsTo);
System.err.println(plainSelectNot);// SELECT * FROM test WHERE test.user_id <> '123456'
// 其他运算符, 参考上面代码添加表达式即可
GreaterThan gt = new GreaterThan(); // ">"
GreaterThanEquals geq = new GreaterThanEquals(); // ">="
MinorThan mt = new MinorThan(); // "<"
MinorThanEquals leq = new MinorThanEquals();// "<="
IsNullExpression isNull = new IsNullExpression(); // "is null"
isNull.setNot(true);// "is not null"
LikeExpression nlike = new LikeExpression();
nlike.setNot(true); // "not like"
Between bt = new Between();
bt.setNot(true);// "not between"
// WHERE LIKE
LikeExpression likeExpression = new LikeExpression(); // 创建Like表达式对象
likeExpression.setLeftExpression(new Column("username")); // 表达式左边
likeExpression.setRightExpression(new StringValue("张%")); // 右边表达式
PlainSelect plainSelectLike = (PlainSelect) select.getSelectBody();
plainSelectLike.setWhere(likeExpression);
System.err.println(plainSelectLike); // SELECT * FROM test WHERE username LIKE '张%'
// WHERE IN
Set<String> deptIds = Sets.newLinkedHashSet(); // 创建IN范围的元素集合
deptIds.add("0001");
deptIds.add("0002");
ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList())); // 把集合转变为JSQLParser需要的元素列表
InExpression inExpression = new InExpression(new Column("dept_id "), itemsList); // 创建IN表达式对象，传入列名及IN范围列表
PlainSelect plainSelectIn = (PlainSelect) select.getSelectBody();
plainSelectIn.setWhere(inExpression);
System.err.println(plainSelectIn); // SELECT * FROM test WHERE dept_id IN ('0001', '0002')
// WHERE BETWEEN AND
Between between = new Between();
between.setBetweenExpressionStart(new LongValue(18)); // 设置起点值
between.setBetweenExpressionEnd(new LongValue(30)); // 设置终点值
between.setLeftExpression(new Column("age")); // 设置左边的表达式，一般为列
PlainSelect plainSelectBetween = (PlainSelect) select.getSelectBody();
plainSelectBetween.setWhere(between);
System.err.println(plainSelectBetween); // SELECT * FROM test WHERE age BETWEEN 18 AND 30
// WHERE AND 多个条件结合,都需要成立
AndExpression andExpression = new AndExpression(); // AND 表达式
andExpression.setLeftExpression(equalsTo); // AND 左边表达式
andExpression.setRightExpression(between); // AND 右边表达式
PlainSelect plainSelectAnd = (PlainSelect) select.getSelectBody();
plainSelectAnd.setWhere(andExpression);
System.err.println(plainSelectAnd); // SELECT * FROM test WHERE test.user_id = '123456' AND age BETWEEN 18 AND 30
// WHERE OR 多个条件满足一个条件成立返回
OrExpression orExpression = new OrExpression();// OR 表达式
orExpression.setLeftExpression(equalsTo); // OR 左边表达式
orExpression.setRightExpression(between); // OR 右边表达式
PlainSelect plainSelectOr = (PlainSelect) select.getSelectBody();
plainSelectOr.setWhere(orExpression);
System.err.println(plainSelectOr); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30
// ORDER BY 排序
OrderByElement orderByElement = new OrderByElement(); // 创建排序对象
orderByElement.isAsc(); // 设置升序排列 从小到大
orderByElement.setExpression(new Column("col01")); // 设置排序字段
PlainSelect plainSelectOrderBy = (PlainSelect) select.getSelectBody();
plainSelectOrderBy.addOrderByElements(orderByElement);
System.err.println(plainSelectOrderBy); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30 ORDER BY col01
}
多表JOIN
/**
* 多表SQL查询
* JOIN / INNER JOIN: 如果表中有至少一个匹配，则返回行
* LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
* RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
* FULL JOIN: 只要其中一个表中存在匹配，就返回行
*/
public void testSelectManyTable() {
Table t1 = new Table("tab1").withAlias(new Alias("t1").withUseAs(true)); // 表1
Table t2 = new Table("tab2").withAlias(new Alias("t2", false)); // 表2
PlainSelect plainSelect = new PlainSelect().addSelectItems(new AllColumns()).withFromItem(t1); // SELECT * FROM tab1 AS t1
// JOIN ON 如果表中有至少一个匹配，则返回行
Join join = new Join(); // 创建Join对象
join.withRightItem(t2); // 添加Join的表 JOIN t2 =>JOIN tab2 t2
EqualsTo equalsTo = new EqualsTo(); // 添加 = 条件表达式 t1.user_id = t2.user_id
equalsTo.setLeftExpression(new Column(t1, "user_id "));
equalsTo.setRightExpression(new Column(t2, "user_id "));
join.withOnExpression(equalsTo);// 添加ON
plainSelect.addJoins(join);
System.err.println(plainSelect); // SELECT * FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id = t2.user_id
// 设置join参数可实现其他类型join
// join.setLeft(true); LEFT JOIN
// join.setRight(true); RIGHT JOIN
// join.setFull(true); FULL JOIN
// join.setInner(true);
}
SQL函数
/**
* SQL 函数
* SELECT function(列) FROM 表
*/
public void testFun() throws JSQLParserException {
Table t1 = new Table("tab1").withAlias(new Alias("t1").withUseAs(true)); // 表1
PlainSelect plainSelect = new PlainSelect();
plainSelect.setFromItem(t1); // 设置FROM t1= > SELECT FROM tab1 AS t1
List<SelectItem> selectItemList = new ArrayList<>(); // 查询元素集合
SelectExpressionItem selectExpressionItem001 = new SelectExpressionItem(); // 元素1表达式
selectExpressionItem001.setExpression(new Column(t1,"col001"));
SelectExpressionItem selectExpressionItem002 = new SelectExpressionItem(); // 元素2表达式
selectExpressionItem002.setExpression(new Column(t1,"col002"));
selectItemList.add(0, selectExpressionItem001); // 添加入队
selectItemList.add(1, selectExpressionItem002); // 添加入队
// COUNT
SelectExpressionItem selectExpressionItemCount = new SelectExpressionItem(); // 创建函数元素表达式
selectExpressionItemCount.setAlias(new Alias("count")); // 设置别名
Function function = new Function(); // 创建函数对象 Function extends ASTNodeAccessImpl implements Expression
function.setName("COUNT"); // 设置函数名
ExpressionList expressionListCount = new ExpressionList(); // 创建参数表达式
expressionListCount.setExpressions(Collections.singletonList(new Column(t1, "id")));
function.setParameters(expressionListCount); // 设置参数
selectExpressionItemCount.setExpression(function);
selectItemList.add(2,selectExpressionItemCount);
plainSelect.setSelectItems(selectItemList); // 添加查询元素集合入select对象
System.err.println(plainSelect); // SELECT t1.col001, t1.col002, COUNT(t1.id) AS count FROM tab1 AS t1
}
JSqlParser进行SQL解析
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.operators.relational.NamedExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.collections.CollectionUtils;
import sun.net.www.content.text.plain;
import java.util.List;
import java.util.Objects;
public class Main {
static String sql1 = "select t1.f1,t1.f2,t2.id,count(*) from table t1 left join table1 t2 right join (select * from table2) t3 where t1.id='12121' or (t1.id between 1 and 3 and t1.id>'22112') group by t.f1 order by t.f1 desc,tf2 asc limit 1,20";
static String sql2 = "insert into table(f1,f2) values (1,2)";
static String sql2_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
static String sql3 = "update table set f1=2,f2=3 where f1=1212";
static String sql3_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
static String sql4_1 = "delete from table where 1=1";
static String sql_5 = "create table table_name2 as select * from table_name1 t1 where t1.id = '333'";
static String sql5_1 = "CREATE TABLE `gen_table` (\n" +
" `table_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
" `table_name` varchar(200) DEFAULT '' COMMENT '表名称',\n" +
" `table_comment` varchar(500) DEFAULT '' COMMENT '表描述',\n" +
" `sub_table_name` varchar(64) DEFAULT NULL COMMENT '关联子表的表名',\n" +
" `sub_table_fk_name` varchar(64) DEFAULT NULL COMMENT '子表关联的外键名',\n" +
" `class_name` varchar(100) DEFAULT '' COMMENT '实体类名称',\n" +
" `tpl_category` varchar(200) DEFAULT 'crud' COMMENT '使用的模板（crud单表操作 tree树表操作 sub主子表操作）',\n" +
" `package_name` varchar(100) DEFAULT NULL COMMENT '生成包路径',\n" +
" `module_name` varchar(30) DEFAULT NULL COMMENT '生成模块名',\n" +
" `business_name` varchar(30) DEFAULT NULL COMMENT '生成业务名',\n" +
" `function_name` varchar(50) DEFAULT NULL COMMENT '生成功能名',\n" +
" `function_author` varchar(50) DEFAULT NULL COMMENT '生成功能作者',\n" +
" `gen_type` char(1) DEFAULT '0' COMMENT '生成代码方式（0zip压缩包 1自定义路径）',\n" +
" `gen_path` varchar(200) DEFAULT '/' COMMENT '生成路径（不填默认项目路径）',\n" +
" `options` varchar(1000) DEFAULT NULL COMMENT '其它生成选项',\n" +
" `create_by` varchar(64) DEFAULT '' COMMENT '创建者',\n" +
" `create_time` datetime DEFAULT NULL COMMENT '创建时间',\n" +
" `update_by` varchar(64) DEFAULT '' COMMENT '更新者',\n" +
" `update_time` datetime DEFAULT NULL COMMENT '更新时间',\n" +
" `remark` varchar(500) DEFAULT NULL COMMENT '备注',\n" +
" PRIMARY KEY (`table_id`)\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代码生成业务表';";
static String sql1_1 = "select `t2a_cust_c`.`CUST_ID` AS `CUST_ID`,`t2a_cust_c`.`CUST_NAME` AS `CUST_NAME`,`t2a_cust_c`.`CUST_EN_NAME` AS `CUST_EN_NAME`,`t2a_cust_c`.`CUST_STS` AS `cust_sts`,`t2a_cust_c`.`CUST_TYPE` AS `CUST_TYPE`,`t2a_cust_c`.`CERT_TYPE` AS `cert_type`,`t2a_cust_c`.`CERT_NO` AS `CERT_NO`,`t2a_cust_c`.`ORG_ID` AS `ORG_ID`,`t2a_cust_c`.`BIZ_SCOPE` AS `BIZ_SCOPE`,'' AS `NATION_CD`,NULL AS `INCOME_AMT`,`t2a_cust_c`.`CREATE_DT` AS `CREATE_DT`,'' AS `IS_STAFF`,`t2a_cust_c`.`IS_FREE_TRADE` AS `IS_FREE_TRADE`,`t2a_cust_c`.`CUST_NAT` AS `CUST_NAT`,`t2a_cust_c`.`PBC_INDUS` AS `pbc_indus`,'' AS `pbc_ocp`,`t2a_cust_c`.`CERT_INVALID_DT` AS `CERT_INVALID_DT` from `t2a_cust_c` union all select `t2a_cust_i`.`CUST_ID` AS `CUST_ID`,`t2a_cust_i`.`CUST_NAME` AS `CUST_NAME`,`t2a_cust_i`.`CUST_EN_NAME` AS `CUST_EN_NAME`,`t2a_cust_i`.`CUST_STS` AS `cust_sts`,`t2a_cust_i`.`CUST_TYPE` AS `CUST_TYPE`,`t2a_cust_i`.`CERT_TYPE` AS `cert_type`,`t2a_cust_i`.`CERT_NO` AS `CERT_NO`,`t2a_cust_i`.`ORG_ID` AS `ORG_ID`,'' AS `BIZ_SCOPE`,`t2a_cust_i`.`NATION_CD` AS `NATION_CD`,`t2a_cust_i`.`INCOME_AMT` AS `INCOME_AMT`,`t2a_cust_i`.`CREATE_DT` AS `CREATE_DT`,`t2a_cust_i`.`IS_STAFF` AS `IS_STAFF`,`t2a_cust_i`.`IS_FREE_TRADE` AS `IS_FREE_TRADE`,`t2a_cust_i`.`CUST_NAT` AS `CUST_NAT`,'' AS `pbc_indus`,`t2a_cust_i`.`PBC_OCP` AS `pbc_ocp`,`t2a_cust_i`.`CERT_INVALID_DT` AS `CERT_INVALID_DT` from `t2a_cust_i`";
public static void main(String[] args) {
testSimpleSelectSql(sql1_1);
// testSimpleInsertSql(sql2);
// testSimpleInsertSql(sql2_1);
// testSimpleUpdateSql(sql3);
// testSimpleDeleteSql(sql4_1);
// testSimpleCreateSql(sql5_1);
}
private static void testSimpleCreateSql(String sql_5) {
try {
Statement statement = CCJSqlParserUtil.parse(sql_5);
if (statement instanceof CreateTable) {
Table table = ((CreateTable) statement).getTable();
System.out.println(table);
Select select = ((CreateTable) statement).getSelect();
if (select != null){
String s = select.toString();
testSimpleSelectSql(s);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
//解析sql
public static void testSimpleSelectSql(String sql1) {
System.out.println("=================测试查询==================");
try {
Select select = (Select) CCJSqlParserUtil.parse(sql1);
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(select);
// 获取到查询sql中的所有表名,下面的逻辑是对sql的细致拆分
System.out.println("表名:" + tableList);
if (select.getSelectBody() instanceof PlainSelect) { // 普通查询
// 复杂sql会多次调用此处方法，所以抽出作为公共类使用
getSelectMsg(select);
}else if (select.getSelectBody() instanceof WithItem){ // WITH语句
}else if (select.getSelectBody() instanceof SetOperationList){ // INTERSECT、EXCEPT、MINUS、UNION语句
SetOperationList setOperationList = (SetOperationList)select.getSelectBody();
List<SelectBody> selects = setOperationList.getSelects();
for (int i = 0; i < selects.size(); i++) {
// 此处又是符合普通sql的拆解逻辑
getSelectMsg(select);
}
}else if (select.getSelectBody() instanceof ValuesStatement){ // VALUES语句
}
} catch (JSQLParserException e) {
e.printStackTrace();
}
System.out.println("=================测试查询==================");
}
public static void testSimpleInsertSql(String sql) {
System.out.println("=================测试插入sql==================");
System.out.println("测试sql:" + sql);
try {
Insert insert = (Insert) CCJSqlParserUtil.parse(sql);
System.out.println("插入的表" + insert.getTable());
System.out.println("插入的列" + insert.getColumns());
if (Objects.nonNull(insert.getSelect())) {
SelectBody selectBody = insert.getSelect().getSelectBody();
System.out.println("来自：" + selectBody);
} else {
System.out.println("普通插入");
System.out.println("插入的值" + insert.getItemsList());
}
} catch (JSQLParserException e) {
e.printStackTrace();
}
System.out.println("=================测试插入sql==================");
}
public static void testSimpleUpdateSql(String sql) {
System.out.println("=================测试更新sql==================");
System.out.println("测试sql:" + sql);
try {
Update update = (Update) CCJSqlParserUtil.parse(sql);
System.out.println("更新的表" + update.getTable());
System.out.println("更新的列" + update.getColumns());
System.out.println("更新的值" + update.getExpressions());
System.out.println("条件" + update.getWhere());
} catch (JSQLParserException e) {
e.printStackTrace();
}
System.out.println("=================测试更新sql==================");
}
public static void testSimpleDeleteSql(String sql) {
System.out.println("=================测试删除sql==================");
System.out.println("测试sql:" + sql);
try {
Delete delete = (Delete) CCJSqlParserUtil.parse(sql);
System.out.println("删除的表" + delete.getTable());
System.out.println("条件的列" + delete.getWhere());
} catch (JSQLParserException e) {
e.printStackTrace();
}
System.out.println("=================测试删除sql==================");
}
public static String joinTypeStr(Join join) {
if (join.isLeft()) {
return "左连接";
}
if (join.isRight()) {
return "左连接";
}
if (join.isFull()) {
return "全连接";
}
if (join.isCross()) {
return "交叉连接";
}
return null;
}
public static void getSelectMsg(Select select){
PlainSelect plain = (PlainSelect) select.getSelectBody();
List<Join> joins = plain.getJoins();
if (CollectionUtils.isNotEmpty(joins)) {
for (Join join : joins) {
FromItem rightItem = join.getRightItem();
if (rightItem instanceof Table) {
Table table = (Table) (rightItem);
System.out.println("连接类型:" + joinTypeStr(join) + " 表：" + table.getName() + " 别名：" + table.getAlias());
} else if (rightItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) (rightItem);
System.out.println("连接类型:" + joinTypeStr(join) + " 子查询：" + subSelect.getSelectBody() + " 别名：" + rightItem.getAlias());
}
}
}
List<SelectItem> selectItems = plain.getSelectItems();
for (SelectItem selectItem : selectItems) {
if (selectItem instanceof AllColumns) {
System.out.println("获取的是表中的全部列: * ");
continue;
}
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
Expression expression = selectExpressionItem.getExpression();
//判断表达式是否是函数
if (expression instanceof Function) {
Function function = (Function) expression;
NamedExpressionList namedParameters = function.getNamedParameters();
if (namedParameters != null) {
List<Expression> expressions = namedParameters.getExpressions();
System.out.println(expressions);
}
System.out.println("函数：" + ((Function) expression).getName());
boolean allColumns = function.isAllColumns();
System.out.println("传入的是全部列：" + allColumns);
//判断表达式是否是列
} else if (expression instanceof Column) {
System.out.println("查询值：" + ((Column) expression).getColumnName());
}
}
// System.out.println("表名:" + tableList);
Expression where = plain.getWhere();
if (where != null) {
System.out.println("条件:" + where);
}
//排序
List<OrderByElement> orderByElements = plain.getOrderByElements();
if (Objects.nonNull(orderByElements)) {
for (OrderByElement orderByElement : orderByElements) {
Expression expression = orderByElement.getExpression();
if (expression instanceof Column) {
Column column = (Column) (expression);
System.out.println("排序字段:" + column.getColumnName() + "," + (orderByElement.isAsc() ? "正序" : "倒序"));
}
}
}
//获取分组
GroupByElement groupBy = plain.getGroupBy();
if (Objects.nonNull(groupBy)) {
List<Expression> groupByExpressions = groupBy.getGroupByExpressions();
for (Expression groupByExpression : groupByExpressions) {
if (groupByExpression instanceof Column) {
Column column = (Column) (groupByExpression);
System.out.println("分组字段:" + column.getColumnName());
}
}
}
//分页
Limit limit = plain.getLimit();
if (Objects.nonNull(limit)) {
System.out.println("行:" + limit.getRowCount());
System.out.println("偏移量:" + limit.getOffset());
}
}
}
评论区