Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make this select * from two joined tables query in HQL form?

I had two hibernate entity here with annotation:

@Entity
@Table(name = "CLIENT")
public class Client {

    private Long pkClient;
    private String name;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="PK_CLIENT")
    public Long getPkClient() {
        return pkClient;
    }
    public void setPkClient(Long pkClient) {
        this.pkClient = pkClient;
    }

    @Column(name="NAME")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    ...
}

@Entity
@Table(name="ACCOUNT")
public class Account {

    private Long pkClientAccount;
    private Long fkClient;
    private String accountNo;

    @Id
    @Column(name="PK_CLIENT_ACCOUNT")
    @GeneratedValue(strategy=GenerationType.AUTO)
    public Long getPkClientAccount() {
        return pkClientAccount;
    }
    public void setPkClientAccount(Long pkClientAccount) {
        this.pkClientAccount = pkClientAccount;
    }

    @Column(name="FK_CLIENT")
    public Long getFkClient() {
        return fkClient;
    }
    public void setFkClient(Long fkClient) {
        this.fkClient = fkClient;
    }

    @Column(name="ACCOUNT_NO")
    public String getAccountNo() {
        return accountNo;
    }
    public void setAccountNo(String accountNo) {
        this.accountNo = accountNo;
    }

    ...
}

The relationship is one-to-many which a Client has many Account. Table ACCOUNT has foreign key (FK_CLIENT) to table CLIENT's primary key (PK_CLIENT).

I want to perform this query in HQL form:

select * from ACCOUNT a inner join CLIENT b on a.FK_CLIENT = b.PK_CLIENT

This mean, all properties from Account and Client entity will be selected.

Anyone know how to make that query in HQL form?

AFAIK, in HQL we can only select one entity.

Note:
I cannot use @ManyToOne mapping in Account entity because there is already fkClient property and I can't change this because the get/setFkClient has already been used in other places.

The code above has been simplified by removing unrelated parts to make easier to read. If you find a typo, please let me know in the comment section since I typed the code manually.

like image 790
null Avatar asked Mar 28 '13 08:03

null


People also ask

How do I join two tables in HQL?

We can apply the Joins in Hibernate by using the HQL query or native SQL query. To make a join between the two tables, the two tables must be in a logical relationship. We can achieve the relationship between two tables by applying the parent table's primary key as a child table's foreign key.

Can we use join in HQL query?

HQL Join : HQL supports inner join, left outer join, right outer join and full join. For example, select e.name, a. city from Employee e INNER JOIN e.

How do you write a subquery in HQL?

A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Note that HQL subqueries can occur only in the select or where clauses.

What is cross join in HQL?

More than one entity can also appear in HQL which will perform cartesian product that is also known as cross join.


2 Answers

Yes, you can select several entities with HQL. Hibernate will return an array of type Object[].

select 
    account, 
    client 
from Account account, Client client 
where account.fkClient = client.pkClient
like image 105
Étienne Miret Avatar answered Sep 19 '22 11:09

Étienne Miret


With Hibernate 5.1, it's now possible to join entities even if the mapping doesn't mirror the database table relationship.

So, this HQL query is valid from Hibernate 5.1:

select 
    account, 
    client 
from Account account 
join Client client on account.fkClient = client.pkClient
like image 24
Vlad Mihalcea Avatar answered Sep 21 '22 11:09

Vlad Mihalcea