Joins, reporting queries, and subselects 14.3.2 (Web hosting account) Reporting queries
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.