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 Instead of a List of Items, this query returns a List of Object[] arrays. At index 0 is the Item, and at index 1 is the Bid. A particular Item may appear multiple times, once for each associated Bid. These duplicate items are duplicate in-memory references, not duplicate instances! If you don t want the Bids in the query result, you may specify a SELECT clause in HQL (it s mandatory anyway for JPA QL). You use the alias in a SELECT clause to project only the objects you want: select i from Item i join i.bids b where i.description like ‘%Foo%’ and b.amount > 100 Now the generated SQL looks like this: select i.DESCRIPTION, i.INITIAL_PRICE, … from ITEM i inner join BID b on i.ITEM_ID = b.ITEM_ID where i.DESCRIPTION like ‘%Foo%’ and b.AMOUNT > 100 The query result contains just Items, and because it s an inner join, only Items that have Bids: Query q = session.createQuery(”select i from Item i join i.bids b”); Iterator items = q.list().iterator(); while ( items.hasNext() ) { Item item = (Item) items.next(); } As you can see, using aliases in HQL and JPA QL is the same for both direct classes and joined associations. You used a collection in the previous examples, but the syntax and semantics are the same for single-valued associations, such as many-toone and one-to-one. You assign aliases in the FROM clause by naming the association and then use the aliases in the WHERE and possibly SELECT clause. HQL and JPA QL offer an alternative syntax for joining a collection in the FROM clause and to assign it an alias. This IN() operator has its history in an older version of EJB QL. It s semantics are the same as those of a regular collection join. You can rewrite the last query as follows: select i from Item i in(i.bids) b where i.description like ‘%Foo%’ and b.amount > 100 The from Item i in(i.bids) b results in the same inner join as the earlier example with fromItemijoini.bids b.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

Joins, reporting queries, and subselects Joins expressed in the FROM clause Hibernate differentiates between the purposes for joining. Suppose you re querying Items. There are two possible reasons why you may be interested in joining them with Bids. You may want to limit the item returned by the query on the basis of some criterion that should be applied to their Bids. For example, you may want all Items that have a bid of more than $100; hence this requires an inner join. You aren t interested in items that have no bids so far. On the other hand, you may be primarily interested in the Items, but you may want to execute an outer join just because you want to retrieve all the Bids for the queried Items in the same single SQL statement, something we called eager join fetching earlier. Remember that you prefer to map all associations lazy by default, so an eager, outer-join fetch query is used to override the default fetching strategy at runtime for a particular use case. Let s first write some queries that use inner joins for the purpose of restriction. If you want to retrieve Item instances and restrict the result to items that have bids with a certain amount, you have to assign an alias to a joined association: from Item i join i.bids b where i.description like ‘%Foo%’ and b.amount > 100 This query assigns the alias i to the entity Item and the alias b to the joined Items bids. You then use both aliases to express restriction criteria in the WHERE clause. The resulting SQL is: select i.DESCRIPTION, i.INITIAL_PRICE, … b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON from ITEM i inner join BID b on i.ITEM_ID = b.ITEM_ID where i.DESCRIPTION like ‘%Foo%’ and b.AMOUNT > 100 The query returns all combinations of associated Bids and Items as ordered pairs: Query q = session.createQuery(”from Item i join i.bids b”); Iterator pairs = q.list().iterator(); while ( pairs.hasNext() ) { Object[] pair = (Object[]) pairs.next(); Item item = (Item) pair[0]; Bid bid = (Bid) pair[1]; }
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

CHAPTER 14 Querying with HQL and JPA QL all mapped to the same table together with the User data. You can also write a path expression in the SELECT clause: select distinct u.homeAddress.city from User u This query returns a List of Strings. Because duplicates don t make much sense, you eliminate them with DISTINCT. The second usage of multipart path expressions is implicit association joining: from Bid bid where bid.item.description like ‘%Foo%’ This results in an implicit join on the many-to-one associations from Bidto Item the name of this association is item. Hibernate knows that you mapped this association with the ITEM_ID foreign key in the BID table and generates the SQL join condition accordingly. Implicit joins are always directed along many-to-one or one-to-one associations, never through a collection-valued association (you can t write item.bids.amount). Multiple joins are possible in a single path expression. If the association from Item to Category is many-to-one (instead of the current many-to-many), you can write from Bid bid where bid.item.category.name like ‘Laptop%’ We frown on the use of this syntactic sugar for more complex queries. SQL joins are important, and especially when optimizing queries, you need to be able to see at a glance exactly how many of them there are. Consider the following query (again, using a many-to-one from Item to Category): from Bid bid where bid.item.category.name like ‘Laptop%’ and bid.item.successfulBid.amount > 100 How many joins are required to express this in SQL? Even if you get the answer right, it takes more than a few seconds to figure out. The answer is three; the generated SQL looks something like this: select … from BID B inner join ITEM I on B.ITEM_ID = I.ITEM_ID inner join CATEGORY C on I.CATEGORY_ID = C.CATEGORY_ID inner join BID SB on I.SUCCESSFUL_BID_ID = SB.BID_ID where C.NAME like ‘Laptop%’ and SB.AMOUNT > 100 It s more obvious if you express this query with explicit HQL and JPA QL joins in the FROM clause.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Joins, reporting queries, and subselects HQL and JPA QL join options In Hibernate queries, you don t usually specify a join condition explicitly. Rather, you specify the name of a mapped Java class association. This is basically the same feature we d prefer to have in SQL, a join condition expressed with a foreign key constraint name. Because you ve mapped most, if not all, foreign key relationships of your database schema in Hibernate, you can use the names of these mapped associations in the query language. This is really syntactical sugar, but it s convenient. For example, the Item class has an association named bids with the Bid class. If you name this association in a query, Hibernate has enough information in the mapping document to then deduce the table join expression. This helps make queries less verbose and more readable. In fact, HQL and JPA QL provide four ways of expressing (inner and outer) joins: An implicit association join An ordinary join in the FROM clause A fetch join in the FROM clause A theta-style join in the WHERE clause Later we show you how to write a join between two classes that don t have an association defined (a theta-style join) and how to write ordinary and fetch joins in the FROM clause of a query. Implicit association joins are common abbreviations. (Note that we decided to make the following examples easier to read and understand by often omitting the SELECT clause valid in HQL, invalid in JPA QL.) Implicit association joins So far, you ve used simple qualified property names like bid.amount and item.description in queries. HQL and JPA QL support multipart property path expressions with a dot notation for two different purposes: Querying components Expressing implicit association joins The first use is straightforward: from User u where u.homeAddress.city = ‘Bangkok’ You reference parts of the mapped component Address with a dot notation. No tables are joined in this query; the properties of the homeAddress component are
We recommend high quality webhost to host and run your jsp application: christian web host services.

CHAPTER 14 Querying with HQL and JPA QL Figure 14.2 The result table of an ANSI-style inner join of two tables Second, you filter these joined rows using a join condition. (Any good database engine has much more sophisticated algorithms to evaluate a join; it usually doesn t build a memory-consuming product and then filters all rows.) The join condition is a boolean expression that evaluates to true if the joined row is to be included in the result. In case of the left outer join, each row in the (left) ITEM table that never satisfies the join condition is also included in the result, with NULL values returned for all columns of BID. A right outer join retrieves all bids and null if a bid has no item not a sensible query in this situation. Right outer joins are rarely used; developers always think from left to right and put the driving table first. In SQL, the join condition is usually specified explicitly. (Unfortunately, it isn t possible to use the name of a foreign key constraint to specify how two tables are to be joined.) You specify the join condition in the ON clause for an ANSI-style join or in the WHEREclause for a so-called theta-style join, where I.ITEM_ID=B.ITEM_ID. We now discuss the HQL and JPA QL join options. Remember that both are based on and translated into SQL, so even if the syntax is slightly different you should always refer to the two examples shown earlier and verify that you understood what the resulting SQL and resultset looks like. Figure 14.3 The result of an ANSI-style left outer join of two tables
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

Joins, reporting queries, and subselects 14.3 Joins, reporting queries, and subselects It s difficult to categorize some queries as advanced and others as basic. Clearly, the queries we ve shown you in the previous sections of this chapter aren t going to get you far. At the least you also need to know how joins work. The ability to arbitrarily join data is one of the fundamental strengths of relational data access. Joining data is also the basic operation that enables you to fetch several associated objects and collections in a single query. We now show you how basic join operations work and how you use them to write a dynamic fetching strategy. Other techniques we d consider advanced include nesting of statements with subselects and report queries that aggregate and group results efficiently. Let s start with joins and how they can be used for dynamic fetching. 14.3.1 Joining relations and associations You use a join to combine data in two (or more) relations. For example, you may join the data in the ITEM and BID tables, as shown in figure 14.1. (Note that not all columns and possible rows are shown; hence the dotted lines.) What most people think of when they hear the word join in the context of SQL databases is an inner join. An inner join is the most important of several types of joins and the easiest to understand. Consider the SQL statement and result in figure 14.2. This SQL statement is an ANSI-style inner join in the FROM clause. If you join tables ITEM and BID with an inner join, using their common attributes (the ITEM_ID column), you get all items and their bids in a new result table. Note that the result of this operation contains only items that have bids. If you want all items, and NULL values instead of bid data when there is no corresponding bid, you use a (left) outer join, as shown in figure 14.3. You can think of a table join as working as follows. First, you take a product of the two tables, by taking all possible combinations of ITEM rows with BID rows. Figure 14.1 The ITEM and BID tables are obvious candidates for a join operation.
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 Getting distinct results When you use a SELECT clause, the elements of the result are no longer guaranteed to be unique. For example, item descriptions aren t unique, so the following query may return the same description more than once: select item.description from Item item It s difficult to see how it could be meaningful to have two identical rows in a query result, so if you think duplicates are likely, you normally use the DISTINCT keyword: select distinct item.description from Item item This eliminates duplicates from the returned list of Item descriptions. Calling functions It s also (for some Hibernate SQL dialects) possible to call database specific SQL functions from the SELECT clause. For example, the following query retrieves the current date and time from the database server (Oracle syntax), together with a property of Item: select item.startDate, current_date() from Item item The technique of database functions in the SELECT clause isn t limited to database- dependent functions. it works with other more generic (or standardized) SQL functions as well: select item.startDate, item.endDate, upper(item.name) from Item item This query returns Object[]s with the starting and ending date of an item auction, and the name of the item all in uppercase. In particular, it s possible to call SQL aggregate functions, which we ll cover later in this chapter. Note, however, that the Java Persistence standard and JPA QL don t guarantee that any function that isn t an aggregation function can be called in the SELECT clause. Hibernate and HQL allow more flexibility, and we think other products that support JPA QL will provide the same freedom to a certain extent. Also note that functions that are unknown to Hibernate aren t passed on to the database as an SQL function call, as they are in the WHERE clause. You have to register a function in your org.hibernate.Dialect to enable it for the SELECT clause in HQL. The previous sections should get you started with basic HQL and JPA QL. It s time to look at the more complex query options, such as joins, dynamic fetching, subselects, and reporting queries.
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Basic HQL and JPA QL queries and operations to restrict and order the result. All you need now is the ability to project the data of this result to what you need in your application. 14.2.3 Projection The SELECT clause performs projection in HQL and JPA QL. It allows you to specify exactly which objects or properties of objects you need in the query result. Simple projection of entities and scalar values For example, consider the following HQL query: from Item i, Bid b This is a valid HQL query, but it s invalid in JPA QL the standard requires that you use a SELECT clause. Still, the same result that is implicit from this product of Item and Bid can also be produced with an explicit SELECT clause. This query returns ordered pairs of Item and Bid instances: Query q = session.createQuery(”from Item i, Bid b”); // Query q = em.createQuery(”select i, b from Item i, Bid b”); Iterator pairs = q.list().iterator(); // Iterator pairs = q.getResultList().iterator(); while ( pairs.hasNext() ) { Object[] pair = (Object[]) pairs.next(); Item item = (Item) pair[0]; Bid bid = (Bid) pair[1]; } This query returns a List of Object[]. At index 0is the Item, and at index 1 is the Bid. Because this is a product, the result contains every possible combination of Item and Bid rows found in the two underlying tables. Obviously, this query isn t useful, but you shouldn t be surprised to receive a collection of Object[] as a query result. The following explicit SELECT clause also returns a collection of Object[]s: select i.id, i.description, i.initialPrice from Item i where i.endDate > current_date() The Object[]s returned by this query contain a Longat index 0, a String at index 1, and a BigDecimal or MonetaryAmount at index 2. These are scalar values, not entity instances. Therefore, they aren t in any persistent state, like an entity instance would be. They aren t transactional and obviously aren t checked automatically for dirty state. We call this kind of query a scalar query.
Check Tomcat Web Hosting services for best quality webspace to host your web application.

CHAPTER 14 Querying with HQL and JPA QL Most of these HQL functions translate into a counterpart in SQL you ve probably used before. This translation table is customizable and extendable with an org.hibernate.Dialect. Check the source code of the dialect you re using for your database; you ll probably find many other SQL functions already registered there for immediate use in HQL. Keep in mind that every function that isn t included in the org.hibernate.Dialect superclass may not be portable to other database management systems! Another recent addition to the Hibernate API is the addSqlFunction() method on the Hibernate Configuration API: Configuration cfg = new Configuration(); cfg.addSqlFunction( “lpad”, new StandardSQLFunction(”lpad”, Hibernate.STRING) ); … cfg.buildSessionFactory(); This operation adds the SQL function lpad to HQL. See the Javadoc of Standard- SQLFunction and its subclasses for more information. HQL even tries to be smart when you call a function that wasn t registered for your SQL dialect: Any function that is called in the WHERE clause of an HQL statement, and that isn t known to Hibernate, is passed directly to the database, as an SQL function call. This works great if you don t care about database portability, but it requires that you keep your eyes open for nonportable functions if you do care. Finally, before we move on to the SELECT clause in HQL and JPA QL, let s see how results can be ordered. Ordering query results All query languages provide some mechanism for ordering query results. HQL and JPA QL provide an ORDER BY clause, similar to SQL. This query returns all users, ordered by username: from User u order by u.username You specify ascending and descending order using asc or desc: from User u order by u.username desc You may order by multiple properties: from User u order by u.lastname asc, u.firstname asc You now know how to write a FROM, WHERE, and ORDER BY clause. You know how to select the entities you want to retrieve instances of and the necessary expressions
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Basic HQL and JPA QL queries Table 14.2 Standardized JPA QL functions Function Applicability UPPER(s), LOWER(s) String values; returns a string value CONCAT(s1, s2) String values; returns a string value SUBSTRING(s, offset, length) String values (offset starts at 1); returns a string value TRIM( [[BOTH|LEADING|TRAILING] char [FROM]] s) Trims spaces on BOTH sides of s if no char or other specification is given; returns a string value LENGTH(s) String value; returns a numeric value LOCATE(search, s, offset) Searches for position of ssin s starting at offset; returns a numeric value ABS(n), SQRT(n), MOD(dividend, divisor) Numeric values; returns an absolute of same type as input, square root as double, and the remainder of a division as an integer SIZE(c) Collection expressions; returns an integer, or 0 if empty All the standardized JPA QL functions may be used in the WHERE and HAVING clauses of a query (the latter you ll see soon). The native HQL is a bit more flexible. First, it offers additional portable functions, as shown in table 14.3. Table 14.3 Additional HQL functions Function Applicability BIT_LENGTH(s) Returns the number of bits in s CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() Returns the date and/or time of the database management system machine SECOND(d), MINUTE(d), HOUR(d), DAY(d), MONTH(d), YEAR(d) Extracts the time and date from a temporal argument CAST(t as Type) Casts a given type t to a Hibernate Type INDEX(joinedCollection) Returns the index of joined collection element MINELEMENT(c), MAXELEMENT(c), MININDEX(c), MAXINDEX(c), ELEMENTS(c), INDICES(c) Returns an element or index of indexed collections (maps, lists, arrays) Registered in org.hibernate.Dialect Extends HQL with other functions in a dialect
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.