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 Expressions with collections All expressions in the previous sections included only single-valued path expressions: user.email, bid.amount, and so on. You can also use path expressions that end in collections in the WHERE clause of a query, with the right operators. For example, let s assume you want to restrict your query result by the size of a collection: from Item i where i.bids is not empty This query returns all Item instances that have an element in their bids collection. You can also express that you require a particular element to be present in a collection: from Item i, Category c where i.id = ‘123′ and i member of c.items This query returns Item and Category instances usually you add a SELECT clause and project only one of the two entity types. It returns an Item instance with the primary key ‘123′ (a literal in single quotes) and all Category instances this Item instance is associated with. (Another trick you use here is the special .id path; this field always refers to the database identifier of an entity, no matter what the name of the identifier property is.) There are many other ways to work with collections in HQL and JPA QL. For example, you can use them in function calls. Calling functions An extremely powerful feature of HQL is the ability to call SQL functions in the WHERE clause. If your database supports user-defined functions (most do), you can put this to all sorts of uses, good or evil. For the moment, let s consider the usefulness of the standard ANSI SQL functions UPPER() and LOWER(). These can be used for case-insensitive searching: from User u where lower(u.email) = ‘foo@hibernate.org’ Another common expression is concatenation although SQL dialects are different here, HQL and JPA QL support a portable concat() function: from User user where concat(user.firstname, user.lastname) like ‘G% K%’ Also typical is an expression that requires the size of a collection: from Item i where size(i.bids) > 3 JPA QL standardizes the most common functions, as summarized in table 14.2.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Basic HQL and JPA QL queries The percentage symbol stands for any sequence of characters; the underscore can be used to wildcard a single character. You can define an escape character if you want a literal percentage or underscore: from User u where u.firstname not like ‘\%Foo%’ escape=’\’ This query returns all users with a firstname that starts with %Foo. HQL and JPA QL support arithmetic expressions: from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0 Logical operators (and parentheses for grouping) are used to combine expressions: from User user where user.firstname like ‘G%’ and user.lastname like ‘K%’ from User u where ( u.firstname like ‘G%’ and u.lastname like ‘K%’ ) or u.email in (’foo@hibernate.org’, ‘bar@hibernate.org’ ) You can see the precedence of operators in table 14.1, from top to bottom. The listed operators and their precedence are the same in HQL and JPA QL. The arithmetic operators, for example multiplication and addition, are self- explanatory. You ve already seen how binary comparison expressions have the same semantics as their SQL counterpart and how to group and combine them with logical operators. Let s discuss collection handling. Table 14.1 HQL and JPA QL operator precedence Operator Description . Navigation path expression operator +, -Unary positive or negative signing (all unsigned numeric values are considered positive) *, / Regular multiplication and division of numeric values +, -Regular addition and subtraction of numeric values =, <>, <, >, >=, <=, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, Binary comparison operators with SQL semantics IS [NOT] EMPTY, [NOT] MEMBER [OF] Binary operators for collections in HQL and JPA QL NOT, AND, OR Logical operators for ordering of expression evaluation
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

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.