JSqlParser入门教程之常用示例

一、JSqlParser简介

  JSqlParser是一个SQL语句解析器，它可以把SQL转换为Java对象进行操作的工具包，支持Oracle，SqlServer，MySQL，PostgreSQL等常用数据库。

  Github地址：https://github.com/JSQLParser/JSqlParser

二、常用示例

  首先，在项目中引入依赖：

<dependency>
    <groupId>com.github.jsqlparsergroupId>
    <artifactId>jsqlparserartifactId>
    <version>4.3version>
    <scope>compilescope>
dependency>

1、入门示例——解析SQL

  PlainSelect类是解析的SQL语句的Java对象，包括了各种元素，如下所示：

	/**
     * 入门Demo1，解析SQL语句
     * @throws JSQLParserException
     */
    public static void testSelect() throws JSQLParserException {
        // 使用工具类把SQL转换为Select对象
        Select select = (Select) CCJSqlParserUtil.parse("SELECT username,age,sex FROM t_sys_user");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        System.out.println(plainSelect.getFromItem());//获取表名
    }

2、入门示例——构建Select 语句

  提供了如下四种构建Select的方法，同时还提供了设置别名等方法。

	/**
     * 入门Demo2，构建简单的Select SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildSelect() throws JSQLParserException {
        Select select = SelectUtils.buildSelectFromTable(new Table("t_sys_user").withAlias(new Alias("user")));

        Select select1 = SelectUtils.buildSelectFromTableAndExpressions(new Table("t_sys_user"),"username");

        Select select2 = SelectUtils.buildSelectFromTableAndExpressions(new Table("t_sys_user"),new Column("username"));

        SelectItem[] selectItems = new SelectItem[2];
        selectItems[0]=new SelectExpressionItem(new Column("username"));
        selectItems[1]=new SelectExpressionItem(new Column("age"));
        Select select3 = SelectUtils.buildSelectFromTableAndSelectItems(new Table("t_sys_user"), selectItems);

        System.out.println(select3.getSelectBody().toString());
    }

3、入门示例——构建Insert 语句

	/**
     * 入门Demo3，构建简单的Insert SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildInsert() throws JSQLParserException {

        Insert insert = new Insert();
        //设置表名
        insert.setTable(new Table("t_sys_user"));
        //设置字段
        List<Column> columnList = Arrays.asList(new Column("username"), new Column("age"));
        insert.setColumns(columnList);
        //设置字段值
        MultiExpressionList valueList = new MultiExpressionList();
        valueList.addExpressionList(Arrays.asList(new StringValue("张三"), new StringValue("33")));
        insert.setItemsList(valueList);

        System.out.println(insert);
    }

4、入门示例——构建Update 语句

  在构建Update语句的时候，需要用到Where语句，这里使用了EqualsTo 对象，后续有专门讲解Where的示例。

	/**
     * 入门Demo4，构建简单的Update SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildUpdate() throws JSQLParserException {
        Update update = new Update();
        update.setTable(new Table("t_sys_user"));
        //设置字段
        List<Column> columnList = Arrays.asList(new Column("username"), new Column("age"));
        //update.setColumns(columnList);//弃用
        update.addUpdateSet(new Column("username"),new StringValue("张三"));
        update.addUpdateSet(new Column("age"),new StringValue("33"));

        //添加where条件
        EqualsTo equalsTo = new EqualsTo(); // 等于表达式
        equalsTo.setLeftExpression(new Column("username"));
        equalsTo.setRightExpression(new StringValue("李四"));
        update.setWhere(equalsTo);

        System.out.println(update);
    }

5、入门示例——构建Delete 语句

 	/**
     * 入门Demo5，构建简单的Delete SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildDelete() throws JSQLParserException {
        Delete delete = new Delete();
        delete.setTable(new Table("t_sys_user"));

        //添加where条件
        EqualsTo equalsTo = new EqualsTo(); // 等于表达式
        equalsTo.setLeftExpression(new Column("username"));
        equalsTo.setRightExpression(new StringValue("李四"));
        delete.setWhere(equalsTo);

        System.out.println(delete);
    }

6、入门示例——构建Where 语句

  在JSqlParser中，提供了很多的用于构建Where语句的Java对象，下面只是选择了几个自认为比较常用的进行了呈现，更多的可以查看源码，用法和逻辑基本类似。

	/**
     * 入门Demo6，构建Where SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildWhere() throws JSQLParserException {
        Select select = SelectUtils.buildSelectFromTable(new Table("t_sys_user"));
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        //Where 等于 =
        EqualsTo equalsTo = new EqualsTo(); // 等于表达式
        equalsTo.setLeftExpression(new Column("username"));
        equalsTo.setRightExpression(new StringValue("李四"));
        plainSelect.setWhere(equalsTo);//会被后面的setWhere()方法覆盖

        //Where 大于 > 类似=
        GreaterThan gt = new GreaterThan();
        //Where 小于 < 类似=
        MinorThan mt = new MinorThan();

        //Where like
        LikeExpression likeExpression = new LikeExpression();
        likeExpression.setLeftExpression(new Column("username"));
        likeExpression.setRightExpression(new StringValue("张%"));
        plainSelect.setWhere(likeExpression);//会被后面的setWhere()方法覆盖

        //Where AND 连接多个条件
        AndExpression andExpression = new AndExpression();
        andExpression.setLeftExpression(equalsTo);
        andExpression.setRightExpression(likeExpression);
        plainSelect.setWhere(andExpression);

        //Where BETWEEN
        Between between = new Between();
        between.setBetweenExpressionStart(new LongValue(18));
        between.setBetweenExpressionEnd(new LongValue(30));
        between.setLeftExpression(new Column("age"));

        //Where OR 连接多个条件
        OrExpression orExpression = new OrExpression();
        orExpression.setLeftExpression(andExpression);
        orExpression.setRightExpression(between);
        plainSelect.setWhere(orExpression);


        System.out.println(plainSelect);
    }

7、入门示例——构建OrderBy/Limit 语句

  在JSqlParser中，构建SQL语句的时候，我们不需要关心不同子语句的前后顺序，JSqlParser会按照标准SQL语句的顺序进行构建，比如OrderBy和Limit 同时存在时，我们不用担心前后顺序，造成的SQL语法错误。

	/**
     * 入门Demo7，构建OrderBy/Limit SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildOrderBy() throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse("SELECT username,age,sex FROM t_sys_user");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        Limit limit = new Limit();
        limit.setOffset(new LongValue("1"));
        limit.setRowCount(new LongValue("10"));
        plainSelect.withLimit(limit);
        //OrderBy
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setAsc(true);
        orderByElement.setExpression(new Column("age"));
        plainSelect.addOrderByElements(orderByElement);

        System.out.println(plainSelect);
    }

8、入门示例——构建GroupBy 语句

	/**
     * 入门Demo8，构建GroupBy SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildGroupBy() throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse("SELECT sex, sum(1) FROM t_sys_user");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        GroupByElement groupByElement = new GroupByElement();
        groupByElement.addGroupByExpressions(new Column("sex"));

        plainSelect.setGroupByElement(groupByElement);

        System.out.println(plainSelect);
    }

9、入门示例——构建Join 语句

	/**
     * 入门Demo9，构建join SQL语句
     * @throws JSQLParserException
     */
    public static void testBuildJoinSql() throws JSQLParserException {
        Table user = new Table("t_sys_user").withAlias(new Alias("user").withUseAs(true)); // 表1
        Table role = new Table("t_sys_role").withAlias(new Alias("role", false)); // 表2

        PlainSelect plainSelect = new PlainSelect().addSelectItems(new AllColumns()).withFromItem(user);

        Join join = new Join();
        join.setLeft(true);
        join.withRightItem(role);
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column(user, "id "));
        equalsTo.setRightExpression(new Column(role, "user_id"));
        join.addOnExpression(equalsTo);

        plainSelect.addJoins(join);

        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setAsc(true);
        orderByElement.setExpression(new Column(user,"age"));
        plainSelect.addOrderByElements(orderByElement);

        System.out.println(plainSelect);
    }