HQL Parameter binding

HQL Parameter binding

Hibernate query language flexible enough to fit all development needs to execute SQL query to get data from table. It supports almost all approach which developer can think of. Below are different approach which HQL supports:

• Supports native SQL
• Supports named parameters
• Supports positional parameters

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

Let’s see one by one with Java example:

• Native SQL: As it name suggests you could directly write SQL statement and execute against the database and only one thing in place to table and column name you will have to give class and its attribute name. Below is example:

int motherId = 1;
        List<Object[]> moList = session.createQuery("select "
                + "firstName,lastName from Mother where "
                + "motherId ="+motherId).list();

Complete java example:

package com.javahonk;

import java.util.List;

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

import com.javahonk.util.HibernateUtil;

public class NativeSQLQuery {

    static SessionFactory sf = HibernateUtil.getSessionFactory();

    public static void main(String[] args) {

        Session session = sf.openSession();
        session.beginTransaction();

        System.out.println("Native SQL query example\n");
        
        int motherId = 1;
        List<Object[]> moList = session.createQuery("select "
                + "firstName,lastName from Mother where "
                + "motherId ="+motherId).list();

        System.out.println("Mother table data\n");

        for (Object object : moList) {

            Object[] objects = (Object[]) object;
            System.out.println("First Name: " + objects[0] + ""
                    + "  Last Name: " + objects[1]);

        }

        session.getTransaction().commit();
        session.close();

    }
}

Above approach is not recommended because chances are SQL injection can be used to clutter entire coding.

• Named Parameters: There are many ways you could use and below are shown some examples:

1. Java Bean object can be directly bound to named query parameters:

    Mother mother = new Mother();
    mother.setFirstName("Mary");
    mother.setLastName("Kay");

    Query query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setProperties(mother);
    List<Mother> moList = query.list();

2. Java Map can be bound to named query parameters:

    System.out.println("Example using Map object \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");
    
    Map<String, String> map = new HashMap<String, String>();
    map.put("firstName", "Mary");
    map.put("lastName", "Kay");

    query.setProperties(map);
    moList = query.list();

3. Use setParameter as named query:

    System.out.println("Example using setParameter \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setParameter("firstName", "Mary");
    query.setParameter("lastName", "Kay");
    moList = query.list();

4. Use set object value as named query:

    System.out.println("Example using set proper object value \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setString("firstName", "Mary");
    query.setString("lastName", "Kay");
    moList = query.list();

Complete java example:

package com.javahonk;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
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 HQLNamedParameter {

    static SessionFactory sf = HibernateUtil.getSessionFactory();

    public static void main(String[] args) {

    Session session = sf.openSession(); 

    //**************************************************************
    System.out.println("Example using java bean object \n");
    Mother mother = new Mother();
    mother.setFirstName("Mary");
    mother.setLastName("Kay");

    Query query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setProperties(mother);
    List<Mother> 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");

        }

    }
    
    //**************************************************************
    System.out.println("Example using Map object \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");
    
    Map<String, String> map = new HashMap<String, String>();
    map.put("firstName", "Mary");
    map.put("lastName", "Kay");

    query.setProperties(map);
    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");

        }

    }
    
    //**************************************************************
    System.out.println("Example using setParameter \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setParameter("firstName", "Mary");
    query.setParameter("lastName", "Kay");
    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");

        }

    }
    
    //**************************************************************
    System.out.println("Example using set proper object value \n");
    query = session.createQuery("from Mother where "
            + "firstName = :firstName and lastName = :lastName");

    query.setString("firstName", "Mary");
    query.setString("lastName", "Kay");
    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");

        }

    }
    
    //**************************************************************
    

    session.close();

    }
}

Output:

• Positional parameters: Native SQL queries also supports positional parameters. Please see example below:

    System.out.println("Example using positional parameters \n");
    
    Query query = session.createQuery("from Mother where "
            + "firstName = ? and lastName = ?");

    query.setString(0,"Mary");
    query.setString(1,"Kay");
    List<Mother> moList = query.list();

Complete java example:

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 HQLPositionalParameters {

    static SessionFactory sf = HibernateUtil.getSessionFactory();

    public static void main(String[] args) {

    Session session = sf.openSession(); 

    System.out.println("Example using positional parameters \n");
    
    Query query = session.createQuery("from Mother where "
            + "firstName = ? and lastName = ?");

    query.setString(0,"Mary");
    query.setString(1,"Kay");
    List<Mother> 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");

        }

    }

    session.close();

    }
}

For more information about HQL please read this hibernate official tutorial