JPA以多对多的关系查询多个表

问题描述

有三个表：医院， Medical_Service 和 Language_Service ，
医院可以提供医疗服务和语言服务。所以有两个多对多的关系。

现在我想用 postcode = 3000 和 medical service = Emergency 。

DaoImpl： b $ b

  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（医疗，医疗）; 
返回query.getResultList（）; 
 
 
  

此外，如果我想通过邮政编码搜索医疗类型和三种表格的语言，如何编写一个jsql。

警告：

Hospital.class

  @Entity 
 @Table（name =Hospital）
 public class Hospital {
 
 @Id 
 @GeneratedValue 
 private int hospital_id; 
 
 private String hospital_name; 
 
私人字符串邮政编码; 
 
 private String Suburb; 
 
私有字符串地址; 
 
私有字符串类型; 
 
私人字符串类别; 
 
私人字符串经度; 
 
私人字符串纬度; 
 
私人字符串电子邮件; 
 
私人字符串网站; 
 
 private String phoneno; 
 
私人字符串isemergency; 
 
私人字符串代理类型; 
 
私人字符串传真; 
 
 @ManyToMany 
 @JoinTable（
 name =Hospital_Medical，
 joinColumns = @ JoinColumn（name =Hospital_id，referencedColumnName =Hospital_id），
 inverseJoinColumns = @ JoinColumn（name =Medical_id，referencedColumnName =Medical_id））
 private List< MedicalService>服务; 
 
 @ManyToMany 
 @JoinTable（
 name =Hospital_Language，
 joinColumns = @ JoinColumn（name =Hospital_id，referencedColumnName =Hospital_id），
 inverseJoinColumns = @ JoinColumn（name =Language_id，referencedColumnName =Language_id））
 private List< Language>语言; 
 
 // Setter and Getter 
} 
  

MedicalService.class

  @Entity 
 @Table（name =Medical_Service）
公共类MedicalService {
 
 @Id 
 private int medical_id; 
 
 private String medical_name; 
 
私人字符串描述; 
 
 @ManyToMany（mappedBy =services）
 private List< Hospital>医院; 
 // 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）
私人列表< Hospital>医院; 
 // 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字段（用于如果你很乐意使用本地查询，你可以这样做：

 
 
 
 
  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，提供紧急服务。然后，外部选择选择Hospital_id位于内部SELECT中的所有医院（即他们提供紧急服务），但也仅选择邮政编码为3000的医院。

使用一个本地查询，你会做这样的事情：

  int postcode = 3000; 
 String service =紧急; 
 
 StringBuilder sb = new StringBuilder（）; 
 sb.append（SELECT * FROM Hospital WHERE Postcode =）; 
 sb.append（postcode）; 
 sb.append（AND Hospital_id IN SELECT 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）; 
列表<医院> result = query.getResultList（）; 
  

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:

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
}

解决方案

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

这篇关于JPA以多对多的关系查询多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！