HQL: Positional parameter binding

HQL use ? to represent positional parameter values. You have to set your parameter according to the position sequence.

Example

String empId = "4";

String lastName = "Gurram";

Query query = session.createQuery("from Employee where id =? or lastName=?" );

query.setString(0, empId);

query.setString(1, lastName);

package myFirstHibernate;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;


@Entity
public class Employee {
  @Id @GeneratedValue
  private int id;
  private String firstName;
  private String lastName;
  
  public int getId() {
    return id;
  }
  
  public void setId(int id) {
    this.id = id;
  }
  
  public String getFirstName() {
    return firstName;
  }
  
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  
  public String getLastName() {
    return lastName;
  }
  
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  
}

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>
  
    <!--  Database Connection settings -->
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost/sample</property>
    <property name="connection.username">root</property>
    <property name="connection.password">tiger</property>
    
    <!-- Enable the logging of all the generated SQL statements to the console -->
    <property name="show_sql">true</property>
    
    <!-- Format the generated SQL statement to make it more readable, -->
    <property name="format_sql">false</property>
    
    <!-- Hibernate will put comments inside all generated SQL statements to hint what’s the generated SQL trying to do -->
    <property name="use_sql_comments">false</property>
    
    <!-- This property makes Hibernate generate the appropriate SQL for the chosen database. -->
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    
    <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">create</property>
    
    <!-- mappings for annotated classes -->
    <mapping class="myFirstHibernate.Employee" />
    
  </session-factory>
  
</hibernate-configuration>

package myFirstHibernate;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;


public class TestEmployee {
  
  /* Step 1: Create session factory */
  private static SessionFactory getSessionFactory() {
    Configuration configuration = new Configuration().configure();
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().
    applySettings(configuration.getProperties());
    SessionFactory factory = configuration.buildSessionFactory(builder.build());
        return factory;
    }
  
  public static void main(String args[])throws Exception{
    
    Employee emp1 = new Employee();
    Employee emp2 = new Employee();
    Employee emp3 = new Employee();
    Employee emp4 = new Employee();
    Employee emp5 = new Employee();
    Employee emp6 = new Employee();
    Employee emp7 = new Employee();
    Employee emp8 = new Employee();
    
    emp1.setFirstName("Hari Krishna");
    emp1.setLastName("Gurram");
    
    emp2.setFirstName("Shreyas");
    emp2.setLastName("Desai");
    
    emp3.setFirstName("Piyush");
    emp3.setLastName("Rai");
    
    emp4.setFirstName("Rama Krishna");
    emp4.setLastName("Gurram");
    
    emp5.setFirstName("Sailaja");
    emp5.setLastName("Navakotla");
    
    emp6.setFirstName("Keerthi");
    emp6.setLastName("Parush");
    
    emp7.setFirstName("Gopi");
    emp7.setLastName("Battu");
    
    emp8.setFirstName("Jaideep");
    emp8.setLastName("Gera");
    
    SessionFactory sessionFactory = getSessionFactory();
    Session session = sessionFactory.openSession();
    session.beginTransaction();
    
    /* Saving objects to database */
    session.save(emp1);
    session.save(emp2);
    session.save(emp3);
    session.save(emp4);
    session.save(emp5);
    session.save(emp6);
    session.save(emp7);
    session.save(emp8);
  
    String empId = "4";
    String lastName = "Gurram";
    Query query = session.createQuery("from Employee where id =? or lastName=?" );
    query.setString(0, empId);
    query.setString(1, lastName);
    List<Employee> list =  (List<Employee>)query.list();
    
    for(Employee emp : list)
      System.out.println(emp.getId() + " " + emp.getFirstName() + " " + emp.getLastName());
        
    session.getTransaction().commit();
    session.close(); 

    
  }
}

Run TestEmployee class, you will get output like below.

Hibernate: drop table if exists Employee
Hibernate: create table Employee (id integer not null auto_increment, firstName varchar(255), lastName varchar(255), primary key (id))
Dec 23, 2014 10:54:05 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Dec 23, 2014 10:54:06 AM org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
WARN: [DEPRECATION] Encountered positional parameter near line 1, column 42.  Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
Dec 23, 2014 10:54:06 AM org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
WARN: [DEPRECATION] Encountered positional parameter near line 1, column 56.  Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ where employee0_.id=? or employee0_.lastName=?
1 Hari Krishna Gurram
4 Rama Krishna Gurram

In previous post, I had explained SQL injection attack, let us try the same example using parameter binding. Update TestEmployee class like below and re run test.

package myFirstHibernate;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;


public class TestEmployee {
  
  /* Step 1: Create session factory */
  private static SessionFactory getSessionFactory() {
    Configuration configuration = new Configuration().configure();
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().
    applySettings(configuration.getProperties());
    SessionFactory factory = configuration.buildSessionFactory(builder.build());
        return factory;
    }
  
  public static void main(String args[])throws Exception{
    
    Employee emp1 = new Employee();
    Employee emp2 = new Employee();
    Employee emp3 = new Employee();
    Employee emp4 = new Employee();
    Employee emp5 = new Employee();
    Employee emp6 = new Employee();
    Employee emp7 = new Employee();
    Employee emp8 = new Employee();
    
    emp1.setFirstName("Hari Krishna");
    emp1.setLastName("Gurram");
    
    emp2.setFirstName("Shreyas");
    emp2.setLastName("Desai");
    
    emp3.setFirstName("Piyush");
    emp3.setLastName("Rai");
    
    emp4.setFirstName("Rama Krishna");
    emp4.setLastName("Gurram");
    
    emp5.setFirstName("Sailaja");
    emp5.setLastName("Navakotla");
    
    emp6.setFirstName("Keerthi");
    emp6.setLastName("Parush");
    
    emp7.setFirstName("Gopi");
    emp7.setLastName("Battu");
    
    emp8.setFirstName("Jaideep");
    emp8.setLastName("Gera");
    
    SessionFactory sessionFactory = getSessionFactory();
    Session session = sessionFactory.openSession();
    session.beginTransaction();
    
    /* Saving objects to database */
    session.save(emp1);
    session.save(emp2);
    session.save(emp3);
    session.save(emp4);
    session.save(emp5);
    session.save(emp6);
    session.save(emp7);
    session.save(emp8);
  
    String empId = "4 or 1=1";
    Query query = session.createQuery("from Employee where id =?");
    query.setString(0, empId);
    List<Employee> list =  (List<Employee>)query.list();
    
    for(Employee emp : list)
      System.out.println(emp.getId() + " " + emp.getFirstName() + " " + emp.getLastName());
        
    session.getTransaction().commit();
    session.close(); 

    
  }
}

You will get only employee details with id 4. You won’t get all the employee details like previous post.

Output looks like below.

Hibernate: drop table if exists Employee
Hibernate: create table Employee (id integer not null auto_increment, firstName varchar(255), lastName varchar(255), primary key (id))
Dec 23, 2014 10:57:09 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Dec 23, 2014 10:57:10 AM org.hibernate.hql.internal.ast.HqlSqlWalker generatePositionalParameter
WARN: [DEPRECATION] Encountered positional parameter near line 1, column 42.  Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ where employee0_.id=?
4 Rama Krishna Gurram

Prevoius                                                 Next                                                 Home