Course Module 4. Working with databases - Lecture: Requests with parameters

4.1 Parameters for queries

Hibernate allows you to pass parameters to queries. Thus, all work with queries and the database is greatly simplified.

It is very rare to find immutable queries. At first, it seems that you just need to return a list of goods from the database. And then it turns out that you need an up-to-date list of products for a specific user on a specific date. Sorted by the required field, and not yet the whole list, but a specific page: for example, products from 21 to 30.

And this is exactly what parameterized queries solve. You write a query in HQL, and then you replace the values that can be changed with “special names” - parameters. And then separately when executing the request, you can pass the values of these parameters.

Let's write an HQL query that will return all tasks for a user with a specific name:

from EmployeeTask where employee.name = "Ivan Ivanovich"

Now let's replace the name with a parameter:

from EmployeeTask where employee.name = :username

And this is how our Java code for finding tasks will look like:


String hql = "from EmployeeTask where employee.name = :username";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter("username", "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

Also, instead of a parameter name, you can use just a number:


String hql = "from EmployeeTask where employee.name = :1";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter(1, "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

Although it is better, of course, to use the name - it is much easier to read and maintain such code.

4.2 setParameterList() method.

There are also cases when the parameter value is not one, but represents a list of objects. For example, we want to check that the professions of employees are contained in a certain list.

How could this be done:


String hql = "from EmployeeTask where occupation IN (:occupation_list)";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameterList("occupation_list", new String[] {"Programmer", "Tester"});
List<EmployeeTask> resultLIst = query.list();

4 types of list can be passed as a parameter value:

array of objects: Object[]
collection: Collection
typed array: T[]
typed collection: Collection<T>

If you decide to pass a typed collection or array, then you need to pass the data type as the third parameter. Example:


String hql = "from EmployeeTask where occupation IN (:occupation_list)";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameterList("occupation_list", new String[] {"Programmer", "Tester"}, String.class);
List<EmployeeTask> resultLIst = query.list();

When working with list parameters, you can also use a number instead of the parameter name. But again, the name is more convenient.

4.3 Protection against SQL Injection

One of the most important purposes of the parameters is to protect the database from SQL injections. Many novice programmers, instead of using parameters, would simply glue together a string of several parts.

Instead of writing like this:


String hql = "from EmployeeTask where employee.name = :username";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter("username", "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

Would write like this:


String hql = "from EmployeeTask where employee.name = " + "Ivan Ivanovich";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
List<EmployeeTask> resultLIst = query.list();

Never do that!Never stick together an SQL/HQL query from multiple parts. Because sooner or later the username will come to you from the client. And the evil hacker will give you a string like""Ivan"; DROP TABLE user;"

And then your query to the database will take the form:


from EmployeeTask where employee.name = "Ivan"; DROP TABLE user;

And it's still good if your data is simply deleted. You can also write like this:


from EmployeeTask where employee.name = "Ivan";
UPDATE user SET password = '1' WHERE user.role = 'admin'

Or like this:


from EmployeeTask where employee.name = "Ivan";
UPDATE user SET role = 'admin' WHERE user.id = 123;