In this tutorial, you will see the use of Restrictions class in java. It is used to restrict the retrieval of data from database.
Syntax:
Criteria criteria = session.createCriteria(Model.class); criteria.add(Restrictions.eq(propertyName, propertyValue));
Criteria restrictions query
The Restrictions class provide many methods to do the comparison operation.
Restrictions.eq
This is used to apply an “equal” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber = 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.eq("rollNumber", 3)); List list = criteria.list();
Restrictions.lt
This is used to apply a “less than” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber < 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.lt("rollNumber", 3)); List list = criteria.list();
Restrictions.gt
This is used to apply a “greater than” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber > 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.gt("rollNumber", 3)); List list = criteria.list();
Restrictions.le
This is used to apply a “less than or equal” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber <= 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.le("rollNumber", 3)); List list = criteria.list();
Restrictions.ge
This is used to apply a “greater than or equal” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber >= 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.ge("rollNumber", 3)); List list = criteria.list();
Restrictions.ne
This is used to apply a “not equal” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber <> 3;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.ne("rollNumber", 3)); List list = criteria.list();
Restrictions.in
This is used to apply an “in” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber IN (3,5,7);
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.in("rollNumber", new Integer[]{3,5,7})); List list = criteria.list();
Restrictions.or
This returns the disjunction of two expressions.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber > 3 OR course='MA';
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.or(Restrictions.gt("rollNumber", 3), Restrictions.eq("course", "MA")); List list = criteria.list();
Restrictions.not
This returns the negation of an expression.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber NOT IN (3,5,7);
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.not(Restrictions.in("rollNumber", new Integer[]{3,5,7}))); List list = criteria.list();
Restrictions.like
This is used to apply a “like” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE studentName LIKE %RAJPUT%;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.like("studentName", "%RAJPUT%")); List list = criteria.list();
Restrictions.isNull
This is used to apply an “is null” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE studentName IS NULL;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.isNull("studentName")); List list = criteria.list();
Restrictions.isNotNull
This is used to apply an “is not null” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE studentName IS NOT NULL;
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.isNotNull("studentName")); List list = criteria.list();
Restriction.between
This is used to apply a “between” constraint to the named property.
Simple SQL query >>
SELECT * FROM student WHERE rollNumber BETWEEN (3,7);
Above query in Criteria API >>
Criteria criteria = session.createCriteria(Student.class); criteria.add(Restrictions.between("rollNumber", 3, 7)); List list = criteria.list();
Restriction.allEq
This is used to apply an “equals” constraint to each property in the key set of a Map.
Simple SQL query >>
SELECT * FROM user WHERE userName = +userName AND userPassword = +userPassword;
Above query in Criteria API >>
Map map = new HashMap(); map.put("username", username); map.put("userPassword", userPassword); Criteria criteria = session.createCriteria(User.class); criteria.add(Restrictions.allEq(map)); List list = criteria.uniqueResult();
In the Next Chapter we will discuss more about the Cacheing in Hibernate.
<<Previous Chapter 30<< >>Next Chapter 32>>
Very good example. It had cleared my doubts regarding syntax of Criteria query construction.
I also request you to provide example of hibernate query QBE(Query by Example)
Thanks Farhan.
Please check hibernate tutorial page. It will be there. https://www.dineshonjava.com/hibernate-3-on-baby-steps/
Hi sir
I am writing criteria for Getting List of Weight of user.
like:Criteria criteria = sessionFactory.openSession().createCriteria(Weight.class);
SubscriptionFlowUser subscriptionFlowUser=new SubscriptionFlowUser();
subscriptionFlowUser.setUserID(5l);
criteria.add(Restrictions.eq(“users”, subscriptionFlowUser));
criteria.setMaxResults(n);
List list = criteria.list();
But it execute two query for this query:
First select the all mapped detail along with weight list:
select this_.WeightID as WeightID1_31_11_, this_.WeightUpdateTime as WeightUp2_31_11_, this_.UserID as UserID4_31_11_, this_.Weight as Weight3_31_11_, subscripti2_.UserID as UserID1_27_0_, subscripti2_1_.Country as Country2_27_0_, subscripti2_1_.CreateTime as CreateTi3_27_0_, subscripti2_1_.DOB as DOB4_27_0_, subscripti2_1_.EmailAddress as EmailAdd5_27_0_, subscripti2_1_.FirstName as FirstNam6_27_0_, subscripti2_1_.IsBlocked as IsBlocke7_27_0_, subscripti2_1_.IsDeleted as IsDelete8_27_0_, subscripti2_1_.IsSubscriptionFlowSaved as IsSubscr9_27_0_, subscripti2_1_.LastName as LastNam10_27_0_, subscripti2_1_.NotificationStatus as Notific11_27_0_, subscripti2_1_.Password as Passwor12_27_0_, subscripti2_1_.SocialID as SocialI13_27_0_, subscripti2_1_.ToolTipStatus as ToolTip14_27_0_, subscripti2_1_.ToolTipTime as ToolTip15_27_0_, subscripti2_1_.UpdateTime as UpdateT16_27_0_, subscripti2_1_.UserRole as UserRol17_27_0_, subscripti2_.ActivityLevel as Activit22_24_0_, subscripti2_.Age as Age1_24_0_, subscripti2_.Allergens as Allergen2_24_0_, subscripti2_.Diet as Diet23_24_0_, subscripti2_.Forearm as Forearm3_24_0_, subscripti2_.Gender as Gender24_24_0_, subscripti2_.Height as Height4_24_0_, subscripti2_.Hip as Hip5_24_0_, subscripti2_.IsActivityLevelKnown as IsActivi6_24_0_, subscripti2_.IsBodyFatPercentageKnown as IsBodyFa7_24_0_, subscripti2_.IsDeliverdToDoorQuestionShown as IsDelive8_24_0_, subscripti2_.IsNutritionFlowSaved as IsNutrit9_24_0_, subscripti2_.WantsSimpleMeasurement as WantsSi10_24_0_, subscripti2_.WorkoutReminder as Workout11_24_0_, subscripti2_.MealChangeFrequecy as MealCha25_24_0_, subscripti2_.MealDelivery as MealDel12_24_0_, subscripti2_.MealFrequency as MealFre13_24_0_, subscripti2_.ProgressPace as Progres26_24_0_, subscripti2_.SubscriptionPlan as Subscri27_24_0_, subscripti2_.UOM as UOM28_24_0_, subscripti2_.Waist as Waist14_24_0_, subscripti2_.WeightGoal as WeightG29_24_0_, subscripti2_.WorkoutDifficultyLevel as Workout15_24_0_, subscripti2_.WorkoutReminderTime as Workout16_24_0_, subscripti2_.Wrist as Wrist17_24_0_, subscripti2_.X as X18_24_0_, subscripti2_.Y as Y19_24_0_, subscripti2_.Z as Z20_24_0_, progresspi3_.UserID as UserID4_27_13_, progresspi3_.ProgressPicID as Progress1_18_13_, progresspi3_.ProgressPicID as Progress1_18_1_, progresspi3_.Picture as Picture2_18_1_, progresspi3_.UpdateTime as UpdateTi3_18_1_, progresspi3_.UserID as UserID4_18_1_, activityle4_.ActivityLevelID as Activity1_1_2_, activityle4_.ActivityLevel as Activity2_1_2_, bfpercenta5_.UserID as UserID4_27_14_, bfpercenta5_.BFPercentageID as BFPercen1_3_14_, bfpercenta5_.BFPercentageID as BFPercen1_3_3_, bfpercenta5_.BFPercentage as BFPercen2_3_3_, bfpercenta5_.BFPercentageCreationTime as BFPercen3_3_3_, bfpercenta5_.UserID as UserID4_3_3_, diet6_.DietID as DietID1_6_4_, diet6_.DietName as DietName2_6_4_, gender7_.GenderID as GenderID1_9_5_, gender7_.Gender as Gender2_9_5_, mealchange8_.MealChangeFrequencyID as MealChan1_11_6_, mealchange8_.MealChangeFrequency as MealChan2_11_6_, progresspa9_.ProgressPaceID as Progress1_17_7_, progresspa9_.ProgressPace as Progress2_17_7_, progresspa9_.ProgressPaceCreationTime as Progress3_17_7_, subscripti10_.SubscriptionPlanID as Subscrip1_25_8_, subscripti10_.StripePlan as StripePl2_25_8_, subscripti10_.SubscriptionPlan as Subscrip3_25_8_, subscripti10_.SubscriptionPlanAmount as Subscrip4_25_8_, uom11_.UOMID as UOMID1_26_9_, uom11_.UOM as UOM2_26_9_, weightgoal12_.WeightGoalID as WeightGo1_32_10_, weightgoal12_.WeightGoal as WeightGo2_32_10_ from Weight this_ left outer join SubscriptionFlowUser subscripti2_ on this_.UserID=subscripti2_.UserID left outer join Users subscripti2_1_ on subscripti2_.UserID=subscripti2_1_.UserID left outer join ProgressPic progresspi3_ on subscripti2_.UserID=progresspi3_.UserID left outer join ActivityLevel activityle4_ on subscripti2_.ActivityLevel=activityle4_.ActivityLevelID left outer join BFPercentage bfpercenta5_ on subscripti2_.UserID=bfpercenta5_.UserID left outer join Diet diet6_ on subscripti2_.Diet=diet6_.DietID left outer join Gender gender7_ on subscripti2_.Gender=gender7_.GenderID left outer join MealChangeFrequecy mealchange8_ on subscripti2_.MealChangeFrequecy=mealchange8_.MealChangeFrequencyID left outer join ProgressPace progresspa9_ on subscripti2_.ProgressPace=progresspa9_.ProgressPaceID left outer join SubscriptionPlan subscripti10_ on subscripti2_.SubscriptionPlan=subscripti10_.SubscriptionPlanID left outer join UOM uom11_ on subscripti2_.UOM=uom11_.UOMID left outer join WeightGoal weightgoal12_ on subscripti2_.WeightGoal=weightgoal12_.WeightGoalID where this_.UserID=5 order by progresspi3_.UpdateTime desc, bfpercenta5_.BFPercentageCreationTime desc limit 10
Second one for only weight list:
select weight0_.UserID as UserID4_27_0_, weight0_.WeightID as WeightID1_31_0_, weight0_.WeightID as WeightID1_31_1_, weight0_.WeightUpdateTime as WeightUp2_31_1_, weight0_.UserID as UserID4_31_1_, weight0_.Weight as Weight3_31_1_ from Weight weight0_ where weight0_.UserID=5 order by weight0_.WeightUpdateTime desc
please help me to resolve this issue
Two tables have join to each other and if you are using eager loading then it will load all related from other table as well.
Two tables have join to each other and if you are using eager loading then it will load all related from other table as well.
i used fetch type lazy but i use Fetch mode join