www.developer.com/java/ent/article.php/3322131

Back to Article

Introducing HQL: The Object-Oriented Query Language from Hibernate March 5, 2004 Aggregate Functions Aggregate functions, when used in HQL queries, return an aggregate value (such as sum, average, and count) calculated from property values of all objects satisfying other query criteria. These functions can be used along with the distinct and all options, to return aggregate values calculated from only distinct values and all values (except null values), respectively. Following is a list of aggregate functions with their respective syntax; all of them are self-explanatory. count( [ distinct | all ] object | object.property ) count(*)     (equivalent to count(all ...), counts null values also) sum ( [ distinct | all ] object.property) avg( [ distinct | all ] object.property) max( [ distinct | all ] object.property) min( [ distinct | all ] object.property) Example: select sum(emp.sal) from comp.Emp as emp Subqueries Subqueries are queries within queries, surrounded by parentheses (). Subqueries get executed before the main query and are used to provide criteria for grouping/ordering/aggregating or narrowing query result with where clause. It should be noted that HQL queries might contain subqueries only when subqueries are supported by the underlying database. Example: select dept.emp_name from comp.Dept as dept where dept.emp_sal > (select avg(dept.emp_sal) from dept) For more information on HQL elements, refer to Hibernate's Web site. Note: HQL queries, except classnames and variables used in them, are case-insensitive. In other words, select is same as SELECT or even select; although it is a general practice to write HQL queries in lowercase for the sake of better readability. HQL at Work HQL can be used in any one of the following ways in the Hibernate-based persistence layer code of application: Using the createQuery() method of a Session object that returns a Query object. First, instantiate the Session object using the openSession() method of SessionFactory. Then, invoke the createQuery() method on the resulting object. Example: Session session; Query q1 = session.createQuery("select emp.sal from comp.Emp as emp"); List result = q1.list(); Here, the Query object q1 uses the list() method to return a List object containing a list of query results. Alternatively, the same task can be accomplished using the following code: List result = session.createQuery("select emp.sal from comp.Emp as emp").list(); Or simply, session.createQuery("select emp.sal from comp.Emp as emp").list(); Using Criteria API (for QBC and QBE). Here, instead of hand-coded HQL, Criteria API allows specifying various query criteria and executes the equivalent HQL query behind the scenes. First, a Criteria object is obtained by invoking the Session object's createCriteria() method, providing a class of object(s) desired to be returned as a query result, as a method argument. Now, the query criteria can be specified by using various methods of the Criteria object, for instance: add( criteria_expression): for specifying general query criteria such as ordering/grouping of query results (as with the order by/group by clauses), or conditional expressions (as in the where clause). setMaxResults(no_of_result): for specifying a maximum limit of query results. uniqueResult(): to return only non-duplicate query results. list(): for returning a List object containing all query results. Note: The Query object also consists of similar query criteria-specifying methods, such as Criteria, that can be used make the query code look clean and less verbose. Example: Session session; //some code List result = session.createCriteria(Emp.class) .add(Expression.lt("emp_no", empno) /*emp_no < empno*/ .add(Order.desc(emp_sal)) .list(); These two methods can be used to perform query operations with HQL in the persistence layer code of the application. They also support native SQL queries in place of HQL. This approach helps you to fall back on native SQL in cases where HQL doesn't provide certain features required but are available through native SQL only. Conclusion Hibernate Query Language (HQL) is a rich and powerful object-oriented query language available with the Hibernate O/R Mapping Framework. This query language, designed as a "minimal object-oriented extension to SQL," allows you to represent SQL queries in object-oriented terms—by using objects and properties of objects. HQL provides many advanced features compared to SQL, yet is easier to learn and use as its syntax and basic features are very similar to SQL. It facilitates writing database-type independent queries that are converted to a local SQL dialect of the underlying database at runtime. About the Author Mugdha Chauhan (formerly Mugdha Vairagade) is a senior IT consultant and author. An Open Source supporter, she frequently writes articles and tutorials on useful emerging Open Source projects. Major tech portals including developer.com, IBM developerWorks, CNET Networks, Slashdot, and many eZines regularly publish her work. Her expertise and interests include Java, Linux, XML, wireless application development, and Open Source.

Search:

WebMediaBrands Corporate Info

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs

Solutions

Whitepapers and eBooks

Microsoft Articles: Visual Studio 2010 Adobe Article: Adobe Helps PHP Developers Create Rich Internet Applications Microsoft Whitepaper: How Windows Server 2008 R2 Helps Optimize IT and Save You Money Adobe Article: Java Developers Finding a Home at Adobe Flex IBM Articles: A Smarter Business Needs Smarter Technology Intel Xeon Processor Increases Server Efficiency and Capabilities Intel Tech Brief: Automated Energy Efficiency for the Intelligent Enterprise Oracle Whitepapers - Innovation for IT Leaders Avaya Article: Communication-Enabled Mashups--Empowering Both Business Owners and IT

Internet.com eBook: IT Manager's Guide to Social Networking Internet.com eBook: Get Ready for Windows 7 Microsoft Article: Expression Web 3--New Features for PHP Developers Whitepapers: Manage Your Business Infrastracture with IBM Whitepaper: Maximize Your Storage Investment with HP Internet.com eBook: Becoming a Better Project Manager Microsoft Article: Stress Free and Efficient Designer/Developer Collaboration MORE WHITEPAPERS, EBOOKS, AND ARTICLES

Webcasts

Webcast: Connecting PHP to Microsoft Technologies Video: Microsoft Partner Program Benefits Video: Windows Azure Debugging Tips SAP BusinessObjects Webcast: Unlock the Power of Reporting with Crystal Reports

IBM Webcast: Speed Business Innovation with Reduced Cost and Risk Video: Deploy Applications on Windows Azure MORE WEBCASTS, PODCASTS, AND VIDEOS

Downloads and eKits

Download: Microsoft Visual Studio 2010 Beta 2 Downloads & Free Trials: Microsoft's New Efficiency Resource Center Get the Windows 7 SDK

Free Trial: Red Gate SQL Backup Pro 6 Iron Speed Designer Application Generator MORE DOWNLOADS, EKITS, AND FREE TRIALS

Tutorials and Demos

Internet.com Hot List: Get the Inside Scoop on the Hottest IT and Developer Products All About Botnets

MORE TUTORIALS, DEMOS AND STEP-BY-STEP GUIDES