Java JPA GROUP BY 实体 - 这可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20117110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA GROUP BY entity - is this possible?
提问by robson
Is possible to select data in JPA with grouping by referenced entity?
是否可以通过引用实体分组来选择 JPA 中的数据？
I mean: I have two entities - insurance and referenced many-to-one vehicle. Insurance entity has validTill field (and vehicle field of course).
我的意思是：我有两个实体 - 保险和引用的多对一车辆。保险实体具有 validTill 字段（当然还有车辆字段）。
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?
这是因为 JPA 将引用车辆的所有字段添加到查询而不是 GROUP BY。这是我做错了什么吗？或者也许只是不可能做到这一点？
EDIT:
编辑：
TraInsurance entity
TraInsurance实体
@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;
}
}
采纳答案by pqian
Please explicitly use JOINin this use case:
请在此用例中明确使用JOIN：
SELECT ve, MAX(v.validTill)
FROM TraInsurance v JOIN v.vehicle ve
GROUP BY ve
ORDER BY MAX(v.validTill)
回答by thanhnguyen
If you pass an entity inside the GROUP BY, Hibernate automatically adds its idto the transformed SQL of the underlying DB. In addition, the values in the GROUP BY must exist in the SELECT clause. Thus, instead of select the whole object, you can select its id, then from those ids, you can retrieve the object again.
如果您在 GROUP BY 中传递一个实体，Hibernate 会自动将其id添加到底层数据库的转换后的 SQL 中。此外，GROUP BY 中的值必须存在于 SELECT 子句中。因此，您可以选择它的 id，而不是选择整个对象，然后从这些ids 中，您可以再次检索该对象。
SELECT DISTINCT v.vehicle.id, max(v.validTill)
FROM TraInsurance v
GROUP BY v.vehicle.id
ORDER BY max(v.validTill)
If it takes time and requires DB hits to retrieve Vehicleobjects from their ids, you can select all of Vehicle's attributes in the SELECT and put them in the GROUP BY. Then you can construct the Vehicleobject from those attributes without accessing to DB.
如果需要时间并需要 DB 命中才能从其 id 中检索Vehicle对象，您可以在 SELECT 中选择Vehicle的所有属性并将它们放在 GROUP BY 中。然后，您可以根据这些属性构造Vehicle对象，而无需访问 DB。
