CHAPTER 14 Querying with HQL (Professional web hosting) and JPA QL
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.