JPA GROUP BY实体 - 这可能吗？ [英] JPA GROUP BY entity - is this possible?

问题描述

有可能通过引用实体分组来选择JPA中的数据吗？

我的意思是：我有两个实体 - 保险和参考多对一车辆。保险实体有validitill字段（当然是车辆领域）。

我想选择车辆并且它是最新的保险。下面的查询不起作用：

  SELECT DISTINCT v.vehicle，
 max（v.validTill）as lastValidTill 
 FROM TraInsurance v 
 GROUP BY v.vehicle 
 ORDER BY lastValidTill 
  

<上面的查询失败，出现错误：

 错误：列travehicle1_.id_brand必须出现在GROUP BY子句中或在一个集合函数中使用
  

这是因为JPA将所有参考车辆的字段添加到查询而不是GROUP通过。这里是我做错了什么吗？或者，也许这是不可能做到这一点的？

编辑： 实体

  @Entity 
 @Table（name =TRA_INSURANCES，schema =public）
 @SequenceGenerator（name =TRA_INSURANCES_SEQ，sequenceName =TRA_INSURANCES_SEQ，allocationSize = 1）
 public class TraInsurance实现EntityInt，Serializable {
 
 private static final long serialVersionUID = 1L; 
 
 @Id 
 @Column（name =id，nullable = false）
 @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =TRA_INSURANCES_SEQ）
私人长ID; 
 
 @NotNull 
 @ManyToOne 
 @JoinColumn（nullable = false，name =id_vehicle）
私人TraVehicle车辆; 
 
 @NotNull 
 @Column（name =valid_from，nullable = false）
私有日期validFrom; 
 
 @Column（name =valid_till，nullable = false）
私有日期validTill; 
 
 @NotNull 
 @ManyToOne 
 @JoinColumn（nullable = false，name =id_company）
私人公司公司; 
 
 @Column（name =policy_no，nullable = true，length = 50）
 private String policyNumber; 
 
 @Column（name =rate，nullable = true，precision = 12，scale = 2）
 private BigDecimal rate; 
 
 @Column（name =discount_percent，nullable = true）
 private Float discountPercent; 
 
 @Column（nullable = true）
私有字符串描述; 
 
 public TraInsurance（）{} 
 
 public Long getId（）{
 return id; 
} 
 
 public void setId（Long id）{
 this.id = id; 
} 
 
 public TraVehicle getVehicle（）{
 return vehicle; 
} 
 
 public void setVehicle（TraVehicle vehicle）{
 this.vehicle = vehicle; 
} 
 
 public Date getValidFrom（）{
 return validFrom; 
} 
 
 public void setValidFrom（Date validFrom）{
 this.validFrom = validFrom; 
} 
 
 public Date getValidTill（）{
 return validTill; 
} 
 
 public void setValidTill（Date validTill）{
 this.validTill = validTill; 
} 
 
 public Company getCompany（）{
 return company; 
} 
 
 public void setCompany（公司公司）{
 this.company = company; 
} 
 
 public String getPolicyNumber（）{
 return policyNumber; 
} 
 
 public void setPolicyNumber（String policyNumber）{
 this.policyNumber = policyNumber; 
} 
 
 public BigDecimal getRate（）{
 return rate; 
} 
 
 public void setRate（BigDecimal rate）{
 this.rate = rate; 
} 
 
 public Float getDiscountPercent（）{
 return discountPercent; 
} 
 
 public void setDiscountPercent（Float discountPercent）{
 this.discountPercent = discountPercent; 
} 
 
 public String getDescription（）{
 return description; 
} 
 
 public void setDescription（String description）{
 this.description = description; 
} 
 
 @Override 
 public int hashCode（）{
 final int prime = 31; 
 int result = 1; 
 result = prime * result +（（id == null）？0：id.hashCode（））; 
 result = prime * result 
 +（（validFrom == null）？0：validFrom.hashCode（））; 
 result = prime * result +（（vehicle == null）？0：vehicle.hashCode（））; 
返回结果; 
 
 $ b @Override 
 public boolean equals（Object obj）{
 if（this == obj）
 return true; 
 if（obj == null）
 return false; 
 if（！（obj instanceof TraInsurance））
 return false; 
 TraInsurance other =（TraInsurance）obj; 
 if（id == null）{
 if（other.id！= null）
 return false; 
} else if（！id.equals（other.id））
 return false; 
 if（validFrom == null）{
 if（other.validFrom！= null）
 return false; 
} else if（！validFrom.equals（other.validFrom））
 return false; 
 if（vehicle == null）{
 if（other.vehicle！= null）
 return false; 
} else if（！vehicle.equals（other.vehicle））
 return false; 
返回true; 
} 
 
} 
  

解决方案

请在此用例中明确使用 JOIN ：

  SELECT ve，MAX（v。 validTill）
 FROM TraInsurance v JOIN v.vehicle ve 
 GROUP BY ve 
 ORDER BY MAX（v.validTill）
  

Is possible to select data in JPA with grouping by referenced entity?

I mean: I have two entities - insurance and referenced many-to-one vehicle. Insurance entity has validTill field (and vehicle field of course).

I'd like to select vehicle and it's latest insurance. The query below doesn't work:

SELECT DISTINCT v.vehicle, 
                max(v.validTill) as lastValidTill 
FROM TraInsurance v 
     GROUP BY v.vehicle 
     ORDER BY lastValidTill

The query above fails with error:

ERROR: column "travehicle1_.id_brand" must appear in the GROUP BY clause or be used in an aggregate function

This is because JPA adds all fields from referenced vehicle to query and not to GROUP BY. Is here something I do wrong? Or maybe it's just not possible to do this?

EDIT:

TraInsurance entity

@Entity
@Table(name = "TRA_INSURANCES", schema="public")
@SequenceGenerator(name = "TRA_INSURANCES_SEQ", sequenceName = "TRA_INSURANCES_SEQ", allocationSize = 1)
public class TraInsurance implements EntityInt, Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TRA_INSURANCES_SEQ")
    private Long                    id;

    @NotNull
    @ManyToOne
    @JoinColumn(nullable = false, name = "id_vehicle")
    private TraVehicle              vehicle;

    @NotNull
    @Column(name = "valid_from", nullable = false)
    private Date                    validFrom;

    @Column(name = "valid_till", nullable = false)
    private Date                    validTill;

    @NotNull
    @ManyToOne
    @JoinColumn(nullable = false, name = "id_company")
    private Company                 company;

    @Column(name = "policy_no", nullable = true, length = 50)
    private String                  policyNumber;

    @Column(name = "rate", nullable = true, precision = 12, scale = 2)
    private BigDecimal              rate;

    @Column(name = "discount_percent", nullable = true)
    private Float                   discountPercent;

    @Column(nullable = true)
    private String                  description;    

    public TraInsurance() {}

    public Long getId() {
        return id;
    }

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

    public TraVehicle getVehicle() {
        return vehicle;
    }

    public void setVehicle(TraVehicle vehicle) {
        this.vehicle = vehicle;
    }  

    public Date getValidFrom() {
        return validFrom;
    }

    public void setValidFrom(Date validFrom) {
        this.validFrom = validFrom;
    }

    public Date getValidTill() {
        return validTill;
    }

    public void setValidTill(Date validTill) {
        this.validTill = validTill;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getPolicyNumber() {
        return policyNumber;
    }

    public void setPolicyNumber(String policyNumber) {
        this.policyNumber = policyNumber;
    }

    public BigDecimal getRate() {
        return rate;
    }

    public void setRate(BigDecimal rate) {
        this.rate = rate;
    }

    public Float getDiscountPercent() {
        return discountPercent;
    }

    public void setDiscountPercent(Float discountPercent) {
        this.discountPercent = discountPercent;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result
                + ((validFrom == null) ? 0 : validFrom.hashCode());
        result = prime * result + ((vehicle == null) ? 0 : vehicle.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (!(obj instanceof TraInsurance))
            return false;
        TraInsurance other = (TraInsurance) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (validFrom == null) {
            if (other.validFrom != null)
                return false;
        } else if (!validFrom.equals(other.validFrom))
            return false;
        if (vehicle == null) {
            if (other.vehicle != null)
                return false;
        } else if (!vehicle.equals(other.vehicle))
            return false;
        return true;
    }  

}

解决方案

Please explicitly use JOIN in this use case:

SELECT ve, MAX(v.validTill)
FROM TraInsurance v JOIN v.vehicle ve
GROUP BY ve
ORDER BY MAX(v.validTill)

这篇关于JPA GROUP BY实体 - 这可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！