Spring Boot项目中SQL SELECT语句无法返回COUNT()结果的问题排查

Hey there, let's figure out why your numOfVisit field isn't getting populated correctly in the UserLocation entity!

问题根源 #

The core issue here is how BeanPropertyRowMapper maps SQL result columns to your entity fields. This mapper relies on exact name matching between the SQL result column names and your entity's property names.

In your current query, you're using COUNT(ul.dayOfVisit) without assigning an alias. When H2 executes this, it returns a column with a default name like COUNT(UL.DAYOFVISIT) (case might vary based on database settings), which doesn't match the numOfVisit property in your UserLocation class. That's why the value stays at the default 0 instead of the actual count.

解决方案 #

1. 给COUNT()结果添加别名

修改你的SQL查询，给COUNT()结果指定一个和实体类属性完全匹配的别名：

public List<UserLocation> getUserLocationList(Long userId) {
    MapSqlParameterSource namedParameters = new MapSqlParameterSource();
    // 为COUNT()结果添加AS numOfVisit别名
    String query = "SELECT l.locName, COUNT(ul.dayOfVisit) AS numOfVisit FROM location l " +
            "INNER JOIN userLocation ul ON l.locId = ul.locId " +
            "INNER JOIN sec_user sc ON ul.userId = sc.userId " +
            "WHERE sc.userId = :userId AND ul.locId = 1" +
            "GROUP BY l.locName";
    namedParameters.addValue("userId", userId);
    return jdbc.query(query, namedParameters, new BeanPropertyRowMapper<UserLocation>(UserLocation.class));
}

这样SQL返回的列名就会和numOfVisit完全匹配，RowMapper就能正确把统计值映射到实体属性上了。

2. 可选：检查数据类型兼容性

COUNT()函数返回的数值通常是Long类型（因为统计值可能很大），而你的实体类用了int类型存储numOfVisit，小数值没问题，但如果统计值超过Integer.MAX_VALUE就会出问题。你可以二选一：

• 更新UserLocation实体类，把numOfVisit改为Long类型：

  @Data
  @NoArgsConstructor
  public class UserLocation {
      private String locName;
      private Long numOfVisit; // 改为Long类型
  }
  

• 或者在SQL里把COUNT结果强制转换为整数（如果你确定统计值不会超出int范围）：

  CAST(COUNT(ul.dayOfVisit) AS INTEGER) AS numOfVisit
  

额外检查点 #

• 确认userLocation表中存在匹配userId和locId = 1的记录，如果没有记录，COUNT()会返回0，现在这个值也能正确映射到numOfVisit了。
• 如果还是有问题，可以临时用queryForList()调试，查看返回的列名：

  List<Map<String, Object>> results = jdbc.queryForList(query, namedParameters);
  if (!results.isEmpty()) {
      System.out.println("返回的列名：" + results.get(0).keySet());
  }
  

这能帮你确认H2实际返回的列名，验证别名是否生效。

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