欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

關(guān)于JSqlparser使用攻略(高效的SQL解析工具)

 更新時間:2022年11月18日 10:12:26   作者:澄風(fēng)  
這篇文章主要介紹了關(guān)于JSqlparser使用攻略(高效的SQL解析工具),具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教

JSqlparser github地址

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提供很多的數(shù)據(jù)庫語法解析支持其中支持很多oracle的特殊語法。

可以結(jié)合mybatis的攔截修改SQL來實現(xiàn)多租戶、SQL拼接甚至聯(lián)表的功能。

可以參考我之前寫的MyBatis通過攔截修改SQL

Maven 引用

遠(yuǎn)程倉庫

<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 {
        // 根據(jù)sql創(chuàng)建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表達(dá)式
                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);
    }
}

創(chuàng)建Select的方式

創(chuàng)建Select(非SQL String 創(chuàng)建)

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

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

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

解析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);

結(jié)果:

SELECT a AS A1, b AS A2, c AS A3 FROM test

SQL函數(shù)

/**
 * SQL 函數(shù)
 * 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); // 設(shè)置FROM t1= >  SELECT  FROM tab1 AS t1
    List<SelectItem> selectItemList = new ArrayList<>(); // 查詢元素集合
    SelectExpressionItem selectExpressionItem001 = new SelectExpressionItem(); // 元素1表達(dá)式
    selectExpressionItem001.setExpression(new Column(t1,"col001"));
    SelectExpressionItem selectExpressionItem002 = new SelectExpressionItem(); // 元素2表達(dá)式
    selectExpressionItem002.setExpression(new Column(t1,"col002"));
    selectItemList.add(0, selectExpressionItem001); // 添加入隊
    selectItemList.add(1, selectExpressionItem002); // 添加入隊

    // COUNT
    SelectExpressionItem selectExpressionItemCount = new SelectExpressionItem(); // 創(chuàng)建函數(shù)元素表達(dá)式
    selectExpressionItemCount.setAlias(new Alias("count")); // 設(shè)置別名
    Function function = new Function(); // 創(chuàng)建函數(shù)對象  Function extends ASTNodeAccessImpl implements Expression
    function.setName("COUNT"); // 設(shè)置函數(shù)名
    ExpressionList expressionListCount = new ExpressionList(); // 創(chuàng)建參數(shù)表達(dá)式
    expressionListCount.setExpressions(Collections.singletonList(new Column(t1, "id")));
    function.setParameters(expressionListCount); // 設(shè)置參數(shù)
    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(); // 等于表達(dá)式
    equalsTo.setLeftExpression(new Column(table, "user_id")); // 設(shè)置表達(dá)式左邊值
    equalsTo.setRightExpression(new StringValue("123456"));// 設(shè)置表達(dá)式右邊值
    PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 轉(zhuǎn)換為更細(xì)化的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")); // 設(shè)置表達(dá)式左邊值
    notEqualsTo.setRightExpression(new StringValue("123456"));// 設(shè)置表達(dá)式右邊值
    PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody();
    plainSelectNot.setWhere(notEqualsTo);
    System.err.println(plainSelectNot);//  SELECT * FROM test WHERE test.user_id <> '123456'

    // 其他運算符, 參考上面代碼添加表達(dá)式即可
    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(); // 創(chuàng)建Like表達(dá)式對象
    likeExpression.setLeftExpression(new Column("username")); // 表達(dá)式左邊
    likeExpression.setRightExpression(new StringValue("張%")); // 右邊表達(dá)式
    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(); // 創(chuàng)建IN范圍的元素集合
    deptIds.add("0001");
    deptIds.add("0002");
    ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList())); // 把集合轉(zhuǎn)變?yōu)镴SQLParser需要的元素列表
    InExpression inExpression = new InExpression(new Column("dept_id "), itemsList); // 創(chuàng)建IN表達(dá)式對象,傳入列名及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)); // 設(shè)置起點值
    between.setBetweenExpressionEnd(new LongValue(30)); // 設(shè)置終點值
    between.setLeftExpression(new Column("age")); // 設(shè)置左邊的表達(dá)式,一般為列
    PlainSelect plainSelectBetween = (PlainSelect) select.getSelectBody();
    plainSelectBetween.setWhere(between);
    System.err.println(plainSelectBetween); // SELECT * FROM test WHERE age BETWEEN 18 AND 30

    //  WHERE AND 多個條件結(jié)合,都需要成立
    AndExpression andExpression = new AndExpression(); // AND 表達(dá)式
    andExpression.setLeftExpression(equalsTo); // AND 左邊表達(dá)式
    andExpression.setRightExpression(between);  // AND 右邊表達(dá)式
    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 表達(dá)式
    orExpression.setLeftExpression(equalsTo); // OR 左邊表達(dá)式
    orExpression.setRightExpression(between);  // OR 右邊表達(dá)式
    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(); // 創(chuàng)建排序?qū)ο?
    orderByElement.isAsc(); //  設(shè)置升序排列 從小到大
    orderByElement.setExpression(new Column("col01")); // 設(shè)置排序字段
    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(); // 創(chuàng)建Join對象
     join.withRightItem(t2); // 添加Join的表 JOIN t2 =>JOIN tab2 t2
     EqualsTo equalsTo = new EqualsTo(); // 添加 = 條件表達(dá)式  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

     // 設(shè)置join參數(shù)可實現(xiàn)其他類型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()) {
    ...
}

總結(jié)

我們可以借助JSqlparser來解析SQL并且動態(tài)拼接生成SQL,在Mybatis-plus中的租戶其實也是類似這樣實現(xiàn)的。

甚至有興趣的同學(xué)可以自己做一個SQL拼裝器,將前臺篩選的條件轉(zhuǎn)換為SQL進(jìn)行查詢。所有的查詢字段、條件、聯(lián)表等等都做成動態(tài)拼裝。

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • Shiro+Cas微服務(wù)化及前后端完全分離

    Shiro+Cas微服務(wù)化及前后端完全分離

    這篇文章主要為大家詳細(xì)介紹了Shiro+Cas微服務(wù)化及前后端完全分離,文中示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2019-12-12
  • SpringBoot集成FTP文件服務(wù)器簡單應(yīng)用方式

    SpringBoot集成FTP文件服務(wù)器簡單應(yīng)用方式

    這篇文章主要介紹了SpringBoot集成FTP文件服務(wù)器簡單應(yīng)用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2024-07-07
  • 一文詳解Java中字符串的基本操作

    一文詳解Java中字符串的基本操作

    這篇文章主要為大家詳細(xì)介紹了Java中字符串的基本操作,例如遍歷、統(tǒng)計次數(shù),拼接和反轉(zhuǎn)等以及String的常用方法,感興趣的可以了解一下
    2022-08-08
  • Spring中的Sentinel熔斷降級詳解

    Spring中的Sentinel熔斷降級詳解

    這篇文章主要介紹了Spring中的Sentinel熔斷降級詳解,熔斷降級是一種保護(hù)系統(tǒng)穩(wěn)定性和可用性的機制,旨在防止故障的擴(kuò)散和蔓延,提高用戶體驗和信任度,需要的朋友可以參考下
    2023-09-09
  • SpringMVC HttpMessageConverter報文信息轉(zhuǎn)換器

    SpringMVC HttpMessageConverter報文信息轉(zhuǎn)換器

    ??HttpMessageConverter???,報文信息轉(zhuǎn)換器,將請求報文轉(zhuǎn)換為Java對象,或?qū)ava對象轉(zhuǎn)換為響應(yīng)報文。???HttpMessageConverter???提供了兩個注解和兩個類型:??@RequestBody,@ResponseBody???,??RequestEntity,ResponseEntity??
    2023-01-01
  • SpringBoot時間格式化的方法小結(jié)

    SpringBoot時間格式化的方法小結(jié)

    SpringBoot中的時間格式化通常指的是將Java中的日期時間類型轉(zhuǎn)換為指定格式的字符串,或者將字符串類型的時間解析為Java中的日期時間類型,本文小編將給大家詳細(xì)總結(jié)了SpringBoot時間格式化的方法,剛興趣的小伙伴跟著小編一起來看看吧
    2023-10-10
  • 關(guān)于Spring框架中異常處理情況淺析

    關(guān)于Spring框架中異常處理情況淺析

    最近學(xué)習(xí)Spring時,認(rèn)識到Spring異常處理的強大,這篇文章主要給大家介紹了關(guān)于Spring框架中異常處理情況的相關(guān)資料,通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2021-08-08
  • 一文帶你熟練掌握J(rèn)ava中的日期時間相關(guān)類

    一文帶你熟練掌握J(rèn)ava中的日期時間相關(guān)類

    我們在開發(fā)時,除了數(shù)字、數(shù)學(xué)這樣的常用API之外,還有日期時間類,更是會被經(jīng)常使用,比如我們項目中必備的日志功能,需要記錄異常等信息產(chǎn)生的時間,本文就帶各位來學(xué)習(xí)一下相關(guān)的日期時間類有哪些
    2023-05-05
  • Java中Boolean和boolean的區(qū)別詳析

    Java中Boolean和boolean的區(qū)別詳析

    boolean是基本數(shù)據(jù)類型Boolean是它的封裝類,和其他類一樣,有屬性有方法,可以new,下面這篇文章主要給大家介紹了關(guān)于Java中Boolean和boolean區(qū)別的相關(guān)資料,需要的朋友可以參考下
    2022-07-07
  • 淺談SpringBoot2.4 配置文件加載機制大變化

    淺談SpringBoot2.4 配置文件加載機制大變化

    這篇文章主要介紹了淺談SpringBoot2.4 配置文件加載機制大變化,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-08-08

最新評論