胖虎的工具箱-编程导航 胖虎的工具箱-编程导航

JSqlparser 使用攻略（高效的SQL解析工具）

2年前 (2022) 程序员胖胖胖虎阿
396 0 0

JSqlparser github地址

目录

    • Maven 引用
      • 远程仓库
      • 依赖包
    • SQL解析
      • 获取SQL中的信息
      • 创建Select的方式
        • 创建Select（非SQL String 创建）
      • Insert 插入字段和值
      • where条件中字段替换
      • 解析SQL例子
        • 获取所有tableNames
        • 自动生成别名
        • SQL函数
        • 单表where条件拼装
        • JOIN 拼装
    • 校验SQL
    • 总结

JSqlParser is a SQL statement parser. It translates SQLs in a traversable hierarchy of Java classes. JSqlParser is not limited to one database but provides support for a lot of specials of Oracle, SqlServer, MySQL, PostgreSQL … To name some, it has support for Oracles join syntax using (+), PostgreSQLs cast syntax using ::, relational operators like != and so on.

JSqlParser 是SQL语句分析的插件，他使用Java语言去解析SQL。sqlparser提供很多的数据库语法解析支持其中支持很多oracle的特殊语法。

可以结合mybatis的拦截修改SQL来实现多租户、SQL拼接甚至联表的功能。

可以参考我之前写的MyBatis 通过拦截修改SQL

Maven 引用

远程仓库

<repositories>
     <repository>
         <id>jsqlparser-snapshots</id>
         <snapshots>
             <enabled>true</enabled>
         </snapshots>
         <url>https://oss.sonatype.org/content/groups/public/</url>
     </repository>
</repositories>

依赖包

<dependency>
	<groupId>com.github.jsqlparser</groupId>
	<artifactId>jsqlparser</artifactId>
	<version>4.4</version>
</dependency>

SQL解析

获取SQL中的信息

public class TestSqlparser {

    public static void main(String[] args) throws JSQLParserException {
        // 根据sql创建select
        Select stmt = (Select) CCJSqlParserUtil.parse("SELECT col1 AS a, col2 AS b, col3 AS c FROM table T WHERE col1 = 10 AND col2 = 20 AND col3 = 30");

        Map<String, Expression> map = new HashMap<>();
        Map<String, String> mapTable = new HashMap<>();

        ((PlainSelect) stmt.getSelectBody()).getFromItem().accept(new FromItemVisitorAdapter() {
            @Override
            public void visit(Table table) {
                // 获取别名 => 表名
                mapTable.put(table.getAlias().getName(), table.getName());
            }
        });

        ((PlainSelect) stmt.getSelectBody()).getWhere().accept(new ExpressionVisitorAdapter() {
            @Override
            public void visit(AndExpression expr) {
                // 获取where表达式
                System.out.println(expr);
            }
        });

        for (SelectItem selectItem : ((PlainSelect)stmt.getSelectBody()).getSelectItems()) {
            selectItem.accept(new SelectItemVisitorAdapter() {
                @Override
                public void visit(SelectExpressionItem item) {
                    // 获取字段别名 => 字段名
                    map.put(item.getAlias().getName(), item.getExpression());
                }
            });
        }

        System.out.println("map " + map);
        System.out.println("mapTables" + mapTable);
    }
}

JSqlparser 使用攻略（高效的SQL解析工具）

创建Select的方式

创建Select（非SQL String 创建）

@Test
public void testCreateSelect1 () throws JSQLParserException {
     Select select1 = SelectUtils.buildSelectFromTable(new Table("mytable"));
     Select select2 = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), new Column("a"), new Column("b"));
     Select select3 = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), "a+b", "test");


     System.out.println(select1.toString());
     System.out.println(select2.toString());
     System.out.println(select3.toString());
 }

JSqlparser 使用攻略（高效的SQL解析工具）

Insert 插入字段和值

@Test
public void testCreateSelect2() throws JSQLParserException {
    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() {

        public void visit(SubSelect subSelect) {
            throw new UnsupportedOperationException("Not supported yet.");
        }

        public void visit(ExpressionList expressionList) {
            expressionList.getExpressions().add(new LongValue(5));
        }

        @Override
        public void visit(NamedExpressionList namedExpressionList) {

        }

        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());
}

JSqlparser 使用攻略（高效的SQL解析工具）

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

public class ReplaceColumnValues {

    static class ReplaceColumnAndLongValues extends ExpressionDeParser {

        @Override
        public void visit(StringValue stringValue) {
            this.getBuffer().append("?");
        }

        @Override
        public void visit(LongValue longValue) {
            this.getBuffer().append("?");
        }
    }

    public static String cleanStatement(String sql) throws JSQLParserException {
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expr = new ReplaceColumnAndLongValues();

        SelectDeParser selectDeparser = new SelectDeParser(expr, buffer);
        expr.setSelectVisitor(selectDeparser);
        expr.setBuffer(buffer);
        StatementDeParser stmtDeparser = new StatementDeParser(expr, selectDeparser, buffer);

        Statement stmt = CCJSqlParserUtil.parse(sql);

        stmt.accept(stmtDeparser);
        return stmtDeparser.getBuffer().toString();
    }

    public static void main(String[] args) throws JSQLParserException {
        System.out.println(cleanStatement("SELECT 'abc', 5 FROM mytable WHERE col='test'"));
        System.out.println(cleanStatement("UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'"));
        System.out.println(cleanStatement("INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')"));
        System.out.println(cleanStatement("DELETE FROM table1 where col=5 and col2=4"));
    }
}

输出：

SELECT ?, ? FROM mytable WHERE col = ?
UPDATE table1 A SET A.columna = ? WHERE A.cod_table = ?
INSERT INTO example (num, name, address, tel) VALUES (?, ?, ?, ?)
DELETE FROM table1 WHERE col = ? AND col2 = ?

where条件中字段替换

替换条件字段col_1到col1

@Test
 public void replace () throws JSQLParserException {
     Select stmt = (Select) CCJSqlParserUtil.parse("SELECT col1 AS a, col2 AS b, col3 AS c FROM table WHERE col_1 = 10 AND col_2 = 20 AND col_3 = 30");
     System.out.println("before " + stmt.toString());

     ((PlainSelect)stmt.getSelectBody()).getWhere().accept(new ExpressionVisitorAdapter() {
         @Override
         public void visit(Column column) {
             column.setColumnName(column.getColumnName().replace("_", ""));
         }
     });

     System.out.println("after " + stmt.toString());
 }

JSqlparser 使用攻略（高效的SQL解析工具）

解析SQL例子

Statement stmt = CCJSqlParserUtil.parse("SELECT * FROM tab1");
Statements stmt = CCJSqlParserUtil.parseStatements("SELECT * FROM tab1; SELECT * FROM tab2");
Expression expr = CCJSqlParserUtil.parseExpression("a*(5+mycolumn)");

可以直接将String SQL片段解析成Expression再将expr插入到SQL语句中。

获取所有tableNames

Statement statement = CCJSqlParserUtil.parse("SELECT * FROM MY_TABLE1");
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(selectStatement);

自动生成别名

Select select = (Select) CCJSqlParserUtil.parse("select a,b,c from test");
final AddAliasesVisitor instance = new AddAliasesVisitor();
select.getSelectBody().accept(instance);

结果：
SELECT a AS A1, b AS A2, c AS A3 FROM test

SQL函数

/**
 * SQL 函数
 * SELECT function(列) FROM 表
 */
@Test
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
}

单表where条件拼装

/**
 * 单表SQL查询
 *
 * @throws JSQLParserException
 */
@Test
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: 只要其中一个表中存在匹配，就返回行
  */
 @Test
 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

String sql = "DROP INDEX IF EXISTS idx_tab2_id;";

// validate statement if it's valid for all given databases.
Validation validation = new Validation(Arrays.asList(DatabaseType.SQLSERVER, DatabaseType.MARIADB,
                DatabaseType.POSTGRESQL, DatabaseType.H2), sql);
List<ValidationError> errors = validation.validate();

// validate against pre-defined FeaturesAllowed.DML set
String sql = "CREATE TABLE tab1 (id NUMERIC(10), val VARCHAR(30))";
Validation validation = new Validation(Arrays.asList(FeaturesAllowed.DML), sql);
List<ValidationError> errors = validation.validate();
// only DML is allowed, got error for using a DDL statement
log.error (errors);

Validates metadata such as names of tables, views, columns for their existence or non-existence

java.sql.Connection connection = ...;
String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null";
Validation validation = new Validation(Arrays.asList(new JdbcDatabaseMetaDataCapability(connection, 	
// NamesLookup: Databases handle names differently
        NamesLookup.UPPERCASE)), sql);
List<ValidationError> errors = validation.validate();

// do something else with the parsed statements
Statements statements = validation.getParsedStatements();

// check for validation-errors
if (!errors.isEmpty()) {
    ...
}

总结

我们可以借助JSqlparser来解析SQL并且动态拼接生成SQL，在Mybatis-plus中的租户其实也是类似这样实现的。甚至有兴趣的同学可以自己做一个SQL拼装器，将前台筛选的条件转换为SQL进行查询。所有的查询字段、条件、联表等等都做成动态拼装。

参考文章

JSqlParser入门系列(3)-JSqlParser之WHERE、多表Join、函数构建SQL
SqlParser

版权声明：程序员胖胖胖虎阿 发表于 2022年11月24日 下午11:56。
转载请注明：JSqlparser 使用攻略（高效的SQL解析工具） | 胖虎的工具箱-编程导航

相关文章

把字符串转换成整数
程序员胖胖胖虎阿
216
IntelliJ IDEA2021激活工具(IDEA全新激活破解教程,亲测有效!!)
程序员胖胖胖虎阿
292
IDEA2022激活码激活教程(IntelliJ IDEA最新版注册码,激活至2099年)
程序员胖胖胖虎阿
123
spring cloud gateway Route配置
程序员胖胖胖虎阿
296
Java之面向对象3（终结篇）
程序员胖胖胖虎阿
330
IDEA永久激活工具(IDEA2022.2激活码激活教程,激活破解教程)
程序员胖胖胖虎阿
145

暂无评论

暂无评论...
致力于称为最好的程序员导航网站

友链申请 免责声明 关于我们

Copyright © 2022  胖虎的工具箱-编程导航   陇ICP备2022001249号