Join two tables using Hibernate in Spring Boot Example

Join two tables using Hibernate in Spring Boot Example:

Many enterprise applications whichever using hibernate uses sql queries only for joins still and uses hibernate only for save/update operations. Today we are going to see how to use hibernate to join two tables. We are going to see how to get count(*) using hibernate along with group by function in hibernate projections.

How to create hibernate session from EntityManager?

Use @PersistenceContext annotation to get entityManager object. EntityManager can be used to get the hibernate session using getDelegate() method.

 

[java]
@Service
public class StoreServiceImpl implements StoreService {

@PersistenceContext
private EntityManager entityManager;

@Override
public List getAllStoresWithCount() {
// getting hibernate session
org.hibernate.Session session = (Session) entityManager.getDelegate();
}

}
[/java]

 

Join two tables using JoinType:

 

[java]
org.hibernate.Session session = (Session) entityManager.getDelegate();
Criteria query = session.createCriteria(Store.class, “s”); // s is alias
query.createAlias(“couponList”, “c”, JoinType.LEFT_OUTER_JOIN);
[/java]

 

Here criteria is created with alias and join type as left outer join.

Store class:

[java]
@Entity
public class Store {
@OneToMany(mappedBy = “store”)
private List couponList;

// I did not generate any getters/setters.
}

[/java]

 

 

Coupon Class:

[java]
@Entity
public class Coupon {
@ManyToOne
private Store store;
public Store getStore() {
return store;
}

public void setStore(Store store) {
this.store = store;
}
}
[/java]

 

 

Getting the count(*) with group by:

 

[java]
query.setProjection(Projections.projectionList()
.add(Projections.groupProperty(“s.storeName”))
.add(Projections.rowCount()));
[/java]

 

Selecting the required columns from the joins:

Add the required columns to projects property, it will return the result also the same order how you add.
All the strings like storeName, storeDesc inside property method should match with your store entity fields. (it is not column name).

[java]
query.setProjection(Projections.projectionList().add(Projections.property(“s.storeId”))
.add(Projections.property(“s.storeName”)).add(Projections.property(“s.storeDesc”))
.add(Projections.property(“s.storeUrl”)).add(Projections.property(“s.storeAffUrl”))
.add(Projections.property(“s.storeIsFeatured”)).add(Projections.property(“s.storeIsActive”))
.add(Projections.property(“s.storeSlug”)).add(Projections.property(“s.storeLogo”))
.add(Projections.groupProperty(“s.storeName”))
.add(Projections.rowCount()));
[/java]

 

Executing and getting the result list:

[java]
List stores = query.list();
for (int i = 0; i < stores.size(); i++) {
Object[] storeObj = (Object[]) stores.get(i);
System.out.println(storeObj[0]);
}
[/java]

 

Please let me know if you have any questions in the below comments section.