druid sql parser 解析插入sql片段

最新推荐文章于 2023-03-25 17:07:32 发布

ciciheng

最新推荐文章于 2023-03-25 17:07:32 发布

阅读量637

点赞数 1

文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/rattlebush_heng/article/details/121598938

版权

本文探讨了如何解析SQL语句，检查其是否包含WHERE、GROUP BY和ORDER BY子句，并在必要时插入权限检查。重点在于MySQL数据库下，通过示例展示了如何处理复杂的SQL查询和权限控制策略。

摘要由CSDN通过智能技术生成

这里写自定义目录标题

public static void main(String[] args) {
        String sql = "select a.*,b.class_name " +
                "from (select b.user_id, b.class_name from class_name b where b.id>1 order by b.id desc) a " +
                "left join class_user b on b.user_id = a.id " +
                "where a.id >1 " +
                "order by a.id asc";

        String sql1 = "select a.* from (select b.user_id, b.class_name from class_user b) a";

        String sql2 = "SELECT id FROM user WHERE status = 1;\n" +
                "SELECT id FROM order WHERE create_time > '2018-01-01'";
        String dbType = JdbcConstants.MYSQL;

        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);

        // 只考虑一条语句
        SQLStatement statement = statementList.get(0);

        boolean hasWhere = false;
        boolean hasGroupBy = false;
        boolean hasOrderBy = false;
        // 只考虑查询语句
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;
        SQLSelectQuery sqlSelectQuery     = sqlSelectStatement.getSelect().getQuery();
        // 非union的查询语句
        if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;

            // 获取where条件
            SQLExpr where = sqlSelectQueryBlock.getWhere(); // where是最外层条件，子查询条件不算
            if (where != null) hasWhere = true;
            // 获取分组
            SQLSelectGroupByClause groupBy = sqlSelectQueryBlock.getGroupBy();
            if (groupBy != null) hasGroupBy = true;
            // 获取排序
            SQLOrderBy orderBy = sqlSelectQueryBlock.getOrderBy(); // order by是最外层排序，子查询排序不算
            if (orderBy != null) hasOrderBy = true;

            // union的查询语句
        }

        String permissionSql = " where 1=1 /permission ********";
        permissionSql = hasWhere ? permissionSql.substring(10) : permissionSql;

        // 判断是否含有group 和 order
        StringBuilder sb = new StringBuilder(sql);//构造一个StringBuilder对象做插入
        int i = 0;
        if (hasGroupBy) {
            i = sql.lastIndexOf(" group by");
        } else {
            if (hasOrderBy) {
                i = sql.lastIndexOf(" order by");
            }
        }
        if (i > 0) {
            sb.insert(i, permissionSql);//在指定的位置1，插入指定的字符串
            sql = sb.toString();
        }
        System.out.println(sql);

        // 删除语句
        SQLDeleteStatement sqlDeleteStatement = (SQLDeleteStatement) statement;
        SQLExpr where = sqlDeleteStatement.getWhere();
        if (where != null) hasWhere = true;

        // 插入语句
        SQLInsertStatement sqlInsertStatement = (SQLInsertStatement) statement;
        where = sqlInsertStatement.
        if (where != null) hasWhere = true;
    }