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 logical expressions by comparing properties of objects to other properties or literal values using the built-in comparison operators. FAQ What is ternary logic? A row is included in an SQL resultset if and only if the WHERE clause evaluates to true. In Java, notNullObject==null evaluates to false and null==null evaluates to true. In SQL, NOT_NULL_COLUMN=null and null=null both evaluate to null, not true. Thus, SQL needs a special operator, IS NULL, to test whether a value is null. This ternary logic is a way of handling expressions that may be applied to null column values. Treating null not as a special marker but as a regular value is an SQL extension to the familiar binary logic of the relational model. HQL and JPA QL have to support this ternary logic with ternary operators. Let s walk through the most common comparison operators. Comparison expressions HQL and JPA QL support the same basic comparison operators as SQL. Here are a few examples that should look familiar if you know SQL: from Bid bid where bid.amount between 1 and 10 from Bid bid where bid.amount > 100 from User u where u.email in (’foo@bar’, ‘bar@foo’) Because the underlying database implements ternary logic, testing for null values requires some care. Remember that null = null doesn t evaluate to true in SQL, but to null. All comparisons that use a null operand evaluate to null. (That s why you usually don t see the null literal in queries.) HQL and JPA QL provide an SQL-style IS [NOT] NULL operator: from User u where u.email is null from Item i where i.successfulBid is not null This query returns all users with no email address and items which are sold. The LIKE operator allows wildcard searches, where the wildcard symbols are % and _, as in SQL: from User u where u.firstname like ‘G%’ This expression restricts the result to users with a firstname starting with a capital G. You may also negate the LIKE operator, for example, in a substring match expression: from User u where u.firstname not like ‘%Foo B%’
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

Basic HQL and JPA QL queries Of course, this also works for interfaces this query returns all serializable persistent objects: from java.io.Serializable Likewise, the following criteria query returns all persistent objects (yes, you can select all the tables of your database with such a query): from java.lang.Object Note that Java Persistence doesn t standardize polymorphic queries that use non- mapped interfaces. However, this works with Hibernate EntityManager. Polymorphism applies not only to classes named explicitly in the FROM clause, but also to polymorphic associations, as you ll see later in this chapter. We ve discussed the FROM clause, now let s move on to the other parts of HQL and JPA QL. 14.2.2 Restriction Usually, you don t want to retrieve all instances of a class. You must be able express constraints on the property values of objects returned by the query. This is called restriction. The WHERE clause is used to express a restriction in SQL, HQL, and JPA QL. These expressions may be as complex as you need to narrow down the piece of data you re looking for. Note that restriction doesn t only apply to SELECT statements; you also use a restriction to limit the scope of an UPDATE or DELETE operation. This is a typical WHERE clause that restricts the results to all User objects with the given email address: from User u where u.email = ‘foo@hibernate.org’ Notice that the constraint is expressed in terms of a property, email, of the User class, and that you use an object-oriented notion for this. The SQL generated by this query is select u.USER_ID, u.FIRSTNAME, u.LASTNAME, u.USERNAME, u.EMAIL from USER u where u.EMAIL = ‘foo@hibernate.org’ You can include literals in your statements and conditions, with single quotes. Other commonly used literals in HQL and JPA QL are TRUE and FALSE: from Item i where i.isActive = true A restriction is expressed using ternary logic. The WHERE clause is a logical expression that evaluates to true, false, or null for each tuple of objects. You construct
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

CHAPTER 14 Querying with HQL and JPA QL from Item as item The as keyword is always optional. The following is equivalent: from Item item Think of this as being a bit like the temporary variable declaration in the following Java code: for ( Iterator i = allQueriedItems.iterator(); i.hasNext(); ) { Item item = (Item) i.next(); … } You assign the alias item to queried instances of the Item class, allowing you to refer to their property values later in the code (or query). To remind yourself of the similarity, we recommend that you use the same naming convention for aliases that you use for temporary variables (camelCase, usually). However, we may use shorter aliases in some of the examples in this book, such as i instead of item, to keep the printed code readable. FAQ Are HQL and JPA QL case sensitive? We never write HQL and JPA QL keywords in uppercase; we never write SQL keywords in uppercase either. It looks ugly and antiquated most modern terminals can display both uppercase and lowercase characters. However, HQL and JPA QL aren t case-sensitive for keywords, so you can write FROM Item AS item if you like shouting. Polymorphic queries HQL and JPA QL, as object-oriented query languages, support polymorphic queries queries for instances of a class and all instances of its subclasses, respectively. You already know enough HQL and JPA QL to be able to demonstrate this. Consider the following query: from BillingDetails This returns objects of the type BillingDetails, which is an abstract class. In this case, the concrete objects are of the subtypes of BillingDetails: CreditCard and BankAccount. If you want only instances of a particular subclass, you may use from CreditCard The class named in the from clause doesn t even need to be a mapped persistent class; any class will do! The following query returns all persistent objects: from java.lang.Object
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

Basic HQL and JPA QL queries 14.2 Basic HQL and JPA QL queries Let s start with some simple queries to get familiar with the HQL syntax and semantics. We apply selection to name the data source, restriction to match records to the criteria, and projection to select the data you want returned from a query. TRY IT Testing Hibernate queries The Hibernate Tools for the Eclipse IDE support a Hibernate Console view. You can test your queries in the console window, and see the generated SQL and the result immediately. You ll also learn JPA QL in this section, because it s a subset of the functionality of HQL we ll mention the differences when necessary. When we talk about queries in this section, we usually mean SELECT statements, operations that retrieve data from the database. HQL also supports UPDATE, DELETE, and even INSERT..SELECT statements, as we discussed in chapter 12, section 12.2.1, Bulk statements with HQL and JPA QL. JPA QL includes UPDATE and DELETE. We won t repeat these bulk operations here and will focus on SELECT statements. However, keep in mind that some differences between HQL and JPA QL may also apply to bulk operations for example, whether a particular function is portable. SELECT statements in HQL work even without a SELECT clause; only FROM is required. This isn t the case in JPA QL, where the SELECT clause isn t optional. This isn t a big difference in practice; almost all queries require a SELECT clause, whether you write JPA QL or HQL. However, we start our exploration of queries with the FROM clause, because in our experience it s easier to understand. Keep in mind that to translate these queries to JPA QL, you must theoretically add a SELECT clause to complete the statement, but Hibernate lets you execute the query anyway if you forget it (assuming SELECT *). 14.2.1 Selection The simplest query in HQL is a selection (note that we don t mean SELECT clause or statement here, but from where data is selected) of a single persistent class: from Item This query generates the following SQL: select i.ITEM_ID, i.NAME, i.DESCRIPTION, … from ITEM i Using aliases Usually, when you select a class to query from using HQL or JPA QL, you need to assign an alias to the queried class to use as a reference in other parts of the query:
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

CHAPTER 14 Querying with HQL and JPA QL A much more common solution is the encapsulation of queries in the orm.xml deployment descriptor: … select i from Item i … select i from Item i where i.description like :desc You can see that the Java Persistence descriptor supports an extension point: the hints element of a named-query definition. You can use it to set Hibernate-specific hints, as you did earlier programmatically with the Query interface. Native SQL queries have their own element and can also be either defined inside or outside an entity mapping: select i.NAME from ITEM i where i.DESC = :desc Embedding native SQL is much more powerful than we ve shown so far (you can define arbitrary resultset mappings). We ll get back to other SQL emedding options in the next chapter. We leave it up to you if you want to utilize the named query feature. However, we consider query strings in the application code (except if they re in annotations) to be the second choice; you should always externalize query strings if possible. You now know how to create, prepare, and execute a query with the Hibernate and Java Persistence APIs and metadata. It s time to learn the query languages and options in more detail. We start with HQL and JPA QL.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Creating and running queries All query hints that you set earlier with an API can also be set declaratively: Named queries don t have to be HQL or JPA QL strings; they may even be native SQL queries and your Java code doesn t need to know the difference: This is useful if you think you may want to optimize your queries later by fine-tuning the SQL. It s also a good solution if you have to port a legacy application to Hibernate, where SQL code was isolated from the hand-coded JDBC routines. With named queries, you can easily port the queries one-by-one to mapping files. We ll have much more to say about native SQL queries in the next chapter. Defining a named query with annotations The Java Persistence standard specifies the @NamedQuery and @NamedNativeQuery annotations. You can either place these annotations into the metadata of a particular class or into JPA XML descriptor file. Note that the query name must be globally unique in all cases; no class or package name is automatically prefixed. Let s assume you consider a particular named query to belong to a particular entity class: package auction.model; import …; @NamedQueries({ @NamedQuery( name = “findItemsByDescription”, query = “select i from Item i where i.description like :desc” ), … }) @Entity @Table(name = “ITEM”) public class Item { … }
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 ries related to a particular persistent class (or a set of classes) encapsulated with the other metadata of that class in an XML mapping file. Or, if you use annotations, you can create named queries as metadata of a particular entity class or put them into an XML deployment descriptor. The name of the query is used to call it from application code. Calling a named query In Hibernate, the getNamedQuery()method obtains a Query instance for a named query: session.getNamedQuery(”findItemsByDescription”) .setString(”desc”, description); In this example, you call the named query findItemsByDescription and bind a string argument to the named parameter desc. Java Persistence also supports named queries: em.createNamedQuery(”findItemsByDescription”) .setParameter(”desc”, description); Named queries are global that is, the name of a query is considered to be a unique identifier for a particular SessionFactory or persistence unit. How and where they re defined, in XML mapping files or annotations, is no concern of your application code. Even the query language doesn t matter. Defining a named query in XML metadata You can place a named query inside any element in your XML metadata. In larger applications, we recommend isolating and separating all named queries into their own file. Or, you may want some queries to be defined in the same XML mapping file as a particular class. The defines a named HQL or JPA QL query: You should wrap the query text into a CDATA instruction so the XML parser doesn t get confused by any characters in your query string that may accidentally be considered XML (such as the less than operator). If you place a named query definition inside a element, instead of the root, it s prefixed with the name of the entity class; for example, findItemsByDescription is then callable as auction.model.Item.findItemsByDescription. Otherwise, you need to make sure the name of the query is globally unique.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Creating and running queries itemCursor.scroll(-3); itemCursor.close(); This code doesn t make much sense; it displays the most interesting methods on the ScrollableResults interface. You can set the cursor to the first and last Item object in the result, or get the Item the cursor is currently pointing to with get(). You can go to a particular Item by jumping to a position with setRowNumber() or scroll backward and forward with next() and previous(). Another option is scrolling forward and backward by an offset, with scroll(). Hibernate Criteria queries can also be executed with scrolling instead of list(); the returned ScrollableResults cursor works the same. Note that you absolutely must close the cursor when you re done working with it, before you end the database transaction. Here is a Criteria example that shows the opening of a cursor: ScrollableResults itemCursor = session.createCriteria(Item.class) .scroll(ScrollMode.FORWARD_ONLY); … // Scroll only forward itemCursor.close() The ScrollMode constants of the Hibernate API are equivalent to the constants in plain JDBC. In this case, the constant ensures that your cursor can only move forward. This may be required as a precaution; some JDBC drivers don t support scrolling backward. Other available modes are ScrollMode.SCROLL_INSENSITIVE and ScrollMode.SCROLL_SENSITIVE. An insensitive cursor won t expose you to modified data while the cursor is open (effectively guaranteeing that no dirty reads, unrepeatable reads, or phantom reads can slip into your resultset). On the other hand, a sensitive cursor exposes newly committed data and committed modifications to you while you work on your resultset. Note that the Hibernate persistence context cache still provides repeatable read for entity instances, so only modified scalar values you project in the resultset can be affected by this setting. So far, the code examples we ve shown all embed query string literals in Java code. This isn t unreasonable for simple queries, but once you begin considering complex queries that must be split over multiple lines, this gets a bit unwieldy. 14.1.3 Using named queries We don t like to see HQL or JPA QL string literals scattered all over the Java code, unless really necessary. Hibernate lets you externalize query strings to the mapping metadata, a technique that is called named queries. This lets you store all que
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 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.