Querying with HQL and JPA QL in Java

Creating QR Code 2d barcode in Java Querying with HQL and JPA QL

Starting from the fortieth object, you retrieve the next 20 objects. Note that there is no standard way to express pagination in SQL Hibernate knows the tricks to make this work efficiently on your particular database. You can even add this flexible pagination option to an SQL query. Hibernate will rewrite your SQL for pagination:

Query sqlQuery = session.createSQLQuery("select {u.*} from USERS {u}") .addEntity("u", User.class); sqlQuery.setFirstResult(40); sqlQuery.setMaxResults(20);

You may use the method-chaining coding style (methods return the receiving object instead of void) with the Query and Criteria interfaces, rewriting the two previous examples as follows:

Query query = session.createQuery("from User u order by u.name asc") .setMaxResults(10); Criteria crit = session.createCriteria(User.class) .addOrder( Order.asc("name") ) .setFirstResult(40) .setMaxResults(20);

Chaining method calls is less verbose and is supported by many Hibernate APIs. The Java Persistence query interfaces also support pagination and method chaining for JPA QL and native SQL queries with the javax.persistence.Query interface:

Query query = em.createQuery("select u from User u order by u.name asc") .setFirstResult(40) .setMaxResults(20);

Next in preparing your query is the setting of any runtime parameters. Considering parameter binding Without runtime parameter binding, you have to write bad code:

String queryString = "from Item i where i.description like '" + search + "'"; List result = session.createQuery(queryString).list();

You should never write this code because a malicious user could search for the following item description that is, by entering the value of search in a search dialog box as

As you can see, the original queryString is no longer a simple search for a string but also executes a stored procedure in the database! The quote characters aren t escaped; hence the call to the stored procedure is another valid expression in the query. If you write a query like this, you open up a major security hole in your application by allowing the execution of arbitrary code on your database. This is known as an SQL injection security issue. Never pass unchecked values from user input to the database! Fortunately, a simple mechanism prevents this mistake. The JDBC driver includes functionality for safely binding values to SQL parameters. It knows exactly what characters in the parameter value to escape, so that the previous vulnerability doesn t exist. For example, the quote characters in the given search are escaped and are no longer treated as control characters but as a part of the search string value. Furthermore, when you use parameters, the database is able to efficiently cache precompiled prepared statements, improving performance significantly. There are two approaches to parameter binding: using positional or using named parameters. Hibernate and Java Persistence support both options, but you can t use both at the same time for a particular query. With named parameters, you can rewrite the query as

String queryString = "from Item item where item.description like :search";

The colon followed by a parameter name indicates a named parameter. Then, bind a value to the search parameter:

Query q = session.createQuery(queryString) .setString("search", searchString);

Because searchString is a user-supplied string variable, you call the setString() method of the Query interface to bind it to the named parameter (:search). This code is cleaner, much safer, and performs better, because a single compiled SQL statement can be reused if only bind parameters change. Often, you ll need multiple parameters:

String queryString = "from Item item" + " where item.description like :search" + " and item.date > :minDate"; Query q = session.createQuery(queryString) .setString("search", searchString) .setDate("minDate", mDate);

The same query and code looks slightly different in Java Persistence:

Query q = em.createQuery(queryString) .setParameter("search", searchString) .setParameter("minDate", mDate, TemporalType.DATE);

The setParameter() method is a generic operation that can bind all types of arguments, it only needs a little help for temporal types (the engine needs to know if you want only the date, time, or a full timestamp bound). Java Persistence supports only this method for binding of parameters (Hibernate, by the way, has it too). Hibernate, on the other hand, offers many other methods, some of them for completeness, others for convenience, that you can use to bind arguments to query parameters. Using Hibernate parameter binding You ve called setString() and setDate() to bind arguments to query parameters. The native Hibernate Query interface provides similar convenience methods for binding arguments of most of the Hibernate built-in types: everything from setInteger() to setTimestamp() and setLocale(). They re mostly optional; you can rely on the setParameter() method to figure out the right type automatically (except for temporal types). A particularly useful method is setEntity(), which lets you bind a persistent entity (note that setParameter() is smart enough to understand even that automatically):

session.createQuery("from Item item where item.seller = :seller") .setEntity("seller", theSeller);

However, there is also a generic method that allows you to bind an argument of any Hibernate type:

String queryString = "from Item item" + " where item.seller = :seller and" + " item.description like :desc"; session.createQuery(queryString) .setParameter( "seller", theSeller, Hibernate.entity(User.class) ) .setParameter( "desc", description, Hibernate.STRING );

This works even for custom user-defined types, like MonetaryAmount:

Query q = session.createQuery("from Bid where amount > :amount"); q.setParameter( "amount", givenAmount, Hibernate.custom(MonetaryAmountUserType.class) );