Java执行MySQL查询时，非聚合字段别名未生效的问题排查

执行以下MySQL查询时，数据库客户端返回的结果能正确显示别名，但Java代码中除了scopes字段，其余字段获取到的都是原列名而非设置的别名。

MySQL查询语句 #

SELECT
    RESOURCE_SERVER_RESOURCE.ID as `rsid`,
    RESOURCE_SERVER_RESOURCE.NAME as `rsname`,
    JSON_ARRAYAGG(RESOURCE_SERVER_SCOPE.NAME) as `scopes`
FROM
    POLICY_CONFIG
    INNER JOIN ASSOCIATED_POLICY ON POLICY_CONFIG.POLICY_ID = ASSOCIATED_POLICY.ASSOCIATED_POLICY_ID
    INNER JOIN RESOURCE_POLICY ON ASSOCIATED_POLICY.POLICY_ID = RESOURCE_POLICY.POLICY_ID
    INNER JOIN RESOURCE_SERVER_RESOURCE ON RESOURCE_SERVER_RESOURCE.ID = RESOURCE_POLICY.RESOURCE_ID
    LEFT JOIN SCOPE_POLICY ON ASSOCIATED_POLICY.POLICY_ID = SCOPE_POLICY.POLICY_ID
    LEFT JOIN RESOURCE_SERVER_SCOPE ON RESOURCE_SERVER_SCOPE.ID = SCOPE_POLICY.SCOPE_ID
GROUP BY
    RESOURCE_SERVER_RESOURCE.ID;

Java执行代码 #

public static List<Map<String, Object>> executeSelectQuery(String query) throws Exception {
    List<Map<String, Object>> resultList = new ArrayList<>();
    Connection con = getConnection();
    try {
        PreparedStatement pst = con.prepareStatement(query);
        ResultSet rs = pst.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        while (rs.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                Object columnValue = rs.getObject(i);
                rowMap.put(columnName, columnValue);
            }
            resultList.add(rowMap);
        }
    } finally {
        con.close();
    }
    return resultList;
}

解决方案 #

核心修复：替换getColumnName为getColumnLabel #

将代码中获取列名的rsmd.getColumnName(i)替换为rsmd.getColumnLabel(i)，修改后的循环代码如下：

for (int i = 1; i <= columnCount; i++) {
    String columnName = rsmd.getColumnLabel(i);
    Object columnValue = rs.getObject(i);
    rowMap.put(columnName, columnValue);
}

原理说明 #

• getColumnName()：返回数据库表的原始列名，对于关联查询中带表前缀的字段，会忽略别名返回原始名称（比如ID而非rsid）。
• getColumnLabel()：返回查询语句中定义的别名，完全匹配你在SQL中设置的rsid、rsname等别名。

额外排查项（若修复后仍异常） #

• 检查JDBC驱动版本：确保使用mysql-connector-java 5.1.38+，旧版本存在别名解析的兼容性问题。
• 验证SQL别名拼写：确认别名无拼写错误，反引号`包裹别名是MySQL的标准写法，无需修改。
• 检查MySQL sql_mode：执行SELECT @@sql_mode;查看配置，避免开启干扰别名返回的模式。

内容的提问来源于stack exchange，提问作者Prifulnath