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 Insurance where lngInsuranceId='1'

Where Clause can be used with or without Select Clause. Here the example code:


package roseindia.tutorial.hibernate;
 
 import org.hibernate.Session;
 import org.hibernate.*;
 import org.hibernate.cfg.*;
 
 import java.util.*;
 
 /**
  * @author Deepak Kumar
  *
  * http://www.roseindia.net
  * HQL Where Clause Example
  * Where Clause With Select Clause Example
  */
 public class WhereClauseExample {
   public static void main(String[] args) {
   Session session = null;
 
   try{
     // This step will read hibernate.cfg.

xml and prepare hibernate for use
     SessionFactory sessionFactory = new 

Configuration().configure().

buildSessionFactory();
     session =sessionFactory.openSession();
      
       System.out.println("***************

****************");
       System.out.println("Query using 

Hibernate Query Language");
     //Query using Hibernate Query Language
      String SQL_QUERY =" from Insurance

 as insurance where insurance.

lngInsuranceId='1'";
      Query query = session.createQuery

(SQL_QUERY);
      for(Iterator it=query.iterate()

;it.hasNext();){
        Insurance insurance=(Insurance)it

.next();
        System.out.println("ID: " + insurance.

getLngInsuranceId());
        System.out.println("Name: " 

+ insurance. getInsuranceName());
        
      }
      System.out.println("****************

***************");
      System.out.println("Where Clause With

 Select Clause");
     //Where Clause With Select Clause
      SQL_QUERY ="Select insurance.

lngInsuranceId,insurance.insuranceName," +
      "insurance.investementAmount,

insurance.investementDate from Insurance

 insurance "+ " where insurance.

lngInsuranceId='1'";
      query = session.createQuery(SQL_QUERY);
      for(Iterator it=query.iterate();it.

hasNext();){
        Object[] row = (Object[]) it.next();
        System.out.println("ID: " + row[0]);
        System.out.println("Name: " + row[1]);
        
      }
      System.out.println("***************

****************");
 
         session.close();
   }catch(Exception e){
     System.out.println(e.getMessage());
   }finally{
     }    
   }
 }

To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:

*******************************

Query using Hibernate Query Language

Hibernate: select insurance0_.ID as col_0_0_ from insurance insurance0_ where (insurance0_.ID='1')

ID: 1

Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?

Name: Car Insurance

*******************************

Where Clause With Select Clause

Hibernate: select insurance0_.ID as col_0_0_, insurance0_.insurance_name as col_1_0_, insurance0_.invested_amount as col_2_0_, insurance0_.investement_date as col_3_0_ from insurance insurance0_ where (insurance0_.ID='1')

ID: 1

Name: Car Insurance

*******************************

� View all related tutorials

Current Comments

10 comments so far (post your own) View All Comments Latest 10 Comments:

you change the alias name

Posted by kabkabaa on Thursday, 11.13.08 @ 07:19am | #81641

View This Comment Separately

you just change alias name USR in to Lowercase letters and try.

Posted by jack on Thursday, 11.13.08 @ 04:13am | #81637

View This Comment Separately

Have you tried using antlr-2.7.6.jar in the classpath...
This will help, as i was having same exception, Now it's working fine..

-Saket

Posted by Saket on Friday, 12.14.07 @ 17:31pm | #42261

View This Comment Separately

You can copy antlr-2.7.6.jar in your classpath.
This will help, as i was getting the same, but now..it's working fine..

Posted by Saket on Friday, 12.14.07 @ 17:28pm | #42260

View This Comment Separately

u have to diaplay the java

Posted by k.v.s.s.Prasad on Thursday, 07.12.07 @ 14:44pm | #21138

View This Comment Separately

I am experiencing a prob while tryign to execute a select query. It throws "Object not mapped" error in createQuery() statement. I have the mapping xml & appropriate java object...

Following is the code:

String qryString =
" FROM USERS AS USR " +
" WHERE USR.LOGIN_NAME = '" + userData.getLoginName() + "'" +
" AND USR.PASSWORD = '" + userData.getPassword() + "'";

Query hbnQry = session.createQuery(qryString);

Please help.

Posted by ganesh on Wednesday, 06.20.07 @ 17:29pm | #19789

View This Comment Separately

Exception in thread "main" java.lang.NoClassDefFoundError: antlr/ANTLRException
at org.hibernate.hql.ast.ASTQueryTranslatorFactory.createQueryTranslator(ASTQueryTranslatorFactory.java:35)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:72)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at hiber.SelectHQLExample.main(SelectHQLExample.java:29)
Java Result: 1
while running this example

Posted by soumita roy on Tuesday, 05.22.07 @ 16:08pm | #17007

View This Comment Separately

how doi install hybernate without a database.is it possible to work local by adding jars to the classpath

Posted by bhavesh thanki on Monday, 02.19.07 @ 21:02pm | #8400

View This Comment Separately

very awesome tutorial for beginners.but they have to be precise on what saying

Posted by NewEncodings on Tuesday, 02.13.07 @ 17:42pm | #7761

View This Comment Separately

Deepak there is no need for creating preparestatement isn't it?

Posted by janardhan on Tuesday, 12.12.06 @ 12:01pm | #880

View This Comment Separately