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

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