I have following structure: Bank has name and list of Offices. Office has city property.
How can i select Banks with their own lists of Offices only with particular city using hql?
Code:
Bank
@Entity
@Table(name = "BANKS")
public class Bank {
public Bank() {
}
public Bank(String name) {
this.name = name;
}
@Id
@Column(name = "ID")
@GeneratedValue
private int id;
@Column(name = "name")
private String name;
/*@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)*/
@OneToMany(mappedBy = "bank", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
/*@JoinColumn(name = "BANK_ID")*/
private List<Office> officeList;
Office
@Entity
@Table(name = "OFFICES")
public class Office {
public Office() {
}
public Office(String city, String address, String workingHours, Bank bank) {
this.city = city;
this.address = address;
this.workingHours = workingHours;
this.bank = bank;
}
@Id
@Column(name = "ID")
@GeneratedValue
private int id;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "BANK_ID")
@JsonBackReference
private Bank bank;
public void setBank(Bank bank) {
this.bank = bank;
}
public Bank getBank() {
return bank;
}
@Column(name = "CITY")
private String city;
@Column(name = "ADDRESS")
private String address;
@Column(name = "WORKING_HOURS")
private String workingHours;
I’ve wrote request but it works wrong:
sessionFactory.getCurrentSession().createQuery("select b from Bank b join b.officeList o" +
" where o.city = ?").setString(0, city).list();
Any help will be much appreciated.
Answer
Regarding the first point, duplicates are expected because you join with a collection, and you can easily eliminate them with distinct:
select distinct b from Bank b join b.officeList o where o.city = :city
The second point (that officeList contains only the matching property) is conceptually much more trickier.
The point is that during initialization of the officeList collection for a Bank instance, Hibernate loads all the offices associated with the corresponding bank. That’s what Hibernate is supposed to do: to reflect the database state in the object graph. That has nothing to do which entities/tables you joined and which where conditions you specified in the original query you used to retrieve the bank.
However, there is the ability to initialize children associations in the same query in which you load parents by using the [left] join fetch construct. For example, to load all banks together with all their offices in one query you can do:
select distinct b from Bank b left join fetch b.officeList
Hibernate supports aliasing and using the fetch joined entities further in the where condition. This way you are basically initializing the collection only with the subset of the elements that are in the database. So, to achieve what you need you could change your query to:
select distinct b from Bank b join fetch b.officeList o where o.city = :city
However, keep in mind that using fetch joined associations in filtering conditions in queries is not supported by the JPA specification. Also, you should investigate what the impact on the second-level cache is if you use it and this collection is cached there.