工作时遇到一个问题就是想用JPA分组查询并展示出数量,并按数量排序,终于参考无数资料搞了出来
@Autowired
private EntityManager entityManager;
private List<Tuple> userMessageVos (){
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<CallRecordEntity> root = cq.from(CallRecordEntity.class);
List<Order> orderList = new ArrayList<>();
orderList.add(cb.desc(cb.count(root)));//按照查询出来的数量进行排序
Path<String> userName = root.get("userName");//返回值中的用户名
cq.select(cb.tuple(userName,cb.count(root)));
cq.groupBy(userName);//通过姓名分组
cq.orderBy(orderList);//排序
TypedQuery<Tuple> q = entityManager.createQuery(cq);
List<Tuple> result = q.getResultList();//查询
return result;
}
@Override
public List<UserMessageVo> userMessageVo(String month) {
List<UserMessageVo> userMessageVoList = new ArrayList<>();
List<Tuple> list = userMessageVos();
//因为数据比较多,在这里只展示十个
for (int i = 0;i<10;i++){
Tuple tuple = list.get(i);
UserMessageVo userMessageVo = new UserMessageVo();
userMessageVo.setUserName((String)tuple.get(0));
userMessageVo.setIcount((Long) tuple.get(1));
userMessageVoList.add(userMessageVo);
}
return userMessageVoList;
}
其中UserMessageVo 是返回值,CallRecordEntity是实体类
package com.wf.demo.entity.vo;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
/**
* @Author WF
* @DesCription 员工通话量记录返回值
* @Date 2019/3/8 17:18
**/
@Getter
@Setter
public class UserMessageVo {
@ApiModelProperty("员工姓名")
private String userName;
@ApiModelProperty("数量")
private Long icount;
public UserMessageVo(){}
public UserMessageVo(String userName,Long icount){
this.userName = userName;
this.icount = icount;
}
}
package com.wf.demo.entity;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.*;
import java.io.File;
import java.util.Date;
/**
* @Author WF
* @DesCription 通话记录实体类
* @Date 2019/3/8 17:08
**/
@Getter
@Setter
@Entity
@Table(name = "business_info_call_record")
public class CallRecordEntity {
@ApiModelProperty("呼叫id")
@Id
private String callId;
@ApiModelProperty("主叫号码")
private String caller;
@ApiModelProperty("被叫号码")
private String called;
@ApiModelProperty("振铃时长")
.....................
.....................
.....................
swagger中的返回值