Recent Posts

Sunday, 4 September 2016

HQL Subqueries Tutorial


Subqueries
     For databases that support sub selects, hibernate supports subqueries within queries. 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.

SQL: SELECT * FROM ACCOUNT WHERE BALANCE > (SELECT AVG(BALANCE) FROM ACCOUNT)

HQL: FROM Account a WHERE a.balance > (SELECT AVG(b.balance) FROM Account b)
Code Block
String hql = "FROM Account a WHERE a.balance> (SELECT AVG(b.balance) FROM Account b)";
Query query = session.createQuery(hql);
List<Account> list= query.list();
for(Account account : list) {
   System.out.println("Account id :" + account.getAccountId());
   System.out.println("Name :" + account.getName());
   System.out.println("Balance :" +account.getBalance());
}
SQL: SELECT ACNO, NAME, (SELECT AVG(BALANCE) FROM ACOOUNT), BALANCE FROM ACCOUNT

HQL: SELECT a.accountId, a.name, (SELECT AVG(b.balance) FROM Account b) AS avgBalance, a.balance FROM Account a
Code Block
String hql = "SELECT a.accountId, a.name, (SELECT AVG(b.balance) FROM Account b) AS avgBalance, a.balance FROM Account a";
Query query = session.createQuery(hql);
List<Object[]> list= query.list();
for(Object[] cols : list) {
   System.out.println("Account id :" + cols[0]);
   System.out.println("Name :" + cols[1]);
   System.out.println("Balance :" +cols[2]);
}
Polymorphic queries
     A query like
from Payment as pa
     returns instances not only of Payment, but also of sub classes like ChequePayment and CashPayment. Hibernate queries can name any Java class or interface in the from clause. The query will return instances of all persistent classes that extend that class or implement the interface. The following query would return all persistent objects.

from java.lang.Object o

returns Object[]
HQL: SELECT a.name, a.balance FROM Account a
Code Block
String hql = "SELECT a.name, a.balance FROM Account a";
Query query = session.createQuery(hql);
List<Object[]> accounts = query.list();
for(Object[] cols : accounts) {
   for(Object[] column : cols) {
      System.out.println(column+"\t");
   }
}
     With this query.list() method returns java.util.List object and that list contains Object[] arrays.
returns java.util.List
HQL:
SELECT new list(a.name, a.balance) FROM Account a
Code Block
String hql = "SELECT new list(a.name, a.balance) FROM Account a";
Query query = session.createQuery(hql);
List<List> lists = query.list();
for(List list : lists) {
   System.out.println("Name : " +list.get(0));
   System.out.println("Balance : " +list.get(1));
}
     With this query.list() method returns java.util.List object and that list contains again java.util.List objects one per each record. And size of each list object depends on number of selected properties.
returns User defined object
HQL: SELECT new Account(a.accno, a.name, a..balance) FROM Account a
Code Block
String hql = "SELECT new Account(a.accno, a.name, a..balance) FROM Account a";
Query query = session.createQuery(hql);
List<Account> lists = query.list();
for(Account account : lists) {
   System.out.println("Account Id : " +account.getAccountId());
   System.out.println("Account Id : " +account.getName());
   System.out.println("Account Id : " +account.getBalance());
}
     With this query.list() method returns java.util.List object and that list contains Account objects. Number of Account objects. Number Account objects depends on number of records.
returns Map object
HQL: SELECT new map(MAX(a.balance) AS maxBalance, MIN(a.balance) AS minBalance, COUNT(*) AS count) FROM Account a
Code Block
String hql = "SELECT new map(MAX(a.balance) AS maxBalance, MIN(a.balance) AS minBalance, COUNT(*) AS count) FROM Account a";
Query query = session.createQuery(hql);
List list = query.list();
Map<String, Object> map = (Map<String, Object>) list.get(0);
System.out.println("Count : " +map.get("count"));
System.out.println("Max Balance : " +map.get("maxBalance"));
System.out.println("Min Balance : " +map.get("minBalance"));

Next Tutorial  Criteria Tutorial

Previous Tutorial  Hibernate Pagination Tutorial 

No comments:

Post a Comment