JPA query in multiple tables with many-to-many rel

问题:

There are three tables: Hospital, Medical_Service and Language_Service, Hospital can provide medical service and language service. So there are two many-to-many relationships.

Simple ERD

Now I want to search hospital data with postcode = 3000 and medical service = Emergency.

DaoImpl:

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();

    }

Moreover, if I want to search by postcode, medical type and language from three tables, how to write a jsql.

Warnings:

ERROR: org.hibernate.hql.internal.ast.ErrorCounter - Path expected for join! Path expected for join! at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:378) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3858) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3644)

Apr 02, 2016 10:54:30 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [appServlet] in context with path [/travel] threw exception [Request processing failed; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: Postcode of: com.health.entity.Hospital [SELECT h FROM com.health.entity.Hospital h INNER JOIN Medical_Service m ON h.hospital_id = m.hospital_id WHERE h.Postcode = :postcode AND m.Medical_name = :medical]] with root cause org.hibernate.QueryException: could not resolve property: Postcode of: com.health.entity.Hospital at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:83) at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:77) at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1978) at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:367)

Hospital.class

@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
}

MedicalService.class

@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
}

Language.class

@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
}

回答1:

I think your query might be wrong, which could be the cause of the problem.

You're currently using:

 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

The problem might be that Medical_Service doesn't contain a Hospital_id field (used in the JOIN).

If you're happy to use native queries you could do this:

 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')

The inner SELECT gets all of the Hospital_id's for hospitals that offer an Emergency service. The outer select then selects all hospitals where the Hospital_id is in the inner SELECT (i.e. they offer an Emergency service) but also only those with a postcode of 3000.

To use a native query you'd do something like this:

    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();