Recent Posts

Saturday, 11 June 2016

Hibernate Query Language (HQL) Part-2




Aggregate Functions
     Functions that operate against groups of resulting records. The supported aggregate functions are
* avg(...), sum(...), min(...), max(...)
* count(*)
* count(...), count(distinct...), count(all...)
Example
SQL: SELECT MIN(BALANCE), MAX(BALANCE), AVG(BALANCE) FROM ACCOUNT;
HQL: SELECT MIN(a.balance), MAX(a.balance), AVG(a.balance) FROM Account a;
Code Block
String hqlQuery = "SELECT MIN(a.balance), MAX(a.balance), AVG(a.balance) FROM Account a";
Query query = session.createQuery(hqlQuery);
List<Object[]> aggrs = query.list();
for(Object[] aggrObject: aggrs) {
    System.out.println("Min :" +aggrObject[0]);
    System.out.println("Max :" +aggrObject[1]);
    System.out.println("Avg :" +aggrObject[2]);
}
Aggregate function with "GROUP BY" clause
Example 1: Group by single column
SQL: SELECT NAME, AVG(BALANCE) FROM ACCOUNT GROUP BY NAME;
HQL: SELECT a.name, AVG(a.balance) FROM Account a GROUP BY a.name;
Code Block
String hqlQuery = "SELECT a.name, AVG(a.balance) FROM Account a GROUP BY a.name";
Query query = session.createQuery(hqlQuery);
List<Object[]> list = query.list();
for(Object[] group: list) {
    System.out.println("Name :" +group[0]);
    System.out.println("Avg balance :" +group[1]);
}
     Here List object contains one Object[] array per each group. Type of type elements in the Object[] array depends on the aggregate function return type, selected property types.
Example 2: Group by multiple columns
SQL: SELECT NAME, BALANCE, MIN(BALANCE), MAX(BALANCE), AVG(BALANCE) FROM ACCOUNT GROUP BY BALANCE, NAME;
HQL: SELECT a.name, a.balance, MIN(a.balance), MAX(a.balance), AVG(a.balance) FROM Account a GROUP BY a.balance,a.name;
Code Block
String hqlQuery = "SELECT a.name, a.balance, MIN(a.balance), MAX(a.balance), AVG(a.balance) FROM Account a GROUP BY a.balance,a.name";
Query query = session.createQuery(hqlQuery);
List<Object[]> list = query.list();
for(Object[] group: list) {
    System.out.println("Name :" +group[0]);
    System.out.println("Balance :" +group[1]);
    System.out.println("Min balance :" +group[2]);
    System.out.println("Max balance :" +group[3]);
    System.out.println("Avg balance :" +group[4]);
}
Aggregate function with "GROUP BY" clause and "HAVING" clause
Example
SQL: SELECT NAME, AVG(BAL) FROM ACCOUNT GROUP BY NAME HAVING NAME LIKE '%ashok%';
HQL: SELECT a.name, AVG(a.bal) FROM Account GROUP BY a.namr HAVING a.name LIKE '%ashok%';
Code Block
String hqlQuery = "SELECT a.name, AVG(a.bal) FROM Account GROUP BY a.namr HAVING a.name LIKE '%ashok%'";
Query query = session.createQuery(hqlQuery);
List<Object[]> list = query.list();
for(Object[] group: list) {
    System.out.println("Name :" +group[0]);
    System.out.println("Avg Balance :" +group[1]);
}
ORDER BY clause with "ASC" and "DESC"
Example 1: single column on order by clause
SQL: SELCT * FROM ACCOUNT ORDER BY BALANCE
HQL: FROM Account a ORDER BY a.balance
Code Block
String hqlQuery = "FROM Account a ORDER BY a.balance";
Query query = session.createQuery(hqlQuery);
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}
Note: ORDER BY clause by default it will take Ascending order. If we want to specify the order explicitly, we can write the query as follows.
HQL: FROM Account a ORDER BY a.balance ASC (Ascending order)
HQL: FROM Account a ORDER BY a.balance DESC(Descending order)
Example 2: Multiple columns on order by clause
SQL: SELECT * FROM ACCOUNT ORDER BY BALANCE, NAME
HQL: select * FROM Account a ORDER BY a.balance, a.name
Code block
String hqlQuery = "FROM Account a ORDER BY a.balance, a.name";
Query query = session.createQuery(hqlQuery);
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}
Between Clause
SQL: SELECT * FROM ACCOUNT WHERE BALANCE BETWEEN 2000 and 8000;
HQL: FROM Account a WHERE a.balance between :lowerBound and :upperBound
          The negated forms can be written as follows
SQL: SELECT * FROM ACCOUNT WHERE BALANCE NOT BETWEEN 2000 and 8000;
HQL: FROM Account a WHERE a.balance not between :lowerBound and :upperBound
Code Block
String hqlQuery = "FROM Account a WHERE a.balance between :lowerBound and :upperBound";
Query query = session.createQuery(hqlQuery);
query.setParameter("lowerBound",2000.0);
query.setParameter("upperBound",8000.0);
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}
In Clause
SQL: SELECT * FROM ACCOUNT WHERE NAME IN ('Ashok','Vinod','Dillesh')
HQL: FROM Account a WHERE a.name in (:listOfValues)
          The negated forms can be written as follows
SQL: SELECT * FROM ACCOUNT WHERE NAME NOT IN ('Ashok','Vinod','Dillesh')
HQL: FROM Account a WHERE a.name not in (:listOfValues)
Code Block
String hqlQuery = "FROM Account a WHERE a.name in (:listOfValues)";
Query query = session.createQuery(hqlQuery);
List<String> listOfValues = new ArrayList<String>();
listOfValues.add("Ashok");
listOfValues.add("Vinod");
listOfValues.add("Dillesh");
query.setParameterList("listOfValues",listOfValues);
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}
OR
String hqlQuery = "FROM Account a WHERE a.name in (:listOfValues)";
Query query = session.createQuery(hqlQuery);
Object[] listOfValues = new Object[3];
listOfValues[0] = "Ashok";
listOfValues[1] = "Vinod";
listOfValues[2] = "Dillesh";
query.setParameterList("listOfValues",listOfValues);
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}
Like Clause
SQL: SELECT * FROM ACCOUNT WHERE NAME LIKE '%Ashok%'
HQL: FROM Account a WHERE a.name LIKE :name
          The negated forms can be written as follows
SQL: SELECT * FROM ACCOUNT WHERE NAME NOT LIKE '%Ashok%'
HQL: FROM Account a WHERE a.name NOT LIKE :name
Code Block
String hqlQuery = "FROM Account a WHERE a.name in (:listOfValues)";
Query query = session.createQuery(hqlQuery);
query.setParameter("name","%Ashok%");
List<Account> list = query.list();
for(Account account: list) {
    System.out.println("Name :" +account.getAccountId());
    System.out.println("Name :" +account.getName());
    System.out.println("Balance :" +account.getBalance());
}

Other details coming soon...

No comments:

Post a Comment