I would like to show you, how to retrieve object from db using queries with parameter(s).
This above snippet we have the same result using question mark. Rembember that postion of parameters are 0 indexed.
Query query = session.createQuery("from User u where u.id =?").setString(0, "4");
This is depend on database vendor, but you shouldn't using query in this way.
You can try to build queries with 2 and more parameters it's very simple.
When simply query with parameter is not enough....
Now, let's imagine that you would like to select all of your users with the first letter of their name is M and their surname begins with S or B.
Believe me there is a Query but it won't be look nice, there is a rescue .... Criteria !
Believe me there is a Query but it won't be look nice, there is a rescue .... Criteria !
Criteria iterface is very handy, you can find there many SQL equivalent for example:
- like
-between
- in ( Used in collection, but if you try to find String using "S%" it won't work, it takes full String like "John" and try to find a name in your table contains excatly this String, so any SQL wildcard "doesn't pass the exam here..")
-notNull / isNull and many many others!
I mentioned about method Restriction.in.
Let's look at this example:
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.like("firstName", "M%")).add(Restrictions.in("lastName", new String[] { "S%", "B%" }));
From the logic point of view this Criteria query should return me the same result like above from snippet, but it's not true. Method Restrictions.in is searching iterating through every record searching exactly fits String : S% and after that B% (in meaning String as a String not like a SQL Wildcard).
But if you modify above Criteria query to the following:
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.like("firstName", "M%")).add(Restrictions.in("id", new Integer[] {6,7,8,9}));
You will get the output.
Maybe you don't know but MySQL doesn't support charlist wild cards, this mean that query like this
SELECT * FROM User
WHERE lastName LIKE '[KTM]%'
Will cause an error, instead of you should use a regexp something like follow:
select * from User where lastName REGEXP '[KTM].*';
- like
-between
- in ( Used in collection, but if you try to find String using "S%" it won't work, it takes full String like "John" and try to find a name in your table contains excatly this String, so any SQL wildcard "doesn't pass the exam here..")
-notNull / isNull and many many others!
I mentioned about method Restriction.in.
Let's look at this example:
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.like("firstName", "M%")).add(Restrictions.in("lastName", new String[] { "S%", "B%" }));
From the logic point of view this Criteria query should return me the same result like above from snippet, but it's not true. Method Restrictions.in is searching iterating through every record searching exactly fits String : S% and after that B% (in meaning String as a String not like a SQL Wildcard).
But if you modify above Criteria query to the following:
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.like("firstName", "M%")).add(Restrictions.in("id", new Integer[] {6,7,8,9}));
You will get the output.
Maybe you don't know but MySQL doesn't support charlist wild cards, this mean that query like this
SELECT * FROM User
WHERE lastName LIKE '[KTM]%'
Will cause an error, instead of you should use a regexp something like follow:
select * from User where lastName REGEXP '[KTM].*';