Chapter 08 Group Query, subquery, native SQL

First, group query

Use the group BY keyword to group data, use the HAVING keyword to set constraints on grouped data, and complete data grouping and statistics

1.1 Aggregation functions: Often used to achieve data statistics functions

①count () Statistics record number of records

②sum () sum

③min () to find the minimum value

④max () to find the maximum value

⑤avg () averaging

Example: Query the average worker number for all employees

Note: The SELECT statement queries only one value, so you cannot use list () and the list is projected to be a collection. The Uniqueresult () method is available here

     /*      * Group query  All employees ' average work number         */        @Test    publicvoid  Avgtest () {        query Query=session.createquery ("Select AVG (empId) from EMP");        Object count=query.uniqueresult ();        System.out.println (count);    }

Output Result:

Second, sub-query

The subquery statement is applied to the WHERE clause of the HQL query statement.

Sub-query Keywords:

All: Any records returned by the subquery statement

Any: One of the records returned by the subquery statement

Some: and "any" mean the same

In: Same meaning as "=any"

Exists: Sub-query statement returns at least one record

Example: Query for departments where all employees pay less than 2000

     /*      * Sub   -query query all employees pay less than 2000 of     the department */        @Test    publicvoid  childtest () {        List<Dept> list=session.createquery ("from Dept D where 2000>all (select E.sal from D.emps E) and d.emps.size>0 "). List ();          for (Dept dept:list) {            System.out.println (Dept.getdeptname ());        }    }

Output Result:

Third, native SQL queries and named queries

Note: For native SQL queries, the Createsqlquery () method of the session is used to create the SQLQuery object, and the Createsqlquery () method's parameters are the SQL statements of the underlying database, and the SQLQuery interface inherits the query interface. The statement cannot return a strongly typed Dept object, which needs to be encapsulated into a Dept entity class using addentity ()

Example: Querying the Department name

     /*      * Native SQL     */    @Testpublicvoid  sqltest () {        sqlquery Query=session.createsqlquery ("SELECT * from Dept1"). Addentity (Dept.class);        List<Dept> list=query.list ();          for (Dept dept:list) {            System.out.println (Dept.getdeptname ());        }    }    

Output Result:

Named query: Defines a query statement as a string in a mapping file

To configure the SQL statement to a file

：

Chapter 08 Group Query, subquery, native SQL