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

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.

Add This Tutorial To:
Del.icio.us	Digg	Google	Spurl	Blink	Furl	Simpy	Y! MyWeb

Current Comments

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

Hello All,

Is there any way to limit the number of rows returned from a query when using findByNamedQueryAndNamedParam().

findByNamedQueryAndNamedParam(String queryName, String[] paramNames, Object[] values)
- Execute a named query for persistent instances, binding a number of values to ":" named parameters in the query string.

I have seen setMaxResults() for org.hibernate.Query class to set limit on number of rows returned. Any help on this is appreciated.

Thanks in advance.
Sebastian Abraham

Posted by Sebastian Abraham on Wednesday, 12.30.09 @ 16:48pm | #93698

View This Comment Separately

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