Quering and queries with parameter(s)

Hello!

I would like to show you, how to retrieve object from db using queries with parameter(s).

In this example we are searching a user with name "John". This is HQL example where we used only 1 parameter. Please remember that we are using a properties name from User class (I mean firstName, lastName, id -  you cannot use directly column names from your database). We are using now "labeled paramaters", but we can use questions marks (?), but  remember about type of this parameter and their sequence in query.



This above snippet we have the same result using question mark. Rembember that postion of parameters are 0 indexed.

In example above, we used an indexed typed parameter (?). You should try to set this parameter to String, BigDecimal etc and examine what will be execute of this code. Sometimes query will be executing fine, when we try to find user with ID= 4 and we set a parameter like a String("4")

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 !

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].*';