JPA进行CriteriaQuery进行查询注意事项

1.pojo类

@Entity
@Table(name = "report_workload")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@JsonIdentityInfo(generator = JSOGGenerator.class)
public class ReportWorkload {
    private int id;
    private Integer flowWorkItemApprId;
    private Integer busId;
    private Integer deptId;
    private Integer staffId;
    private Integer busiValueIndustryId;
    private Integer busiValueScaleId;
    private String taskName;
    private Integer count;
    private BigDecimal amount;
    private Date approvalTime;
    private String reportTime;

    private String deptName;
    private String staffName;


    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "flow_work_item_appr_id")
    public Integer getFlowWorkItemApprId() {
        return flowWorkItemApprId;
    }

    public void setFlowWorkItemApprId(Integer flowWorkItemApprId) {
        this.flowWorkItemApprId = flowWorkItemApprId;
    }

    @Basic
    @Column(name = "bus_id")
    public Integer getBusId() {
        return busId;
    }

    public void setBusId(Integer busId) {
        this.busId = busId;
    }

    @Basic
    @Column(name = "dept_id")
    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    @Basic
    @Column(name = "staff_id")
    public Integer getStaffId() {
        return staffId;
    }

    public void setStaffId(Integer staffId) {
        this.staffId = staffId;
    }

    @Basic
    @Column(name = "busi_value_industry_id")
    public Integer getBusiValueIndustryId() {
        return busiValueIndustryId;
    }

    public void setBusiValueIndustryId(Integer busiValueIndustryId) {
        this.busiValueIndustryId = busiValueIndustryId;
    }

    @Basic
    @Column(name = "busi_value_scale_id")
    public Integer getBusiValueScaleId() {
        return busiValueScaleId;
    }

    public void setBusiValueScaleId(Integer busiValueScaleId) {
        this.busiValueScaleId = busiValueScaleId;
    }

    @Basic
    @Column(name = "task_name")
    public String getTaskName() {
        return taskName;
    }

    public void setTaskName(String taskName) {
        this.taskName = taskName;
    }

    @Basic
    @Column(name = "count")
    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }

    @Basic
    @Column(name = "amount")
    public BigDecimal getAmount() {
        return amount;
    }

    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }

    @Basic
    @Column(name = "approval_time")

    public Date getApprovalTime() {
        return approvalTime;
    }

    public void setApprovalTime(Date approvalTime) {
        this.approvalTime = approvalTime;
    }

    @Basic
    @Column(name = "report_time")
    public String getReportTime() {
        return reportTime;
    }

    public void setReportTime(String reportTime) {
        this.reportTime = reportTime;
    }


    @Transient
    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    @Transient
    public String getStaffName() {
        return staffName;
    }

    public void setStaffName(String staffName) {
        this.staffName = staffName;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof ReportWorkload)) return false;

        ReportWorkload that = (ReportWorkload) o;

        return id == that.id;

    }

    @Override
    public int hashCode() {
        return id;
    }

    public ReportWorkload(int id, Integer flowWorkItemApprId,
                          Integer busId, Integer deptId, Integer staffId,
                          Integer busiValueIndustryId, Integer busiValueScaleId,
                          String taskName, Long count, BigDecimal amount,
                          Date approvalTime, String reportTime) {
        this.id = id;
        this.flowWorkItemApprId = flowWorkItemApprId;
        this.busId = busId;
        this.deptId = deptId;
        this.staffId = staffId;
        this.busiValueIndustryId = busiValueIndustryId;
        this.busiValueScaleId = busiValueScaleId;
        this.taskName = taskName;
        this.count = Integer.parseInt(count+"");
//        this.count = count;
        this.amount = amount;
        this.approvalTime = approvalTime;
        this.reportTime = reportTime;
    }

    public ReportWorkload() {
    }
}

在进行聚合函数sum求和时，原来是int会自动提升为long,不做特殊处理就会报以下错误了：

org.hibernate.hql.internal.ast.DetailedSemanticException: Unable to locate appropriate constructor on class [com.changfa.frame.data.entity.report.Report
Workload]. Expected arguments are: int, int, int, int, int, int, int, java.lang.String, long, java.math.BigDecimal, java.util.Date, java.lang.String
	at org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor(ConstructorNode.java:182)
	at org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:144)
	at org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1092)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2359)

会提示你查询数据库返回的类型和你的构造函数类型对应不上。

service层：

通过注解将EntityManager加载进来：

    @PersistenceContext
    private EntityManager em;

查询方法：

 public List<ReportWorkload> reportworkloadsearch(String reportTime, String deptId, String staffId, String typeId, String industryId) {

        List<ReportWorkload> reportWorkloadList = new ArrayList<>();
        CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
        CriteriaQuery<ReportWorkload> cq = criteriaBuilder.createQuery(ReportWorkload.class);
        Root<ReportWorkload> rt = cq.from(ReportWorkload.class);
        cq.multiselect(rt.get("id"),rt.get("flowWorkItemApprId"),
                rt.get("busId"),rt.get("deptId"),rt.get("staffId"),
                rt.get("busiValueIndustryId"),rt.get("busiValueScaleId"),
                rt.get("taskName"),criteriaBuilder.sum(rt.get("count")),
                criteriaBuilder.sum(rt.get("amount")),rt.get("approvalTime"),
                rt.get("reportTime"));

        if(reportTime!=null&&reportTime!=""){
            cq.where(criteriaBuilder.equal(rt.get("reportTime"), reportTime));
        }
        if(deptId!=null&&deptId!=""){
            cq.where(criteriaBuilder.equal(rt.get("deptId"), Integer.parseInt(deptId)));
        }
        if(staffId!=null&&staffId!=""){
            cq.where(criteriaBuilder.equal(rt.get("staffId"), Integer.parseInt(staffId)));
        }
        if(typeId!=null&&typeId!=""){
            cq.where(criteriaBuilder.equal(rt.get("typeId"), Integer.parseInt(typeId)));
        }
        if(industryId!=null&&industryId!=""){
            cq.where(criteriaBuilder.equal(rt.get("industryId"), Integer.parseInt(industryId)));
        }

        cq.groupBy(rt.get("busId"),rt.get("deptId"),rt.get("taskName"));
        reportWorkloadList = em.createQuery(cq).getResultList();

        return reportWorkloadList;
    }

在进行cq.multiselect自定义返回字段时，必须在对应的pojo中给一个对应的返回字段构造函数