SQL语句解析(alibaba-druid&metadata)

alibaba-druid

github源码地址：https://github.com/alibaba/druid

引入pom依赖

	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
		<version>${druid-version}</version>
	</dependency>

maven中央仓库: http://central.maven.org/maven2/com/alibaba/druid/

使用提供的工具进行sql解析

public void test () {
	//sql语句，本期使用postgresql举例测试，可解析jsonb查询条件
	String sql = "select '{\"nickname\": \"goodspeed\", \"avatar\": \"avatar_url\"}'::json->'nickname' as nickname，" +
                "max(t1.age) as maxAge,t1.user as t1User,t1.name as t1Name,t2.id as t2Id from emp_table t1 " +
                "left join table_two t2 on t1.id = t2.id where t1.sex = 1";
	//使用mysql时，可以改为JdbcConstants.MYSQL
    String dbType = JdbcConstants.POSTGRESQL;
    //格式化sql 缺省大写格式
    String result = SQLUtils.format(sql, dbType);
    System.out.println("格式化SQL : " + result);
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    //解析出的独立语句的个数
    System.out.println("size is:" + stmtList.size());
    for (int i = 0; i < stmtList.size(); i++) {
        SQLStatement stmt = stmtList.get(i);
        Set<String> set = new HashSet<>();
        //获取查询内容
        PGSelectQueryBlock pgSelectQueryBlock =  ((PGSelectQueryBlock)(((SQLSelectStatement) stmt).getSelect()).getQuery());
        List<SQLSelectItem> list = pgSelectQueryBlock.getSelectList();
        //获取where条件
        SQLExpr where = pgSelectQueryBlock.getWhere();
        for(SQLSelectItem item : list){
        	//将查询的字段列放入set
            set.add(item.getAlias());
        }
        System.out.println("查询字段 : " + set);
        PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
        stmt.accept(visitor);
        System.out.println("查询条件 ： " + where);
        System.out.println("表名 : " + visitor.getTables());
		//获取函数
        System.out.println("使用函数 : " + visitor.getAggregateFunctions());
    }
}

执行结果

上述方法是基于sql语句层面进行的解析，不会与数据库产生交互，所以测试时，表名和字段名可自行修改。

MetaData解析

public void test() {
	String sql = "select id as aliasId,data as aliasData,test_float as float," +
                "test_boolean as boolean ,test_date as date ,test_time as time,test_varchar as varchar," +
                "test_json as json ,test_text as text from entity_menu";
    DataSource dataSource = jdbcTemplate.getDataSource();
    try (Connection connection = dataSource.getConnection()) {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 0;i < columnCount;i++) {
            String columnName = metaData.getColumnName(i + 1);
            System.out.println("列名：" + columnName);
            System.out.println("表名：" + metaData.getTableName(i+1));
            System.out.println("列类型别名：" + metaData.getColumnTypeName(i+1));
            System.out.println("java类型名称：" + metaData.getColumnClassName(i+1));
            System.out.println("是否只读：" + metaData.isReadOnly(i+1));
        }
    } catch (Exception e) {
        log.error(e.getMessage());
        throw new SchemaMetaException(MessageUtils.get("error.meta_data_analysis_exception"));
    }
}

执行结果

自增序列返回的列类型是serial

使用metadata解析，是基于数据库层面的，所以表名和字段名都需要与数据库中的一致。