本文主要是介绍JSQLPARSER解析SQL知识入门，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
基础篇
JSQLParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。 支持Oracle，SQLServer，MySQL，PostgreSQL等常用数据库。但各种数据库系统的SQL语法都在动态变化，可以解析某些（不是全部）。
JSQLParser就是一个把SQL转换为JAVA对象操作的工具包，但是发现此类文章较少，文档也不太详细，所以写个博客供参考。 github地址
入门
添加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 LIKELikeExpression 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 INSet<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 ANDBetween 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)); // 表1Table t2 = new Table("tab2").withAlias(new Alias("t2", false)); // 表2PlainSelect 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 t2EqualsTo equalsTo = new EqualsTo(); // 添加 = 条件表达式 t1.user_id = t2.user_idequalsTo.setLeftExpression(new Column(t1, "user_id "));equalsTo.setRightExpression(new Column(t2, "user_id "));join.withOnExpression(equalsTo);// 添加ONplainSelect.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)); // 表1PlainSelect plainSelect = new PlainSelect();plainSelect.setFromItem(t1); // 设置FROM t1= > SELECT FROM tab1 AS t1List<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); // 添加入队// COUNTSelectExpressionItem selectExpressionItemCount = new SelectExpressionItem(); // 创建函数元素表达式selectExpressionItemCount.setAlias(new Alias("count")); // 设置别名Function function = new Function(); // 创建函数对象 Function extends ASTNodeAccessImpl implements Expressionfunction.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();}}//解析SQLpublic 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());}}
}
这篇关于JSQLPARSER解析SQL知识入门的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
