Spring boot JPA中的自定义查询问题

Question

我试图按范围计算记录数，但在运行服务时出错：

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '<' near line 1, column 58 [SELECT new net.BBB.ProjectB.entity.AgeCount(COUNT(s.edad < 15 OR NULL), COUNT(s.edad < 25 AND s.edad > 16 OR NULL), COUNT(s.edad < 35 AND s.edad > 26 OR NULL), COUNT(s.edad < 45 AND s.edad > 36 OR NULL), COUNT(s.edad > 46 OR NULL),FROM net.BBB.ProjectB.entity.Student AS s) ]

package net.BBB.ProjectB.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import net.BBB.ProjectB.entity.AgeCount;
import net.BBB.ProjectB.entity.NacionalityCount;
import net.BBB.ProjectB.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Long> {

    List<Student> findByNombre(String nombre);

    @Query(value = "SELECT new net.BBB.ProjectB.entity.NacionalityCount(COUNT(s.nacionalidad), s.nacionalidad) "
            + "FROM Student AS s GROUP BY s.nacionalidad")
    List<NacionalityCount> countByNacionalidad();

    @Query(value = "SELECT new net.BBB.ProjectB.entity.AgeCount(COUNT(s.edad < 15 OR NULL), "
            + "COUNT(s.edad < 25 AND s.edad > 16 OR NULL), COUNT(s.edad < 35 AND s.edad > 26 OR NULL), "
            + "COUNT(s.edad < 45 AND s.edad > 36 OR NULL), COUNT(s.edad > 46 OR NULL),"
            + "FROM Student AS s) ")
    List<AgeCount> countByAge();

}

第一个查询很好，第二个是问题。以防万一，该实体的名称是Student，它有一个名为“edad”的属性，我试图通过范围来计算它。我已经尝试过在数据库上工作的其他查询，例如：

SELECT COUNT(s.edad BETWEEN 0 AND 16 OR NULL), 
        COUNT(s.edad BETWEEN 17 AND 25 OR NULL), 
        COUNT(s.edad BETWEEN 26 AND 35 OR NULL),
        COUNT(s.edad BETWEEN 35 AND 45 OR NULL)
FROM students AS s;

很明显是用实体的名字。我不知道是否有办法让它起作用。

Answer 1

不完全确定，但在您的HQL查询中，您在末尾有一个“，”加上一个“）”，应该在其他地方？

  + "COUNT(s.edad < 45 AND s.edad > 36 OR NULL), COUNT(s.edad > 46 OR NULL),"
            + "FROM Student AS s) ")

通常，我会尝试一步一步地修复这个查询。即。用一个静态数字替换所有计数，然后用实际表达式替换1个计数，一旦成功，替换下一个等等。