jsqlparser：对SQL查询语句进行处理，如获取表名数据库名等

jsqlparser

对SQL查询语句进行处理，如获取表名数据库名等

• 导入依赖

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

• 测试使用

public class SqlTest {
    public static void main(String[] args) throws JSQLParserException {
        //1、获取原始sql输入
        String sql = "SELECT u.username, u.age,u.sex FROM das.user_ittr as u where u.id=1 and u.age in (1,2,3) ;";
        //构建解析器
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        //获取select对象
        SelectBody selectBody = select.getSelectBody();
        System.err.println(selectBody.toString());

        //解析sql
        PlainSelect plainSelect = (PlainSelect) selectBody;
        //解析表对象
        Table table = (Table) plainSelect.getFromItem();
        // 库名
        System.out.println(table.getSchemaName());
        //表名称
        System.out.println(table.getName());
        //别名
        System.out.println(table.getAlias().getName());

        //查询列
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        selectItems.stream().forEach(selectItem -> {
            System.out.println(selectItem.toString());
        });

        System.out.println(SqlValidUtil.validSqlTableName(sql, "ittr$"));

        System.out.println(SqlValidUtil.getSchemaTable(sql));
    }
}

• 工具类

public class SqlValidUtil {

    /**
     * 检查sql是否正确
     * @param sql
     * @return
     */
    public static boolean validSql(String sql) {
        try {
            CCJSqlParserUtil.parse(sql);
            return true;
        } catch (JSQLParserException e) {
            return false;
        }
    }

    /**
     * 校验表名是否正确
     * @param sql
     * @param pattern 正则表达式
     * @example SqlValidUtil.validSqlTableName(sql, " ittr$ ")  校验表名是否以ittr结尾
     */
    public static boolean validSqlTableName(String sql, String pattern) {
        PlainSelect plainSelect = getPlainSelect(sql);
        Table table = (Table) plainSelect.getFromItem();

        Pattern regex = Pattern.compile(pattern);
        Matcher matcher = regex.matcher(table.getName());
        return matcher.find();
    }

    /**
     * 校验sql是否包含指定列名
     * @param sql
     * @param validColumns 必须包含的列
     */
    public static boolean validSqlColumn(String sql, List<String> validColumns) {
        try {
            PlainSelect plainSelect = getPlainSelect(sql);
            List<SelectItem> selectItems = plainSelect.getSelectItems();
            // 去除别名后的列名
            List<String> selectColumns = selectItems.stream()
                    .map(item -> item.toString().substring(item.toString().lastIndexOf(".") + 1))
                    .collect(Collectors.toList());

            return selectColumns.containsAll(validColumns);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 获得数据库和表
     * @param sql
     * @return [数据库, 数据表]
     */
    public static List<String> getSchemaTable(String sql) {
        PlainSelect plainSelect = getPlainSelect(sql);
        Table table = (Table) plainSelect.getFromItem();
        return new ArrayList<>(Arrays.asList(table.getSchemaName(), table.getName()));
    }


    /**
     * 解析Sql对象
     * @param sql
     * @return
     */
    private static PlainSelect getPlainSelect(String sql) {
        try {
            if (validSql(sql)) {
                Select select = (Select) CCJSqlParserUtil.parse(sql);
                SelectBody selectBody = select.getSelectBody();
                PlainSelect plainSelect = (PlainSelect) selectBody;
                return plainSelect;
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return null;
    }
}