Java 加入两个表 HQL 查询

Join two tables HQL query

提问by DieZZzz

How can i join two tables using HQL?

如何使用 HQL 连接两个表？

At first, here is my SQL create query for two tables:

首先，这是我对两个表的 SQL 创建查询：

CREATE TABLE `subject` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)

CREATE TABLE `employee` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `subject_id` INT(11) UNSIGNED NOT NULL,
    `surname` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_employee_subject` (`subject_id`),
    CONSTRAINT `FK_employee_subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)

I'm using Netbeans and here is my generated entities.

我正在使用 Netbeans，这是我生成的实体。

Subject entity:

主体实体：

@Entity
@Table(name = "subject", catalog = "university")
public class Subject implements java.io.Serializable {

    private Integer id;
    private String name;
    private Set<Employee> employees = new HashSet<Employee>(0);
    private Set<Report> reports = new HashSet<Report>(0);

    public Subject() {
    }

    public Subject(String name) {
        this.name = name;
    }

    public Subject(String name, Set<Employee> employees, Set<Report> reports) {
        this.name = name;
        this.employees = employees;
        this.reports = reports;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "id", unique = true, nullable = false)
    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "name", nullable = false, length = 50)
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "subject")
    public Set<Employee> getEmployees() {
        return this.employees;
    }

    public void setEmployees(Set<Employee> employees) {
        this.employees = employees;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "subject")
    public Set<Report> getReports() {
        return this.reports;
    }

    public void setReports(Set<Report> reports) {
        this.reports = reports;
    }

}

Employee entity:

员工实体：

@Entity
@Table(name = "employee", catalog = "university")
public class Employee implements java.io.Serializable {

    private Integer id;
    private Subject subject;
    private String surname;
    private Set<Report> reports = new HashSet<Report>(0);

    public Employee() {
    }

    public Employee(Subject subject, String surname) {
        this.subject = subject;
        this.surname = surname;
    }

    public Employee(Subject subject, String surname, Set<Report> reports) {
        this.subject = subject;
        this.surname = surname;
        this.reports = reports;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "id", unique = true, nullable = false)
    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "subject_id", nullable = false)
    public Subject getSubject() {
        return this.subject;
    }

    public void setSubject(Subject subject) {
        this.subject = subject;
    }

    @Column(name = "surname", nullable = false, length = 50)
    public String getSurname() {
        return this.surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "employee")
    public Set<Report> getReports() {
        return this.reports;
    }

    public void setReports(Set<Report> reports) {
        this.reports = reports;
    }

}

I've tried to use query like this, but it doesn't work:

我尝试使用这样的查询，但它不起作用：

select employee.id, employee.surname, subject.name from Employee employee, Subject subject where employee.subject_id=subject.id

Here is my stacktrace, after using suggested query

这是我的堆栈跟踪，使用建议的查询后

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: by near line 1, column 102 [select employee.id, employee.surname, subject.name from by.bsuir.yegoretsky.model.Employee employee, by.bsuir.yegoretsky.model.Subject subject where employee.subject_id=subject.id]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1796)

And the screenshoot:

和屏幕截图：

采纳答案by JB Nizet

HQL uses entity names and entity property names. Never table or column names. There is no subject_idproperty in the entity Employee.

HQL 使用实体名称和实体属性名称。从不使用表名或列名。subject_id实体中没有属性Employee。

I suggest you read the documentation about HQL, and especially about joins and associations.

我建议您阅读有关 HQL 的文档，尤其是有关joins 和 associations的文档。

The query you need is

您需要的查询是

select employee.id, employee.surname, subject.name from Employee employee
join employee.subject subject

回答by srinivas gowda

Hope it may work

希望它可以工作

String hql = "from Subject as subject, Employee as emp";

List<?> list = session.createQuery(hql).list();

for(int i=0; i<list.size(); i++) {
    Object[] row = (Object[]) list.get(i);
    Subject subject= (Subject )row[0];
    Employee employee = (Employee)row[1];
}