一，jsqlparser简介
jsqlparser是sql语法解析器，可以生成java类层次结构（是用java开发的解析器）。
主页地址：http://jsqlparser.sourceforge.net
使用过程中的bug
1.tablenamefinder只能得到所有不重复的表名tablename，如果发生重复只能得到一个，不能重复获取
2.left join 获取查询条件的时候，没有left关键字
-----------join连接查询---------- [INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name] [JOIN table_name2 ON table_name1.column_name = table_name2.column_name] [RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name]
3.查询的过程中如果字段是* ,得到的结果也是*，目前没有发现输入* 直接返回该表格的所有字段
二，jsqlparser功能
总结来说jsqlparser有以下主要功能：
1.解析sql语句
2.加法和乘法的简单的表达式求值器
3.构建sql
三，jsqlparser使用
解析sql语句块
1.导入maven依赖
1 <!-- https://mvnrepository.com/artifact/net.sf.jsqlparser/jsqlparser --> 2 <dependency> 3 <groupId>net.sf.jsqlparser</groupId> 4 <artifactId>jsqlparser</artifactId> 5 <version>0.8.0</version> 6 </dependency>
2.参考代码
package com.jsqlparser; import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.expression.Expression; import net.sf.jsqlparser.expression.operators.conditional.OrExpression; import net.sf.jsqlparser.expression.operators.relational.EqualsTo; import net.sf.jsqlparser.parser.CCJSqlParserManager; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.schema.Column; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.select.*; import net.sf.jsqlparser.util.TablesNamesFinder; import net.sf.jsqlparser.util.deparser.ExpressionDeParser; import java.io.StringReader; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @describer: * @author:fanzhihui * @Date:2019/6/13 * @Time:11:50 */ public class SqlParser { /** * 查询字段 * @param sql * @return * @throws JSQLParserException */ private static List<String> test_select_items(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); List<SelectItem> selectitems = plain.getSelectItems(); List<String> str_items = new ArrayList<String>(); if (selectitems != null) { for (SelectItem selectitem : selectitems) { str_items.add(selectitem.toString()); } } return str_items; } /** * 查询表名 table * @param sql * @return * @throws JSQLParserException */ private static List<String> test_select_table(String sql) throws JSQLParserException { Statement statement = CCJSqlParserUtil.parse(sql); // System.out.println(statement.toString()); Select selectStatement = (Select) statement; TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); return tablesNamesFinder .getTableList(selectStatement); } /** * 查询 join * @param sql * @return * @throws JSQLParserException */ private static List<String> test_select_join(String sql) throws JSQLParserException { Statement statement = CCJSqlParserUtil.parse(sql); Select selectStatement = (Select) statement; PlainSelect plain = (PlainSelect) selectStatement.getSelectBody(); //System.out.println(plain.toString()); List<Join> joinList = plain.getJoins(); List<String> tablewithjoin = new ArrayList<String>(); if (joinList != null) { for (Join join : joinList) { join.setLeft(false); tablewithjoin.add(join.toString()); //注意 ， leftjoin rightjoin 等等的to string()区别 } } return tablewithjoin; } /** * 查询where */ private static String test_select_where(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); Expression where_expression = plain.getWhere(); return where_expression.toString(); } /** * 对where的结果进行解析 */ private static void testParseWhere1(String sql){ try { Select select = (Select) CCJSqlParserUtil.parse(sql); SelectBody selectBody = select.getSelectBody(); PlainSelect plainSelect = (PlainSelect)selectBody; Expression where = plainSelect.getWhere(); ExpressionDeParser expressionDeParser = new ExpressionDeParser(); plainSelect.getWhere().accept(expressionDeParser); // 此处根据where实际情况强转 最外层 EqualsTo equalsTo = (EqualsTo)where; System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); } catch (JSQLParserException e) { e.printStackTrace(); } } /** * where两个条件or连接 * 代码中有两个条件or连接，可回忆转成OrExpression,里面还是两个EqualsTo。 */ private static void testParseWhere2(String sql){ try { Select select = (Select)CCJSqlParserUtil.parse(sql); SelectBody selectBody = select.getSelectBody(); PlainSelect plainSelect = (PlainSelect)selectBody; Expression where = plainSelect.getWhere(); ExpressionDeParser expressionDeParser = new ExpressionDeParser(); plainSelect.getWhere().accept(expressionDeParser); // 此处根据where实际情况强转 最外层 OrExpression orExpression = (OrExpression)where; EqualsTo equalsTo = (EqualsTo)orExpression.getLeftExpression(); System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); System.out.println("--------"); equalsTo = (EqualsTo)orExpression.getRightExpression(); System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); } catch (JSQLParserException e) { e.printStackTrace(); } } /** * where三个条件or连接 *得到的第一层的leftExpression还是一个orExpression,rightExpression是一个EqualsTo */ private static void testParseWhere3(String sql){ try { Select select = (Select)CCJSqlParserUtil.parse(sql); SelectBody selectBody = select.getSelectBody(); PlainSelect plainSelect = (PlainSelect)selectBody; Expression where = plainSelect.getWhere(); ExpressionDeParser expressionDeParser = new ExpressionDeParser(); plainSelect.getWhere().accept(expressionDeParser); // 此处根据where实际情况强转 最外层 OrExpression orExpression = (OrExpression)where; OrExpression leftOrExpression = (OrExpression)orExpression.getLeftExpression(); EqualsTo equalsTo = (EqualsTo)leftOrExpression.getLeftExpression(); System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); System.out.println("--------"); equalsTo = (EqualsTo)leftOrExpression.getRightExpression(); System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); System.out.println("--------"); equalsTo = (EqualsTo)orExpression.getRightExpression(); System.out.println("Table:"+((Column)equalsTo.getLeftExpression()).getTable()); System.out.println("Field:"+((Column)equalsTo.getLeftExpression()).getColumnName()); System.out.println("equal:"+equalsTo.getRightExpression()); } catch (JSQLParserException e) { e.printStackTrace(); } } /** * 查询 group by * @param sql * @return * @throws JSQLParserException */ private static List<String> test_select_groupby(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); List<Expression> GroupByColumnReferences = plain .getGroupByColumnReferences(); List<String> str_groupby = new ArrayList<String>(); if (GroupByColumnReferences != null) { for (Expression groupByColumnReference : GroupByColumnReferences) { str_groupby.add(groupByColumnReference.toString()); } } return str_groupby; } /** * 查询order by */ private static List<String> test_select_orderby(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); List<OrderByElement> OrderByElements = plain.getOrderByElements(); List<String> str_orderby = new ArrayList<String>(); if (OrderByElements != null) { for (OrderByElement orderByElement : OrderByElements) { str_orderby.add(orderByElement.toString()); } } return str_orderby; } /** * 子查询 */ private static Map test_select_subselect(SelectBody selectBody) throws JSQLParserException { Map<String, String> map = new HashMap<String, String>(); if (selectBody instanceof PlainSelect) { List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems(); for (SelectItem selectItem : selectItems) { if (selectItem.toString().contains("(") && selectItem.toString().contains(")")) { map.put("selectItemsSubselect", selectItem.toString()); } } Expression where = ((PlainSelect) selectBody).getWhere(); String whereStr = where.toString(); if (whereStr.contains("(") && whereStr.contains(")")) { int firstIndex = whereStr.indexOf("("); int lastIndex = whereStr.lastIndexOf(")"); CharSequence charSequence = whereStr.subSequence(firstIndex, lastIndex + 1); map.put("whereSubselect", charSequence.toString()); } FromItem fromItem = ((PlainSelect) selectBody).getFromItem(); // System.out.println("111----"+((PlainSelect) selectBody).getFromItem()); // System.out.println(fromItem); if (fromItem instanceof SubSelect) { map.put("fromItemSubselect", fromItem.toString()); } } else if (selectBody instanceof WithItem) { SqlParser.test_select_subselect(((WithItem) selectBody).getSelectBody()); } return map; } /** * 单表操作测试 * @throws Exception */ @org.junit.Test public void jsqlparser2() throws Exception{ /** * 查询字段 */ System.out.println("-----------查询字段----------"); String sql = "SELECT id,name,age FROM TABLE1"; List<String> list = test_select_items(sql); System.out.println(list); /** * 查询表名 */ System.out.println("-----------查询表名----------"); List<String> list1 = test_select_table(sql); System.out.println(list1); /** * join */ System.out.println("-----------join连接查询表名----------"); String sql2 = "select id,name,age from table1 t1 left join table2 t2 on t1.id=t2.id left join table3 t3 on t3.id=t2.id"; List<String> list2 = test_select_table(sql2); System.out.println(list2); //inner join System.out.println("-----------join连接查询----------"); String sql7 = "SELECT column_name(s)\n" + "FROM table_name1\n" + "INNER JOIN table_name2 \n" + "ON table_name1.column_name=table_name2.column_name;"; List<String> selectJoin1 = test_select_join(sql7); System.out.println(selectJoin1); //left join String sql8 = "SELECT column_name(s)\n" + "FROM table_name1\n" + "LEFT JOIN table_name2 \n" + "ON table_name1.column_name=table_name2.column_name;"; List<String> selectJoin2 = test_select_join(sql8); System.out.println(selectJoin2); //right join String sql9 = "SELECT column_name(s)\n" + "FROM table_name1\n" + "right join table_name2 \n" + "ON table_name1.column_name=table_name2.column_name;"; List<String> selectJoin3 = test_select_join(sql9); System.out.println(selectJoin3); /** * where */ System.out.println("-----------where查询所有的条件----------"); String sql3="select * from table1 where id=1 and id=2"; String selectWhere = test_select_where(sql3); System.out.println(selectWhere); /** * where or条件查询 */ System.out.println("-----------where单个条件or连接----------"); String sqlw1 = "select *from A as a left join B on a.bid = B.id left join C on A.cid = C.id left join D on B.did = D.id where a.id = 23"; testParseWhere1(sqlw1); System.out.println("-----------where两个条件or连接--------"); String sqlw2 = "select *from A as a left join B on a.bid = B.id left join C on A.cid = C.id left join D on B.did = D.id where a.id = 23 or b.id = 34"; testParseWhere2(sqlw2); System.out.println("-----------where三个条件or连接--------"); String sqlw3 = "select *from A as a left join B on a.bid = B.id left join C on A.cid = C.id left join D on B.did = D.id where a.id = 23 or b.id = 34 or c.id = 54"; testParseWhere3(sqlw3); /** * 查询group by */ System.out.println("-----------group by--------"); String sql4="select id,name,age from core_user group by id"; List<String> select_groupby = test_select_groupby(sql4); System.out.println(select_groupby); /** * 查询order by */ System.out.println("-----------order by--------"); String sql5="select id,name,age from core_user order by id"; List<String> select_orderby = test_select_orderby(sql5); System.out.println(select_orderby); /** * 子查询 */ System.out.println("-----------子查询--------"); String sql6 = "select age,sex,(select dno from employee where salary >=5000) from (select dno from employee5 where salary >=5000) a where did in(select dno from employee3 where salary >=5000);"; CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select selectStatement = (Select) parserManager.parse(new StringReader(sql6)); Map map = test_select_subselect(selectStatement.getSelectBody()); System.out.println(map.toString()); //查询结果 /*{whereSubselect=(SELECT dno FROM employee3 WHERE salary >= 5000), fromItemSubselect=(SELECT dno FROM employee5 WHERE salary >= 5000) a, selectItemsSubselect=(SELECT dno FROM employee WHERE salary >= 5000) } 说明： whereSubselect 条件 fromItemSubselect 表格 selectItemsSubselect字段 */ } }
3.测试结果
-----------查询字段---------- [id, name, age] -----------查询表名---------- [TABLE1] -----------join连接查询表名---------- [table1, table2, table3] -----------join连接查询---------- [INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name] [JOIN table_name2 ON table_name1.column_name = table_name2.column_name] [RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name] -----------where查询所有的条件---------- id = 1 AND id = 2 -----------where单个条件or连接---------- Table:a Field:id equal:23 -----------where两个条件or连接-------- Table:a Field:id equal:23 -------- Table:b Field:id equal:34 -----------where三个条件or连接-------- Table:a Field:id equal:23 -------- Table:b Field:id equal:34 -------- Table:c Field:id equal:54 -----------group by-------- [id] -----------order by-------- [id] -----------子查询-------- {whereSubselect=(SELECT dno FROM employee3 WHERE salary >= 5000), fromItemSubselect=(SELECT dno FROM employee5 WHERE salary >= 5000) a, selectItemsSubselect=(SELECT dno FROM employee WHERE salary >= 5000)} Process finished with exit code 0
使用JSqlParser进行加法和乘法的非常简单的表达式求值器。ExpressionDeParser用于遍历和解释解析树。
1.参考代码
package com.jsqlparser; import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.expression.LongValue; import net.sf.jsqlparser.expression.operators.arithmetic.Addition; import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.util.deparser.ExpressionDeParser; import java.util.Stack; /** * @describer: * @author:fanzhihui * @Date:2019/6/14 * @Time:9:47 */ public class Expression { static void evaluate(String expr) throws JSQLParserException { final Stack<Long> stack = new Stack<Long>(); System.out.println("expr=" + expr); net.sf.jsqlparser.expression.Expression parseExpression= CCJSqlParserUtil.parseExpression(expr); ExpressionDeParser deparser = new ExpressionDeParser() { @Override public void visit(Addition addition) { super.visit(addition); long sum1 = stack.pop(); long sum2 = stack.pop(); stack.push(sum1 + sum2); } @Override public void visit(Multiplication multiplication) { super.visit(multiplication); long fac1 = stack.pop(); long fac2 = stack.pop(); stack.push(fac1 * fac2); } @Override public void visit(LongValue longValue) { super.visit(longValue); stack.push(longValue.getValue()); } }; StringBuilder b = new StringBuilder(); deparser.setBuffer(b); parseExpression.accept(deparser); System.out.println(expr + " = " + stack.pop() ); } public static void main( String[] args ) throws JSQLParserException { evaluate("4+5*6"); evaluate("4*5+6"); evaluate("4*(5+6)"); evaluate("4*(5+6)*(2+3)"); }
2.测试结果
expr=4+5*6 4+5*6 = 34 expr=4*5+6 4*5+6 = 26 expr=4*(5+6) 4*(5+6) = 44 expr=4*(5+6)*(2+3) 4*(5+6)*(2+3) = 220 Process finished with exit code 0
SQL构建
1.参考代码（简单的插入）
package com.jsqlparser; import net.sf.jsqlparser.expression.LongValue; import net.sf.jsqlparser.expression.operators.relational.ExpressionList; import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor; import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.schema.Column; import net.sf.jsqlparser.schema.Table; import net.sf.jsqlparser.statement.insert.Insert; import net.sf.jsqlparser.statement.select.Select; import net.sf.jsqlparser.statement.select.SubSelect; import net.sf.jsqlparser.util.SelectUtils; import org.junit.Test; /** * @describer: * @author:fanzhihui * @Date:2019/6/14 * @Time:10:34 */ public class CreateTabel { /** * 简单的插入 * @throws Exception */ public static void createTable() throws Exception{ Insert insert = (Insert) CCJSqlParserUtil.parse("insert into mytable (col1) values (1)"); System.out.println(insert.toString()); //adding a column insert.getColumns().add(new Column("col2")); //adding a value using a visitor insert.getItemsList().accept(new ItemsListVisitor() { @Override public void visit(SubSelect subSelect) { throw new UnsupportedOperationException("Not supported yet."); } @Override public void visit(ExpressionList expressionList) { expressionList.getExpressions().add(new LongValue(5)); } @Override public void visit(MultiExpressionList multiExprList) { throw new UnsupportedOperationException("Not supported yet."); } }); System.out.println(insert.toString()); //adding another column insert.getColumns().add(new Column("col3")); //adding another value (the easy way) ((ExpressionList)insert.getItemsList()).getExpressions().add(new LongValue(10)); System.out.println(insert.toString()); } @Test public void add() throws Exception{ createTable(); } }
2.测试结果
INSERT INTO mytable (col1) VALUES (1) INSERT INTO mytable (col1, col2) VALUES (1, 5) INSERT INTO mytable (col1, col2, col3) VALUES (1, 5, 10) Process finished with exit code 0
原文：
https://blog.csdn.net/wocan23/article/details/82383070
https://blog.csdn.net/liu22985342/article/details/82935980
https://blog.csdn.net/qq_26458903/article/details/89924187
声明：此博客为个人学习之用，如与其他作品雷同，纯属巧合，转载请指明出处！
分类:
jsqlparser
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理 了，记录一下