HQL Where Clause Example

Where Clause is used to limit the results returned from database. It can be used with aliases and if the aliases are not present in the Query, the properties can be referred by name. For example:
from UserDetails user where user.userId = 3

Look in the example…

UserDetails.java

package com.sdnext.hibernate.tutorial.dto;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="USERS")
public class UserDetails 
{
 @Id
 @Column(name="USER_ID")
 @GeneratedValue(strategy=GenerationType.AUTO)
 private int userId;
 
 @Column(name="USER_NAME")
 private String userName;
 
 public int getUserId() {
  return userId;
 }
 public void setUserId(int userId) {
  this.userId = userId;
 }
 public String getUserName() {
  return userName;
 }
 public void setUserName(String userName) {
  this.userName = userName;
 }
}

hibernate.cfg.xml

<hibernate-configuration> 
 <session-factory> 
  <!-- Database connection settings -->
   <property name="connection.driver_class">com.mysql.jdbc.Driver</property> 
   <property name="connection.url">jdbc:mysql://localhost:3306/hibernateDB</property> 
   <property name="connection.username">root</property> 
   <property name="connection.password">root</property> 

  <!-- JDBC connection pool (use the built-in) -->
   <property name="connection.pool_size">1</property> 

  <!-- SQL dialect -->
   <property name="dialect">org.hibernate.dialect.MySQLDialect</property> 

  <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property> 
  
  <!-- Disable the second-level cache -->
   <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> 

  <!-- Echo all executed SQL to stdout -->
   <property name="show_sql">true</property> 
  
  <!-- Drop and re-create the database schema on startup -->
   <property name="hbm2ddl.auto">update</property> 
     
   <mapping class="com.sdnext.hibernate.tutorial.dto.UserDetails">
  </mapping></session-factory> 
 </hibernate-configuration>

HibernateTestDemo.java

package com.sdnext.hibernate.tutorial;

import java.util.Iterator;

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

import com.sdnext.hibernate.tutorial.dto.UserDetails;
public class HibernateTestDemo {

 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  
  String SQL_QUERY = "FROM UserDetails user WHERE user.userId = 2";
  Query query = session.createQuery(SQL_QUERY);
    
  for(Iterator it=query.iterate();it.hasNext();)
  {
   UserDetails user = (UserDetails)it.next();
   System.out.println("User Name: " + user.getUserName());
   System.out.println("User Id: " + user.getUserId());
  }
  session.getTransaction().commit();
  session.close();
 }
}