Hibernate -- HQL, QBC retrieval method, hqlqbc

Hibernate -- HQL, QBC retrieval method, hqlqbc

I. HQL Retrieval Method

Test the HQL retrieval method using a bidirectional one-to-multiple method. Take Department and Employee as examples.

Table creation statement:

CREATE TABLE department(    dept_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,    dept_name VARCHAR(50));CREATE INDEX FK_8hf3vewo7w3v9doungcc51wwy ON department (dept_id);

CREATE TABLE employee(    emp_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,    emp_name VARCHAR(50),    salary FLOAT,    dept_id_fk INT(11),    CONSTRAINT FK_miun1wlqp4ujpsgfshyfi7g9j FOREIGN KEY (dept_id_fk) REFERENCES department (dept_id));CREATE INDEX FK_miun1wlqp4ujpsgfshyfi7g9j ON employee (dept_id_fk);

Corresponding entity and hbm File

public class Department {    private Integer deptId;    private String deptName;    private Set<Employee> emps = new HashSet<>();}    

public class Employee {    private Integer empId;    private String empName;    private Float salary;    private Department dept;}

<Hibernate-mapping> <class name = "com. solverpeng. hql. department "table =" department "schema =" hibernate "> <id name =" deptId "column =" dept_id "> <generator class =" native "/> </id> <property name = "deptName" column = "dept_name"/> <set name = "emps" inverse = "true"> <key> <column name = "dept_id_fk"/> </ key> <one-to-operate not-found = "ignore" class = "com. solverpeng. hql. employee "/> </set> </class> 1. Set various query conditions in the query statement

@Testpublic void testHql(){    Department dept = new Department();    dept.setDeptId(7);    List<Employee> list = session.createQuery("FROM Employee e where e.empName like ? and e.empId > ? and dept = ? order by e.empId " +            "desc ")            .setString(0, "%b%").setInteger(1, 3).setEntity(2, dept).list();    for(Employee employee : list) {        System.out.println(employee);    }}

Note:

(1) create a Query object using the createQuery (hql) method of the Session. hql supports dynamic parameter binding. Call the Query method to execute the Query.

(2) The Query interface supports chained operations. Its setXxx () method returns its own instance.

(3) method setEntity (obj), obj only needs to bind an id.

(4) order by sorting is supported.

(5) The parameter starts from 0.

@Testpublic void testHqlNamed() {    List<Employee> list = session.createQuery("from Employee e where e.empName like :name and e.empId > :id and e.dept = ?")            .setString("name", "%a%").setInteger("id", 1).list();    for(Employee employee : list) {        System.out.println(employee);    }}

Note:

(1) query by parameter name is supported. The defined parameter name starts.

2. Partial attributes of the query object (the query result only contains partial attributes of the object)

@Testpublic void testPropertyQuery() {    Department dept = new Department();    dept.setDeptId(7);    List<Object[]> list = session.createQuery("select empName, empId from Employee where dept = ?").setEntity(0, dept).list();    for(Object[] objects : list) {        System.out.println(Arrays.asList(objects));    }}

Note:

(1) In this case, an Object [] array is queried.

@Testpublic void testPropertyQuery2() {    Department dept = new Department();    dept.setDeptId(7);    List<Employee> list = session.createQuery("select new Employee (empId, empName) from Employee where dept = ?").setEntity(0, dept)            .list();    for(Employee employee : list) {        System.out.println(employee);    }}

(1) The queried data is of the Employee type.

(2) You need to define the corresponding constructor in the Employee object class. Pay attention to the order. Add a constructor without parameters.

(3) The Distinct keyword can be used for deduplication.

3. Paging Query

@Testpublic void testHqlPage() {    int pageNo = 2;    int pageSize = 3;    List<Employee> list = session.createQuery("from Employee").setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).list();    for(Employee employee : list) {        System.out.println(employee);    }}

(1) setFirstResult (int firstResult): Specifies the object from which to start retrieval.

(2) setMaxResults (int maxResult): set the number of records retrieved each time.

4. name query (rarely used)

@Testpublic void testNamedQuery() {    int pageNo = 3;    int pageSize = 5;    List<Employee> employees = session.getNamedQuery("findAllEmployees").setFirstResult((pageNo - 1) * pageSize).setMaxResults(            pageSize).list();    for(Employee employee : employees) {        System.out.println(employee);    }}

Note:

(1) findAllEmployees is defined in the Employee. hbm. xml file and is defined using the <query> element, which is tied with the class node.

(2) Use the getNamedQuery () method of the Session to obtain the corresponding Query object.

5. Aggregate functions and group queries

@Testpublic void testFunction() {    List<Object[]> salary =            session.createQuery("select dept.deptName, min(salary), max(salary) from Employee group by dept HAVING min(salary) > :salary")                    .setFloat("salary", 4000).list();    for(Object[] objects : salary) {        System.out.println(Arrays.asList(objects));    }}

Note:

(1) GROUP data through group by and set constraints on GROUP data through HAVING.

(2) Aggregate functions that can be called: count (), min (), max (), sum (), avg ()

6. Urgent left outer link and left outer link

(1) Urgent left outer link

@Testpublic void testHqlFetch() {    List list = session.createQuery("from Department d left join fetch d.emps").list();}

Print SQL:

Hibernate: 
    select
        department0_.dept_id as dept1_0_0_,
        emps1_.emp_id as emp1_1_1_,
        department0_.dept_name as dept2_0_0_,
        emps1_.emp_name as emp2_1_1_,
        emps1_.salary as salary3_1_1_,
        emps1_.dept_id_fk as dept4_1_1_,
        emps1_.dept_id_fk as dept4_0_0__,
        emps1_.emp_id as emp1_1_0__ 
    from
        hibernate.department department0_ 
    left outer join
        hibernate.employee emps1_ 
            on department0_.dept_id=emps1_.dept_id_fk

Note:

• The Employee object is also queried.
• Reference of the Entity objects stored in the collection returned by the list () method. Each Department-associated Employee set is initialized.
• The distinct keyword can be used to remove duplicates, or a HashSet () can be used to remove duplicates (new ArrayList <> (new partition HashSet (depts ))).
• In this case, the retrieval policy in the configuration file is ignored.

(2) left outer link

@Testpublic void testHqlLeftJoin2() {    List<Object[]> list = session.createQuery("from Department d left join d.emps").list();    for(Object[] objects : list) {        System.out.println(Arrays.asList(objects));    }}

Hibernate:     select        department0_.dept_id as dept1_0_0_,        emps1_.emp_id as emp1_1_1_,        department0_.dept_name as dept2_0_0_,        emps1_.emp_name as emp2_1_1_,        emps1_.salary as salary3_1_1_,        emps1_.dept_id_fk as dept4_1_1_     from        hibernate.department department0_     left outer join        hibernate.employee emps1_             on department0_.dept_id=emps1_.dept_id_fk

Note:

• The collection returned by the list () method stores the object array type.
• The initialization time of the Employee set is determined based on the configuration file.

7. Urgent internal connection and internal connection

(1) inner join fetch is similar to the urgent left outer link. objects at the other end of the join are initialized during query.

(2) inner join, similar to the left outer link, is used to determine the initialization time of the other end based on the retrieval policy in the configuration file.

8. Summary

(1) If no explicit search policy is specified in HQL, use the search policy in the configuration file.

(2) HQL ignores the urgent left outer link search policy set in the configuration file. If you want HQL to adopt an urgent left outer link search policy, you must explicitly specify it in the HQL statement.

Ii. QBC Search Method

1. Set various query Conditions

(1) like, gt, sorting

@ Testpublic void testQBC () {Criteria criteria = session. createCriteria (Employee. class); criteria. add (Restrictions. like ("empName", "a", MatchMode. ANYWHERE); criteria. add (Restrictions. gt ("salary", 1000F); // sort criteria. addOrder (Order. desc ("salary"); List list = criteria. list (); System. out. println (list );}

Hibernate:     select        this_.emp_id as emp1_1_0_,        this_.emp_name as emp2_1_0_,        this_.salary as salary3_1_0_,        this_.dept_id_fk as dept4_1_0_     from        hibernate.employee this_     where        this_.emp_name like ?         and this_.salary>?     order by        this_.salary desc

(2) and, or

public void testQbc2() {    Criteria criteria = session.createCriteria(Employee.class);    Conjunction conjunction = Restrictions.conjunction();    conjunction.add(Restrictions.like("empName", "a", MatchMode.ANYWHERE));    Department department = new Department();    department.setDeptId(6);    conjunction.add(Restrictions.eq("dept", department));    Disjunction disjunction = Restrictions.disjunction();    disjunction.add(Restrictions.gt("salary", 1000F));    disjunction.add(Restrictions.lt("salary", 20000F));    criteria.add(conjunction).add(disjunction);    criteria.list();}

Hibernate:     select        this_.emp_id as emp1_1_0_,        this_.emp_name as emp2_1_0_,        this_.salary as salary3_1_0_,        this_.dept_id_fk as dept4_1_0_     from        hibernate.employee this_     where        (            this_.emp_name like ?             and this_.dept_id_fk=?        )         and (            this_.salary>?             or this_.salary<?        )

2. Paging Query

@ Testpublic void testQbc4 () {Criteria criteria = session. createCriteria (Employee. class); // pagination int pageNo = 2; int pageSize = 4; List <Employee> list = criteria. setFirstResult (pageNo-1) * pageSize ). setMaxResults (pageSize ). list ();}

Hibernate:     select        this_.emp_id as emp1_1_0_,        this_.emp_name as emp2_1_0_,        this_.salary as salary3_1_0_,        this_.dept_id_fk as dept4_1_0_     from        hibernate.employee this_ limit ?,        ?

3. Aggregate Function Query

@Testpublic void testQbc3() {    Criteria criteria = session.createCriteria(Employee.class);    criteria.setProjection(Projections.max("salary"));    String maxSalary = criteria.uniqueResult().toString();    System.out.println(maxSalary);}

Hibernate:     select        max(this_.salary) as y0_     from        hibernate.employee this

4. Summary

(1) create a QBC query: session. createCriteria ()

(2) like Keyword: Restrictions. like (), MatchMode. ANYWHERE

(3) sorting: criteria. addOrder (), Order. desc, Order. asc

(4) AND: Conjunction conjunction = Restrictions. conjunction ()

(5) Or: Disjunction disjunction = Restrictions. disjunction ()

(6) clustering function: criteria. setProjection (Projections. max ("salary "))

(7) query a single object: criteria. uniqueResult ()

(8) query object list: criteria. list ()

Iii. Local SQL

@Testpublic void testNativeSql() {    Employee employee = new Employee();    employee.setEmpId(5);    String empName = (String) session.createSQLQuery("SELECT emp_name FROM employee where emp_id = ?")            .setEntity(0, employee).uniqueResult();    System.out.println(empName);}

Hibernate:     SELECT        emp_name     FROM        employee     where        emp_id = ?bb2

You can use session. createSQLQuery () to create a local SQL query object.

Iv. HQL update operations

@Testpublic void testHqlUpdate() {    session.createQuery("delete from Employee where empId = ?").setInteger(0, 13).executeUpdate();}

Hibernate:     delete     from        hibernate.employee     where        emp_id=?

V. Summary

This section describes HQL, QBC, and local SQL queries. Query objects are created through sessions. Session. createQuery (), session. createCriteria (), and session. createSQLQuery ().

QBC provides a more thorough and Java-oriented method than HQL. When learning HQL, you need to focus on the urgent left outer link. Localized query is a supplement to HQL.

When learning, pay attention to comparative learning.

For more information, see documentation/manual/en-US/html_single/index.html.