Hibernate Native SQL Queries

Hibernate Native SQL Queries

Hibernate also provide functionality to execute directly native SQL query to fetch data from database. Below is examples:

Note: This demo is extension of previous tutorial. If you are interested to see full working example with all set up please refer this tutorial Hibernate One To Many Annotation Example

• Pure native SQL statement where hibernate maps all return value to java bean and return you specified object:

Query query = session.createSQLQuery("select * from Mother "
                + "where first_Name = :first_Name")
                .addEntity(Mother.class);
        query.setString("first_Name", "Mary");
        List<Mother> moList = query.list();

• OR you could also use this:

query = session.createSQLQuery("select * from Mother "
                + "where first_Name = 'Mary'")
                .addEntity(Mother.class);
        moList = query.list();

• Native SQL statement where hibernate maps return value as an object array:

query = session.createSQLQuery("select first_Name, last_Name "
                + "from Mother where first_Name ='Mary'");
        List<Object[]> list = query.list();

• OR you could also use this:

query = session.createSQLQuery("select first_Name, last_Name "
                + "from Mother where first_Name = :first_Name");
        query.setString("first_Name", "Mary");
        list = query.list();

• Complete java class:

package com.javahonk;

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

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

import com.javahonk.bean.Child;
import com.javahonk.bean.Mother;
import com.javahonk.util.HibernateUtil;

public class NativeSQLQuery {
    
    static SessionFactory sf = HibernateUtil.getSessionFactory();

    public static void main(String[] args) {
        
        Session session = sf.openSession(); 
        
        //********************************************************
        
        Query query = session.createSQLQuery("select * from Mother "
                + "where first_Name = :first_Name")
                .addEntity(Mother.class);
        query.setString("first_Name", "Mary");
        List<Mother> moList = query.list();
        
        //OR you could also use this
        query = session.createSQLQuery("select * from Mother "
                + "where first_Name = 'Mary'")
                .addEntity(Mother.class);
        moList = query.list();
        
        for (Mother mother2 : moList) {
            System.out.println("Mother table data:");
            System.out.println("First Name: "+mother2.getFirstName()
                    +" Last Name: "+mother2.getLastName()+" City: "
                    +mother2.getCity()+" State: "+mother2.getState()
                    +" Zip: "+mother2.getZip()+"\n");
            System.out.println("Child table data:");
            Set<Child> childs=mother2.getChilds();
            for (Iterator<Child> iterator = childs.iterator(); 
                    iterator.hasNext();) {
                Child child3 = (Child) iterator.next();
                System.out.println("First Name: "
                +child3.getFirstName()+" Last name: "
                        +child3.getLastName()+"\n");
    
            }
    
        }
        
        
        //********************************************************
        
        query = session.createSQLQuery("select first_Name, last_Name "
                + "from Mother where first_Name ='Mary'");
        List<Object[]> list = query.list();
        
        //OR you could also use this
        query = session.createSQLQuery("select first_Name, last_Name "
                + "from Mother where first_Name = :first_Name");
        query.setString("first_Name", "Mary");
        list = query.list();
        
        for (Object object : list) {
            Object[] li = (Object[])object;
            System.out.println("First Name: "+li[0]+" Last Name: "
                                    +li[1]);
        }
        
        session.close();
        
    }

}

Output:

For more information about HQL please read this hibernate official tutorial