mysql - 将 SQL Join 查询转换为 HQL Join 查询

Question

实际上，我使用 Entity 类获取结果(我不太了解查询连接)，但是下表不是从我的 Entity 创建的 类( session 安排除外)，现在我想加入表格，在获取结果时卡住了我的表如下:

mysql> desc meetingschedule;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| meeting_id   | int(11)          | NO   | PRI | NULL    | auto_increment |
| meeting_date | date             | YES  |     | NULL    |                |
| status       | tinyint(1)       | YES  |     | NULL    |                |
| client_id    | int(10) unsigned | YES  | MUL | NULL    |                |
| remark_id    | int(10) unsigned | YES  | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
13 rows in set (0.03 sec)                                                  

mysql> desc client;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| client_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| firm_name | varchar(90)      | NO   |     | NULL    |                |
| is_active | char(1)          | YES  |     | NULL    |                |
| parent_id | int(10) unsigned | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

mysql> desc client_city;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| client_id | int(10) unsigned | NO   | MUL | NULL    |       |
| city_id   | int(10) unsigned | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc client_domain;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| client_id | int(10) unsigned | NO   |     | NULL    |       |
| domain_id | int(10) unsigned | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如何将 SQL 连接转换为 HQL？
我的 SQL 查询是:

mysql> select a.* from meetingschedule a join client b on 
       a.client_id = b.client_id join client_city c on 
       b.client_id = c.client_id join client_domain d on 
       b.client_id = d.client_id where c.city_id=28 and d.domain_id=4;

更新:
实体类是
1. MeetingSchedule.java

@Entity
@Table(name="meetingschedule")
public class MeetingSchedule implements Serializable {

    private static final long serialVersionUID = 1L;

    public MeetingSchedule() {
    }

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="meeting_id")
    private Integer meetingId;

    @ManyToOne
    @JoinColumn(
        name="client_id", columnDefinition="INT(10) UNSIGNED",
        foreignKey=@ForeignKey(name="fk_meetingschedule_client")
    )
    private Client client;

    @Column(name="status", columnDefinition="TINYINT(1)")
    private short status;

    @Column(name="meeting_date")
    @Temporal(TemporalType.DATE)
//  @DateTimeFormat(pattern="yyyy-MM-dd")
    private Date meetingDate;

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(name="meeting_employee", 
        joinColumns={@JoinColumn(name="meeting_id")},
        inverseJoinColumns={@JoinColumn(name="emp_id",columnDefinition="INT(10) UNSIGNED")},
        foreignKey=@ForeignKey(name="fk_meeting_employee_meetingschedule_meeting_id"),
        inverseForeignKey=@ForeignKey(name="fk_meeting_employee_employee_emp_id")
    )
    private Set<Employee> employees = new HashSet<>();

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(name="meeting_part",
        joinColumns={@JoinColumn(name="meeting_id")},
        inverseJoinColumns={@JoinColumn(name="part_id",columnDefinition="INT(10) UNSIGNED")},
        foreignKey=@ForeignKey(name="fk_meeting_part_meetingschedule_meeting_id"),
        inverseForeignKey=@ForeignKey(name="fk_meeting_part_part_mst_part_id")
    )
    private Set<Part> parts = new HashSet<>();
}

<强>2。 Client.java

@Entity
@Table(name="client")
public class Client implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="client_id")
    private String client_id;

    @Column(name="is_active")
    private String is_active;

    @Column(name="parent_id")
    private String parent_id;

    @JsonBackReference
    @OneToOne(cascade=CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private ClientCity clientCity;

    @JsonBackReference
    @OneToOne(cascade=CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private ClientState clientState;
}

<强>3。客户城市

@Entity
@Table(name="client_city")
public class ClientCity implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator="fk_client_id")
    @GenericGenerator(strategy="foreign", name="fk_client_id", parameters=@Parameter(name="property", value="clientJoin"))
    @Column(name="client_id")
    private String client_id;

    @Column(name="city_id")
    private String city_id;

    @OneToOne(mappedBy="clientCity")
    private Client clientJoin;

}

<强>4。客户端域

@Entity
@Table(name="client_domain")
@NamedQuery(name="ClientDomain.findAll", query="SELECT c FROM ClientDomain c")
public class ClientDomain implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name="client_id")
    private int clientId;

    @Column(name="domain_id")
    private int domainId;

    public ClientDomain() {
    }
}

Answer 1

我不确定这是否有效，因为我看不到 Client 类和 ClientDomain 类之间的引用。而且我认为您不能在联接中使用 NamedQuery，因为这会导致语法错误。

通常您的 HQL 查询如下所示:

Query query = session.createQuery(
select a 
from MeetingSchedule a
left join a.client b
left join b.clientCity c
left join b.clientDomain d
where c.city_id = :city_id
and d.domain_id = :domain_id)
.setInteger("city_id", 28)
.setInteger("domain_id", 4)

要从数据库查询它，您需要一个结果对象，例如:

List<MyQueryResult> result = new List<MyQueryResult>();

您可以通过以下方式从数据库获取数据:

result = query.list();