JSqlParser上手

JSqlParser上手

起男 114 2025-02-06

JSqlParser上手

JSqlParser 是一个流行的 Java SQL 解析器库，它提供了强大的功能来解析、分析和操作 SQL 查询语句

依赖

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

DEMO

查询

        String SQL = "SELECT DISTINCT u.id, r.role_name, u.user_name, u.sex, u.email " +
                "FROM t_user u " +
                "LEFT JOIN t_role r ON u.role_id = r.id " +
                "WHERE r.role_name = '管理员' " +
                "ORDER BY u.age DESC " +
                "LIMIT 0,10";
        Select select = (Select) CCJSqlParserUtil.parse(SQL);
        PlainSelect plainSelect = select.getPlainSelect();
        System.out.println("distinct："+plainSelect.getDistinct());
        System.out.println("查询字段："+plainSelect.getSelectItems());
        System.out.println("from表："+plainSelect.getFromItem());
        System.out.println("where子句："+plainSelect.getWhere());
        System.out.println("join子句："+plainSelect.getJoins());
        System.out.println("limit子句："+plainSelect.getLimit());
        System.out.println("offset子句："+plainSelect.getOffset());
        System.out.println("order by子句："+plainSelect.getOrderByElements());

        //取消去重
        plainSelect.setDistinct(null);
        //修改查询字段为*
        List<SelectItem<?>> selectItems = new ArrayList<>();
        selectItems.add(new SelectItem<>(new AllColumns()));
        plainSelect.setSelectItems(selectItems);
        //修改limit
        Limit limit = new Limit();
        limit.setRowCount(new LongValue(5));
        limit.setOffset(new LongValue(0));
        //修改where
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("u.id"));
        equalsTo.setRightExpression(new LongValue(1));
        plainSelect.setWhere(equalsTo);
        //修改order by
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setExpression(new Column("u.age"));
        orderByElement.setAsc(false);//降序
        plainSelect.setOrderByElements(Arrays.asList(orderByElement));

        System.out.println(plainSelect);

插入

        String SQL = "INSERT INTO t_user (role_id, user_name, email, age, sex, register_time )\n" +
                "VALUES ( 1, 'xw', 'isxuwei@qq.com', 25, '男', '2024-04-12 17:37:18' );";
        Insert insert = (Insert) CCJSqlParserUtil.parse(SQL);
        System.out.println("目标表："+insert.getTable());
        System.out.println("插入字段："+insert.getColumns());
        System.out.println("插入值："+insert.getValues());

        ExpressionList<Column> columns = insert.getColumns();
        ExpressionList<Expression> values = (ExpressionList<Expression>) insert
                .getValues().getExpressions();
        //删除字段（字段和值一一对应）
        columns.remove(4);
        values.remove(4);
        //新增字段
        columns.add(new Column("status"));
        values.add(new LongValue(1));
        //更新字段
        Expression expression = values.get(3);
        LongValue longValue = (LongValue) expression;
        longValue.setValue(longValue.getValue()+1);

        System.out.println(insert);

修改

        String SQL = "UPDATE t_user SET email = '373675032@qq.com', phone = '10086' WHERE id = 1";
        Update update = (Update) CCJSqlParserUtil.parse(SQL);
        System.out.println("目标表："+update.getTable());
        List<UpdateSet> updateSets = update.getUpdateSets();
        for (UpdateSet updateSet : updateSets) {
            System.out.println("更新字段："+updateSet.getColumns());
            System.out.println("更新值："+updateSet.getValues());
        }
        System.out.println("更新条件："+update.getWhere());

        //删除字段
        updateSets.remove(1);
        //添加字段
        UpdateSet updateSet = new UpdateSet();
        updateSet.add(new Column("update_time"),new LongValue(System.currentTimeMillis()));
        updateSets.add(updateSet);
        //更新where
        AndExpression expression = new AndExpression();
        expression.withLeftExpression(update.getWhere());
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("deleted"));
        equalsTo.setRightExpression(new LongValue(0));
        expression.withRightExpression(equalsTo);
        update.setWhere(expression);

        System.out.println(update);

删除

        String SQL = "DELETE FROM t_user WHERE id = 1";
        Delete delete = (Delete) CCJSqlParserUtil.parse(SQL);
        System.out.println("目标表："+delete.getTable());
        System.out.println("where条件："+delete.getWhere());

        //修改条件
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("id"));
        equalsTo.setRightExpression(new LongValue(2));
        delete.setWhere(equalsTo);

        System.out.println(delete);
mysql