Hibernate Framework : ORM Tool :: Hibernate Query With Different API Example
- 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
| Hibernate Query API Intro |
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 |
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
Recent Comments