Hibernate学习15（HQL查询）

Hibernate查询方式：

• 1.导航对象图检索方式：根据已有对象导航到其他对象
• 2.OID检索：对象OID检索对象
• 3.HQL检索：使用面向对象HQL查询语言
• 4.QBC检索：使用QBC API来检索对象
• 5.本地SQL检索：使用SQL语言

HQL检索有如下功能：

• 1设定查询条件
• 2检索部分属性
• 3支持分页查询
• 5支持链接查询
• 6支持分组查询，可以使用HAVING和GROUP BY
• 7提供内置函数
• 8支持子查询
• 9支持动态绑定参数
• 10能够使用用户SQL函数

Hibernate基本逻辑代码

    public static void test() {
        StandardServiceRegistry registry = new StandardServiceRegistryBuilder().configure().build();// 配置文件configure()
        SessionFactory sessionFactory = new MetadataSources(registry).buildMetadata().buildSessionFactory();
        Session session = sessionFactory.openSession();
        Transaction transaction = session.beginTransaction();
        //这里写查询方法
        test(session);

        transaction.commit();
        session.close();
        sessionFactory.close();
    }

查询的持久化对象和表

//员工
public class Employee {
    
    private Integer id;
    private String name;
    private float salary;
    private String email;
    private Department dept;

    public Employee() {
        super();
    }
    public Employee(String name, String email) {
        super();
        this.name = name;
        this.email = email;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getSalary() {
        return salary;
    }
    public void setSalary(float salary) {
        this.salary = salary;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Department getDept() {
        return dept;
    }
    public void setDept(Department dept) {
        this.dept = dept;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", name=" + name + ", salary=" + salary + ", email=" + email + ", dept=" + dept
                + "]";
    }
    
    

}

//部门
public class Department {
    private Integer id;
    private String name;
    private Set<Employee> employees=new HashSet<>();
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Set<Employee> getEmployees() {
        return employees;
    }
    public void setEmployees(Set<Employee> employees) {
        this.employees = employees;
    }
    @Override
    public String toString() {
        return "Department [id=" + id + ", name=" + name + ", employees=" + employees + "]";
    }
    
    
    
}

此处省略映射文件（一对多关系）

部门表 员表

HQL检索步骤

• 1.通过Session 的createQuery（）方法获取Query对象 它包涵一个HQL语句和参数

• 2.动态绑定参数

• 3.调用Query执行方法

    /**
     * 第一次查询
     */
    public static void queryA(Session session) {
        // 1.创建Query对象
        String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? ";
        Query query = session.createQuery(hql);
        // 2.绑定参数
        query.setParameter(0, 5255.4f).setParameter(1, "%%");
        // 3.执行查询
        List<Employee> eList = query.list();
        System.out.println(eList.size());
    }

HQL可以直接使用持久化类的类名 和属性名

命名参数：取代"?"为查询条件命名

    /**
     * 命名参数
     * */
    public static void queryB(Session session) {
        // 1.创建Query对象
        String hql = "FROM Employee e WHERE e.salary > :salary AND e.email LIKE :email ";
        Query query = session.createQuery(hql);
        // 2.绑定参数
        query.setParameter("salary", 5255.4f).setParameter("email", "%%");
        // 3.执行查询
        List<Employee> eList = query.list();
        System.out.println(eList.size());
    }

实体参数 :HQL是面向对象的

    /**
     * 实体参数
     * */
    public static void queryC(Session session) {
        // 1.创建Query对象
        String hql = "FROM Employee e WHERE e.salary > ? AND e.dept = ? ";
        Query query = session.createQuery(hql);
        // 2.绑定参数
        Department e=new Department();
        e.setId(1);
        query.setParameter(0, 5255.4f).setParameter(1, e);
        // 3.执行查询
        List<Employee> eList = query.list();
        System.out.println(eList.size());
    }

ORDER BY 排序

    /**
     * ORDER BY 排序
     * */
    public static void queryD(Session session) {
        // 1.创建Query对象
        String hql = "FROM Employee e WHERE  e.dept = ? ORDER BY  e.salary DESC";
        Query query = session.createQuery(hql);
        // 2.绑定参数
        Department e=new Department();
        e.setId(1);
        query.setParameter(0, e);
        // 3.执行查询
        List<Employee> eList = query.list();
        for (Employee employee : eList) {
            System.out.println(employee.getName());
        }
    }

分页查询

    /**
     * 分页查询
     * */
    public static void queryF(Session session) {
        String hql = "FROM Employee e  WHERE e.id > ?";
        Query query = session.createQuery(hql);
        query.setParameter(0, 3);
        int pageNo=0;//分页起始位置包涵
        int pageSize=2;//每页长度
        List<Employee> eList = query.setFirstResult(pageNo).setMaxResults(pageSize).list();
        for (Employee employee : eList) {
            System.out.println(employee.getName());
        }
    }

命名查询 hql放在映射文件中

在映射文件中hibernate-mapping元素下

 <query name="salaryEmps"><![CDATA[FROM Employee e WHERE e.salary > :minSal AND e.salary < :maxSal]]></query>

    /**
     * 命名查询 hql放在映射文件中
     * */
    public static void queryG(Session session) {
        String hql = "FROM Employee e  WHERE e.id > ?";
        Query query = session.getNamedQuery("salaryEmps");
        query.setParameter("minSal", 5000f);
        query.setParameter("maxSal", 20000f);
        List<Employee> eList = query.list();
        for (Employee employee : eList) {
            System.out.println(employee.getName());
        }
    }

投影查询 查询部分属性 默认返回数组

返回数组

    /**
     * 投影查询 查询部分属性 默认返回数组
     * */
    public static void queryH(Session session) {
        String hql = "SELECT e.email, e.name FROM Employee e WHERE e.dept = :dept";
        Query query = session.createQuery(hql);
        Department e=new Department();
        e.setId(1);
        query.setParameter("dept", e);
        List<Object[]> eList = query.list();
        for (Object[] os : eList) {
            System.out.println(Arrays.asList(os));
        }
    }

返回对象

    /**
     * 投影查询 查询部分属性 默认返回数组
     * */
    public static void queryH(Session session) {
        String hql = "SELECT e.email, e.name FROM Employee e WHERE e.dept = :dept";
        Query query = session.createQuery(hql);
        Department e=new Department();
        e.setId(1);
        query.setParameter("dept", e);
        List<Object[]> eList = query.list();
        for (Object[] os : eList) {
            System.out.println(Arrays.asList(os));
        }
    }

报表查询 使用函数

    /**
     * 报表查询
     * */
    public static void queryJ(Session session) {
        //持久化类提供这样的构造器
        String hql = "SELECT min(e.salary), max(e.salary)  FROM Employee e  GROUP BY e.dept HAVING min(salary)> :minsal";
        Query query = session.createQuery(hql);
        query.setParameter("minsal", 1000f);
        List<Object[]> eList = query.list();
        for (Object[] os : eList) {
            System.out.println(Arrays.asList(os));
        }
    }

本地sql：可以执行sql所有操作

    /**
     * 本地sql
     * 可以执行sql所有操作
     * */
    public static void queryL(Session session) {
        String sql = "INSERT INTO department  (name) VALUES(:name)";
        Query query = session.createNativeQuery(sql);//使用createNativeQuery方法
        query.setParameter("name", "本地sql").executeUpdate();
    }