1. 查询语句构造 select

1
select * from user
1
2
3
4
5
6
7
8
9
10
11
public void plainSelect() throws JSQLParserException {
    Statement parse = CCJSqlParserUtil.parse("select * from user");
    Select select = (Select) parse;
    PlainSelect plainSelect = select.getPlainSelect();
}
//或者使用下面的构造
public void plainSelect2() throws JSQLParserException {
    Select select = SelectUtils.buildSelectFromTable(new Table("user"));
    PlainSelect plainSelect = select.getPlainSelect();
    System.out.println("plainSelect = " + plainSelect);
}

2. 返回列

1
select id,name from t_user

直接构造

1
2
3
public void selectItem() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select id,name from t_user")).getPlainSelect();
}

程序中构造

1
2
3
4
5
6
7
8
9
@Test
public void selectItem() throws JSQLParserException {
    PlainSelect plainSelect = SelectUtils.buildSelectFromTableAndSelectItems(
            new Table("t_user"),
            new SelectItem(new Column("id")),
            new SelectItem(new Column("name"))
    ).getPlainSelect();
    System.out.println(plainSelect.toString());
}

3. where

where 中包含了大量的条件语句，通过CCJSqlParserUtil自动可以解析，这里我们主要示例程序中构造SQL，也就可以实现SQL改写了。

3.1. equals

equals左边为列名，右边为值

1
select * from t_user  where id=1
1
2
3
4
5
6
7
8
9
10
11
12
@Test
public void equals() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select * from t_user")).getPlainSelect();
    //我们自己构造一个 where id=1的表达式
    EqualsTo equalsTo = new EqualsTo();
    //设置ID
    equalsTo.setLeftExpression(new Column("id"));
    //设置值
    equalsTo.setRightExpression(new LongValue("1"));
    //设置where
    plainSelect.setWhere(equalsTo);
}

3.2. conditional 条件表达式

3.2.1. and

1
select * from t_user  where id=1 and name='小明'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public void and() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select * from t_user")).getPlainSelect();
    //由于没有条件，这里是为null的
    Expression where = plainSelect.getWhere();
    //先构造 and两边的
    EqualsTo idEquals = new EqualsTo(new Column("id"),new LongValue("1"));
    EqualsTo nameEquals = new EqualsTo(new Column("name"),new StringValue("小明"));

    //构造and
    AndExpression andExpression = new AndExpression();
    //设置左边
    andExpression.setLeftExpression(idEquals);
    //设置右边
    andExpression.setRightExpression(nameEquals);

    //把and添加到where中
    plainSelect.setWhere(andExpression);

}

and在使用时只有left和right两个，是个二叉树结构，多个and连接时是多层的二叉树，但是其构造的语法是不含括号的，需要括号的话自己单独加ParenthesedSelect或ParenthesedList分别用于子查询括号和值括号

我们改写SQL添加条件时的思路是在二叉树上面增减，而不是修改原来有的语句，如

1
select * from t_user  where id=1 and name='小明'

添加一个age=10，则思路应该是重新添加一个and，把原来where的表达式值的和新的and表达式连接 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void and2() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select * from t_user  where id=1 and name='小明'")).getPlainSelect();
    Expression where = plainSelect.getWhere();
    EqualsTo equalsTo = new EqualsTo(new Column("age"), new StringValue("小明"));
    //构造and
    AndExpression andExpression = new AndExpression();
    //设置左边为原来的where后面的
    andExpression.setLeftExpression(where);
    //设置右边
    andExpression.setRightExpression(equalsTo);
    //把新的添加到where中
    plainSelect.setWhere(andExpression);
}

3.2.2. or

同and一样

1
2
3
4
5
6
7
8
9
10
11
12
@Test
public void or() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select * from t_user  where id=1")).getPlainSelect();
    Expression where = plainSelect.getWhere();

    //or语法  原来的基础上添加 or name = '小明'
    OrExpression orExpression = new OrExpression(
            where,
            new EqualsTo(new Column("name"), new StringValue("小明"))
    );
    plainSelect.setWhere(orExpression);
}

3.3. relational 关系表达式

包括 in exists like between <>等，在包net.sf.jsqlparser.expression.operators.relational中

构造关系表达式，可以在and或者or中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public void relationalExpression() throws JSQLParserException {
    PlainSelect plainSelect = ((Select) CCJSqlParserUtil.parse("select * from t_user")).getPlainSelect();

    //in语法  name in ('name1','name2') ，
    //   注意4.4版本使用ExpressionList，默认会带上括号 4.9版本ExpressionList已经不带括号了，继续使用SQL语法会没有括号导致错误，使用时需要用ParenthesedExpressionList
    InExpression inExpression = new InExpression(
            new Column("name"),
            new ParenthesedExpressionList<>(Arrays.asList(new StringValue("name1"), new StringValue("name2")))
    );

    // like语法  name like '%name1%'
    LikeExpression likeExpression = new LikeExpression();
    likeExpression.setLeftExpression(new Column("name"));
    likeExpression.setLeftExpression(new StringValue("%name1%"));

    //between...and...语法 age between 18 and 35
    Between between = new Between();
    between.setLeftExpression(new Column("age"));
    between.setBetweenExpressionStart(new LongValue("18"));
    between.setBetweenExpressionEnd(new LongValue("35"));

    // !=或<> 语法 name != '小明'
    NotEqualsTo notEqualsTo = new NotEqualsTo(new Column("name"), new StringValue("小明"));

    //>=或<= 语法 >或<同理  age>=18 SELECT * FROM t_user WHERE age <= 18
    GreaterThanEquals greaterThanEquals = new GreaterThanEquals();
    greaterThanEquals.setLeftExpression(new Column("age"));
    greaterThanEquals.setRightExpression(new LongValue(18));
    MinorThanEquals minorThanEquals = new MinorThanEquals();
    greaterThanEquals.setLeftExpression(new Column("age"));
    greaterThanEquals.setRightExpression(new LongValue(18));

    //is null / is not null 语法 name is not null
    IsNullExpression isNullExpression = new IsNullExpression();
    isNullExpression.setLeftExpression(new Column("name"));
    isNullExpression.setNot(true); //设置 is not null
    //isNullExpression.setUseIsNull(true); //使用 is null或is not null ，否则使用 ISNULL 或NOT ISNULL
    //isNullExpression.setUseNotNull(false); 设置为true语法不带is

    //match against 语法 全文检索 where (name,id) AGAINST ('苹果')
    FullTextSearch fullTextSearch = new FullTextSearch();
    fullTextSearch.setMatchColumns(new ExpressionList(new Column("name"),new Column("id")));
    fullTextSearch.setAgainstValue(new StringValue("苹果"));

    //设置值where
    plainSelect.setWhere(fullTextSearch);
    System.out.println(plainSelect);
}

4. subQuery 子查询

子查询即在join后面或In里面或者exists中生成一个查询语句，得到临时表
下面是构造一个In的子查询，同理 join 后的子查询一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
public void subQuery2(){

    //构造一个 SELECT * FROM t_user WHERE id IN (SELECT id FROM t_role WHERE age = 18)
    PlainSelect plainSelect = SelectUtils.buildSelectFromTable(new Table("t_user")).getPlainSelect();
    plainSelect.setWhere(
            new InExpression(
                    new Column("id"),
                    //构造IN的右边，带括号的查询
                    new ParenthesedSelect().withSelect(
                            //构造内部查询
                            new PlainSelect()
                                    .withFromItem(new Table("t_role"))  //from
                                    .withSelectItems(Arrays.asList(new SelectItem<>(new Column("id")))) //select 返回列
                                    .withWhere(new EqualsTo(new Column("age"),new LongValue(18))) //where条件
                    )
            )
    );
    System.out.println(plainSelect);
}

5. with as 表达式

with as 子句在select中设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//根据SQL获取到withAs子句
@Test
public void withAs() throws JSQLParserException {
    String sql = "with t_a as (SELECT * from sys_user)SELECT * from t_a where 1=1";
    Select select = (Select) CCJSqlParserUtil.parse(sql);
    List<WithItem> withItemsList = select.getWithItemsList();
    System.out.println(withItemsList.get(0));
}
//创建withas子句
@Test
public void withAs2(){
    Select select = SelectUtils.buildSelectFromTable(new Table("t_user"));
    PlainSelect plainSelect = new PlainSelect()
            .withFromItem(new Table("t_role"))
            .withSelectItems(Arrays.asList(new SelectItem<>(new Column("id"))));
    WithItem withItem = new WithItem();
    withItem.withSelect(new ParenthesedSelect().withSelect(plainSelect))
            .withAlias(new Alias("as_a"));
    select.setWithItemsList(Arrays.asList(withItem));
    System.out.println(select);
}