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