Nearby lessons

15 of 19

Hibernate - Connection Pooling Hibernate - HQL Hibernate - Native SQL Hibernate - Criteria API Hibernate - Filters

Hibernate - Native SQL Queries

📌 What You Will Learn

Understand Hibernate - Native SQL Queries
See working code examples
Learn from common mistakes and Q&A

Learn Hibernate - Native SQL Queries step by step — simple explanations, complete programs with their output, common beginner mistakes, and exam-style MCQs.

Native SQL Queries

List<Object[]> list = query.list();
System.out.println("ENO\tENAME\tESAL\tEADDR");
System.out.println("---------------------------------");
for(Object[] val : list) {
for(Object o: val) {
System.out.print(o+"\t");
System.out.println();

e) IN:It will be used along with where clause to specify a list of values inorder to retrive matched results.

Example01

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename IN ('BBB', 'CCC')");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Native SQL Queries

f) BETWEEN : It able to specify min value and max value inorder to retrive the results which are between the specified min value and max value.

Example02

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename BETWEEN 'BBB' and 'DDD'");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Native SQL Queries

g) LIKE: It able to provide a particular pattern in HQL query inorder to retrive matched results.

Example03

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename LIKE 'B%'");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Native SQL Queries

h) IS NULL: It able to retrive all the results from database table w.r.t a particular column whose value is null.

Example04

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename IS NULL");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Native SQL Queries

i) IS NOT NULL: It able to retrive all the results from database table w.r.t a particular column whose value is not null.

Example05

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename IS NOT NULL");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Parameters

The main intention of parameters in HQL queries is to take dynamic values in HQL queries. In HQL, there are two types of parameters

Positional parameters
Named Parameters

Positional parameters

These parameters are represented in the form of '?' in HQL queries. After specifying these parameters in HQL queries we must set values to these parameters, to set values to positional parameters we have to use the following method.

public void setParameter(int param_Index, xxx value) Where param_Index may start with 0. Where xxx may be byte, short, int,....

Note: In JDBC, positional parameter indexes will start from 1 , but, in HQL parameters indexes will start from 0.

Example07

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<?");
3	query.setParameter(0, 10000.0f);
4	List<Object[]> list = query.list();
5	System.out.println("ENO\tENAME\tESAL\tEADDR");
6	System.out.println("---------------------------------");
7	for(Object[] val : list) {
8	for(Object o: val) {
9	System.out.print(o+"\t");
10	}
11	System.out.println();
12	}
13

Positional parameters

In HQL queries, we are able to provide more than one positional parameter.

Example08

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=? and e.esal<=?");
3	query.setParameter(0, 6000.0f);
4	query.setParameter(1, 8000.0f);
5	List<Object[]> list = query.list();
6	System.out.println("ENO\tENAME\tESAL\tEADDR");
7	System.out.println("---------------------------------");
8	for(Object[] val : list) {
9	for(Object o: val) {
10	System.out.print(o+"\t");
11	}
12	System.out.println();
13	}
14

Named Parameters

These parameters are represented in the form of ':Param_Name' in HQL queries, after providing named parameters in HQL query we have to set values to named parameters, for this, we have to use the following method.

public void setXXX(String param_name, xxx value) Where xxx may be byte, short, int, String,.....

Note: in HQL queries, we are able to provide more than one named parameters.

Example09

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=:min_Sal and e.esal<=:max_Sal");
3	query.setFloat("min_Sal", 6000.0f);
4	query.setFloat("max_Sal", 8000.0f);
5	List<Object[]> list = query.list();
6	System.out.println("ENO\tENAME\tESAL\tEADDR");
7	System.out.println("---------------------------------");
8	for(Object[] val : list) {
9	for(Object o: val) {
10	System.out.print(o+"\t");
11	}
12	System.out.println();
13	}
14

Named Parameters

In Hibernate applications we are able to provide both positional parameters and named parameters with in a single HQL query, but, first we have to provide all positional patameters after that only we have to provide named parameters, we must not provide any positional parameter after named parameter.

Example10

JCode Cell

1
2	Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=? and e.esal<=:max_Sal");
3	query.setParameter(0, 6000.0f);
4	query.setFloat("max_Sal", 8000.0f);
5	List<Object[]> list = query.list();
6	System.out.println("ENO\tENAME\tESAL\tEADDR");
7	System.out.println("---------------------------------");
8	for(Object[] val : list) {
9	for(Object o: val) {
10	System.out.print(o+"\t");
11	}
12	System.out.println();
13	}
14

Named Parameters

If we provide named parameter before positional parameter ion HQL query then we are able to get the following error or Exception.

ERROR: cannot define positional parameter after any named parameters have been defined

Subqueries

Writing a query in another query is called as Sub Query. HQL is supporting sub queries also.

Example12

JCode Cell

1
2	Query query = session.createQuery("select e1.eno, e1.ename, e1.esal, e1.eaddr FROM Employee AS e1 where e1.esal<(select max(e2.esal) from Employee e2)");
3	List<Object[]> list = query.list();
4	System.out.println("ENO\tENAME\tESAL\tEADDR");
5	System.out.println("---------------------------------");
6	for(Object[] val : list) {
7	for(Object o: val) {
8	System.out.print(o+"\t");
9	}
10	System.out.println();
11	}
12

Pagination — form.html

The process of displying results in more than one page is called as Pagination. Displaying 3 results in a page like three pages out of 9 results is called as "Pagination".

We are able to provide Pagination in Hibernate applications by using setFirstResult() and setMaxResult() methods over Query object.

Example:

Example13

JCode Cell

1
2	<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
3	<html>
4	<head>
5	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
6	<title>Insert title here</title>
7	</head>
8	<body>
9	<form method="POST" action="./display">
10	<center>
11	<br><br><br>
12	<input type="submit" value="1" name="button">
13	<input type="submit" value="2" name="button">
14	<input type="submit" value="3" name="button">
15	</center>
16	</form>
17	</body>
18	</html>
19

Pagination — DisplayServlet.java

Example14

JCode Cell

1
2	package com.durgasoft.servlets;
3
4	import java.io.IOException;
5	import java.io.PrintWriter;
6	import java.util.List;
7
8	import javax.servlet.RequestDispatcher;
9	import javax.servlet.ServletException;
10	import javax.servlet.http.HttpServlet;
11	import javax.servlet.http.HttpServletRequest;
12	import javax.servlet.http.HttpServletResponse;
13
14	import com.durgasoft.beans.Employee;
15	import com.durgasoft.service.EmployeeService;
16	public class DisplayServlet extends HttpServlet {
17	private static final long serialVersionUID = 1L;
18	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
19	try {
20	response.setContentType("text/html");
21	PrintWriter out = response.getWriter();
22	int label = Integer.parseInt(request.getParameter("button"));
23
24	EmployeeService empService = new EmployeeService();
25	List<Employee> list = empService.getEmployees(label);
26	out.println("<html>");
27	out.println("<body>");
28	out.println("<center>");
29	out.println("<table border='1'>");
30	out.println("<tr><th>ENO</th><th>ENAME</th><th>ESAL</th><th>EADDR</th></tr>");
31	for(Employee emp : list) {
32	out.println("<tr>");
33	out.println("<td>"+emp.getEno()+"</td><td>"+emp.getEname()+"</td><td>"+emp.getEsal()+"</td><td>"+emp.getEaddr()+"</td>");
34	out.println("</tr>");
35	}
36	out.println("</table></center></body></html>");
37	RequestDispatcher rd = request.getRequestDispatcher("/form.html");
38	rd.include(request, response);
39	} catch (Exception e) {
40	e.printStackTrace();
41
42	}
43	}
44
45	}
46

Pagination — EmployeeService.java

Example15

JCode Cell

1
2	package com.durgasoft.service;
3
4	import java.util.List;
5
6	import org.hibernate.Query;
7	import org.hibernate.Session;
8	import org.hibernate.SessionFactory;
9
10	import com.durgasoft.beans.Employee;
11	import com.durgasoft.util.HibernateUtil;
12
13	public class EmployeeService {
14	List<Employee> list;
15	SessionFactory sessionFactory;
16	Session session;
17	Query query;
18	public EmployeeService() {
19	try {
20	sessionFactory = HibernateUtil.getSessionFactory();
21	session = sessionFactory.openSession();
22	query = session.createQuery("from Employee");
23	query.setMaxResults(3);
24	} catch (Exception e) {
25	e.printStackTrace();
26	}
27	}
28	public List<Employee> getEmployees(int label){
29	try {
30	if(label == 1) {
31	query.setFirstResult(0);
32	}
33	if(label == 2) {
34	query.setFirstResult(3);
35	}
36	if(label == 3) {
37	query.setFirstResult(6);
38	}
39	list = query.list();
40	} catch (Exception e) {
41	e.printStackTrace();
42	}
43
44	return list;
45	}
46	}
47

Pagination — HibernateUtil.java

Example16

JCode Cell

1
2	package com.durgasoft.util;
3
4	import org.hibernate.SessionFactory;
5	import org.hibernate.boot.registry.StandardServiceRegistry;
6	import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
7	import org.hibernate.cfg.Configuration;
8
9	public class HibernateUtil {
10	private static SessionFactory sessionFactory;
11	static {
12	try {
13	Configuration config = new Configuration();
14	config.configure();
15	StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
16	builder = builder.applySettings(config.getProperties());
17	StandardServiceRegistry registry = builder.build();
18	sessionFactory = config.buildSessionFactory(registry);
19	} catch (Exception e) {
20	e.printStackTrace();
21	}
22	}
23	public static SessionFactory getSessionFactory() {
24	return sessionFactory;
25	}
26	}
27

Pagination — Employee.java

Example17

JCode Cell

1
2	package com.durgasoft.beans;
3
4	import javax.persistence.Column;
5	import javax.persistence.Entity;
6	import javax.persistence.Id;
7	import javax.persistence.Table;
8
9	@Entity
10	@Table(name="emp1")
11	public class Employee {
12	@Id
13	@Column(name="ENO")
14	private int eno;
15	@Column(name="ENAME")
16	private String ename;
17	@Column(name="ESAL")
18	private float esal;
19	@Column(name="EADDR")
20	private String eaddr;
21
22	public int getEno() {
23	return eno;
24	}
25	public void setEno(int eno) {
26	this.eno = eno;
27	}
28	public String getEname() {
29	return ename;
30	}
31	public void setEname(String ename) {
32	this.ename = ename;
33	}
34	public float getEsal() {
35	return esal;
36	}
37	public void setEsal(float esal) {
38	this.esal = esal;
39	}
40	public String getEaddr() {
41	return eaddr;
42	}
43	public void setEaddr(String eaddr) {
44	this.eaddr = eaddr;
45	}
46
47
48	}
49

Pagination — hibernate.cfg.xml

Example18

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-configuration PUBLIC
4	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
6	<hibernate-configuration>
7	<!--
8	<session-factory>
9	<property name="connection.driver_Class">oracle.jdbc.OracleDriver</property>
10	<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
11	<property name="connection.user">system</property>
12	<property name="connection.password">durga</property>
13	<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
14	<property name="show_Sql">true</property>
15	<mapping class="com.durgasoft.beans.Employee"/>
16	</session-factory>
17	-->
18	<session-factory>
19	<property name="connection.driver_Class">com.mysql.jdbc.Driver</property>
20	<property name="connection.url">jdbc:mysql://localhost:3306/durgadb</property>
21	<property name="connection.user">root</property>
22	<property name="connection.password">root</property>
23	<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
24	<property name="show_Sql">true</property>
25	<mapping class="com.durgasoft.beans.Employee"/>
26	</session-factory>
27	</hibernate-configuration>
28

Pagination — web.xml

Example19

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
4	<display-name>paginationapp</display-name>
5	<welcome-file-list>
6	<welcome-file>index.html</welcome-file>
7	<welcome-file>index.htm</welcome-file>
8	<welcome-file>index.jsp</welcome-file>
9	<welcome-file>default.html</welcome-file>
10	<welcome-file>default.htm</welcome-file>
11	<welcome-file>default.jsp</welcome-file>
12	</welcome-file-list>
13	<servlet>
14	<description></description>
15	<display-name>DisplayServlet</display-name>
16	<servlet-name>DisplayServlet</servlet-name>
17	<servlet-class>com.durgasoft.servlets.DisplayServlet</servlet-class>
18	</servlet>
19	<servlet-mapping>
20	<servlet-name>DisplayServlet</servlet-name>
21	<url-pattern>/display</url-pattern>
22	</servlet-mapping>
23	</web-app>
24

Native SQL

In Hibernate applications, by using Session we are able to perform database operations on only single record, but, if we want to perform database operations over multiple records then we have to use HQL, but, HQL is not providing environment for Database dependent native operations.

HQL is is able to provide support for DML[insert, update, delete, select] operations, but, it has not provided environment for DDL[create, alter and drop] queries.

HQL is not supporting stored procedures and functions kind of database dependent native operations.

In Hibernate applications, If we want to perform database operations over multiple records , database dependent native operations like preparing stored procedures, functions and accessing tham and to perform DDL operations,.... Hibernate has provided an alternative for HQL , that is, "Native SQL". If we want to use Native SQL in Hibernate applications then we have to use the following steps.

Create SqlQuery object
Execute the SQL query.

Create SqlQuery object

SqlQuery is an object provided by Hibernate in the form of org.hibernate.SqlQuery interface and it able to represent a native sql query.

To create SqlQuery object we have to use the following method.

public SqlQuery createSqlQuery(String query)throws HibernateException EX: SqlQuery query = session.createSqlQuery("select * from emp1");

Execute the SQL query — Employee.java

To execute Sql Query represent by SqlQuery object we have to use the following methods.

public List list() pubhlic Iterator iterator() public ScrollableResults scroll() public Object uniqueResult() public int executeUpdate()

In Native SQL, there are two types of SQL queries.

Entity SQL Queries
Scalar SQL Queries

1) Entity SQL Query

Entity SQLQueries are database dependent sql queries, it can be used to retrive the complete records in the form of an Entity. It will include '*' notation to get all columns data in a record in the form of Entity object.

EX: SqlQuery query = session.createSqlQuery("select * from emp1");

Before executing the query we have to provide entity type to SqlQuery object inorder to store results, for this, we have to use the following method.

public void addEntity(Class cl)

EX: query.addEntity(com.durgasoft.hibernate.Employee.class);

Example:

Example22

JCode Cell

1
2	package com.durgasoft.pojo;
3
4	import javax.persistence.Column;
5	import javax.persistence.Entity;
6	import javax.persistence.Id;
7	import javax.persistence.Table;
8
9	@Entity
10	@Table(name="emp1")
11	public class Employee {
12	@Id
13	@Column(name="ENO")
14	private int eno;
15	@Column(name="ENAME")
16	private String ename;
17	@Column(name="ESAL")
18	private float esal;
19	@Column(name="EADDR")
20	private String eaddr;
21
22	public int getEno() {
23	return eno;
24	}
25	public void setEno(int eno) {
26	this.eno = eno;
27	}
28	public String getEname() {
29	return ename;
30	}
31	public void setEname(String ename) {
32	this.ename = ename;
33	}
34	public float getEsal() {
35	return esal;
36	}
37	public void setEsal(float esal) {
38	this.esal = esal;
39	}
40	public String getEaddr() {
41	return eaddr;
42	}
43	public void setEaddr(String eaddr) {
44	this.eaddr = eaddr;
45	}
46
47
48	}
49

Execute the SQL query — hibernate.cfg.xml

Example23

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-configuration PUBLIC
4	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
6	<hibernate-configuration>
7	<session-factory>
8	<property name="connection.driver_Class">oracle.jdbc.OracleDriver</property>
9	<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
10	<property name="connection.user">system</property>
11	<property name="connection.password">durga</property>
12	<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
13	<property name="show_Sql">true</property>
14	<mapping class="com.durgasoft.pojo.Employee"/>
15	</session-factory>
16	</hibernate-configuration>
17

Execute the SQL query — Test.java

Example24

JCode Cell

1
2	package com.durgasoft.test;
3
4	import java.util.List;
5
6	import org.hibernate.SQLQuery;
7	import org.hibernate.Session;
8	import org.hibernate.SessionFactory;
9	import org.hibernate.boot.registry.StandardServiceRegistry;
10	import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
11	import org.hibernate.cfg.Configuration;
12
13	import com.durgasoft.pojo.Employee;
14
15	public class Test {
16
17	public static void main(String[] args) throws Exception{
18	Configuration cfg = new Configuration();
19	cfg.configure();
20	StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
21	builder = builder.applySettings(cfg.getProperties());
22	StandardServiceRegistry registry = builder.build();
23	SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
24	Session session = sessionFactory.openSession();
25	SQLQuery query = session.createSQLQuery("select * from emp1");
26	query.addEntity(com.durgasoft.pojo.Employee.class);
27	List<Employee> list = query.list();
28	System.out.println("ENO\tENAME\tESAL\tEADDR");
29	System.out.println("---------------------------------");
30	for(Employee e : list) {
31	System.out.print(e.getEno()+"\t");
32	System.out.print(e.getEname()+"\t");
33	System.out.print(e.getEsal()+"\t");
34	System.out.print(e.getEaddr()+"\n");
35	}
36
37	session.close();
38	sessionFactory.close();
39	}
40	}
41

Execute the SQL query

In Native SQL, there are two types of parameters.

Positional Parameters
Named Parameters

Positional parameters are represented in the form of '?' s , we are able to provide more than one positional parameter with in a single sql query. To set values to the positional parameters we have to use the following method from SqlQuery.

public void setXXX(int param_Index, XXX value) Where xxx may be byte, short, int,...

Names parameters are represented in the form of ':name' , we are able to provide more than one named parameter in native sql query. To provide values to the named parameters we have to use the following method.

public void setXXX(String param_Name, xxx value) Where xxx may be byte, short, int,....

In a single native sql query, we are able to provide both positional parameters and named parameters, but, fiorst we must provide all positional parameters after that only we have to provide named parameters, we must not provide any positional parameter after named parameter.

Example25

JCode Cell

1
2	SQLQuery query = session.createSQLQuery("select * from emp1 where esal>=? and esal<=:max_Sal");
3	query.setFloat(0, 6000);
4	query.setFloat("max_Sal", 8000);
5	query.addEntity(com.durgasoft.pojo.Employee.class);
6	List<Employee> list = query.list();
7	System.out.println("ENO\tENAME\tESAL\tEADDR");
8	System.out.println("---------------------------------");
9	for(Employee e : list) {
10	System.out.print(e.getEno()+"\t");
11	System.out.print(e.getEname()+"\t");
12	System.out.print(e.getEsal()+"\t");
13	System.out.print(e.getEaddr()+"\n");
14	}
15

Execute the SQL query — Employee.java

In the above approach, we have declare sql query directly in client application, it is available upto the present client application only, it is not available to other client applications, this approach is called as "Programatic Approach".

In Hibernate applications, if we want use the same sql query in more than one client application then programatic approach is nit suggetible, at each and every client application we have to hardcode the query, it is not suggestible, to overcome this problem we have to use "Declarative approach".

In Declarative approach, we will declare native sql query in mapping file along with a particular logical name and we will get that query from mapping file on the basis of the name, this type of sql queries are called as Named SQL Queries.

To declare sql query in mapping file we have to use the following tags in mapping file.

<hibernate-mapping>
<sql-query name="--">
<return class="--">
---sql query------
</sql-query>
</hibernate-mapping>

'name' attribute in <sql-query> tag will take logical name to the query. <return> tag will take a pojo class name with 'class' attribute inorder to get results in the form of POJO objects.

To get named sql query from mapping file to hibernate Client Application we have to use the following method.

public Query getNamedQuery(String logical_Name) In Declartive native sql query we are able to provide both positional parameters and named parameters depending on the requirement.

Note: In Native SQL queueris we are unable to use '<' symbol in mapping file, because, '<' symbol is treated as starting tag form xml tags, inplace of '<' symbol we have to use '<' symbol in mapping file.

EX:

Example26

JCode Cell

1
2	package com.durgasoft.pojo;
3
4	public class Employee {
5	private int eno;
6	private String ename;
7	private float esal;
8	private String eaddr;
9
10	public int getEno() {
11	return eno;
12	}
13	public void setEno(int eno) {
14	this.eno = eno;
15	}
16	public String getEname() {
17	return ename;
18	}
19	public void setEname(String ename) {
20	this.ename = ename;
21	}
22	public float getEsal() {
23	return esal;
24	}
25	public void setEsal(float esal) {
26	this.esal = esal;
27	}
28	public String getEaddr() {
29	return eaddr;
30	}
31	public void setEaddr(String eaddr) {
32	this.eaddr = eaddr;
33	}
34	}
35

Execute the SQL query — Employee.hbm.xml

Example27

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-mapping PUBLIC
4	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
6	<hibernate-mapping>
7	<class name="com.durgasoft.pojo.Employee" table="emp1">
8	<id name="eno"/>
9	<property name="ename"/>
10	<property name="esal"/>
11	<property name="eaddr"/>
12	</class>
13	<sql-query name="sql_Query">
14	<return class="com.durgasoft.pojo.Employee"/>
15	select * from emp1 where esal > ? and esal < :max_Sal
16	</sql-query>
17	</hibernate-mapping>
18

Execute the SQL query — hibernate.cfg.xml

Example28

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-configuration PUBLIC
4	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
6	<hibernate-configuration>
7	<session-factory>
8	<property name="connection.driver_Class">oracle.jdbc.OracleDriver</property>
9	<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
10	<property name="connection.user">system</property>
11	<property name="connection.password">durga</property>
12	<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
13	<property name="show_Sql">true</property>
14	<mapping resource="Employee.hbm.xml"/>
15	<!-- <mapping class="com.durgasoft.pojo.Employee"/> -->
16	</session-factory>
17	</hibernate-configuration>
18

Execute the SQL query — Test.java

Example29

JCode Cell

1
2	package com.durgasoft.test;
3
4	import java.util.List;
5
6	import org.hibernate.Query;
7	import org.hibernate.SQLQuery;
8	import org.hibernate.Session;
9	import org.hibernate.SessionFactory;
10	import org.hibernate.boot.registry.StandardServiceRegistry;
11	import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
12	import org.hibernate.cfg.Configuration;
13
14	import com.durgasoft.pojo.Employee;
15
16	public class Test {
17
18	public static void main(String[] args) throws Exception{
19	Configuration cfg = new Configuration();
20	cfg.configure();
21	StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
22	builder = builder.applySettings(cfg.getProperties());
23	StandardServiceRegistry registry = builder.build();
24	SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
25	Session session = sessionFactory.openSession();
26	Query query = session.getNamedQuery("sql_Query");
27	query.setFloat(0, 6000);
28	query.setFloat("max_Sal", 8000);
29	List<Employee> list = query.list();
30	System.out.println("ENO\tENAME\tESAL\tEADDR");
31	System.out.println("---------------------------------");
32	for(Employee e : list) {
33	System.out.print(e.getEno()+"\t");
34	System.out.print(e.getEname()+"\t");
35	System.out.print(e.getEsal()+"\t");
36	System.out.print(e.getEaddr()+"\n");
37	}
38
39	session.close();
40	sessionFactory.close();
41	}
42	}
43

Scalar SQL Queries — Employee.java

It is a native SQL query, it able to retrive records data from individual columns and it able to generate results in the form of Object[].

EX: select eno, ename, esal, eaddr from emp1;

To represent scalar sql queries we will use org.hibernate.SqlQuery, to get SqlQuery object we will use the following method.

public SqlQuery createSqlQuery(String query)throws HibernateException

Example:

Example30

JCode Cell

1
2	package com.durgasoft.pojo;
3	public class Employee {
4	private int eno;
5	private String ename;
6	private float esal;
7	private String eaddr;
8
9	public int getEno() {
10	return eno;
11	}
12	public void setEno(int eno) {
13	this.eno = eno;
14	}
15	public String getEname() {
16	return ename;
17	}
18	public void setEname(String ename) {
19	this.ename = ename;
20	}
21	public float getEsal() {
22	return esal;
23	}
24	public void setEsal(float esal) {
25	this.esal = esal;
26	}
27	public String getEaddr() {
28	return eaddr;
29	}
30	public void setEaddr(String eaddr) {
31	this.eaddr = eaddr;
32	}
33
34	}
35

Scalar SQL Queries — Employee.hbm.xml

Example31

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-mapping PUBLIC
4	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
6	<hibernate-mapping>
7	<class name="com.durgasoft.pojo.Employee" table="emp1">
8	<id name="eno"/>
9	<property name="ename"/>
10	<property name="esal"/>
11	<property name="eaddr"/>
12	</class>
13
14	</hibernate-mapping>
15

Scalar SQL Queries — hibernate.cfg.xml

Example32

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-configuration PUBLIC
4	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
6	<hibernate-configuration>
7	<session-factory>
8	<property name="connection.driver_Class">oracle.jdbc.OracleDriver</property>
9	<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
10	<property name="connection.user">system</property>
11	<property name="connection.password">durga</property>
12	<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
13	<property name="show_Sql">true</property>
14	<mapping resource="Employee.hbm.xml"/>
15	</session-factory>
16	</hibernate-configuration>
17

Scalar SQL Queries — Test.java

Example33

JCode Cell

1
2	package com.durgasoft.test;
3
4	import java.util.List;
5
6	import org.hibernate.Query;
7	import org.hibernate.SQLQuery;
8	import org.hibernate.Session;
9	import org.hibernate.SessionFactory;
10	import org.hibernate.boot.registry.StandardServiceRegistry;
11	import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
12	import org.hibernate.cfg.Configuration;
13
14	public class Test {
15
16	public static void main(String[] args) throws Exception{
17	Configuration cfg = new Configuration();
18	cfg.configure();
19	StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
20	builder = builder.applySettings(cfg.getProperties());
21	StandardServiceRegistry registry = builder.build();
22	SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
23	Session session = sessionFactory.openSession();
24	SQLQuery query = session.createSQLQuery("select eno, ename, esal, eaddr from emp1");
25	List<Object[]> list = query.list();
26	System.out.println("ENO\tENAME\tESAL\tEADDR");
27	System.out.println("---------------------------------");
28	for(Object[] obj: list) {
29	System.out.println(obj[0]+"\t"+obj[1]+"\t"+obj[2]+"\t"+obj[3]);
30	}
31
32	session.close();
33	sessionFactory.close();
34	}
35	}
36

Scalar SQL Queries

In Scalar SQL Queries we are able to provide both Positional parameters and Named parameters ,but, first we must provide positional parameters after that only we must provide named parameters. If we provide positional parameters and named parameters in sql query then we must provide values to these parameters , for this, we must use the following methods. public void setXXX(int index, xxx value) public void setXXX(String param_Namem , xxx value)

Example34

JCode Cell

1
2	SQLQuery query = session.createSQLQuery("select eno, ename, esal, eaddr from emp1 where esal >= ? and esal <= :max_Sal");
3	query.setFloat(0, 6000);
4	query.setFloat("max_Sal", 8000);
5	List<Object[]> list = query.list();
6	System.out.println("ENO\tENAME\tESAL\tEADDR");
7	System.out.println("---------------------------------");
8	for(Object[] obj: list) {
9	System.out.println(obj[0]+"\t"+obj[1]+"\t"+obj[2]+"\t"+obj[3]);
10	}
11

Scalar SQL Queries — Employee.java

In Hibernate applications, we are able to provide scalar sql queries in diclarative manner in mapping file. To declare scalar sql queries in mapping file we have to use the following syntax.

<hibernate-mapping>
<sql-query name="--">
<return-scalar column="--" type="--"/>
------scalar sql query ------
</sql-query>
</hibernate-mapping>

where <return-scalar> tag is able to represent a particular scalar[column name], for which, we have to declare data type by using 'type' attribute.

Note: In Named scalar sql query we are able to provide both positional parameters and named parameters, first we have to provide positional parameters after that we have to provide named parameters.

Example:

Example35

JCode Cell

1
2	package com.durgasoft.pojo;
3
4	public class Employee {
5	private int eno;
6	private String ename;
7	private float esal;
8	private String eaddr;
9
10	public int getEno() {
11	return eno;
12	}
13	public void setEno(int eno) {
14	this.eno = eno;
15	}
16	public String getEname() {
17	return ename;
18	}
19	public void setEname(String ename) {
20	this.ename = ename;
21	}
22	public float getEsal() {
23	return esal;
24	}
25	public void setEsal(float esal) {
26	this.esal = esal;
27	}
28	public String getEaddr() {
29	return eaddr;
30	}
31	public void setEaddr(String eaddr) {
32	this.eaddr = eaddr;
33	}
34
35
36	}
37

Scalar SQL Queries — Employee.hbm.xml

Example36

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-mapping PUBLIC
4	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
6	<hibernate-mapping>
7	<class name="com.durgasoft.pojo.Employee" table="emp1">
8	<id name="eno"/>
9	<property name="ename"/>
10	<property name="esal"/>
11	<property name="eaddr"/>
12	</class>
13	<sql-query name="scalarl_sql_query">
14	<return-scalar column="eno" type="int"/>
15	<return-scalar column="ename" type="string"/>
16	<return-scalar column="esal" type="float"/>
17	<return-scalar column="eaddr" type="string"/>
18	select eno, ename, esal, eaddr from emp1 where esal>= ? and esal <= :max_Sal
19	</sql-query>
20	</hibernate-mapping>
21

Scalar SQL Queries — hibernate.cfg.xml

Example37

JCode Cell

1
2	<?xml version="1.0" encoding="UTF-8"?>
3	<!DOCTYPE hibernate-configuration PUBLIC
4	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
5	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
6	<hibernate-configuration>
7	<session-factory>
8	<property name="connection.driver_Class">oracle.jdbc.OracleDriver</property>
9	<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
10	<property name="connection.user">system</property>
11	<property name="connection.password">durga</property>
12	<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
13	<property name="show_Sql">true</property>
14	<mapping resource="Employee.hbm.xml"/>
15	</session-factory>
16	</hibernate-configuration>
17

Scalar SQL Queries — Test.java

Example38

JCode Cell

1
2	package com.durgasoft.test;
3
4	import java.util.List;
5
6	import org.hibernate.Query;
7	import org.hibernate.SQLQuery;
8	import org.hibernate.Session;
9	import org.hibernate.SessionFactory;
10	import org.hibernate.boot.registry.StandardServiceRegistry;
11	import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
12	import org.hibernate.cfg.Configuration;
13

📝 Key Takeaways

Key ideas of Hibernate - Native SQL Queries explained simply
Ready-to-use code examples
Exam-style questions at the end

Hibernate

Nearby lessons

Hibernate - Native SQL Queries

01Native SQL Queries

02Native SQL Queries

03Native SQL Queries

04Native SQL Queries

05Native SQL Queries

06Parameters

07Positional parameters

08Positional parameters

09Named Parameters

10Named Parameters

11Named Parameters

12Subqueries

13Pagination — form.html

14Pagination — DisplayServlet.java

15Pagination — EmployeeService.java

16Pagination — HibernateUtil.java

17Pagination — Employee.java

18Pagination — hibernate.cfg.xml

19Pagination — web.xml

20Native SQL

21Create SqlQuery object

22Execute the SQL query — Employee.java

23Execute the SQL query — hibernate.cfg.xml

24Execute the SQL query — Test.java

25Execute the SQL query

26Execute the SQL query — Employee.java

27Execute the SQL query — Employee.hbm.xml

28Execute the SQL query — hibernate.cfg.xml

29Execute the SQL query — Test.java

30Scalar SQL Queries — Employee.java

31Scalar SQL Queries — Employee.hbm.xml

32Scalar SQL Queries — hibernate.cfg.xml

33Scalar SQL Queries — Test.java

34Scalar SQL Queries

35Scalar SQL Queries — Employee.java

36Scalar SQL Queries — Employee.hbm.xml

37Scalar SQL Queries — hibernate.cfg.xml

38Scalar SQL Queries — Test.java

Native SQL Queries

Native SQL Queries

Native SQL Queries

Native SQL Queries

Native SQL Queries

Parameters

Positional parameters

Positional parameters

Named Parameters

Named Parameters

Named Parameters

Subqueries

Pagination — form.html

Pagination — DisplayServlet.java

Pagination — EmployeeService.java

Pagination — HibernateUtil.java

Pagination — Employee.java

Pagination — hibernate.cfg.xml

Pagination — web.xml

Native SQL

Create SqlQuery object

Execute the SQL query — Employee.java

Execute the SQL query — hibernate.cfg.xml

Execute the SQL query — Test.java

Execute the SQL query

Execute the SQL query — Employee.java

Execute the SQL query — Employee.hbm.xml

Execute the SQL query — hibernate.cfg.xml

Execute the SQL query — Test.java

Scalar SQL Queries — Employee.java

Scalar SQL Queries — Employee.hbm.xml

Scalar SQL Queries — hibernate.cfg.xml

Scalar SQL Queries — Test.java

Scalar SQL Queries

Scalar SQL Queries — Employee.java

Scalar SQL Queries — Employee.hbm.xml

Scalar SQL Queries — hibernate.cfg.xml

Scalar SQL Queries — Test.java