执行自定义查询时出错

执行自定义查询时出错

本文介绍了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,

  1. create another class CountPerson that holds two variables, last_name and count
  2. 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:在结果元组中找不到别名!执行自定义查询时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 21:27