Cheap Java Services With Tomcat, Jsp, Php, Mysql, J2Ee, Servlet, Struts & Hibernate Hosting Support and Coldfusion Web Hosting

CHAPTER 14 Querying with HQL and JPA QL 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();
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

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: select CATEGORY_ID, NAME, PARENT_ID from CATEGORY where NAME like ? 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: select CATEGORY_ID from CATEGORY where NAME like ? 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,
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

CHAPTER 14 Querying with HQL and JPA QL memory in one turn is the most common way to execute a query; we call this listing. Some other options are available that we also discuss next:, iterating and scrolling. Scrolling is about as useful as iteration: You rarely need one of these options. We d guess that more than 90 percent of all query execution relies on the list() and getResultList() methods in a regular application. First, the most common case. Listing all results In Hibernate, the list() method executes the query and returns the results as a java.util.List: List result = myQuery.list(); The Criteria interface also supports this operation: List result = myCriteria.list(); In both cases, one or several SELECTs statements are executing immediately, depending on your fetch plan. If you map any associations or collections as non- lazy, they must be fetched in addition to the data you want retrieved with your query. All these objects are loaded into memory, and any entity objects that are retrieved are in persistent state and added to the persistence context. Java Persistence offers a method with the same semantics, but a different name: List result = myJPAQuery.getResultList(); With some queries you know that the result is only a single instance for example, if you want only the highest bid. In this case, you can read it from the result list by index, result.get(0). Or, you can limit the number of returned rows with setMaxResult(1). Then you may execute the query with the uniqueResult() method, because you know only one object will be returned: Bid maxBid = (Bid) session.createQuery(”from Bid b order by b.amount desc”) .setMaxResults(1) .uniqueResult(); Bid bid = (Bid) session.createCriteria(Bid.class) .add( Restrictions.eq(”id”, id) ) .uniqueResult(); If the query returns more than one object, an exception is thrown. If the query result is empty, a null is returned. This also works in Java Persistence, again with a different method name (and, unfortunately, an exception is thrown if the result is empty):
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

Creating and running queries Query q = session.createQuery(”from Item”) .setComment(”My Comment…”); Criteria criteria = session.createCriteria(Item.class) .setComment(”My Comment…”); Query q = em.createQuery(”select i from Item i”) .setHint(”org.hibernate.comment”, “My Comment…”); The hints you ve been setting so far are all related to Hibernate or JDBC handling. Many developers (and DBAs) consider a query hint to be something completely different. In SQL, a query hint is a comment in the SQL statement that contains an instruction for the SQL optimizer of the database management system. For example, if the developer or DBA thinks that the execution plan selected by the database optimizer for a particular SQL statement isn t the fastest, they use a hint to force a different execution plan. Hibernate and Java Persistence don t support arbitrary SQL hints with an API; you ll have to fall back to native SQL and write your own SQL statement you can of course execute that statement with the provided APIs. (With some database-management systems you can control the optimizer with an SQL comment at the beginning of an SQL statement; in that case, use Query.setComment() to add the hint. In other scenarios, you may be able to write an org.hibernate.Interceptor and manipulate an SQL statement in the onPrepareStatement(sql) method before it s sent to the database.) Finally, you can control whether a query should force a pessimistic lock in the database management system a lock that is held until the end of the database transaction: Query q = session.createQuery(”from Item item”) .setLockMode(”item”, LockMode.UPGRADE); Criteria criteria = session.createCriteria(Item.class) .setLockMode(LockMode.UPGRADE); Both queries, if supported by your database dialect, result in an SQL statement that includes a … FOR UPDATE operation (or the equivalent, if supported by the database system and dialect). Currently, pessimistic locking isn t available (but it s planned as a Hibernate extension hint) on the Java Persistence query interface. Let s assume that queries are now prepared, so you can run them. 14.1.2 Executing a query Once you ve created and prepared a Query or Criteria object, you re ready to execute it and retrieve the result into memory. Retrieving the whole result into
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 14 Querying with HQL and JPA QL Query q = session.createQuery(”from Item”) .setReadOnly(true); Criteria criteria = session.createCriteria(Item.class) .setReadOnly(true); Query q = em.createQuery(”select i from Item i”) .setHint(”org.hibernate.readOnly”, true); All Item objects returned by this query are in persistent state, but no snapshot for automatic dirty checking is enabled in the persistence context. Hibernate doesn t persist any modifications automatically, unless you disable read-only mode with session.setReadOnly(object, false). You can control how long a query is allowed to run by setting a timeout: Query q = session.createQuery(”from Item”) .setTimeout(60); // 1 minute Criteria criteria = session.createCriteria(Item.class) .setTimeout(60); Query q = em.createQuery(”select i from Item i”) .setHint(”org.hibernate.timeout”, 60); This method has the same semantics and consequences as the setQueryTimeout() method on a JDBC Statement. Also related to the underlying JDBC is the fetch size: Query q = session.createQuery(”from Item”) .setFetchSize(50); Criteria criteria = session.createCriteria(Item.class) .setFetchSize(50); Query q = em.createQuery(”select i from Item i”) .setHint(”org.hibernate.fetchSize”, 50); The JDBC fetch size is an optimization hint for the database driver; it may not result in any performance improvement if the driver doesn t implement this functionality. If it does, it can improve the communication between the JDBC client and the database, by retrieving many rows in one batch when the client operates on a query result (that is, on a ResultSet). Because Hibernate is working with the ResultSet behind the scenes, this hint can improve data retrieval if you execute a query with list() which you ll do soon. When you optimize an application you often have to read complex SQL logs. We highly recommend that you enable hibernate.use_sql_comments; Hibernate will then add a comment to each SQL statement it writes to the logs. You can set a custom comment for a particular query with setComment():
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

Creating and running queries Criteria criteria = session.createCriteria(Item.class) .setFlushMode(FlushMode.COMMIT); Query q = em.createQuery(queryString) .setFlushMode(FlushModeType.COMMIT); Hibernate won t flush the persistence context before executing any of these queries. Another optimization is a fine-grained org.hibernate.CacheMode for a particular query result. You used a cache mode in chapter 13, section 13.4.5, Controlling the second-level cache, to control how Hibernate interacts with the second- level cache. If Hibernate retrieves an object by identifier, it looks it up in the first- level persistence context cache and, if enabled, the second-level cache region for this entity. The same happens when you execute a query that returns entity instances: During marshaling of the query result, Hibernate tries to resolve all entity instances by looking them up from the persistence context cache first it ignores the entity data of the query result if the entity instance is in the persistence context cache. And, if the retrieved entity instance wasn t in any cache, Hibernate puts it there after the query completes. You can control this behavior with a CacheMode on a query: Query q = session.createQuery(”from Item”) .setCacheMode(CacheMode.IGNORE); Criteria criteria = session.createCriteria(Item.class) .setCacheMode(CacheMode.IGNORE); Query q = em.createQuery(queryString) .setHint(”org.hibernate.cacheMode”, org.hibernate.CacheMode.IGNORE); A CacheMode.IGNORE, for example, tells Hibernate not to interact with the second- level cache for any entity returned by this query. In other words, any Item retrieved by this query isn t put in the second-level cache. Setting this cache mode is useful if you execute a query that shouldn t update the second-level cache, maybe because the data you re retrieving is only relevant for a particular situation, and shouldn t exhaust the available space in the cache region. In Controlling the persistence context cache in chapter 9, section 9.3.3, we talked about the control of the persistence context and how you can reduce memory consumption and prevent long dirty checking cycles. One way to disable dirty checking for a particular persistent object is to set session.setRead- Only(object, true) (the EntityManager doesn t support this API). You can tell Hibernate that all entity objects returned by a query should be considered read-only (although not detached):
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

CHAPTER 14 Querying with HQL and JPA QL .setParameter(1, searchString) .setParameter(2, minDate, TemporalType.DATE); Not only is this code much less self-documenting than the alternative with named parameters, it s also much more vulnerable to easy breakage if you change the query string slightly: String queryString = “from Item item” + ” where item.date > ?” + ” and item.description like ?”; Every change of the position of the bind parameters requires a change to the parameter binding code. This leads to fragile and maintenance-intensive code. Our recommendation is to avoid positional parameters. They may be more convenient if you build complex queries programmatically, but the Criteria API is a much better alternative for that purpose. If you have to use positional parameters, remember that Hibernate starts counting at 0, but Java Persistence starts at 1, and that you have to add a number to each question mark in a JPA QL query string. They have different legacy roots: Hibernate in JDBC, Java Persistence in older versions of EJB QL. In addition to bind parameters, you often want to apply other hints that influence how a query is executed. Setting query hints Let s assume that you make modifications to persistent objects before executing a query. These modifications are only present in memory, so Hibernate (and Java Persistence providers) flushes the persistence context and all changes to the database before executing your query. This guarantees that the query runs on current data and that no conflict between the query result and the in-memory objects can occur. This is sometimes impractical: for example, if you execute a sequence that consists of many query-modify-query-modify operations, and each query is retrieving a different dataset than the one before. In other words, you don t need to flush your modifications to the database before executing a query, because conflicting results aren t a problem. Note that the persistence context provides repeatable read for entity objects, so only scalar results of a query are a problem anyway. You can disable flushing of the persistence context with setFlushMode() on a Session or EntityManager. Or, if you want to disable flushing only before a particular query, you can set a FlushMode on the Query (Hibernate and JPA) object: Query q = session.createQuery(queryString) .setFlushMode(FlushMode.COMMIT);
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Creating and running queries If you have a JavaBean with seller and description properties, you can call the setProperties() method to bind the query parameters. For example, you can pass query parameters in an instance of the Item class itself: Item item = new Item(); item.setSeller(seller); item.setDescription(description); String queryString = “from Item item” + ” where item.seller = :seller and” + ” item.description like :desccription”; session.createQuery(queryString).setProperties(item); The setProperties() binding matches names of JavaBean properties to named parameters in the query string, internally calling setParameter() to guess the Hibernate type and bind the value. In practice, this turns out to be less useful than it sounds, because some common Hibernate types aren t guessable (temporal types, in particular). The parameter binding methods of Query are null-safe. So the following code is legal: session.createQuery(”from User as u where u.username = :name”) .setString(”name”, null); However, the result of this code is almost certainly not what you intended! The resulting SQL will contain a comparison like USERNAME = null, which always evaluates to null in SQL ternary logic. Instead, you must use the is null operator: session.createQuery(”from User as u where u.username is null”); Using positional parameters If you prefer, you can use positional parameters instead in Hibernate and Java Persistence: String queryString = “from Item item” + ” where item.description like ?” + ” and item.date > ?”; Query q = session.createQuery(queryString) .setString(0, searchString) .setDate(1, minDate); Java Persistence also supports positional parameters: String queryString = “from Item item” + ” where item.description like ?1″ + ” and item.date > ?2″; Query q = em.createQuery(queryString)
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.