使用jsqlparser來建立動态SQL

在某些場景下需要我們在代碼中動态的建立SQL語句來查詢資料，正常的做法就是使用字元串的拼接來完成，但這樣的方式靈活度不高，如果SQL本身比較複雜，那麼其處理邏輯也會顯得比較混亂。下面簡單的介紹下使用開源的jsqlparser來以程式設計的方式建立動态SQL。

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

jsqlparser: https://github.com/JSQLParser/JSqlParser

1.建立普通的SELECT語句

PlainSelect plainSelect = new PlainSelect() ;
//設定查詢的列
plainSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01")    //設定列名
                .withTable(new Table("TABLE01"))) //設定表名[可選]
        .withAlias(new Alias("COLUMN_ALIAS")))); //設定列的别名[可選]
//設定查詢的表
plainSelect.setFromItem(new Table("TABLE01"));

最終生成的結果：

SELECT TABLE01.COLUMN_01 AS COLUMN_ALIAS FROM TABLE01

2.建立子查詢

PlainSelect plainSelect = new PlainSelect() ;
//設定查詢的列
plainSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01")    //設定列名
                .withTable(new Table("TABLE01"))) //設定表名[可選]
        .withAlias(new Alias("COLUMN_ALIAS")))); //設定列的别名[可選]

//子查詢
PlainSelect subSelect = new PlainSelect() ;
subSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01"))));
subSelect.setFromItem(new Table("SUB_TABLE"));

//設定查詢的子表
plainSelect.setFromItem(new SubSelect()
        .withSelectBody(subSelect)
        .withAlias(new Alias("TABLE01")));

最終生成的結果：

SELECT
  TABLE01.COLUMN_01 AS COLUMN_ALIAS 
FROM
  ( SELECT COLUMN_01 FROM SUB_TABLE ) AS TABLE01

3.建立WHERE條件

PlainSelect plainSelect = new PlainSelect() ;
plainSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01")
                .withTable(new Table("TABLE01")))
        .withAlias(new Alias("COLUMN_ALIAS"))));

//子查詢
PlainSelect subSelect = new PlainSelect() ;
subSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01"))));
subSelect.setFromItem(new Table("SUB_TABLE"));

plainSelect.setFromItem(new SubSelect()
        .withSelectBody(subSelect)
        .withAlias(new Alias("TABLE01")));

//where條件
Column column = new Column().withColumnName("COLUMN_01")
        .withTable(new Table("TABLE01"));

Expression expression1 = buildGreaterThanExpression(column,123) ;
Expression expression2 = buildInExpression(column,Arrays.asList("123","345")) ;

Expression whereExpression = buildAndExpression(Arrays.asList(expression1,expression2)) ;
plainSelect.setWhere(whereExpression);

SELECT
  TABLE01.COLUMN_01 AS COLUMN_ALIAS 
FROM
  ( SELECT COLUMN_01 FROM SUB_TABLE ) AS TABLE01 
WHERE
  TABLE01.COLUMN_01 > 123 
  AND TABLE01.COLUMN_01 IN ('123','345')

3.1建立AND表達式

public Expression buildAndExpression(List<Expression> expressionList){
    Assert.notEmpty(expressionList,"建立AND辨別的是子表達式不能為空");
    if(expressionList.size() == 1){
        return expressionList.get(0) ;
    }
    AndExpression andExpression = new AndExpression() ;
    andExpression.withLeftExpression(expressionList.get(0)).withRightExpression(expressionList.get(1)) ;
    for(int i=2;i<expressionList.size();i++){
        AndExpression itemExpression = new AndExpression() ;
        itemExpression.withLeftExpression(andExpression).withRightExpression(expressionList.get(i));
        andExpression = itemExpression ;
    }
    return andExpression ;
}

3.2 建立算數表達式

public Expression buildGreaterThanExpression(Column column,Object value){
    /**
     * GreaterThan: >
     * GreaterThanEquals : >=
     * MinorThan: <
     * MinorThanEquals: <=
     */
    GreaterThan greaterThan = new GreaterThan().withLeftExpression(column) ;
    //greaterThan.withRightExpression(new StringValue(value.toString())) ;
    greaterThan.withRightExpression(new DoubleValue(value.toString())) ;
    return  greaterThan ;
}

3.3 建立IN表達式

public Expression buildInExpression(Column column, List<String> values){
    ExpressionList expressionList = new ExpressionList() ;
    values.forEach(val -> {
        expressionList.addExpressions(new StringValue(val);
    });
    InExpression inExpression = new InExpression()
            .withLeftExpression(column)
            .withRightExpression(new ValueListExpression()
                    .withExpressionList(expressionList)) ;
    //inExpression.withNot(true); //設定成 NOT IN
    return  inExpression ;
}

3.4 建立Equals表達式

public Expression buildEqualsExpression(Column column, Object value){
    //不等于：NotEqualsTo
    EqualsTo equalsTo = new EqualsTo().withLeftExpression(column) ;
    equalsTo.withRightExpression(new StringValue(value.toString()));
    //equalsTo.withRightExpression(new DoubleValue(value.toString()));
    return equalsTo ;
}

3.5 建立Between表達式

public Expression buildBetweenExpression(Column column, Object leftValue,Object rightValue){
    Between between = new Between().withLeftExpression(column) ;
    between.withLeftExpression(column) ;
    between.withBetweenExpressionStart(new DoubleValue(leftValue.toString())) ;
    between.withBetweenExpressionEnd(new DoubleValue(rightValue.toString())) ;
    return between ;
}

3.6 建立Like表達式

public Expression buildLikeExpression(Column column, Object value){
    LikeExpression likeExpression = new LikeExpression()
            .withLeftExpression(column);
    likeExpression.withRightExpression(new StringValue("%" + value.toString() + "%" )) ;
    return likeExpression ;
}

4.建立分組、排序和分頁表達式

PlainSelect plainSelect = new PlainSelect() ;
plainSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01")
                .withTable(new Table("TABLE01")))
        .withAlias(new Alias("COLUMN_ALIAS"))));

//子查詢
PlainSelect subSelect = new PlainSelect() ;
subSelect.addSelectItems(Arrays.asList(new SelectExpressionItem()
        .withExpression(new Column("COLUMN_01"))));
subSelect.setFromItem(new Table("SUB_TABLE"));

plainSelect.setFromItem(new SubSelect()
        .withSelectBody(subSelect)
        .withAlias(new Alias("TABLE01")));

//where條件
Column column = new Column().withColumnName("COLUMN_01")
        .withTable(new Table("TABLE01"));

Expression expression1 = buildGreaterThanExpression(column,123) ;
Expression expression2 = buildInExpression(column,Arrays.asList("123","345")) ;

Expression whereExpression = buildAndExpression(Arrays.asList(expression1,expression2)) ;
plainSelect.setWhere(whereExpression);

//添加分組語句
List<Column> groupByColumns = new ArrayList<>() ;
groupByColumns.add(new Column("COL_01")) ;
for(Column col : groupByColumns){
    plainSelect.addGroupByColumnReference(col);
}

//添加排序語句
List<OrderByElement> orderByElements = new ArrayList<>() ;
OrderByElement orderByElement = new OrderByElement() ;
orderByElement.setExpression(new Column("COL_01"));
orderByElement.setAsc(false);
orderByElements.add(orderByElement) ;
plainSelect.setOrderByElements(orderByElements);

//添加分頁
int pageSize = 10 ;
int pageNum = 1 ;
plainSelect.setLimit(new Limit()
        .withOffset(new LongValue((pageNum-1)*pageSize))
        .withRowCount(new LongValue(pageSize)));

SELECT
  TABLE01.COLUMN_01 AS COLUMN_ALIAS 
FROM
  ( SELECT COLUMN_01 FROM SUB_TABLE ) AS TABLE01 
WHERE
  TABLE01.COLUMN_01 > 123 AND TABLE01.COLUMN_01 IN ( '123', '345' ) 
GROUP BY
  COL_01 
ORDER BY COL_01 DESC 
LIMIT 0,10

5.建立join語句

//添加JOIN語句
EqualsTo equalsExpression = new EqualsTo() ;
equalsExpression.setLeftExpression(new Column(new Table("TABLE01"),"ID"));
equalsExpression.setRightExpression(new Column(new Table("TABLE02"),"ID"));

Join join = new Join(); // 建立Join對象
join.setInner(true); //設定為内關聯
join.withRightItem(new Table("TAB_02")) ;
join.setOnExpressions(Arrays.asList(equalsExpression));
plainSelect.addJoins(join) ;

使用jsqlparser來建立動态SQL

1.建立普通的SELECT語句

2.建立子查詢

3.建立WHERE條件

3.1建立AND表達式

3.2 建立算數表達式

3.3 建立IN表達式

3.4 建立Equals表達式

3.5 建立Between表達式

3.6 建立Like表達式

4.建立分組、排序和分頁表達式

5.建立join語句

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離