Recent Posts

Monday, 18 July 2016

Hibernate Query Language (HQL) Part-3

Using SQL/ Database Functions
SQL: SELECT UPPER(NAME), LOWER(NAME), SYSDATE FROM ACCOUNT
HQL: SELECT UPPER(a.name), LOWER(a.name), SYSDATE FROM ACCOUNT a
String hqlQuery = "SELECT UPPER(a.name), LOWER(a.name), SYSDATE FROM ACCOUNT a";
Query query = session.createQuery(hqlQuery);
List<Object[]> list = query.list();
for(Object[] cols: list) {
    System.out.println("Upper Name :" + cols[0]);
    System.out.println("Lower Name :" + cols[1]);
    System.out.println("Sysdate :" + cols[2]);
}  
uniqueResult()
     If we are expecting only one record as the result of query execution then we will call uniqueResult() method on Query object. If query retirns more than one record this method throws NonUniqueResultException.
Table : Account
ACCNO      NAME        BALANCE
5001       Ashok       7500
5002       Vinod       9500
5003       Ashok       8700
String hqlQuery = "FROM Account a WHERE a.name = :name";
Query query = session.createQuery(hqlQuery);
query.setParameter("name", "Ashok");
Account account = (Account) query.uniqueResult();
System.out.println("Account id :" + account.getAccountId());
System.out.println("Name :" + account.getName());
System.out.println("Balance :" +account.getBalance());
* If the above Query returns more than one record we get Exception: org.hibernate.NonUniqueResultException: query did not return a unique result: 2

* If the query returns only one record uniqueResults() method execute the query and returns the resulted object
String hqlQuery = "FROM Account a WHERE a.accountId = :accountId";
Query query = session.createQuery(hqlQuery);
query.setParameter("accountId", "5001");
Account account = (Account) query.uniqueResult();
System.out.println("Name :" + account.getName());
System.out.println("Balance :" +account.getBalance());
iterate()
     This method is also used to retrieve data from DB like list() method.
String hqlQuery = "FROM Account";
Query query = session.createQuery(hqlQuery);
Iterator<Account> accounts = query.iterate();
Account account = null;
while(accounts.hasNext()) {
   account = accounts.next();
   System.out.println("Account id :" + account.getAccountId());
   System.out.println("Name :" + account.getName());
   System.out.println("Balance :" +account.getBalance());
}
Note 
     Enable "show_sql" property in hibernate.cfg.xml file and observe the console by executing above code block. You can find one select query per one record. That's why it is not suggestible to use this method.
Differences between list() method and iterate() method
list()
1. Returns java.util.List object
2. Query executed only once
3. If we want to process the results in procesentation or service layer we use it.
4. For more number of records selection it is suggested.

iterate()
1. Returns java.util.Iterator object
2. Query executed per record.
3. If we want to process the results in Data access layer we use it.
4. For more number of records selection it is suggested.

Next Tutorial  Hibernate Pagination Tutorial

Previous Tutorial  Hibernate Query Language HQL Part2 
 

No comments:

Post a Comment