java applet qr code reader Creating and running queries in Java

Creator QR Code in Java Creating and running queries

Bid maxBid = (Bid) em.createQuery( "select b from Bid b order by b.amount desc" ).setMaxResults(1) .getSingleResult();

Retrieving all results into memory is the most common way to execute a query. Hibernate supports some other methods that you may find interesting if you want to optimize the memory consumption and execution behavior of a query. Iterating through the results The Hibernate Query interface also provides the iterate() method to execute a query. It returns the same data as list(), but relies on a different strategy for retrieving the results. When you call iterate() to execute a query, Hibernate retrieves only the primary key (identifier) values of entity objects in a first SQL SELECT, and then tries to find the rest of the state of the objects in the persistence context cache, and (if enabled) the second-level cache. Consider the following code:

Query categoryByName = session.createQuery("from Category c where c.name like :name"); categoryByName.setString("name", categoryNamePattern); List categories = categoryByName.list();

This query results in execution of at least one SQL SELECT, with all columns of the CATEGORY table included in the SELECT clause:

If you expect that categories are already cached in the persistence context or the second-level cache, then you need only the identifier value (the key to the cache). This therefore reduces the amount of data fetched from the database. The following SQL is slightly more efficient:

You can use the iterate() method for this:

Query categoryByName = session.createQuery("from Category c where c.name like :name"); categoryByName.setString("name", categoryNamePattern); Iterator categories = categoryByName.iterate();

The initial query retrieves only Category primary key values. You then iterate through the result; Hibernate looks up each Category object in the current persistence context and, if enabled, in the second-level cache. If a cache miss occurs,

Hibernate executes an additional SELECT for each turn, retrieving the full Category object by its primary key from the database. In most cases, this is a minor optimization. It s usually much more important to minimize row reads than to minimize column reads. Still, if your object has large string fields, this technique may be useful to minimize data packets on the network and therefore latency. It should be clear that it s really effective only if the second-level cache region for the iterated entity is enabled. Otherwise it produces n+1 selects! Hibernate keeps the iterator open until you finish iteration through all results or until the Session is closed. You can also close it explicitly with org.hibernate.Hibernate.close(iterator). Also note that Hibernate Criteria and Java Persistence, at the time of writing, don t support this optimization. Another optimized way to execute a query is scrolling through the result. Scrolling with database cursors Plain JDBC provides a feature called scrollable resultsets. This technique uses a cursor that is held on the database management system. The cursor points to a particular row in the result of a query, and the application can move the cursor forward and backward. You can even jump to a particular row with the cursor. One of the situations where you should scroll through the results of a query instead of loading them all into memory involves resultsets that are too large to fit into memory. Usually you try to further restrict the result by tightening the conditions in the query. Sometimes this isn t possible, maybe because you need all of the data, but want to retrieve it in several steps. You ve already seen scrolling in Writing a procedure with batch updates chapter 12, section 12.2.2 and how to implement procedures that work on batches of data, because this is where it s most useful. The following example shows an overview of other interesting options on the ScrollableResults interface:

ScrollableResults itemCursor = session.createQuery("from Item").scroll(); itemCursor.first(); itemCursor.last(); itemCursor.get(); itemCursor.next(); itemCursor.scroll(3); itemCursor.getRowNumber(); itemCursor.setRowNumber(5); itemCursor.previous();