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

Joins, reporting queries, and subselects persistent class, no matter what the real property name of the identifier is. (Again, this special property isn t standardized in JPA QL.) The next query counts the number of bids and calculates the average bid per unsold item: select bid.item.id, count(bid), avg(bid.amount) from Bid bid where bid.item.successfulBid is null group by bid.item.id That query uses an implicit association join. For an explicit ordinary join in the FROM clause (not a fetch join), you can re-express it as follows: select bidItem.id, count(bid), avg(bid.amount) from Bid bid join bid.item bidItem where bidItem.successfulBid is null group by bidItem.id Sometimes, you want to further restrict the result by selecting only particular values of a group. Restricting groups with having The WHERE clause is used to perform the relational operation of restriction upon rows. The HAVING clause performs restriction upon groups. For example, the next query counts users with each last name that begins with A : select user.lastname, count(user) from User user group by user.lastname having user.lastname like ‘A%’ The same rules govern the SELECT and HAVING clauses: Only grouped properties may appear outside of an aggregate function. The next query counts the number of bids per unsold item, returning results for only those items that have more than 10 bids: select item.id, count(bid), avg(bid.amount) from Item item join item.bids bid where item.successfulBid is null group by item.id having count(bid) > 10 Most report queries use a SELECT clause to choose a list of projected or aggregated properties. You ve seen that when there is more than one property or alias
You want to have a cheap webhost for your apache application, then check apache web hosting services.

CHAPTER 14 Querying with HQL and JPA QL The next query returns the minimum and maximum bid amounts for a particular Item: select min(bid.amount), max(bid.amount) from Bid bid where bid.item.id = 1 The result is an ordered pair of BigDecimals (two instances of BigDecimals, in an Object[] array). The special COUNT(DISTINCT) function ignores duplicates: select count(distinct i.description) from Item i When you call an aggregate function in the SELECT clause, without specifying any grouping in a GROUP BY clause, you collapse the result down to a single row, containing the aggregated value(s). This means that (in the absence of a GROUP BY clause) any SELECT clause that contains an aggregate function must contain only aggregate functions. For more advanced statistics and reporting, you need to be able to perform grouping. Grouping aggregated results Just like in SQL, any property or alias that appears in HQL or JPA QL outside of an aggregate function in the SELECT clause must also appear in the GROUP BY clause. Consider the next query, which counts the number of users with each last name: select u.lastname, count(u) from User u group by u.lastname Look at the generated SQL: select u.LAST_NAME, count(u.USER_ID) from USER u group by u.LAST_NAME In this example, the u.lastname isn t inside an aggregate function; you use it to group the result. You also don t need to specify the property you like to count. The generated SQL automatically uses the primary key, if you use an alias that has been set in the FROM clause. The next query finds the average bid amount for each item: select bid.item.id, avg(bid.amount) from Bid bid group by bid.item.id This query returns ordered pairs of Item identifier and average bid amount values. Notice how you use the id special property to refer to the identifier of a
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

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.