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

Joins, reporting queries, and subselects 14.3.2 Reporting queries Reporting queries take advantage of the database s ability to perform efficient grouping and aggregation of data. They re more relational in nature; they don t always return entities. For example, instead of retrieving Item entities that are in persistent state (and automatically dirty checked), a report query may only retrieve the Item names and initial auction prices. If this is the only information you need (maybe even aggregated, the highest initial price in a category, and so on.) for a report screen, you don t need transactional entity instances and can save the overhead of automatic dirty checking and caching in the persistence context. HQL and JPA QL allow you to use several features of SQL that are most commonly used for reporting although they re also used for other things. In reporting queries, you use the SELECT clause for projection and the GROUP BY and HAVING clauses for aggregation. Because we ve already discussed the basic SELECT clause, we ll go straight to aggregation and grouping. Projection with aggregation functions The aggregate functions that are recognized by HQL and standardized in JPA QL are count(), min(), max(), sum() and avg(). This query counts all the Items: select count(i) from Item i The result is returned as a Long: Long count = (Long) session.createQuery(”select count(i) from Item i”) .uniqueResult(); The next variation of the query counts all Items which have a successfulBid (null values are eliminated): select count(i.successfulBid) from Item i This query calculates the total of all the successful Bids: select sum(i.successfulBid.amount) from Item i The query returns a BigDecimal, because the amount property is of type BigDecimal. The SUM() function also recognizes BigInteger property types and returns Long for all other numeric property types. Notice the use of an implicit join in the SELECT clause: You navigate the association (successfulBid) from Item to Bid by referencing it with a dot.
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 You may also want to compare a foreign key value to a query parameter, perhaps to find all Comments from a User: User givenUser = … Query q = session.createQuery( “from Comment c where c.fromUser = :user” ); q.setEntity(”user”, givenUser); List result = q.list(); Alternatively, sometimes you prefer to express these kinds of queries in terms of identifier values rather than object references. An identifier value may be referred to by either the name of the identifier property (if there is one) or the special property name id. (Note that only HQL guarantees that id always refers to any arbitrarily named identifier property; JPA QL doesn t.) These queries are equivalent to the earlier queries: from Item i, User u where i.seller.id = u.id and u.username = ’steve’ from Item i, Bid b where i.seller.id = b.bidder.id However, you may now use the identifier value as a query parameter: Long userId = … Query q = session.createQuery( “from Comment c where c.fromUser.id = :userId” ); q.setLong(”userId”, userId); List result = q.list(); Considering identifier attributes, there is a world of difference between the following queries: from Bid b where b.item.id = 1 from Bid b where b.item.description like ‘%Foo%’ The second query uses an implicit table join; the first has no joins at all! This completes our discussion of queries that involve joins. You learned how to write a simple implicit inner join with dot notation and how to write an explicit inner or outer join with aliases in the FROM clause. We also looked at dynamic fetching strategies with outer and inner join SQL operations. Our next topic is advanced queries that we consider to be mostly useful for reporting.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Joins, reporting queries, and subselects Iterator i = session.createQuery(”from User user, LogRecord log” + ” where user.username = log.username”) .list().iterator (); while ( i.hasNext() ) { Object[] pair = (Object[]) i.next(); User user = (User) pair[0]; LogRecord log = (LogRecord) pair[1]; } You can of course apply a SELECT clause to project only the data you re interested in. You probably won t need to use the theta-style joins often. Note that it s currently not possible in HQL or JPA QL to outer join two tables that don t have a mapped association theta-style joins are inner joins. Finally, it s extremely common to perform queries that compare primary key or foreign key values to either query parameters or other primary or foreign key values. Comparing identifiers If you think about identifier comparison in more object-oriented terms, what you re really doing is comparing object references. HQL and JPA QL support the following: from Item i, User u where i.seller = u and u.username = ’steve’ In this query, i.sellerrefers to the foreign key to the USERtable in the ITEM table (on the SELLER_ID column), and user refers to the primary key of the USER table (on the USER_ID column). This query uses a theta-style join and is equivalent to the much preferred from Item i join i.seller u where u.username = ’steve’ On the other hand, the following theta-style join can t be re-expressed as a FROM clause join: from Item i, Bid b where i.seller = b.bidder In this case, i.seller and b.bidder are both foreign keys of the USER table. Note that this is an important query in the application; you use it to identify people bidding for their own items.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 14 Querying with HQL and JPA QL Query execution options that are based on the SQL result rows, such as pagination with setMaxResults()/setFirstResult(), are semantically incorrect if a collection is eagerly fetched. If you have an eager fetched collection in your query, at the time of writing, Hibernate falls back to limiting the result in-memory, instead of using SQL. This may be less efficient, so we don t recommend the use of JOIN FETCH with setMaxResults()/set- FirstResult(). Future versions of Hibernate may fall back to a different SQL query strategy (such as two queries and subselect fetching) if setMaxResults()/setFirstResult() is used in combination with a JOIN FETCH. This is how Hibernate implements dynamic association fetching, a powerful feature that is essential for achieving high performance in any application. As explained in chapter 13, section 13.2.5, Optimization step by step, tuning the fetch plan and fetching strategy with queries is your first optimization, followed by global settings in mapping metadata when it becomes obvious that more and more queries have equal requirements. The last join option on the list is the theta-style join. Theta-style joins A product lets you retrieve all possible combinations of instances of two or more classes. This query returns all ordered pairs of Users and Category objects: from User, Category Obviously, this isn t usually useful. There is one case where it s commonly used: theta-style joins. In traditional SQL, a theta-style join is a Cartesian product together with a join condition in the WHERE clause, which is applied on the product to restrict the result. In HQL and JPA QL, the theta-style syntax is useful when your join condition isn t a foreign key relationship mapped to a class association. For example, suppose you store the User s name in log records, instead of mapping an association from LogRecord to User. The classes don t know anything about each other, because they aren t associated. You can then find all the Users and their LogRecords with the following theta-style join: from User user, LogRecord log where user.username = log.username The join condition here is a comparison of username, present as an attribute in both classes. If both rows have the same username, they re joined (with an inner join) in the result. The query result consists of ordered pairs:
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 If you write JOIN FETCH. without LEFT, you get eager loading with an inner join (also if you use INNER JOIN FETCH ); a prefetch with an inner join, for example, returns Item objects with their bids collection fully initialized, but no Item objects that don t have bids. Such a query is rarely useful for collections but can be used for a many-to-one association that isn t nullable; for example, join fetch item.seller works fine. Dynamic fetching in HQL and JPA QL is straightforward; however, you should remember the following caveats: You never assign an alias to any fetch-joined association or collection for further restriction or projection. So left join fetch i.bids b where b = … is invalid, whereas left join fetch i.bids b join fetch b.bidder is valid. You shouldn t fetch more than one collection in parallel; otherwise you create a Cartesian product. You can fetch as many single-valued associated objects as you like without creating a product. This is basically the same problem we discussed in chapter 13, section 13.2.5, The Cartesian product problem. HQL and JPA QL ignore any fetching strategy you ve defined in mapping metadata. For example, mapping the bids collection in XML with fetch=”join”, has no effect on any HQL or JPA QL statement. A dynamic fetching strategy ignores the global fetching strategy (on the other hand, the global fetch plan isn t ignored every nonlazy association or collection is guaranteed to be loaded, even if several SQL queries are needed). If you eager-fetch a collection, duplicates may be returned. Look at figure 14.3: This is exactly the SQL operation that is executed for a select i from Item i join fetch i.bids HQL or JPA QL query. Each Item is duplicated on the left side of the result table as many times as related Bid data is present. The List returned by the HQL or JPA QL query preserves these duplicates as references. If you prefer to filter out these duplicates you need to either wrap the List in a Set (for example, with Set noDupes = new LinkedHashSet(resultList)) or use the DISTINCT keyword: select distinctifromItemijoinfetchi.bids note that in this case the DISTINCT doesn t operate at the SQL level, but forces Hibernate to filter out duplicates in memory when marshaling the result into objects. Clearly, duplicates can t be avoided in the SQL result.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

CHAPTER 14 Querying with HQL and JPA QL and projection. The only thing that will make it more efficient is the right dynamic fetching strategy. For example, there is no reason why you need several SQL statements to fetch all Item instances and to initialize their bids collections, or to retrieve the seller for each Item. This can be done at the same time, with a join operation. In HQL and JPA QL you can specify that an associated entity instance or a collection should be eagerly fetched with the FETCH keyword in the FROM clause: from Item i left join fetch i.bids where i.description like ‘%Foo%’ This query returns all items with a description that contains the string “Foo” and all their bids collections in a single SQL operation. When executed, it returns a list of Item instances, with their bids collections fully initialized. This is quite different if you compare it to the ordered pairs returned by the queries in the previous section! The purpose of a fetch join is performance optimization: You use this syntax only because you want eager initialization of the bids collections in a single SQL operation: select i.DESCRIPTION, i.INITIAL_PRICE, … b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON from ITEM i left outer join BID b on i.ITEM_ID = b.ITEM_ID where i.DESCRIPTION like ‘%Foo%’ An additional WITH clause wouldn t make sense here. You can t restrict the Bid instances: All the collections must be fully initialized. You can also prefetch many-to-one or one-to-one associations, using the same syntax: from Bid bid left join fetch bid.item left join fetch bid.bidder where bid.amount > 100 This query executes the following SQL: select b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON i.DESCRIPTION, i.INITIAL_PRICE, … u.USERNAME, u.FIRSTNAME, u.LASTNAME, … from BID b left outer join ITEM i on i.ITEM_ID = b.ITEM_ID left outer join USER u on u.USER_ID = b.BIDDER_ID where b.AMOUNT > 100
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

Joins, reporting queries, and subselects So far, you ve only written inner joins. Outer joins are mostly used for dynamic fetching, which we ll discuss soon. Sometimes you want to write a simple query with an outer join without applying a dynamic fetching strategy. For example, the following query is a variation of the first query and retrieves items and bids with a minimum amount: from Item i left join i.bids b with b.amount > 100 where i.description like ‘%Foo%’ The first thing that is new in this statement is the LEFT keyword. Optionally you can write LEFT OUTER JOIN and RIGHT OUTER JOIN, but we usually prefer the short form. The second change is the additional join condition following the WITH keyword. If you place the b.amount > 100 expression into the WHERE clause you d restrict the result to Item instances that have bids. This isn t what you want here: You want to retrieve items and bids, and even items that don t have bids. By adding an additional join condition in the FROM clause, you can restrict the Bid instances and still retrieve all Item objects. This query again returns ordered pairs of Item and Bid objects. Finally, note that additional join conditions with the WITH keyword are available only in HQL; JPA QL supports only the basic outer join condition represented by the mapped foreign key association. A much more common scenario in which outer joins play an important role is eager dynamic fetching. Dynamic fetching strategies with joins All queries you saw in the previous section have one thing in common: The returned Item instances have a collection named bids. This collection, if mapped as lazy=”true” (default), isn t initialized, and an additional SQL statement is triggered as soon as you access it. The same is true for all single-ended associations, like the seller of each Item. By default, Hibernate generates a proxy and loads the associated User instance lazily and only on-demand. What options do you have to change this behavior? First, you can change the fetch plan in your mapping metadata and declare a collection or single-valued association as lazy=”false”. Hibernate then executes the necessary SQL to guarantee that the desired network of objects is loaded at all times. This also means that a single HQL or JPA QL statement may result in several SQL operations! On the other hand, you usually don t modify the fetch plan in mapping metadata unless you re absolutely sure that it should apply globally. You usually write a new fetch plan for a particular use case. This is what you already did by writing HQL and JPA QL statements; you defined a fetch plan with selection, restriction,
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

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.