Advertisement

---

Top

< Prev

Next >

In our last article, you saw how to retrieve multiple columns of a database table(mapped from a class) using CriteriaQuery API. Today, we are going to show you how to retrieve all the records of a database table in a descending order/ascending order based on the values of specific property of a class, using CriteriaQuery API.

For those, who don't know the steps to use CriteriaQuery API, we are going to explain the important steps once again in this article.

Steps to use CriteriaQuery

Let's say that we want to query a class named UserInfo.

1. Creating the CriteriaBuilder from the Session object.



SessionFactory sf=  new Configuration().configure().buildSessionFactory();  
Session session = sf.openSession();
CriteriaBuilder cb = session.getCriteriaBuilder();





2. Creating the CriteriaQuery of an Object type using CriteriaBuilder object.



CriteriaQuery<Object> crtQ = cb.createQuery(Object.class);





3. Creating a Root collection of a type of objects. Root allows us to define attributes in the query, later on.



Root<UserInfo> root = crtQ.from(UserInfo.class);





4. Specifying the item to be returned in the query result(using Root) i.e. objects of a type referenced by Root.



crtQ.select(root);





5. Using Session object to create a Query object to execute the query.



Query query = session.createQuery(crtQ);





6. Executing the Query to get particular type of collection of records in a List.



List<UserInfo> collection2 = query.getResultList();


or,

Executing the Query to get a single value result then we would call a getSingleResult() method and store the result in an Object.



Object object = query.getSingleList();

Let's understand the CriteriaQuery through an example in the upcoming section.

Note :

• In the upcoming example, We are going to create a Java class which will be mapped to a table in our database(Oracle Express Edition 10g). We are going to use some Hibernate Annotations, hence, there will be no need to create the mapping-resource with extension hbm.xml . For those who are not aware of Hibernate Annotations, please read Hibernate Annotations for reference.


• And yes, one more thing, we are going to create this program in an Integrated Development Environment (IDE), so you may use any IDE of your choice.





Advertisement






Creating an Entity class



We are going to create a general parent class that will contain all the Java code files representing our Model class(whose objects needs to be persisted).

This is a simple Java class whose objects needs to be persisted/saved, these objects are also known as Plain Old Java Objects(POJO) or Entity class. Some may even refer to such class whose objects needs to be persisted as the Model class. This class is an entity class, hence it will be preceded with the @Entity annotation plus a few other annotations used below.


UserInfo.java

package decodejava;


import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.Column;


@Entity
@Table (name ="USER_INFO")
public class UserInfo {
@Id
@GeneratedValue
@Column (name = "ID")
private int id;
	
@Column (name = "First_Name")
private String firstName;
	
@Column (name = "Last_Name")
private String lastName;	
	


public int getId() 
{
	return id;
}
	


public void setId(int id) 
{
	this.id = id;
}
	

public String getFirstName() 
{
	return firstName;
}


public void setFirstName(String firstName)
{
	this.firstName = firstName;
}


public String getLastName() 
{
	return lastName;
}


public void setLastName(String lastName) 
{
		this.lastName = lastName;
}
}








Creating the class that calls the Hibernate API


This class will create some UserInfo objects, which will be persisted in a database table using the Hibernate and finally all the records of this database table are retrieved in an ascending and a descending order of a property named firstName, using the CriteriaQuery API.

The Hiber class creates a Configuration object, used to configure the Hibernate. This is the first object we use when using the Hibernate. This object is used to specify the location of a configuration file and mapping document used by Hibernate. Using Configuration object we can create a SessionFactory object, which is eventually used to create a Session object to perform the object persistence operations.


Hiber.java

package decodejava;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import org.hibernate.query.Query;
import javax.persistence.criteria.Root;


public class Hiber 
{

	public static void main(String[] args) 
	{

	SessionFactory sf=  new Configuration().configure().buildSessionFactory();  
	Session session = sf.openSession();
	
	
	//Creating the first object
	UserInfo ob1 = new UserInfo();
	ob1.setFirstName("D");
	ob1.setLastName("User");

	
	//Creating the second object
	UserInfo ob2 = new UserInfo();
	ob2.setFirstName("A");
	ob2.setLastName("User");

	
	//Creating the third object
	UserInfo ob3 = new UserInfo();
	ob3.setFirstName("Z");
	ob3.setLastName("User");

	

	session.beginTransaction();
	session.save(ob1); //Saving the first object
	session.save(ob2); //Saving the second object
	session.save(ob3); //Saving the third object
	session.getTransaction().commit();
	session.close();
	
	
	
	//Opening a new Session
	session = sf.openSession();
		
		

	//Creating the CriteriaBuilder from the session object
	CriteriaBuilder cb = session.getCriteriaBuilder();
	
	
	//Beginning the Transaction
	session.beginTransaction();
				
	
	
	
	//Creating the CriteriaQuery of an 'Object' type,  to represent result of each query
	CriteriaQuery<Object> crt1 = cb.createQuery(Object.class);
	Root<UserInfo> root1 = crt1.from(UserInfo.class);
	
	//Calling asc() method of CriteriaBuilder to give an ascending order to result of Query
	crt1.orderBy(cb.asc(root1.get("firstName"))); 
	Query query1 = session.createQuery(crt1);
			
				
	//Executing Query to get particular records in a List from UserInfo 
	List<UserInfo> collection1 = query1.getResultList();
			
		
	System.out.println("Retrieving and displaying all records sorted in an ascending order of a property 'firstName' ");
	
	for(UserInfo object : collection1)
	{
		System.out.println("ID : " + object.getId());
		System.out.println("First Name : " + object.getFirstName());
		System.out.println("First Name : " + object.getLastName());
		System.out.println();
	}
	
	
	
	
	
	//Creating the CriteriaQuery of an 'Object' type,  to represent result of each query
	CriteriaQuery<Object> crt2 = cb.createQuery(Object.class);
	Root<UserInfo> root2 = crt2.from(UserInfo.class);
	
	//Calling desc() method of CriteriaBuilder to give a descending order to result of Query
	crt2.orderBy(cb.desc(root2.get("firstName"))); 
	Query query2 = session.createQuery(crt2);
				
					
	//Executing Query to get particular records in a List from UserInfo 
	List<UserInfo> collection2 = query2.getResultList();
				
			
	System.out.println("Retrieving and displaying all records sorted in a descending order of a property named 'firstName' ");
		
	for(UserInfo object : collection2)
	{
		System.out.println("ID : " + object.getId());
		System.out.println("First Name : " + object.getFirstName());
		System.out.println("First Name : " + object.getLastName());
		System.out.println();
	}

		
	//Committing the Transaction	
	session.getTransaction().commit();
		
	//closing the session
	session.close();
	
	}
}








Adding JARs



• We are going to add some JARs files to the build path of our Java project. These JARs make the Hibernate work with our database using a specific JDBC Driver for our particular database.
  
  All these JARs are included in the folder named required(within our Hibernate installation folder). So, we need to add all the JARs in the required to our build path of our Java project.
  
  
• Finally, we are going to add one more JAR file. This is a specific JDBC JAR file(ojdbc14.jar) required by Hibernate to connect to our database(Oracle Express Edition 10g) and perform object-relational mapping and object persistence.


Note : Those who don't know how to add JARs to the build path of your Java project in Eclipse IDE, please refer to our section Adding JARs to your Hibernate project in Eclipse.







Adding a configuration file



• Next, we are going to add a configuration file to our project.
  
  This configuration document ends with an extension .cfg.xml, and it is named as hibernate.cfg.xml.
  
  
  This configuration file is an xml file and it allows us to specify the following features -
  • A database to which Hibernate connects to perform object-relational mapping and object persistence.
  • The JDBC driver class for our specific database, used by the Hibernate to connect to the database.
  • The username and password of database, using which the Hibernate connects to the database.
  • An SQL Dialect, used by the Hibernate to instruct the database.
  • A Entity/POJO class which will be mapped to a database table by Hibernate.
  • A command to echo all the SQL commands which are executed by the Hibernate on stdout.
  • The mapping entity class, used by Hibernate to correctly map the class(UserInfo) to a database table.
  
  
  
  hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">



<hibernate-configuration>

<session-factory>


<!-- Database connection settings -->
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@localhost:1521:XE</property>
<property name="connection.username">system</property>
<property name="connection.password">promila21</property>


<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>


<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>


<!-- Disable the second-level cache  -->
<property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>
        
        
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>


<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>


<!-- Names the mapping entity class -->
<mapping class ="decodejava.UserInfo"/>


</session-factory>

</hibernate-configuration>








Directory Structure of Hibernate Project






The picture above depicts how and where to arrange POJO/Entity(.java) file and the class(that calls the hibernate into action) in a specific directory structure.

Project Folder - CriteriaQueryRetrievingRecordsInOrder is the name of our Project and it is a top-level directory.


• This project folder contains the main package of our project i.e. decodejava, which contains POJO/Entity class file i.e. UserInfo.java and the class that calls the hibernate into action i.e. Hiber.java.



• Besides this, the project folder also contains the hibernate configuration file i.e. hibernate.cfg.xml, and as we have used some Hibernate Annotations in this project, hence, there will be no need to create the mapping-resource with extension hbm.xml .



• The project folder also contains all the needed Hibernate JARs present in the required folder of Hibernate installation and the JDBC JAR file(ojdbc14.jar) required by Hibernate to connect to our database.







Execution


When you execute the Hiber.java class, you will see the following output -



Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: create table USER_INFO (ID number(10,0) not null, First_Name varchar2(255 char), Last_Name varchar2(255 char), primary key (ID))
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: insert into USER_INFO (First_Name, Last_Name, ID) values (?, ?, ?)
Hibernate: insert into USER_INFO (First_Name, Last_Name, ID) values (?, ?, ?)
Hibernate: insert into USER_INFO (First_Name, Last_Name, ID) values (?, ?, ?)
Hibernate: select userinfo0_.ID as ID1_0_, userinfo0_.First_Name as First_Name2_0_, userinfo0_.Last_Name as Last_Name3_0_ from USER_INFO userinfo0_ order by userinfo0_.First_Name asc
Retrieving and displaying all records sorted in an ascending order of a property 'firstName' 
ID : 2
First Name : A
First Name : User

ID : 1
First Name : D
First Name : User

ID : 3
First Name : Z
First Name : User

Hibernate: select userinfo0_.ID as ID1_0_, userinfo0_.First_Name as First_Name2_0_, userinfo0_.Last_Name as Last_Name3_0_ from USER_INFO userinfo0_ order by userinfo0_.First_Name desc
Retrieving and displaying all records sorted in a descending order of a property named 'firstName' 
ID : 3
First Name : Z
First Name : User

ID : 1
First Name : D
First Name : User

ID : 2
First Name : A
First Name : User


This output shows you all the SQL commands executed by the Hibernate within the database to create the tables and save three objects and finally retrieving all the records of a database table User_Info in an ascending order and then in an descending order of a property named firstName of UserInfo class, using CriteriaQuery API.


Finally, after executing Hiber class, a tables will be created in the database with the name User_Info.


Next, let's query the database and see what are the contents of this table.



• select * from User_Info 








This table shows you the objects of class UserInfo class mapped by Hibernate to a table named User_Info in the database.






Please share this article -






< Prev
Next >
< Retrieving multiple columns
Retrieving max,min,count,avg >



Advertisement