Hibernate Framework : ORM Tool :: Hibernate Query With Different API Example

by Anuj Verma · Published 20/01/2015 · Updated 07/01/2018

Hibernate Query API Intro
Hibernate Query Thru Query API
Hibernate Query Thru Criteria API
Hibernate Query Thru SQLQuery API
Hibernate Query Thru NamedSQLQuery
Pagination Example
Download Examples

Named Queries for HQL and SQL

One of Hibernate’s best features is the named query, in which your application can store its HQL queries outside the application in the mapping file.

This has many benefits for application maintenance.

The first benefit is that many objects can share queries-you could set up static final strings on classes with the HQL queries, but Hibernate already provides a nice facility for the same thing.
The next benefit is that named queries could also contain native SQL queries-the application calling the named query does not need to know if the named query is SQL or HQL.
This has enormous benefits for migrating SQL-based applications to Hibernate.

Using Native SQL

Although you should probably use HQL whenever possible, Hibernate does provide a way to use native SQL statements directly through Hibernate.
One reason to use native SQL is that your database supports some special features through its dialect of SQL that are not supported in HQL.
Another reason is that you may want to call stored procedures from your Hibernate application.
Underlying Hibernate’s native SQL support is the org.hibernate.SQLQuery interface, which extends the org.hibernate.Query interface already discussed.
Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface.

public SQLQuery createSQLQuery(String queryString) throws HibernateException

Advanced Queries Using Criteria

Hibernate provides three different ways to retrieve data. We have already discussed HQL and the use of native SQL queries-now we add criteria.
The Criteria Query API lets you build nested, structured query expressions in Java, providing a compile-time syntax-checking that is not possible with a query language like HQL or SQL.
The Criteria API also includes query by example (QBE) functionality-this lets you supply example objects that contain the properties you would like to retrieve instead of having to spell the components of the query out step by step.
It also includes projection and aggregation methods, including counts.

Using the Criteria API

The Criteria API allows you to build up a criteria query object programmatically-the org.hibernate.Criteria interface defines the available methods for one of these objects.
The Hibernate Session interface contains several createCriteria() methods.
Pass the persistent object’s class or its entity name to the createCriteria() method, and Hibernate will create a Criteria object that returns instances of the persistence object’s class when your application executes a criteria query.
The simplest example of a criteria query is one with no optional parameters or restrictions- the criteria query will simply return every object that corresponds to the class.

Criteria crit = session.createCriteria(Product.class);
List results = crit.list();

Using Restrictions with Criteria

The Criteria API makes it easy to use restrictions in your queries to selectively retrieve objects; for instance, your application could retrieve only products with a price over $20.
You may add these restrictions to a Criteria object with the add()method.
The add()method takes an org.hibernate.criterion.Criterion object that represents an individual restriction.
You can have more than one restriction for a criteria query.

Hibernate Query Thru Query API

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="myeclipse.connection.profile">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/student.hbm.xml" />
</session-factory>
</hibernate-configuration>

Student.java

package com.javaskool;
public class Student {
	
	  private String id;
	  private String firstName;
	  private Integer age;
	  
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
}

student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.javaskool">
<class name="Student" table="studentTab">
<id name="id" column="student_id"/>
<property name="firstName"></property> 
<property name="age"></property>
</class>
</hibernate-mapping>

TestDrive.java

package com.javaskool;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class TestDrive {	
	public static void main(String[] args) {
		
		Configuration cfg=new Configuration().configure();
		SessionFactory sf=cfg.buildSessionFactory();
		Session sess=sf.openSession();
		Transaction tran=sess.beginTransaction();
		//using Query API
		Query q=sess.createQuery("from com.javaskool.Student where age>20");
						
		List<Student> lt=q.list();
		for(Student s:lt)
		{			
		System.out.println(s.getId()+"\t" +s.getFirstName()+"\t"+s.getAge());
		}		
		tran.commit();
	}
}

Hibernate Generated Query and Result

Hibernate: select student0_.student_id as student1_0_, student0_.firstName as firstName0_, student0_.age as age0_ from studentTab student0_ where student0_.age>20
S001	John	30
S002	James	40

Table Contains following Data

mysql> select * from  studentTab;
+------------+-----------+------+
| student_id | firstName | age  |
+------------+-----------+------+
| S001       | John      |   30 |
| S002       | James     |   40 |
| S003       | Tom       |   20 |
| S004       | Jerry     |   18 |
| S005       | Merry     |   19 |
+------------+-----------+------+
5 rows in set (0.01 sec)

Hibernate Query Thru Criteria API

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="myeclipse.connection.profile">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/student.hbm.xml" />
</session-factory>
</hibernate-configuration>

Student.java

package com.javaskool;
public class Student {
	
	  private String id;
	  private String firstName;
	  private Integer age;
	 
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
}

student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.javaskool">
<class name="Student" table="studentTab">
<id name="id" column="student_id"/>
<property name="firstName"></property>
<property name="age"></property>
</class>
</hibernate-mapping>

TestDrive.java

package com.javaskool;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;

public class TestDrive {
	public static void main(String[] args) {		
		
		Configuration cfg=new Configuration().configure();
		SessionFactory sf=cfg.buildSessionFactory();
		Session sess=sf.openSession();
		Transaction tran=sess.beginTransaction();
		//Using Criteria API
		Criteria crt=sess.createCriteria(Student.class);
		//crt.add(Restrictions.gt("age", 20));		
		//crt.add(Restrictions.lt("age",20));
		Integer i[]={20,30,40};  			
		crt.add(Restrictions.in("age", i));
		//crt.add(Restrictions.like("firstName", "J%"));	
		//Restrictions.isEmpty(propertyName);
		//Restrictions.isNull(propertyName);
		//Restrictions.isNotNull(propertyName);
		//Restrictions.eq(propertyName, value);
		List<Student> lt=crt.list();
		for(Student s:lt)
		{			
		System.out.println(s.getId()+"\t" +s.getFirstName()+"\t"+s.getAge());
		}		
		tran.commit();
	}
}

Hibernate Generated Query and Result

Hibernate: select this_.student_id as student1_0_0_, this_.firstName as firstName0_0_, this_.age as age0_0_ from studentTab this_ where this_.age in (?, ?, ?)
S001	John	30
S002	James	40
S003	Tom	20

Table Contains following Data

mysql> select * from  studentTab;
+------------+-----------+------+
| student_id | firstName | age  |
+------------+-----------+------+
| S001       | John      |   30 |
| S002       | James     |   40 |
| S003       | Tom       |   20 |
| S004       | Jerry     |   18 |
| S005       | Merry     |   19 |
+------------+-----------+------+
5 rows in set (0.01 sec)

Hibernate Query Thru SQLQuery API

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="myeclipse.connection.profile">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/student.hbm.xml" />
</session-factory>
</hibernate-configuration>

Student.java

package com.javaskool;
public class Student {
	
	private String id;
	private String firstName;
	private Integer age;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
}

student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.javaskool">
<class name="Student" table="studentTab">
<id name="id" column="student_id"/>
<property name="firstName"></property>
<property name="age"></property>
</class>
</hibernate-mapping>

TestDrive.java

package com.javaskool;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class TestDrive {
	public static void main(String[] args) {			
		Configuration cfg=new Configuration().configure();
		SessionFactory sf=cfg.buildSessionFactory();
		Session sess=sf.openSession();
		Transaction tran=sess.beginTransaction();
		//Using SQLQuery API
SQLQuery crt=sess.createSQLQuery("select * from studentTab where age between 15 and 25");
		crt.addEntity(com.javaskool.Student.class);
			
		List<Student> lt=crt.list();
		for(Student s:lt)
		{			
			System.out.println(s.getId()+"\t" +s.getFirstName()+"\t"+s.getAge());
		}		
		tran.commit();
	}
}

Hibernate Generated Query and Result

Hibernate: select * from studentTab where age between 15 and 25
S003	Tom	20
S004	Jerry	18
S005	Merry	19

Table Contains following Data

mysql> select * from  studentTab;
+------------+-----------+------+
| student_id | firstName | age  |
+------------+-----------+------+
| S001       | John      |   30 |
| S002       | James     |   40 |
| S003       | Tom       |   20 |
| S004       | Jerry     |   18 |
| S005       | Merry     |   19 |
+------------+-----------+------+
5 rows in set (0.01 sec)

Hibernate Query Thru NamedSQLQuery

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="myeclipse.connection.profile">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/student.hbm.xml" />
</session-factory>
</hibernate-configuration>

Student.java

package com.javaskool;
public class Student {
	
	  private String id;
	  private String firstName;
	  private Integer age;
	 
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
}

student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.javaskool">
<class name="Student" table="students">
<id name="id" column="student_id"/>
<property name="firstName"></property>
<property name="age"></property>
</class>
<sql-query name="myqueryx">
<return-scalar column="student_id" type="java.lang.String"/>
<return-scalar column="firstName" type="java.lang.String"/>
<return-scalar column="age" type="java.lang.Integer"/>
select student_id,firstName,age from students
</sql-query>

</hibernate-mapping>

TestDrive.java

package com.javaskool;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class TestDrive {
	public static void main(String[] args) {
		
		Configuration cfg=new Configuration().configure();
		SessionFactory sf=cfg.buildSessionFactory();
		Session sess=sf.openSession();
		Transaction tran=sess.beginTransaction();
		//Using SQL Query defined in hbm file i.e 'myqueryx'
		Query q=sess.getNamedQuery("myqueryx");
			
		List<Object[]> lt=q.list();
		for(Object[] s:lt)
		{			
			System.out.println(s[0]+"\t" +s[1]+"\t"+s[2]);
		}		
		tran.commit();
	}
}

Hibernate use same Query mentioned in hbm file and Output as below

Hibernate: select student_id,firstName,age from studentTab
S001	John	30
S002	James	40
S003	Tom	20
S004	Jerry	18
S005	Merry	19

Pagination

If you need to specify bounds upon your result set, that is, the maximum number of rows you want to retrieve and/or the first row you want to retrieve, you can use methods of the Query interface:

Query q = sess.createQuery("from Student  s");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

Hibernate knows how to translate this limit query into the native SQL of your DBMS.

Download Examples

Click the below Link to download complete Example

India's Largest Home & Kitchen Store

Hibernate Framework : ORM Tool :: Hibernate Query With Different API Example

You may also like...

Leave a Reply Cancel reply

Recent Posts

Recent Comments

Hibernate Framework : ORM Tool :: Hibernate Query With Different API Example

Named Queries for HQL and SQL

Using Native SQL

Advanced Queries Using Criteria

Using the Criteria API

Using Restrictions with Criteria

hibernate.cfg.xml

Student.java

student.hbm.xml

TestDrive.java

Hibernate Generated Query and Result

Table Contains following Data

hibernate.cfg.xml

Student.java

student.hbm.xml

TestDrive.java

Hibernate Generated Query and Result

Table Contains following Data

hibernate.cfg.xml

Student.java

student.hbm.xml

TestDrive.java

Hibernate Generated Query and Result

Table Contains following Data

hibernate.cfg.xml

Student.java

student.hbm.xml

TestDrive.java

Hibernate use same Query mentioned in hbm file and Output as below

Pagination

Click the below Link to download complete Example

You may also like...

What are the Extension interfaces that are there in hibernate?

What is a meant by light object mapping?

What are the different types of property and class mappings?

Leave a Reply Cancel reply

Recent Posts

Recent Comments