CrudRepository 中的自定义 SQL 查询

Question

我正在尝试在扩展 CrudRepository 的存储库中执行一些 SQL 查询。我在控制器中有以下代码：

@CrossOrigin(origins = "*")

    @GetMapping(path="/all")

    public @ResponseBody List<UserProjection> getAllRequestResponseRecords() {

        return userRequestResponseRepository.findAllProjectedBy() ;

    }

DAO代码如下：

public interface UserRequestResponseRepository extends CrudRepository<UserRequestResponse, Integer> {

    //public static final String FIND_QUERY = "select user.u_httpstatus ,user.u_queryparam from UserRequestResponse user";

    public static final String FIND_QUERY = 

    "select new com.abc.datacollection.entity.UserRequestResponse(user.u_httpstatus ,user.u_queryparam) from UserRequestResponse user";

    @Query(value = FIND_QUERY)

    //public List<UserProjection> getAllRequestResponseRecords();

     List<UserProjection> findAllProjectedBy();

}

班级是：

import java.sql.Date;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class

public class UserRequestResponse {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private String u_httpstatus;

    private String u_error_message;

    private String u_queryparam;

    public UserRequestResponse(String u_httpstatus, String u_queryparam) {

        this.u_httpstatus = u_httpstatus;

        this.u_queryparam = u_queryparam;

    }

    public String getU_httpstatus() {

        return u_httpstatus;

    }

    public void setU_httpstatus(String u_httpstatus) {

        this.u_httpstatus = u_httpstatus;

    }

    public String getU_error_message() {

        return u_error_message;

    }

    public void setU_error_message(String u_error_message) {

        this.u_error_message = u_error_message;

    }

我对如何添加像这样的查询感到困惑：

select u_type,count(u_type) from u_user_click_data group by u_type

如何更改投影以及其他必要的更改是什么？

Answer 1

您可以将 DAO 更改为以下内容，这应该可以工作。public interface UserRequestResponseRepository extends CrudRepository<UserRequestResponse, Integer> {public static final String FIND_QUERY = "select new com.abc.datacollection.entity.UserRequestResponse(user.u_httpstatus ,user.u_queryparam, COUNT(user.u_type)) from UserRequestResponse user GROUP BY user.u_type";@Query(value = FIND_QUERY)//public List<UserProjection> getAllRequestResponseRecords(); List<UserProjection> findAllProjectedBy();}确保 Bean 类构造函数应该具有传递参数。