Tuesday , November 21 2017
Home / Hibernate / Hibernate Native SQL Query Example

Hibernate Native SQL Query Example

In this tutorials, we are going to learn about Hibernate Native SQL with example. Hibernate has given a facility to execute sql commands directly on database with a technique called native sql.

To execute SQL commands from hibernate, Hibernate given us SQLQuery. SQLQuery is an interface which is coming from org.hibernate package. Which  allows the user to declare the types and select list injection points of all entities returned by the query.

We can obtain the SQLQuery instance by calling the createSQLQuery() on hibernate session like below :

Hibernate Native SQL for Complete Row :

String qry = “select * from student”;
SQLQuery sqlQuery = session.createSQLQuery(qry);

In the above example, we are selecting the complete row with sql command. In this case Hibernate converts each row into Object[] but not into Object of pojo class.

If we want to tell the Hibernate, that convert each row into object of pojo class, we need to call addEntity() method of SQLQuery object. That Query object is called EntityQuery.

String qry = “select * from student”;
SQLQuery sqlQuery = session.createSQLQuery(qry);

sqlQuery.addEntity(Student.class);

List list = sqlQuery.list();

In the above example, now the list contains Student class objects. So that we can directly cast into Student type :

Iterator iterator = list.iterator();
        while (iterator.hasNext()) {
            Student student = (Student) iterator.next();
            System.out.println(“sId : ” + student.getStudentId() + ” sName : “
                    + student.getStudentName() + ” age : ” + student.getAge());
        }

Hibernate Native SQL for Partial Row :

While reading partial row with native SQL query, to find the column types, hibernate internally uses ResultSetMetaData. If we want to tell to hibernate explicitly about the columns, which we want to select, then we can call addScalar() on SQLQuery object like below :

String qry = “select * from student”;
SQLQuery sqlQuery = session.createSQLQuery(qry);

sqlQuery.addScalar(“sid”, IntegerType.INSTANCE);
sqlQuery.addScalar(“sname”, StringType.INSTANCE);

List list = sqlQuery.list();

In the above example, we pass the 2 parameters to addScalar() method1 is for column name and 2nd is the type of column. In this case the list contains Object[], since it is partial entity. Why?

Like wise we can do non select operations like insert, update and delete with Hibernate Native SQL query. Here is the complete example :

Hibernate Native SQL Complete Example :

Create Student Pojo :

Student.java


package com.otp.hibernate.pojo;

public class Student {
    private int studentId;
    private String studentName;
    private String address;
    private int age;

    public int getStudentId() {
        return studentId;
    }

    public void setStudentId(int studentId) {
        this.studentId = studentId;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

Hibernate Mapping class :

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>
    <class name="com.otp.hibernate.pojo.Student" table="student"
        schema="onlinetutorialspoint">
        <id name="studentId" column="sid">
            <generator class="increment" />
        </id>
        <property name="studentName" column="sname" />
        <property name="address" column="address" />
        <property name="age" column="age" />
    </class>
</hibernate-mapping>

Run the application :

Main.java


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

import org.hibernate.Filter;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;

import com.otp.hibernate.pojo.Student;

public class Main {

    public static void main(String[] args) {
        Configuration configuration = new Configuration()
                .configure("hibernate.cfg.xml");
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties());
        SessionFactory factory = configuration.buildSessionFactory(builder
                .build());

        Session session = factory.openSession();
        String qry = "select * from student";
        SQLQuery sqlQuery = session.createSQLQuery(qry);

        List list = sqlQuery.list();

        Iterator iterator = list.iterator();

        while (iterator.hasNext()) {
            Object[] object = (Object[]) iterator.next();
            System.out.println("sId : " + object[0] + " sName : " + object[1]
                    + " age : " + object[2]);
        }

        // addEntity
        System.out.println("***********Entity*************");
        String qry2 = "select * from student";
        SQLQuery sqlQuery2 = session.createSQLQuery(qry2);
        sqlQuery2.addEntity(Student.class);

        List list2 = sqlQuery2.list();
        Iterator iterator2 = list2.iterator();

        while (iterator2.hasNext()) {
            Student student = (Student) iterator2.next();
            System.out.println("sId : " + student.getStudentId() + " sName : "
                    + student.getStudentName() + " age : " + student.getAge());
        }

        // addSclar
        System.out.println("***********SCALAR*************");

        String qry3 = "select * from student";
        SQLQuery sqlQuery3 = session.createSQLQuery(qry3);

        sqlQuery3.addScalar("sid", IntegerType.INSTANCE);
        sqlQuery3.addScalar("sname", StringType.INSTANCE);

        List list3 = sqlQuery3.list();
        Iterator iterator3 = list3.iterator();

        while (iterator3.hasNext()) {
            Object[] object = (Object[]) iterator3.next();
            System.out.println("sId : " + object[0] + " sName : " + object[1]);
        }

        // update
        System.out.println("**********Update**********");

        Transaction transaction = session.beginTransaction();
        String qry4 = "update student set age=:age where sid=:id";
        SQLQuery sqlQuery4 = session.createSQLQuery(qry4);
        sqlQuery4.setParameter("id", 2);
        sqlQuery4.setParameter("age", 30);
        sqlQuery4.executeUpdate();

        transaction.commit();
        System.out.println("Completed");
        session.clear();
        session.close();
    }
}

Output :

sId : 1 sName : vizag age : 20
sId : 2 sName : vizag age : 40
sId : 3 sName : hyderabad age : 30
***********Entity*************
sId : 1 sName : chandra age : 20
sId : 2 sName : shekhar age : 40
sId : 3 sName : rahul age : 30
***********SCALAR*************
sId : 1 sName : chandra
sId : 2 sName : shekhar
sId : 3 sName : rahul
**********Update**********
Completed

Though we have a facility to execute SQL commands in hibernate application, it is not recommended because it kills one of the main advantage of hibernate, That is our application becomes database dependent.

The complete example is available for download, have a look.

Happy Learning 🙂

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 *