Hibernate Query Language (HQL)

Hibernate query language is an object oriented query language.
It is same as SQL.

In SQL we use table name to perform operation but in HQL we use class name to perform operation.
So it is called database independent query language.

Advantage of HQL

  • Database independent.
  • Easy to learn.
  • Support pagination.
  • support named query which is simply-fie the maintenance of large application

Let’s understand Hibernate Query language with Example.

To understand it let’s create table and insert some data.

Userdata.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.spy.operation;
 
import javax.persistence.Cacheable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
 
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;
import org.hibernate.annotations.NamedQuery;
 
@Entity
public class Userdata 
{
    @Id @GeneratedValue(strategy=GenerationType.AUTO)
	int id;
	String user;
 
	public int getId() 
	{
		return id;
	}
	public void setId(int id) 
	{
		this.id = id;
	}
	public String getUser() 
	{
		return user;
	}
	public void setUser(String user) 
	{
		this.user = user;
	}
 
}

Hibernate.cfg.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?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">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/test</property>
<property name="connection.username">root</property>
<property name="connection.password">root</property>
 
<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">10</property>
 
<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">update</property>
 
 
 
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
 
<!-- Disable the second-level cache  -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
 
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>
 
<mapping class="com.spy.operation.Userdata"/>
 
</session-factory>
 
</hibernate-configuration>

Testmain.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.spy.operation;
 
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
public class Testmain 
{
	public static void main(String[] args) 
	{
 
 
		for(int i=1;i<=10;i++)
		{
 
		Userdata user=new Userdata();
		user.setUser("user "+i); 
 
		}
 
 
		SessionFactory sessionfactory=new Configuration().configure().buildSessionFactory();
		Session session=sessionfactory.openSession();
		session.beginTransaction();
 
             //Saving object in database
 
		for(int i=1;i<=10;i++)
		{
		Userdata user=new Userdata();
		user.setUser("user "+i); 
		session.save(user);
 
		}
 
		session.getTransaction().commit();
		session.close();
 
	}
}

Output:

1
2
3
4
5
6
7
8
9
10
11
Output:
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)
Hibernate: insert into Userdata (user) values (?)

Userdata table
user_data

Retrieving data using HQL.

Retrievedata.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
package com.spy.query;
 
import java.util.List;
 
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
import com.spy.operation.Userdata;
 
public class RetrieveData 
{
	public static void main(String[] args) 
 
 
	{
 
 
		Userdata user=new Userdata();
 
		SessionFactory sf=new Configuration().configure().buildSessionFactory();
 
		Session session=sf.openSession();
 
		session.beginTransaction();
 
		Query query=session.createQuery("from Userdata");
 
		List<Userdata> user1=query.list();
 
 
		 for (Userdata userdata : user1)
		  {	
			System.out.println(userdata.getUser());	
		  }
 
	 }
 
}

Output:

1
2
3
4
5
6
7
8
9
10
11
Hibernate: select userdata0_.id as id1_, userdata0_.user as user1_ from Userdata userdata0_
user 1
user 2
user 3
user 4
user 5
user 6
user 7
user 8
user 9
user 10

Using where clause to retrieve data.

Userdata.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
package com.spy.query;
 
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import com.spy.operation.Userdata;
public class RetrieveData 
{
	public static void main(String[] args) 
	{	
		SessionFactory sf=new Configuration().configure().buildSessionFactory();
		Session session=sf.openSession();
		session.beginTransaction();
 
		Query query=session.createQuery("from Userdata where id>7");
 
		List<Userdata> user1=query.list();
 
		for (Userdata userdata : user1)
		{
                	System.out.println(userdata.getUser());			
		}	
	}
}

Output:

1
2
3
4
Hibernate: select userdata0_.id as id1_, userdata0_.user as user1_ from Userdata userdata0_
user 8
user 9
user 10

Pagination in Hibernate:

If you want limit on retrieving data.
Suppose you have very large set of data.
And you only want some of data is retrieved then hibernate provide method handle it

setFirstResultset
it defines starting point from where data retrieving start

setMaxResults
it defines how many number of record Retrieve.

Pagination.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.spy.operation;
import java.awt.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class Pagination
{
 
public static void main(String[] args)
{
	SessionFactory sf=newConfiguration().configure().buildSessionFactory();
 
	Session session=sf.openSession();
	session.beginTransaction();
 
	Query query=session.createQuery("from Userdata");
 
	query.setFirstResult(5);
	query.setMaxResults(4);
 
	java.util.List<Userdata> users=query.list();
 
	session.getTransaction().commit();
 
	session.close();
 
	for (Userdata userdata : users)
	{
	   System.out.println(userdata.getUser());
	}
	}
}
Output:
Hibernate: select userdata0_.id as id1_, userdata0_.user as user1_ from
Userdata userdata0_ limit ?, ?
user 6
user 7
user 8
user 9

Note:

Here We fetch whole object means all data member
but if you want to fetch some specific member variable data
Then

1
Query query=session.createQuery("select user from Userdata");

Now here we fetch user column it is in String.
So while retrieving you must Define list type as String.

1
List<String> user=query.list();

Injecting Parameter in query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
package com.spy.operation;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class Injection
{
		public static void main(String[] args)
		{
			SessionFactory sf=new Configuration().configure().buildSessionFactory();
 
			Session session=sf.openSession();
			session.beginTransaction();
 
			//here we define userid value
			String userid="5";
 
			//injecting userid to query.
			Query query=session.createQuery("from Userdata where id > userid");
 
			/*
 
			another way to inject
 
			You can also decide that value at run time as like in Preparestatement in JDBC
			Query query=session.createQuery("from Userdata where id > userid");
 
			//setting value of ?
			query.setInteger(0,Integer.parseInt(userid));
 
			*/
 
 
			List<Userdata> users=query.list();
			session.getTransaction().commit();
 
			session.close();
 
 
			for (Userdata userdata : users)
			{
			System.out.println(userdata.getUser());
		    }
		}
}

One thought on “Hibernate Query Language (HQL)

Leave a Reply

Your email address will not be published. Required fields are marked *