Hibernate notes HQL query condition query, aggregate function, subquery, navigation query

This note continues to use the Dept Department table, EMP staff table, one-to-many pair of bidirectional mappings.

1 Conditional query

1.1 Query Employee Form EMP Age eage less than 30, monthly esal greater than 20000 employee name Ename

Sql:select ename from EMP where eage<? >?; Esal

Hql:select ename from Emp where eage<? and esal;?

1.2 Settings and aliases for question marks

Question mark (? ) is set using the. Setparameter (position, property value) The position of the question mark in the SQL statement is starting at 1 and starting from zero in HQL.

In hql, the question mark (? ) can also be used, as follows: Immediately following the alias, Setparameter ("age", 30) method changes, the original number is changed to "Alias", as shown below

String hql= "Select ename from Emp where Eage<:age and esal >:sal";
Query query= session.createquery (HQL). Setparameter ("Age", "+"). Setparameter ("Sal", 20000f). List ();

1.3 Setting of a set of question marks

You can also set aliases for a set of question marks: for example, to query employee names in 1, 2 departments

Hql= "Select ename from Emp where do in (?,?)";

Hql= "Select ename from Emp where do in (:D)";

Query query= session.createquery (HQL). Setparameter ("D", New object[]{1,2}). List ();

 Public voidTest9 ()throwsexception{
Configuration config=NewConfiguration (). Configure ();
Sessionfactory sessionfactory= config.buildsessionfactory ();
Session session=NULL;
Transaction tr=NULL;
        Try{
Session= sessionfactory.opensession ();
Tr=session.begintransaction ();
String hql= "Select ename from Emp where eage<? and esal;?";
Query query= session.createquery (HQL). Setparameter (0,). Setparameter (1, 20000f); Set the question mark, starting with 0.
list<string> List = Query.list (); 
For  (String message:list) {            
System.out.println (message); 
            }                                                                                      
Tr.commit ();
}Catch(Exception e) {
Tr.rollback ();
}finally{
            if(session!=NULL){
Session.close ();
}
            if(sessionfactory!=NULL){
Sessionfactory.close ();
}
}
}

Copy Code

Results:

Hibernate:select Emp0_.ename as col_0_0_ from EMP emp0_ where emp0_.eage<? and Emp0_.esal>?
L
Bai Bai Ho
Article
Lin

2 commonly used keywords for conditional queries

2.1 Distinct filter for duplicate values

Query all employee names in the Employee table and remove duplicate values

Hql= "SELECT distinct ename from EMP";

2.2 Delete Deletion

Delete an employee older than 25

hql= "Delete Emp where eage>25"; Delete users older than 25 years

Query query=session.createquery (HQL);

Query.executeupdate (); Executes the Executeupdate method to return the int type.

Tx.commit (); Success, then commit, operation on database

2.3 Update Updates

The age of update employee number 11 is 22

String hql= "Update Emp s set s.eage= ' where s.eid=11"; UPDATE statement

Query query=session.createquery (HQL);

Query.executeupdate (); Perform

Tx.commit (); Successful, then submit

2.4 Between...and ... And not between ... and ... determine query scope

Find employee names from 20 to 30 in the employee table

Hql= "Select ename from Emp where eage between and 30"

2.5 in and not in determine query collection

Find employee names that are part of the low-cost department

Hql= "Select ename from Emp where do in (UP)"

2.6 Like for fuzzy query

There are two wildcard characters available when you use like for fuzzy queries: "%" and "_". "%" represents a character greater than or equal to 0, and "_" represents a single character with a length of 1.

Find employees with Liu in their names in the employee table

Hql= "Select ename from Emp where ename like '% Liu '"

2.7 logic with and logic or or

Find employees whose salary is greater than 2000 and whose age is less than 30

Hql= "Select ename from Emp where esal>2000 and eage<30";

Find employees who are older than 40 or younger than 30

Hql= "Select ename from Emp where eage>40 or eage<30";

2.8 Order By to sort the results

A sort of salary, from small to large

Hql= "Select Esal from EMP where order by esal ASC";

Sort of age, flashbacks,

Hql= "Select ename from EMP where order by eage DESC";

2.9 Group by Group Records

To find out the average salary of each group according to the Department group

Hql= "SELECT AVG (esal) from EMP Group by Do";

2.10 Having to filter groups

Identify employee names with an average salary greater than 10000 for the employee group according to the Department group

Hql= "Select ename from EMP GROUP by do having AVG (esal) >10000";

3 Aggregation functions

Query the number of employees in the EMP employee table.

Sql:select count (ename) from EMP;

Hql:select count (ename) from EMP;

Aggregation Functions and meanings:

Hql:select avg (esal) from EMP; Salary average

Hql:select Max (esal) from EMP; Salary Maximum Value

Hql:select sum (esal) from EMP; Minimum wage value

     Public voidTest91 ()throwsexception{
Configuration config=NewConfiguration (). Configure ();
Sessionfactory sessionfactory= config.buildsessionfactory ();
Session session=NULL;
Transaction tr=NULL;
        Try{
Session= sessionfactory.opensession ();
Tr=session.begintransaction ();
String hql= "SELECT count (ename) from EMP";
Object count=session.createquery (HQL). Uniqueresult (); The Uniqueresult () method returns the type of object
System.out.println (count);
                       
Tr.commit ();
}Catch(Exception e) {
Tr.rollback ();
}finally{
            if(session!=NULL){
Session.close ();
}
            if(sessionfactory!=NULL){
Sessionfactory.close ();
}
}
}

Copy Code

Results:

Hibernate:select count (emp0_.ename) as col_0_0_ from EMP emp0_
15

4 Sub-query

Hibernate supports subqueries, the so-called subquery is, to query the fields and information in a table, conditions in table B. syntax is similar to SQL statements.

 Public voidTest9 ()throwsexception{
Configuration config=NewConfiguration (). Configure ();
Sessionfactory sessionfactory= config.buildsessionfactory ();
Session session=NULL;
Transaction tr=NULL;
        Try{
Session= sessionfactory.opensession ();
Tr=session.begintransaction ();

           String hql= "Select ename from Emp where do in (select Do from Dept where daddress = 302)";
Query query= session.createquery (HQL); 
list<string> List = Query.list (); 
For  (String message:list) {            
System.out.println (message); 
            }                                                                                               
Tr.commit ();
}Catch(Exception e) {
Tr.rollback ();
}finally{
            if(session!=NULL){
Session.close ();
}
            if(sessionfactory!=NULL){
Sessionfactory.close ();
}
}
}

Copy Code

Results:

Hibernate:select Emp0_.ename as col_0_0_ from EMP emp0_ where do in (select Dept1_.did from dept dept1_ where Dept1_.dad dress=302)
Li Shimin
Cao cao
and Shen
Liu Poems

5 Navigating queries

e.dept.daddress=301

The location of the employee's department is 301.

 Public voidTest9 ()throwsexception{
Configuration config=NewConfiguration (). Configure ();
Sessionfactory sessionfactory= config.buildsessionfactory ();
Session session=NULL;
Transaction tr=NULL;
        Try{
Session= sessionfactory.opensession ();
Tr=session.begintransaction ();

String hql= "Select ename from Emp e where e.dept.daddress=301";
Query query= session.createquery (HQL); 
list<string> List = Query.list (); 
For  (String message:list) {            
System.out.println (message); 
            }                                                                
Tr.commit ();
}Catch(Exception e) {
Tr.rollback ();
}finally{
            if(session!=NULL){
Session.close ();
}
            if(sessionfactory!=NULL){
Sessionfactory.close ();
}
}
}

Copy Code

Results:

Hibernate:select Emp0_.ename as col_0_0_ from EMP emp0_, dept dept1_ where Emp0_.did=dept1_.did and dept1_.daddress=301
Andy lau
Helong
Bai Bai Ho
Article
Lin