当前位置：首页 > news >正文

查询出跨4个表以上的查询

news 来源：原创 2024/5/3 5:42:27

不过不考虑子查询等, 可以粗略认为是语句中含有4个以上的join
不想一个一个找.
于是想:
方法一: spring中直接取出所有的mapper bean的解析后的sql 语句, 然后按条件过滤, 再导出excel. 可惜的是, 这儿的sql语句已经被mybatis解析成了嵌套的各类节点, 没法直接看原语句了. 所以不行.
public static void main(String[] args) {

    SpringApplication application = new SpringApplication(xxxApplication.class);
    application.addInitializers(new SpringUtils());
    application.setRegisterShutdownHook(false);
    application.run(args);
    outputSql();
}

private static void outputSql() {
    final Collection<MappedStatement> mappedStatements = SpringUtils.getApplicationContext().getBean(SqlSessionFactory.class).getConfiguration().getMappedStatements();
    int ignoreCount = 0;
    int errorCount = 0;
    int predictOutPutCount = 0;
    final String replacer = "JOIN";
    final int maxFrequency = 0; //join出现的次数下限(不含)
    final int maxLengthGap = replacer.length() * maxFrequency;
    for (final Object obj : mappedStatements) {
        if (obj instanceof MappedStatement) {
            MappedStatement statement = (MappedStatement) obj;
            if (!statement.getId().contains("Mapper") && !statement.getId().contains("mapper")) {
                ignoreCount++;
                continue;
            }
            final String rawSql = getStaticSql(statement);
            if (StringUtils.isBlank(rawSql)) {
                errorCount++;
                continue;
            }
            final String upperCaseSql = rawSql.toUpperCase();
            System.out.println("upperCaseSql = " + upperCaseSql);
            if (upperCaseSql.length() - (upperCaseSql.replaceAll(replacer, "")).length() > maxLengthGap) { //Pattern.compile.matcher计算效率应该更高
                System.out.println("rawSql = " + rawSql);
            }
        }
        ignoreCount++;
    }
    System.out.println("处理sql总数 ===  " + mappedStatements.size());
    System.out.println("预计导出sql总数 ===  " + predictOutPutCount);
    System.out.println("出错sql总数 ===  " + errorCount);
    System.out.println("极简sql总数 ===  " + ignoreCount);
}

private static String getStaticSql(final MappedStatement statement) {
    SqlSource sqlSource = statement.getSqlSource();
    if (sqlSource instanceof StaticSqlSource) {
        final Field field = ReflectionUtils.findField(StaticSqlSource.class, "sql");
        field.setAccessible(true);
        return ReflectionUtils.getField(field, sqlSource).toString();
    }

// if (sqlSource instanceof RawSqlSource) {
// sqlSource = JSON.toJavaObject((JSONObject) (((JSONObject) JSONObject.toJSON(sqlSource)).get(“sqlSource”)), StaticSqlSource.class);
// return ((JSONObject) JSONObject.toJSON(sqlSource)).getString(“sql”);
// }

    if (sqlSource instanceof RawSqlSource) {
        final Field field = ReflectionUtils.findField(RawSqlSource.class, "sqlSource");
        field.setAccessible(true);
        final Object sqlSource1 = ReflectionUtils.getField(field, sqlSource);
        final Field field2 = ReflectionUtils.findField(StaticSqlSource.class, "sql");
        field2.setAccessible(true);
        return ReflectionUtils.getField(field2, sqlSource1).toString();

//
// sqlSource = JSON.toJavaObject((JSONObject) (((JSONObject) JSONObject.toJSON(sqlSource)).get(“sqlSource”)), StaticSqlSource.class);
// return ((JSONObject) JSONObject.toJSON(sqlSource)).getString(“sql”);
}
if (sqlSource instanceof DynamicSqlSource) {
final Field field2 = ReflectionUtils.findField(DynamicSqlSource.class, “rootSqlNode”);
field2.setAccessible(true);
final Object sqlNode = ReflectionUtils.getField(field2, sqlSource);
return JSON.toJSONString(sqlNode);
}
return null;
}
方法二: 读取文件, 按需导出
方法三: 按正则查询, 一个个复制到excel
方法四: idea规范插件可以直接找出来吗