Spring数据JPA：在结果元组中找不到别名！执行自定义查询时出错

问题描述

我试图使用spring数据jpa的 @Query 注释在mysql数据库上执行自定义查询。

表格是

  + ------------ + ------ --------- + ------ + ----- + --------- + ------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------ + --------------- + ------ + ----- + --- ------ + ------- + 
 | id |十进制（10,0）| NO | PRI | NULL | | 
 | first_name | varchar（20）|是| | NULL | | 
 | last_name | varchar（20）|是| | NULL | | 
 + ------------ + --------------- + ------ + ----- + --- ------ + ------- + 
  

和mysql中的查询是

  select last_name，count（last_name） 
  

---

在Spring数据jpa中实现它。我使用这个逻辑，

1. 创建另一个类 CountPerson ，它包含两个变量， last_name 和 count


2. 使用@Query编写查询，该方法返回 CountPerson 类的对象列表。

在spring数据jpa中的查询是

  @Query（select p.lastName，count（p.lastName）as Person p group by p.lastName）
  

---

当代码编译和Web服务器启动正常时，当我尝试运行相关方法时，我得到

 出现意外错误（type = Internal Server Error，status = 500）。 
在结果元组中找不到别名！确保你的查询定义了别名！嵌套异常是java.lang.IllegalStateException：在结果元组中找不到别名！确保你的查询定义了别名！ 
  

搜索此错误显示，说明它是一个固定的错误。所以我想我的问题是不同的

---

代码是

Person class

  //进口
 
 @Entity 
 @Table（name =人）
 public class Person {
 
 @Id 
 Long id; 
字符串firstName; 
字符串姓氏; 
 
私人Person（）{} 
 //构造函数
} 
  

Person存储库类

$ p $ //进口
@Transactional
公共接口PersonRepository扩展CrudRepository< ; Person，Long> {

@Query（select p.lastName，count（p.lastName）as Person p group by p.lastName）
public List< CountPerson> countbylastname（）;

$ / code>

控制器类

  @Controller 
 public class PersonController {
 
 @Autowired 
 PersonRepository存储库; 
 
 @RequestMapping（/ count）
 @ResponseBody 
 public List< CountPerson> countbylastname（）{
 return repository.countbylastname（）; 
 
 
 $ / code $ / pre 
 
计数人员类别
 
 
  public class CountPerson {
 String lastName; 
 int count; 
 
 protected CountPerson（）{} 
 
 public CountPerson（String lastName，int count）{
 this.lastName = lastName; 
 this.count = count; 
} 
} 
  
 
 
解决方案
在那里...（心，所以我希望它是完美的）你需要创建一个新的CountPerson（...）
 从人员p选择新的com.mypackage.CountPerson（p.last_name，count（p.last_name））... 
  
 JpaRepository只能轻松返回Person对象，但您可以自己在HQL中创建对象。
 
I am trying to execute a custom query on the mysql database using the @Query annotation of spring data jpa.
The table is
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | decimal(10,0) | NO   | PRI | NULL    |       |
| first_name | varchar(20)   | YES  |     | NULL    |       |
| last_name  | varchar(20)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
and the query as in mysql is
select last_name,count(last_name) as count from person group by last_name;
While implementing this in Spring data jpa. I'm using this logic,
create another class CountPerson that holds two variables, last_name and count
Use @Query to write the query, and the method returns list of object of CountPerson class. 
The query as in spring data jpa is
@Query("select p.lastName,count(p.lastName) as count from Person p group by p.lastName")
While the code compiles and the web server starts fine, when I try to run the related method, I get
There was an unexpected error (type=Internal Server Error, status=500).
No aliases found in result tuple! Make sure your query defines aliases!; nested exception is java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!
Searching for this error shows spring data jpa: No aliases found in result tuple! Make sure your query defines aliases which says that it is a fixed bug. So I guess my issue is different
The codes are
Person class
//imports

@Entity
@Table(name = "person")
public class Person{

    @Id
    Long id;
    String firstName;
    String lastName;

    private Person(){}
    //constructor
}
Person repository class
//imports
@Transactional
public interface PersonRepository extends CrudRepository<Person,Long>{

    @Query("select p.lastName,count(p.lastName) as count from Person p group by p.lastName")
    public List<CountPerson> countbylastname();
}
Controller class
@Controller
public class PersonController{

    @Autowired
    PersonRepository repository;

    @RequestMapping("/count")
    @ResponseBody
    public List<CountPerson> countbylastname(){
        return repository.countbylastname();
    }
}
Count Person class
public class CountPerson{
    String lastName;
    int count;

    protected CountPerson(){}

    public CountPerson(String lastName,int count){
        this.lastName = lastName;
        this.count = count;
    }
}
 解决方案 
Almost there... (by heart, so I hope it's perfect) You'll need to create a new CountPerson(...)
select new com.mypackage.CountPerson(p.last_name, count(p.last_name)) from person p ...
The JpaRepository can only easily return Person objects, but you can create objects in HQL yourself.
                        
这篇关于Spring数据JPA：在结果元组中找不到别名！执行自定义查询时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！