CrudRepository中的自定义SQL查询 [英] Custom SQL queries in CrudRepository
本文介绍了CrudRepository中的自定义SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在扩展CrudRepository的存储库中执行一些SQL查询. 我在Controller中有以下代码:
I'm trying to execute some SQL queries in my repository which extends CrudRepository. I have the following code in Controller:
@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;
}
public String getU_queryparam() {
return u_queryparam;
}
public void setU_queryparam(String u_queryparam) {
this.u_queryparam = u_queryparam;
}
}
预测是:
public interface UserProjection {
String getU_httpstatus();
String getU_queryparam();
}
我对如何添加查询(诸如此类)感到困惑:
I am confused about how I can add queries like (something like this):
select u_type,count(u_type) from u_user_click_data group by u_type
我如何更改投影,还有哪些其他必要的更改?
How do I change the projection and what are the other necessary changes?
推荐答案
您可以将DAO更改为以下内容,并且应该可以使用.
You can change the DAO to below and this should work.
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类构造函数应具有传递的参数.
Make sure the Bean class constructor should have the passing parameters.
验证查询是否为有效的JPA查询(此处).
Verify that the query is valid JPA query(here).
这篇关于CrudRepository中的自定义SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文