JPA原生查询自定义对象别名：按日期分组统计特定标题Profile数量

1. 先处理数据库连接异常（Communications link failure） #

这个异常属于JDBC连接层面问题，和查询逻辑无关，优先排查以下点：

• 确认MySQL服务是否正常启动
• 检查配置文件（application.properties/yaml）中的数据库URL、用户名、密码是否正确，MySQL 8.x版本需确保URL包含serverTimezone参数，且时区与数据库一致
• 验证应用服务器的防火墙/安全组是否允许访问MySQL默认端口（3306）
• 确认MySQL驱动版本与服务器版本兼容（如MySQL 8.x需搭配mysql-connector-java 8.x版本）

2. 修复分组查询的实现问题 #

核心问题分析 #

• 原生SQL查询（nativeQuery=true）不支持JPQL的new com.example...构造器语法
• 接口投影时，SQL结果的别名必须与接口的get方法名严格对应
• title like :title%的写法会导致参数绑定失败，需用安全的字符串拼接方式避免SQL注入

正确实现方案 #

方案1：接口投影（推荐）

保持UserCreatedByDate为接口：

public interface UserCreatedByDate {
    String getCreated();
    Long getCounter();
}

修改Repository方法，用CONCAT拼接前缀，确保SQL别名与接口方法匹配：

@Repository
public interface ProfileRepository extends CrudRepository<Profile, Long> {
    @Query(value = "SELECT FROM_UNIXTIME(created_at/1000,'%m-%d-%Y') AS created, COUNT(*) AS counter " +
            "FROM profile " +
            "WHERE title LIKE CONCAT(:title, '%') " +
            "AND created_at >= :from " +
            "AND created_at < :to " +
            "GROUP BY created",
            nativeQuery = true)
    List<UserCreatedByDate> findCountByDateRangeAndTitlePrefix(
            @Param("from") Long from,
            @Param("to") Long to,
            @Param("title") String title);
}

方案2：DTO类映射

如果要使用带注解的UserCreatedByDate类：

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class UserCreatedByDate {    
    private String created;
    private Long counter;
}

Repository方法写法与方案1一致，Spring Data会自动将SQL别名映射到DTO字段（需字段名与别名完全匹配，且DTO有默认构造器）：

@Repository
public interface ProfileRepository extends CrudRepository<Profile, Long> {
    @Query(value = "SELECT FROM_UNIXTIME(created_at/1000,'%m-%d-%Y') AS created, COUNT(*) AS counter " +
            "FROM profile " +
            "WHERE title LIKE CONCAT(:title, '%') " +
            "AND created_at >= :from " +
            "AND created_at < :to " +
            "GROUP BY created",
            nativeQuery = true)
    List<UserCreatedByDate> findCountByDateRangeAndTitlePrefix(
            @Param("from") Long from,
            @Param("to") Long to,
            @Param("title") String title);
}

3. 验证查询逻辑 #

将生成的SQL提取到MySQL客户端执行，确认结果符合预期：

SELECT FROM_UNIXTIME(created_at/1000,'%m-%d-%Y') AS created, COUNT(*) AS counter 
FROM profile 
WHERE title LIKE 'your_test_prefix%' 
AND created_at >= 1674153600000 -- 示例：2023-01-20 00:00:00的毫秒时间戳
AND created_at < 1675276800000 -- 示例：2023-02-01 00:00:00的毫秒时间戳
GROUP BY created;

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