JPQL计算一对多关系中多个子项匹配的父对象 [英] JPQL count Parent Objects on Multiple Children Match in OneToMany Relationship
问题描述
在JavaEE JPA Web应用程序中，要素实体与患者实体具有双向的ManyToOne关系.我想写一个查询来统计具有一个或多个匹配条件特征的患者人数.我将EclipseLink用作持久性提供程序.
In a JavaEE JPA web application, Feature entity has bidirectional ManyToOne relationship with Patient Entity. I want to write a query to count the number of Patients who have one or more matching criteria features. I use EclipseLink as the Persistence Provider.
例如，我要计算具有'variableName'='Sex'和'variableData'='Female'且具有'variableName'='吸烟'和'variableData'=是的.
For example, I want to count the number of patients who have a feature with 'variableName' = 'Sex' and 'variableData' = 'Female' and another feature with 'variableName' = 'smoking' and 'variableData' = 'yes'.
如何编写JPQL查询以获取患者人数?
How can I write a JPQL query to get the count of patients?
在第一个答案之后，我尝试了该查询未获得预期的任何结果.
After the first answer, I tried this Query does not give any results as expected.
public void querySmokingFemales(){
String j = "select count(f.patient) from Feature f "
+ "where ((f.variableName=:name1 and f.variableData=:data1)"
+ " and "
+ " (f.variableName=:name2 and f.variableData=:data2))";
Map m = new HashMap();
m.put("name1", "sex");
m.put("data1", "female");
m.put("name2", "smoking");
m.put("data2", "yes");
count = getFacade().countByJpql(j, m);
}
Patient实体如下.
The Patient entity is as follows.
@Entity
public class Patient implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@OneToMany(mappedBy = "patient")
private List<Feature> features;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Patient)) {
return false;
}
Patient other = (Patient) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "entity.Patient[ id=" + id + " ]";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Feature> getFeatures() {
return features;
}
public void setFeatures(List<Feature> features) {
this.features = features;
}
}
这是要素实体.
@Entity
public class Feature implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String variableName;
private String variableData;
@ManyToOne
private Patient patient;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Feature)) {
return false;
}
Feature other = (Feature) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "entity.Feature[ id=" + id + " ]";
}
public String getVariableName() {
return variableName;
}
public void setVariableName(String variableName) {
this.variableName = variableName;
}
public String getVariableData() {
return variableData;
}
public void setVariableData(String variableData) {
this.variableData = variableData;
}
public Patient getPatient() {
return patient;
}
public void setPatient(Patient patient) {
this.patient = patient;
}
}
推荐答案
对于单个功能计数，您可以使用此
select count(f.patient) from Feature f where f.variableName=:name and f.variableData:=data
两个功能计数
select count(distinct p) from Patient p, Feature f1, Feature f2
where
p.id=f1.patient.id and p.id=f2.patient.id and
f1.variableName=:name1 and f1.variableData:=data1 and
f2.variableName=:name2 and f2.variableData:=data2
多个功能计数解决方案有些棘手.可以使用org.springframework.data.jpa.domain.Specification
Multiple feature counts solution is a bit tricky. org.springframework.data.jpa.domain.Specification can be used
public class PatientSpecifications {
public static Specification<Patient> hasVariable(String name, String data) {
return (root, query, builder) -> {
Subquery<Fearure> subquery = query.subquery(Fearure.class);
Root<Fearure> feature = subquery.from(Fearure.class);
Predicate predicate1 = builder.equal(feature.get("patient").get("id"), root.get("id"));
Predicate predicate2 = builder.equal(feature.get("variableName"), name);
Predicate predicate3 = builder.equal(feature.get("variableData"), data);
subquery.select(operation).where(predicate1, predicate2, predicate3);
return builder.exists(subquery);
}
}
}
然后您的PatientRepository必须扩展org.springframework.data.jpa.repository.JpaSpecificationExecutor<Patient>
Then your PatientRepository have to extend org.springframework.data.jpa.repository.JpaSpecificationExecutor<Patient>
@Repository
public interface PatientRepository
extends JpaRepository<Patient, Long>, JpaSpecificationExecutor<Patient> {
}
您的服务方式:
@Service
public class PatientService {
@Autowired
PatientRepository patientRepository;
//The larger map is, the more subqueries query would involve. Try to avoid large map
public long countPatiens(Map<String, String> nameDataMap) {
Specification<Patient> spec = null;
for(Map.Entry<String, String> entry : nameDataMap.entrySet()) {
Specification<Patient> tempSpec = PatientSpecifications.hasVariable(entry.getKey(), entry.getValue());
if(spec != null)
spec = Specifications.where(spec).and(tempSpec);
else spec = tempSpec;
}
Objects.requireNonNull(spec);
return patientRepository.count(spec);
}
}
这篇关于JPQL计算一对多关系中多个子项匹配的父对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
