一离例、Hibernate三種查詢方式

問(wèn)題：查詢員工名字中含有a字符案腺，且id在1~10之間的員工

1狐援、HQL：使用面向?qū)ο蟮牟樵冋Z(yǔ)句

• 推薦使用的方式（在查詢普通簡(jiǎn)單結(jié)果的時(shí)候使用）
• 寫(xiě)SQL栏账，把SQL中的屬性變成對(duì)象的屬性

    @Test
    public void HQLTest() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.name LIKE :name AND e.id BETWEEN :fv AND :lv";
        Query query = session.createQuery(hql)
                .setParameter("name", "%a%")
                .setParameter("fv", 1L)
                .setParameter("lv", 10L);
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e:list) {
            System.out.println(e);
        }
    }

2梁钾、SQL：在Hibernate當(dāng)中也可以使用SQL來(lái)查詢

• 推薦使用的方式（在查詢復(fù)查報(bào)表的時(shí)候使用武学，提示性能）

    public void SQLTest() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String sql = "SELECT e.* FROM Employee e WHERE e.name LIKE :name AND e.id BETWEEN :fv AND :lv";
        SQLQuery query = session.createSQLQuery(sql);
        query.setString("name", "%a%")
                .setLong("fv", 1L)
                .setLong("lv", 10L);
        List<Object[]> list = query.list();
        session.getTransaction().commit();

        for (Object[] obj:list) {
            System.out.println(Arrays.toString(obj));
        }
    }

3祭往、Criteria：一種完全面向?qū)ο蟮牟樵兎绞?/h4>

• 使用Restrictions來(lái)設(shè)置查詢限制條件，不推薦使用

    @Test
    public void CriteriaTest() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        Criteria criteria = session.createCriteria(Employee.class);
        Criterion like = Restrictions.like("name", "%a%");
        criteria.add(like)
                .add(Restrictions.between("id", 1L, 10L));
        List<Employee> list = criteria.list();
        session.getTransaction().commit();

        for (Employee e :list) {
            System.out.println(e);
        }
    }

二火窒、數(shù)據(jù)

• Department

package com.revanwang.common;


import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class Department {
    private Long        id;
    private String      name;
    private String      provice;
    private String      city;
    private String      street;
    private String      sn;

    private Employee    manager;//MANAGER_ID

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", provice='" + provice + '\'' +
                ", city='" + city + '\'' +
                ", street='" + street + '\'' +
                ", sn='" + sn + '\'' +
                '}';
    }
}

• Employee

package com.revanwang.common;

import lombok.Getter;
import lombok.Setter;

import java.math.BigDecimal;
import java.util.Date;

@Setter
@Getter
public class Employee {
    private Long        id;
    private String      name;
    private BigDecimal  salary;
    private Date        hireDate;

    private Department  dept;   // DEPT_ID

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", hireDate=" + hireDate +
                '}';
    }
}

• Phone

package com.revanwang.common;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class Phone {
    private Long        id;
    private PhoneType   types;
    private String      number;

    private Employee    employee;   //EMPLOYEE_ID

    @Override
    public String toString() {
        return "Phone{" +
                "id=" + id +
                ", types='" + types + '\'' +
                ", number='" + number + '\'' +
                '}';
    }
}

• PhoneType

package com.revanwang.common;

public enum PhoneType {
    CELL, WORK
}

• Project

package com.revanwang.common;

import lombok.Getter;
import lombok.Setter;

import java.util.ArrayList;
import java.util.List;

@Setter
@Getter
public class Project {
    private Long        id;
    private String      name;

    private Employee    manager;    //MANAGER_ID
    private List<Employee> emps = new ArrayList<>();//many2many

}

• common.xml

<?xml version="1.0"?>
        <!DOCTYPE hibernate-mapping PUBLIC
                "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.revanwang.common">

    <!-- Department -->
    <class name="Department">
        <id name="id">
            <generator class="native"></generator>
        </id>
        <property name="name"/>
        <property name="provice"/>
        <property name="city"/>
        <property name="street"/>
        <property name="sn"/>

        <many-to-one name="manager" column="MANAGER_ID"/>
    </class>

    <!-- Employee -->
    <class name="Employee">
        <id name="id">
            <generator class="native"></generator>
        </id>
        <property name="name"/>
        <property name="salary" column="salay"/>
        <property name="hireDate" type="date"/>

        <many-to-one name="dept" column="DEPT_ID"/>
    </class>

    <!-- Phone -->
    <class name="Phone">
        <id name="id">
            <generator class="native"></generator>
        </id>
        <property name="types">
            <type name="org.hibernate.type.EnumType">
                <param name="enumClass">com.revanwang.common.PhoneType</param>
                <param name="useNamed">true</param>
            </type>
        </property>
        <property name="number"/>

        <many-to-one name="employee" column="EMPLOYEE_ID"/>
    </class>

    <!-- Phone -->
    <class name="Project">
        <id name="id">
            <generator class="native"></generator>
        </id>
        <property name="name"/>

        <many-to-one name="manager" column="MANAGER_ID"/>
        <bag name="emps" table="project_employee">
            <key column="PROJECT_ID"></key>
            <many-to-many column="EMPLOYEE_ID" class="Employee"/>
        </bag>
    </class>

</hibernate-mapping>

三链沼、HQL

1、HQL中的參數(shù)占位符

問(wèn)題1：查詢姓名中帶有a字符

• 使用"?"占位符

    public void test1() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.name LIKE ?";
        Query query = session.createQuery(hql)
                .setParameter(0, "%a%");
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e:list) {
            System.out.println(e);
        }
    }

• 當(dāng)有相同條件時(shí)

    public void test1() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.name LIKE ? AND e.name LIKE ?";
        Query query = session.createQuery(hql)
                .setParameter(0, "%a%")
                .setParameter(1, "%a%");
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e:list) {
            System.out.println(e);
        }
    }

1.1使用"?"占位符

• ?：代表?號(hào)的位置需要一個(gè)參數(shù)沛鸵，使用Query.setParameter(index, value); 來(lái)設(shè)置值括勺，索引號(hào)index值從0開(kāi)始
• 使用?占位符缆八，使用比較簡(jiǎn)單，但是參數(shù)位置如果發(fā)送變化或者遇到相同的參數(shù)疾捍，必須每次都重新設(shè)置值

1.2使用名稱占位符

• 1.2.1 在需要參數(shù)的地方奈辰，使用:參數(shù)名稱就可以添加一個(gè)名稱占位符
• 1.2.2 使用query.setParameter("參數(shù)名稱", value)的防護(hù)四設(shè)置參數(shù)值
• 1.2.3 參數(shù)修改，只要參數(shù)的名稱不變乱豆，代碼不變奖恰。在HQL中，使用相同名稱的參數(shù)只需要設(shè)置一次值就可以了
• 1.2.4 使用名稱占位符可以直接使用參數(shù)集合宛裕，用于in條件的設(shè)置

    public void test2() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.name LIKE :name AND e.name LIKE :name";
        Query query = session.createQuery(hql).setParameter("name", "%a%");
        List<Employee> list = query.list();

        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

2瑟啃、名稱占位符直接使用參數(shù)集合

問(wèn)題2、查詢salary等于 5000揩尸、6000蛹屿、7000、8000 的員工

    /**
     * 查詢salary等于 5000岩榆、6000错负、7000、8000 的員工
     */
    @Test
    public void test3() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.salary in (:salarys)";
        List<Employee> list = session.createQuery(hql)
                .setParameterList("salarys", new BigDecimal[]{new BigDecimal("5000"), new BigDecimal("6000"), new BigDecimal("7000"), new BigDecimal("8000")})
                .list();
        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

3勇边、直接設(shè)置對(duì)象

• 1犹撒、在Hibernate參數(shù)中，如果比較的是實(shí)體對(duì)象粒褒，可以直接使用對(duì)象的比較识颊，Hibernate會(huì)自動(dòng)的翻譯為外鍵外鍵和主鍵的比較
• 2、在比較實(shí)體的時(shí)候奕坟，可以使用setEntity方法來(lái)設(shè)置實(shí)體參數(shù)值.setEntity方法對(duì)于位置參數(shù)谊囚，和名稱占位符都有效

問(wèn)題3、查詢执赡，id為1的部門(mén)里面的員工

• 使用id

    /**
     * 查詢镰踏，id為1的部門(mén)里面的員工
     */
    @Test
    public void test4() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.dept.id = :deptID";
        Query query = session.createQuery(hql).setParameter("deptID", 1L);
        List<Employee> list = query.list();

        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

• 直接使用對(duì)象

    /**
     * 查詢，id為1的部門(mén)里面的員工
     * 使用 實(shí)體
     */
    @Test
    public void test5() {
        Department department = new Department();
        department.setId(1L);

        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.dept = :dept";
        Query query = session.createQuery(hql).setParameter("dept", department);
        List<Employee> list = query.list();

        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

四沙合、HQL分頁(yè)查詢

• setFirstResult設(shè)置當(dāng)前頁(yè)
• setMaxResult設(shè)置當(dāng)前頁(yè)個(gè)數(shù)

    /**
     * 分頁(yè)查詢
     */
    @Test
    public void test6() {
        //查詢頁(yè)數(shù)
        Integer currentPage = 6;
        //每一頁(yè)個(gè)數(shù)
        Integer pageSize = 3;

        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e";
        Query query = session.createQuery(hql)
                .setFirstResult((currentPage - 1) * pageSize)   //當(dāng)前頁(yè)的開(kāi)始索引
                .setMaxResults(pageSize);   //每頁(yè)個(gè)數(shù)
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e:list) {
            System.out.println(e);
        }
    }

uniqueResult獲取唯一記錄

• 獲取表個(gè)數(shù)

    /**
     * 獲取唯一記錄
     * 使用傳統(tǒng)方式獲取
     */
    @Test
    public void test7() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT COUNT(e) FROM Employee e";
        Query query = session.createQuery(hql);
        List list = query.list();

        session.getTransaction().commit();

        System.out.println(list.get(0));
    }

• uniqueResult

    /**
     * 獲取唯一記錄
     * uniqueResult
     */
    @Test
    public void test8() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT COUNT(e) FROM Employee e";
        Query query = session.createQuery(hql);
        
        //獲取唯一結(jié)果
        Object row = query.uniqueResult();

        session.getTransaction().commit();

        System.out.println(row);
    }

五奠伪、HQL查詢

1、參數(shù)占位符

1.1首懈、 ?占位符

绊率？占位符特點(diǎn)：

• 使用簡(jiǎn)單
• 如果參數(shù)的位置變化，參數(shù)索引會(huì)變化
• 相同的參數(shù)只能手動(dòng)重復(fù)設(shè)置
• 沒(méi)法作為 in 的參數(shù)

問(wèn)題1：查詢工資大于5000的員工

    /**
     * 查詢工資大于5000的員工
     */
    @Test
    public void test1() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.salary > ?";
        Query query = session.createQuery(hql).setParameter(0, new BigDecimal("5000"));
        List<Employee> list = query.list();

        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

問(wèn)題2：查詢id為3究履、5滤否、7的員工信息

/**
     * 查詢id為3,5,7的員工信息
     */
    @Test
    public void test2() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.id = ? or e.id = ? or e.id = ?";
        Query query = session.createQuery(hql)
                .setParameter(0, 3L)
                .setParameter(1, 5L)
                .setParameter(2, 7L);
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

問(wèn)題3：查詢部門(mén)ID為1的員工信息

    /**
     * 查詢部門(mén)ID為1的員工信息
     */
    @Test
    public void test3() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.dept.id = ?";
        Query query = session.createQuery(hql).setParameter(0, 1L);
        List<Employee> list = query.list();

        session.getTransaction().commit();
        for (Employee e:list) {
            System.out.println(e);
        }
    }

1.2、名稱占位符

名稱占位符特點(diǎn)：

• 和位置無(wú)關(guān)
• 相同參數(shù)值最仑，設(shè)置一次值就可以了
• 可以使用名稱參數(shù)為in的參數(shù)設(shè)置多個(gè)值

問(wèn)題1：查詢工資大于5000的員工

    /**
     * 查詢工資大于5000的員工
     */
    @Test
    public void test4() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.salary > :salary";
        Query query = session.createQuery(hql)
                .setParameter("salary", new BigDecimal("5000"));
        List<Employee> list = query.list();

        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

問(wèn)題2：查詢id為3藐俺、5炊甲、7的員工信息

    /**
     * 查詢id為3,5,7的員工信息
     */
    @Test
    public void test5() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.id in (:eids)";
        Query query = session.createQuery(hql)
                .setParameterList("eids", new Long[]{3L, 5L, 7L});
        List<Employee> list = query.list();
        session.getTransaction().commit();

        for (Employee e : list) {
            System.out.println(e);
        }
    }

問(wèn)題3：查詢部門(mén)ID為1的員工信息

    /**
     * 查詢部門(mén)ID為1的員工信息
     */
    @Test
    public void test6() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.dept.id = :deptId";
        Query query = session.createQuery(hql).setParameter("deptId", 1L);
        List<Employee> list = query.list();

        session.getTransaction().commit();
        for (Employee e:list) {
            System.out.println(e);
        }
    }

• 參數(shù)是實(shí)體

    /**
     * 查詢部門(mén)ID為1的員工信息
     * 參數(shù)是實(shí)體
     */
    @Test
    public void test6() {
        Department department = new Department();
        department.setId(1L);

        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.dept = :dept";
        Query query = session.createQuery(hql).setParameter("dept", department);
        List<Employee> list = query.list();

        session.getTransaction().commit();
        for (Employee e:list) {
            System.out.println(e);
        }
    }

2、查詢結(jié)果

1欲芹、查詢對(duì)象實(shí)體

問(wèn)題1：查詢所有員工信息

    /**
     * 查詢所有員工信息
     */
    @Test
    public void test7() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e";
        List<Employee> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (Employee e:list) {
            System.out.println(e);
        }

    }

2卿啡、查詢對(duì)象中某個(gè)屬性

問(wèn)題1：查詢所有員工的名稱

• 查詢的屬性是一個(gè)簡(jiǎn)單類型，查詢結(jié)果就是該類型的List集合

    /**
     * 查詢所有員工的名稱
     */
    @Test
    public void test8() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e.name FROM Employee e";
        List<String> list = session.createQuery(hql).list();

        session.getTransaction().commit();
        for (String name :list) {
            System.out.println(name);
        }
    }

問(wèn)題2：查詢所有的員工所在部門(mén)

    /**
     * 查詢所有的員工的所在部門(mén)
     */
    @Test
    public void test9() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();
        
        //DISTINCT去重
        String hql = "SELECT DISTINCT e.dept FROM Employee e";
        Query query = session.createQuery(hql);
        List<Department> departList = query.list();

        session.getTransaction().commit();

        for (Department d:departList) {
            System.out.println(d);
        }

    }

• 因?yàn)槭莾?nèi)連接菱父，若一個(gè)員工沒(méi)有部門(mén)是不會(huì)被查詢出來(lái)的

3颈娜、查詢對(duì)象的某幾個(gè)屬性

問(wèn)題1：需求:查詢所有員工的編號(hào),名字,工資,及所在部門(mén)的編號(hào)和名稱

    /**
     * 需求:查詢所有員工的編號(hào),名字,工資,及所在部門(mén)的編號(hào)和名稱
     */
    @Test
    public void test10() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e.id, e.name, e.salary, e.dept.id, e.dept.name FROM Employee e";
        List<Object[]> list = session.createQuery(hql).list();

        session.getTransaction().commit();
        for (Object[] obj : list) {
            System.out.println(Arrays.toString(obj));
        }
    }

• 返回結(jié)果是一個(gè)Object[]，在數(shù)組的對(duì)應(yīng)位置就是對(duì)應(yīng)的查詢類型

4浙宜、查詢結(jié)果的封裝

問(wèn)題1：查詢所有員工的編號(hào),名字,工資,及所在部門(mén)的編號(hào)和名稱

4.1官辽、查詢類型Object[]

    /**
     * 返回結(jié)果類型 Object[]
     */
    @Test
    public void testObject() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e.id, e.name, e.salary, e.dept.id, e.dept.name FROM Employee e";
        List<Object[]> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (Object[] obj:list) {
            System.out.println(Arrays.toString(obj));
        }
    }

4.2、List<Object>

    /**
     * 返回結(jié)果類型 List<Object>
     */
    @Test
    public void testListObject() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.id, e.name, e.salary, e.dept.id, e.dept.name) FROM Employee e";
        List<List<Object>> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        System.out.println(list);
    }

4.3粟瞬、Map<Integer, Object>

    /**
     * 返回結(jié)果類型 Map
     */
    @Test
    public void testMap() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new Map(e.id, e.name, e.salary, e.dept.id, e.dept.name) FROM Employee e";
        List<List<Object>> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        System.out.println(list);
    }

4.4同仆、Map<String, Object>使用別名（AS）

    /**
     * 返回結(jié)果類型 MapAS
     */
    @Test
    public void testMapAS() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new Map(" +
                "e.id AS eId, " +
                "e.name  AS eName," +
                " e.salary AS eSalary," +
                " e.dept.id AS dID," +
                " e.dept.name AS dName)" +
                " FROM Employee e";
        List<Map<String, Object>> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        System.out.println(list);
    }

4.5、VO(值對(duì)象)

• EDValueObject

package com.revanwang.query.hql;

import lombok.Data;

import java.math.BigDecimal;

@Data
public class EDValueObject {
    private Long        eId;
    private String      eName;
    private BigDecimal eSalary;
    private Long        dId;
    private String      dName;

    public EDValueObject(Long eId, String eName, BigDecimal eSalary, Long dId, String dName) {
        this.eId = eId;
        this.eName = eName;
        this.eSalary = eSalary;
        this.dId = dId;
        this.dName = dName;
    }
}

• common.xml中導(dǎo)入EDValueObject

<import class="com.revanwang.query.hql.EDValueObject" rename="EDValueObject"/>

• 封裝EDValueObject

    /**
     * 返回結(jié)果類型 EDValueObject
     */
    @Test
    public void testVO() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new EDValueObject(e.id, e.name, e.salary, e.dept.id, e.dept.name) FROM Employee e";
        List<EDValueObject> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (EDValueObject ed : list) {
            System.out.println(ed);
        }
    }

5亩钟、HQL中的集合操作

集合在Hibernate中經(jīng)常出現(xiàn)乓梨，對(duì)集合的操作（size屬性或size函數(shù)獲取集合元素?cái)?shù)量）

問(wèn)題1：查詢有員工參與開(kāi)發(fā)的項(xiàng)目

    @Test
    public void test1() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT p FROM Project p WHERE p.emps.size > 0";
        List<Project> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (Project p : list) {
            System.out.println(p);
        }

    }

問(wèn)題2：查詢出沒(méi)有員工參與的項(xiàng)目

    /**
     * 查詢出沒(méi)有員工參與的項(xiàng)目【對(duì)集合使用size/is empty】
     */
    @Test
    public void test2() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT p FROM Project p WHERE p.emps IS EMPTY";
        List<Project> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (Project p : list) {
            System.out.println(p);
        }

    }

問(wèn)題3：查詢出項(xiàng)目信息鳖轰，按照項(xiàng)目的參與者總數(shù)排序【使用函數(shù)排序】

    /**
     * 查詢出項(xiàng)目信息清酥，按照項(xiàng)目的參與者總數(shù)排序【使用函數(shù)排序】
     */
    @Test
    public void test3() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT p FROM Project p ORDER BY p.emps.size DESC";
        List<Project> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (Project p : list) {
            System.out.println(p);
        }

    }

6、連接查詢

6.1蕴侣、查詢出所有員工及部門(mén)名稱

    /**
     * 查詢出所有員工及部門(mén)名稱【JOIN/LEFT JOIN】隱式內(nèi)連接/顯示內(nèi)連接
     */
    @Test
    public void test4() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.name, e.dept.name) FROM Employee e";
        List<List<Object>> employeeList = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (List<Object> emp: employeeList) {
            System.out.println(emp);
        }

    }

• Hibernate默認(rèn)使用的是INNER JOIN

• LEFT JOIN

    /**
     * 查詢出所有員工及部門(mén)名稱【JOIN/LEFT JOIN】隱式內(nèi)連接/顯示內(nèi)連接
     */
    @Test
    public void test5() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.name, d.name) " +
                "FROM Employee e LEFT JOIN e.dept d";
        List<List<Object>> employeeList = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (List<Object> emp: employeeList) {
            System.out.println(emp);
        }

    }

6.2焰轻、查詢所有的員工，包括沒(méi)有部門(mén)的員工【LEFT JOIN】

    /**
     * 查詢所有的員工,包括沒(méi)有部門(mén)的員工【LEFT JOIN】
     */
    @Test
    public void test6() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.id, e.name, d.name) FROM Employee e LEFT JOIN e.dept d";
        List<List<Object>> list = session.createQuery(hql).list();

        session.getTransaction().commit();

        for (List<Object> e : list) {
            System.out.println(e);
        }
    }

6.3昆雀、查詢出市場(chǎng)部員工信息及電話【LEFT/RIGHT JOIN】

    /**
     * 查詢出市場(chǎng)部員工信息及電話
     */
    @Test
    public void test8() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.id, e.name, p.number) FROM Phone p RIGHT JOIN p.employee e" +
                " WHERE e.dept.name = :deptName";
        Query query = session.createQuery(hql).setParameter("deptName", "市場(chǎng)部");
        List<List<Object>> lists = query.list();

        session.getTransaction().commit();

        for (List<Object> obj : lists) {
            System.out.println(obj);
        }

    }

6.4辱志、查詢所有員工的部門(mén)信息

    /**
     * 查詢所有員工的部門(mén)信息
     */
    @Test
    public void test9() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT d FROM Employee e LEFT JOIN e.dept d";
        Query query = session.createQuery(hql);

        List<Department> list = query.list();

        session.getTransaction().commit();

        for (Department d : list) {
            System.out.println(d);
        }
    }

7、聚集函數(shù)和子查詢

問(wèn)題1：查詢出各個(gè)部門(mén)員工的平均工資和最高工資【使用聚集函數(shù)】

    /**
     * 查詢出各個(gè)部門(mén)員工的平均工資和最高工資
     */
    @Test
    public void test10() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(e.dept.name, AVG(e.salary), MAX(e.salary)) FROM Employee e GROUP BY e.dept";
        Query query = session.createQuery(hql);

        List list = query.list();

        session.getTransaction().commit();

        System.out.println(list);

    }

問(wèn)題2：查詢出各個(gè)項(xiàng)目和該項(xiàng)目參與人數(shù)

    /**
     * 查詢出各個(gè)項(xiàng)目和該項(xiàng)目參與人數(shù)
     */
    @Test
    public void test11() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT new List(pro.id, pro.name, pro.emps.size) FROM Project pro";
        List<List<Object>> list = session.createQuery(hql).list();

        session.getTransaction().commit();
        System.out.println(list);
    }

問(wèn)題3：查詢出大于平均工資的員工信息

    /**
     * 查詢出大于平均工資的員工信息
     */
    @Test
    public void test12() {
        Session session = HibernateUtil.getHibernateSession();
        session.beginTransaction();

        String hql = "SELECT e FROM Employee e WHERE e.salary > (SELECT AVG(es.salary) FROM Employee es)";
        List<Employee> list = session.createQuery(hql).list();

        session.getTransaction().commit();
        System.out.println(list);
    }

8狞膘、命名查詢

在Hibernate中揩懒，執(zhí)行查詢需要先將HQL翻譯成SQL，再執(zhí)行SQL挽封。如果HQL比較復(fù)雜翻譯的效率是比較低的已球。如果一條HQL重復(fù)執(zhí)行，會(huì)重復(fù)翻譯辅愿，效率低下智亮。

Hibernate提供了NamedQuery方式，來(lái)稍微提高靜態(tài)HQL語(yǔ)句的執(zhí)行效率点待。

NamedQuery使用：在實(shí)體映射文件中添加

<!--為HQL起名為findCustomersByName阔蛉，該HQL在hibernate啟動(dòng)的時(shí)候就會(huì)翻譯成SQL -->
<query name="findCustomersByName">
     <![CDATA[from Customer c where c.name like :name]]>
</query>

使用NamedQuery查詢：通過(guò)getNamedQuery，得到的就是已經(jīng)翻譯為SQL的query對(duì)象癞埠，只需要設(shè)置參數(shù)查詢就行了

Query q=session.getNamedQuery("findCustomersByName");

NamedQuery的使用限制：NamedQuery里面只能配置靜態(tài)的HQL状原。