具有多对多关系的多个表中的JPA查询

  • Z时代
  • 2024-01-10
  • 分类：问答

有三个表：Hospital，Medical_Service并且Language_Service，医院能提供的医疗服务和语言服务。因此，存在两个多对多关系。

简单ERD

现在，我想使用postcode = 3000和搜索医院数据medical service = Emergency。

public List<Hospital> findByPostcodeAndMedicalType(String postcode, String medical) {
        String str = "SELECT h FROM Hospital h INNER JOIN Medical_Service m ON h.hospital_id = m.hospital_id WHERE " 
                + "h.Postcode = :postcode AND m.Medical_name = :medical";
        Query query = em.createQuery(str);
        query.setParameter("postcode", postcode);
        query.setParameter("medical", medical);
        return query.getResultList();
    }

而且，如果我想从三个表中按邮政编码，医疗类型和语言进行搜索，那么如何编写一个jsql。

错误：org.hibernate.hql.internal.ast.ErrorCounter-

预期加入的路径！希望加入的路径！在org.hibernate.org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement（HqlSqlBaseWalker.java:3858）在org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement（HqlSqlWalker.java:378）

.antlr.HqlSqlBaseWalker.fromElement（HqlSqlBaseWalker.java:3644）

2016年4月2日晚上10:54:30 org.apache.catalina.core.StandardWrapperValve在路径[/

travel]的上下文中为Servlet

[appServlet]调用SEVERE：Servlet.service（）引发异常[请求处理失败；嵌套异常是java.lang.IllegalArgumentException：org.hibernate.QueryException：

的：com.health.entity.Hospital

[从com.health.entity.Hospital中选择h在IN.JOIN JOIN Medical_Service中启用h.hospital_id

= m.hospital_id在哪里h.Postcode =：postcode和m.Medical_name

=：medical]根本原因是org.hibernate.QueryException：无法解析属性：org.hibernate.persister.entity.AbstractPropertyMapping.propertyException（AbstractPropertyMapping.java:83）上com.health.entity.Hospital的邮政编码：org.hibernate.persister.entity

org.hibernate.persister.entity.AbstractEntityPersister.toType（AbstractEntityPersister.java:1978）的.AbstractPropertyMapping.toType（AbstractPropertyMapping.java:77）在org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType（FromElementType.java

：367）

@Entity
@Table(name = "Hospital")
public class Hospital {
@Id
@GeneratedValue
    private int hospital_id;
    private String hospital_name;
    private String postcode;
    private String suburb;
    private String address;
    private String type;
    private String category;
    private String longitude;
    private String latitude;
    private String email;
    private String website;
    private String phoneno;
    private String isemergency;
    private String agencytype;
    private String fax;
    @ManyToMany
    @JoinTable(
        name = "Hospital_Medical",
        joinColumns=@JoinColumn(name="Hospital_id", referencedColumnName="Hospital_id"),
        inverseJoinColumns=@JoinColumn(name="Medical_id", referencedColumnName="Medical_id"))
private List<MedicalService> services;
    @ManyToMany
    @JoinTable(
        name = "Hospital_Language",
        joinColumns=@JoinColumn(name="Hospital_id", referencedColumnName="Hospital_id"),
        inverseJoinColumns=@JoinColumn(name="Language_id", referencedColumnName="Language_id"))
private List<Language> languages;
//Setter and Getter
}

@Entity
@Table(name = "Medical_Service")
public class MedicalService {
@Id
private int medical_id;
private String medical_name;
private String description;
@ManyToMany(mappedBy="services")
private List<Hospital> hospitals;
//Setter and Getter
}

@Entity
@Table(name = "Language")
public class Language {
@Id
private int language_id;
private String language_name;
private String display_name;
@ManyToMany(mappedBy="languages")
private List<Hospital> hospitals;
//Setter and Getter
}

回答：

我认为您的查询可能有误，这可能是问题的原因。

您当前正在使用：

 SELECT h FROM Hospital h
     INNER JOIN Medical_Service m ON h.hospital_id = m.hospital_id
     WHERE h.Postcode = :postcode AND m.Medical_name = :medical

问题可能是Medical_Service不包含Hospital_id字段（在JOIN中使用）。

如果您愿意使用本机查询，则可以执行以下操作：

 SELECT * FROM Hospital WHERE Postcode = 3000 AND Hospital_id IN
    (SELECT Hospital_id FROM Hospital_Medical hm INNER JOIN Medical_Service m ON hm.Medical_id = m.Medical_id
    where Medical_name = 'Emergency')

内部SELECT获取提供急救服务的医院的所有Hospital_id。然后，外部选择会选择内部SELECT中具有Hospital_id的所有医院（即，它们提供紧急服务），但也会选择邮政编码为3000的医院。

要使用本机查询，您需要执行以下操作：

    int postcode = 3000;
    String service = "Emergency";
    StringBuilder sb = new StringBuilder(); 
        sb.append("SELECT * FROM Hospital WHERE Postcode = ");
        sb.append(postcode);
        sb.append("AND Hospital_id IN SELECT Hospital_id FROM Hospital_Medical hm INNER JOIN "
                + "Medical_Service m ON hm.Medical_id = m.Medical_id where Medical_name = '");
        sb.append(service);
        sb.append("')");
    String queryString = sb.toString();
    Query query = em.createNativeQuery(queryString);
    List<Hospital> result = query.getResultList();

以上是 具有多对多关系的多个表中的JPA查询 的全部内容， 来源链接： utcz.com/qa/427011.html

回到顶部