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

Question

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.

Gosney

If you cannot do a mapping to tell hibernate that those two are related then hibernate cannot help you. You would have to first select your Account objects then create a Collection of Client ids from them and do another select. In any case, this is not how hibernate works, read up on simple relationships. Your Account needs to have a reference to a Client object, not foreign key. It is then the job of hibernate to populate that object reference from the database. – Fidellia

duh, so no solution at all with hibernate? I can't add mapping without breaking other codes. – Gosney

Answer 1

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

Emerick

thanks, it works. I had created the code that call the namedQuery, but I don't know where to put the code? I have AccountDao and ClientDao bean (the application is built with spring+hibernate). Where do you think I should put the code? AccountDao or ClientDao or create another bean? – Gosney

I don't think you should create another bean. IMHO, it doesn't really matter wether you put it in AccountDoa or ClientDao. – Woodsy

Answer 2

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

Bidle

Answer 3

Try following HQL

select account from Account account, Client client where account.fkClient = client.pkClient

For more details please refer Hibernate reference documentation http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html#queryhql-where

Infelicity

Answer 4

You can use:

select * from  TB_1 as a 
left join  TB_2 as b 
on a.ID_TB_1 = b.ID_TB_2 and b.ID_TB_2 is null 

Negate

Please add some details . With that a visitor will find your answer more helpful. – Zootechnics