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