Friday , July 21 2017
Home / Hibernate / Hibernate Named Query with Example

Hibernate Named Query with Example

In this tutorial, we are going to learn about Hibernate Named Query with example. In Hibernate application, if we want to execute a same queries (either SQL or HQL) in different classes, then instead of writing those queries in multiple classes, we can write the queries in XML file and read those queries from XML where ever we want.

In case of named queries, we can configure the queries in hibernate mapping file(hbm.xml). The named queries can be supported in both SQL and HQL. Here is the examples of hibernate named query :

Hibernate Named Query for SQL :

<sql-query name=”sql_select“>select * from customer</sql-query>

To make the SQL command as named query, hibernate has provided <sql-query> tag. While working with <sql-query> tag, we have to give the name to it, so that we can access the query with that name from client class. We can also pass the parameters too, to named query.

<sql-query name=”sql_select_with_param“>select * from customer where cid=?</sql-query>

Accessing the SQL Named Query :

Accessing the normal sql named query :

Query query = session.getNamedQuery(“sql_select”);

Accessing the parametrized sql named query :

Query query = session.getNamedQuery(“sql_select_with_param”).setString(0, “1002”);

Hibernate Named Query for HQL :

<query name=”hql_select“>from Customer c</query>

To make the HQL command as named query, hibernate has provided <query> tag. We have to give the name to the query, so that we can access the query with that name from client class. We can also pass the parameters too, to named query.

<query name=”hql_select_with_param“>from Customer c where c.customerId=?</query>

<query name=”hql_select_with_named_param“>from Customer c where c.customerId=:cId</query>

Accessing the HQL Named Query :

Accessing the normal hql named query :

Query query = session.getNamedQuery(“hql_select”);

Accessing the parametrized hql named query :

Query query = session.getNamedQuery(“hql_select_with_param”).setString(0, “1002”);

Accessing the named parameter hql named query :

Query query = session.getNamedQuery(“hql_select_with_param”).setString(“cId”, “1003”);

Hibernate Named Query Example :

Create Customer pojo :

Customer.java


package com.otp.hibernate.pojo;

public class Customer {
    private int customerId;
    private String customerName;
    private String customerCity;

    public int getCustomerId() {
        return customerId;
    }

    public void setCustomerId(int customerId) {
        this.customerId = customerId;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public String getCustomerCity() {
        return customerCity;
    }

    public void setCustomerCity(String customerCity) {
        this.customerCity = customerCity;
    }

}

Creating Hibernate mapping file :

customer.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>
    <class name="com.otp.hibernate.pojo.Customer" table="customer">
        <id name="customerId" column="cid" />
        <property name="customerName" column="cname" />
        <property name="customerCity" column="ccity" />
    </class>
    <sql-query name="sql_select">select * from customer</sql-query>
    <sql-query name="sql_select_with_param">select * from customer where cid=?
    </sql-query>
    <query name="hql_select">from Customer c</query>
    <query name="hql_select_with_param">from Customer c where c.customerId=?</query>
    <query name="hql_select_with_named_param">from Customer c where c.customerId=:cId</query>
</hibernate-mapping>

On the above configuration file, we have configured both sql and hql named queries. Here the placement is also important, the named queries should be after the <class> tag. If we place it before <class> tag, we will get xml parsing errors.

Run the Application :

Main.java


import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.otp.hibernate.pojo.Customer;
import com.otp.hibernate.pojo.HibernateUtility;

public class Main {

    public static void main(String[] args) {
        SessionFactory factory = HibernateUtility.getSessionFactory();
        Session openSession = factory.openSession();
        System.out.println("*********SQL SELECT***********");
        Query query = openSession.getNamedQuery("sql_select");
        List list = query.list();
        Iterator iterator = list.iterator();
        while (iterator.hasNext()) {
            Object[] object = (Object[]) iterator.next();
            System.out.println("cId : " + object[0] + " cName : " + object[1]
                    + " cCity : " + object[2]);
        }

        System.out.println("*********sql_select_with_param***********");
        Query query5 = openSession.getNamedQuery("sql_select_with_param")
                .setString(0, "1002");
        List list5 = query5.list();
        Iterator iterator5 = list5.iterator();
        while (iterator5.hasNext()) {
            Object[] object = (Object[]) iterator5.next();
            System.out.println("cId : " + object[0] + " cName : " + object[1]
                    + " cCity : " + object[2]);
        }

        System.out.println("*********HQL SELECT***********");
        Query query2 = openSession.getNamedQuery("hql_select");
        List list2 = query2.list();
        Iterator iterator2 = list2.iterator();
        while (iterator2.hasNext()) {
            Customer customer = (Customer) iterator2.next();
            System.out.println("cId : " + customer.getCustomerId()
                    + " cName : " + customer.getCustomerName() + " cCity : "
                    + customer.getCustomerCity());
        }

        System.out.println("*********hql_select_with_param***********");
        Query query3 = openSession.getNamedQuery("hql_select_with_param")
                .setString(0, "1002");
        List list3 = query3.list();
        Iterator iterator3 = list3.iterator();
        while (iterator3.hasNext()) {
            Customer customer = (Customer) iterator3.next();
            System.out.println("cId : " + customer.getCustomerId()
                    + " cName : " + customer.getCustomerName() + " cCity : "
                    + customer.getCustomerCity());
        }

        System.out.println("*********hql_select_with_named_param***********");
        Query query4 = openSession.getNamedQuery("hql_select_with_named_param")
                .setString("cId", "1003");
        List list4 = query4.list();
        Iterator iterator4 = list4.iterator();
        while (iterator4.hasNext()) {
            Customer customer = (Customer) iterator4.next();
            System.out.println("cId : " + customer.getCustomerId()
                    + " cName : " + customer.getCustomerName() + " cCity : "
                    + customer.getCustomerCity());
        }
    }
}

*********SQL SELECT***********
Hibernate: select * from customer
cId : 1001 cName : chandra shekhar cCity : Hyderabad
cId : 1002 cName : Prasad cCity : Visakhapatnam
cId : 1003 cName : Kotesh cCity : Kakinada
*********sql_select_with_param***********
Hibernate: select * from customer where cid=?
cId : 1002 cName : Prasad cCity : Visakhapatnam
*********HQL SELECT***********
Hibernate: select customer0_.cid as cid1_0_, customer0_.cname as cname2_0_, customer0_.ccity as ccity3_0_ from customer customer0_
cId : 1001 cName : chandra shekhar cCity : Hyderabad
cId : 1002 cName : Prasad cCity : Visakhapatnam
cId : 1003 cName : Kotesh cCity : Kakinada
*********hql_select_with_param***********
Hibernate: select customer0_.cid as cid1_0_, customer0_.cname as cname2_0_, customer0_.ccity as ccity3_0_ from customer customer0_ where customer0_.cid=?
cId : 1002 cName : Prasad cCity : Visakhapatnam
*********hql_select_with_named_param***********
Hibernate: select customer0_.cid as cid1_0_, customer0_.cname as cname2_0_, customer0_.ccity as ccity3_0_ from customer customer0_ where customer0_.cid=?
cId : 1003 cName : Kotesh cCity : Kakinada

 

Download Example

About chandrashekhar

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Recommended

Hibernate Restrictions with Example

In this tutorials, we are going to learn about Hibernate Restrictions with Example. In the …

Leave a Reply

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