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

Spring data JPA: getting No aliases found in result tuple! error when executing custom query

提问by Registered User

I am trying to execute a custom query on the mysql database using the @Queryannotation of spring data jpa.

我正在尝试使用@Queryspring 数据 jpa的注释对mysql 数据库执行自定义查询。

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

和 mysql 中的查询是

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,

在 Spring 数据 jpa 中实现这一点时。我正在使用这个逻辑，

1. create another class CountPersonthat holds two variables, last_nameand count
2. Use @Query to write the query, and the method returns list of object of CountPersonclass.

1. 创建另一个CountPerson包含两个变量的类，last_name然后count
2. 使用@Query 编写查询，该方法返回CountPerson类的对象列表。

The query as in spring data jpa is

spring data jpa 中的查询是

@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

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

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 aliaseswhich says that it is a fixed bug. So I guess my issue is different

搜索此错误显示spring data jpa: No aliases found in result tuple！确保您的查询定义了别名，表明它是一个已修复的错误。所以我想我的问题是不同的

---

---

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;
    }
}

回答by Jeroen van Dijk-Jun

Almost there... (by heart, so I hope it's perfect) You'll need to create a new CountPerson(...)

差不多了...（用心，所以我希望它是完美的）你需要创建一个新的 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.

JpaRepository 只能轻松返回 Person 对象，但您可以自己在 HQL 中创建对象。

回答by ajaristi

A more clean solution is to use Spring Data JPA Projections:

一个更干净的解决方案是使用Spring Data JPA Projections：

Yo must replace class for a interface and define only get methods:

你必须替换接口的类并只定义获取方法：

public interface CountPerson {

    String getLastName();

    int getCount();
}

Your Repository method seem like this:

您的 Repository 方法如下所示：

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