HQL Order By Example


Order by clause is used to retrieve the data from database in the sorted order by any property of returned class or components. HQL supports Order By Clause. In our example we will retrieve the data sorted on the insurance type. Here is the java example code:

 
package roseindia.tutorial.hibernate;
import org.hibernate.Session;
import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.*;
/**
 * @author Deepak Kumar
 * 
 * http://www.roseindia.net HQL Order by Clause Example
 *  
 */
public class HQLOrderByExample {
  public static void main(String[] args) {
  Session session = null;
  try {
  // This step will read hibernate.
cfg.xml and prepare hibernate for
  // use
  SessionFactory sessionFactory =
 new Configuration().configure()
  .buildSessionFactory();
  session = sessionFactory.openSession();
  //Order By Example
  String SQL_QUERY = " from Insurance as 
insurance order by insurance.insuranceName";
  Query query = 
session.createQuery(SQL_QUERY);
  for (Iterator it 
= query.iterate(); it.hasNext();) {
  Insurance insurance = (Insurance) it.next();
  System.out.println("ID: " + insurance.
getLngInsuranceId());
  System.out.println("Name: " + 
insurance.getInsuranceName());
  }
  session.close();
  } catch (Exception e) {
  System.out.println(e.getMessage());
  } finally {
  }
  }
}
To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:
Hibernate: select insurance0_.ID as col_0_0_ from insurance insurance0_ order by insurance0_.insurance_name
ID: 1
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Car Insurance
ID: 4
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Car Insurance
ID: 5
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Dental Insurance
ID: 11
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Home Insurance
ID: 12
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Home Insurance
ID: 2
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Life Insurance
ID: 3
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Life Insurance
ID: 6
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Life Insurance
ID: 9
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Medical Insurance
ID: 10
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Medical Insurance
ID: 13
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Motorcycle Insurance
ID: 14
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Motorcycle Insurance
ID: 7
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Travel Insurance
ID: 8
Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?
Name: Travel Insurance

Comments

Post a Comment

Popular posts from this blog

Servlets, JSP, Struts and MVC

Image
1.General Information about servlet: Servlet Request & Response Model: There are three different players in this picture: browser, web server, and servlet Container. In many cases, a web server and a servlet container are running in a same machine even in a single virtual machine. So they are not really distinguished in many cases. Anyway, the role of the web server is to receive HTTP request and then passes it to the web container or servlet container Which then creates Java objects that represent “HTTP request” and a “session” and then dispatches the request to the servlet by invoking service() method defined in the servlet.         The most common form of client requests are HTTP GET and HTTP POST requests. In the HTTP GET request, the user entered information is appended to the URL as a query string. In the HTTP POST request, on the other hand, the user entered information is sent as data. Servlet Classes and Interfaces: This picture shows important
Read more

Hibernate Avg() Function (Aggregate Functions)

In this section, we will show you, how to use the avg() function. Hibernate supports multiple aggregate functions. When they are used in HQL queries, they return an aggregate value ( such as   avg(...), sum(...), min(...), max(...) , count(*), count(...), count(distinct ...), count(all...)   ) calculated from property values of all objects satisfying other query criteria.  Following is a aggregate function (avg() function) with their respective syntax. avg(   [   distinct   |   all   ] object.property): The   avg()   function aggregates the average value of the given column.  Table Name: insurance ID insurance_name invested_amount investement_date 2 Life Insurance 25000 0000-00-00 00:00:00 1 Givan Dhara 20000  2007-07-30 17:29:05 3 Life Insurance   500 2005-10-15 00:00:00 4 Car Insurance    2500 2005-01
Read more

Hibernate Count Query

In this section we will show you, how to use the Count Query. Hibernate supports multiple aggregate functions. when they are used in HQL queries, they return an aggregate value (such as sum, average, and count) calculated from property values of all objects satisfying other query criteria. These functions can be used along with the  distinct  and all  options, to return aggregate values calculated from only  distinct  values and all values (except null values), respectively. Following is a list of aggregate functions with their respective syntax; all of them are self-explanatory. count ( [   distinct   |   all   ] object | object.property ) count(*)  (equivalent to   count(all ...) , counts null values also) sum (   [   distinct   |   all   ] object.property) avg(   [   distinct   |   all   ] object.property) max(   [   distinct   |   all   ] object.property) min(  [  distinct  |  all  ] object.property) Here is the java code for counting the records from insurance tab
Read more