Hibernate入门08_HQL查询

一、Hibernate的HQL查询

hql是面向对象查询，格式：from + 类名 + 类对象 + where + 对象的属性，如果是对数据库进行查询操作先比sql比较优。

二、代码

import java.util.HashSet;import java.util.Set;public class Department {    private Integer id;    private String name;    private Set<Employee> employees = new HashSet<>();    //get/set...}

public class Employee {    private Integer id;    private String name;    private float salary;    private String email;    private Department dept;    public Employee() {    }    public Employee( String email, float salary,Department dept) {        super();        this.salary = salary;        this.email = email;        this.dept = dept;    }    //get/set...    @Override    public String toString() {        return "Employee [id=" + id + ", name=" + name + "]";    }}

Department.hbm.xml

<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><!-- Generated 2017-4-4 15:42:11 by Hibernate Tools 3.4.0.CR1 --><hibernate-mapping>    <class name="com.hibernate.entities.Department" table="DEPARTMENT">        <id name="id" type="java.lang.Integer">            <column name="ID" />            <generator class="native" />

Employee.hbm.xml

<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><!-- Generated 2017-4-4 15:42:11 by Hibernate Tools 3.4.0.CR1 --><hibernate-mapping>    <class name="com.hibernate.entities.Employee" table="EMPLOYEES">        <id name="id" type="java.lang.Integer">            <column name="ID" />            <generator class="native" />        </id>        <property name="name" type="java.lang.String">            <column name="NAME" />        </property>        <property name="salary" type="float">            <column name="SALARY" />        </property>        <property name="email" type="java.lang.String">            <column name="EMAIL" />        </property>        <many-to-one name="dept" class="com.hibernate.entities.Department" fetch="join">            <column name="DEPT_ID" />        </many-to-one>    </class>    <query name="salaryEmps"><![CDATA[FROM Employee e WHERE e.salary > :minSalart AND e.salary < :maxSalary]]></query></hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-configuration PUBLIC        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><hibernate-configuration>    <session-factory>    <!-- 配置链接数据库的基本信息 -->        <property name="connection.username">root</property>        <property name="connection.password">root</property>        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>        <property name="connection.url">jdbc:mysql://localhost:3306/hibernate01</property>        <!-- hibernate的基本信息 -->        <!-- hibernate使用的数据库方言 -->        <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>        <!-- 执行操作时是否在控制台打印sql语句 -->        <property name="show_sql">true</property>        <!-- 是否对sql进行格式化 -->        <property name="format_sql">true</property>        <!-- 指定自动生成数据表的策略 -->        <property name="hbm2ddl.auto">update</property>        <!-- 设置hibernate的隔离级别 -->        <property name="connection.isolation">2</property>        <!-- 删除对象后，设置其OID为null -->        <property name="use_identifier_rollback">true</property>        <!-- 配置C3P0数据源 -->        <property name="c3p0.max_size">100</property>        <property name="c3p0.min_size">5</property>        <property name="c3p0.acquire_increment">2</property>        <property name="c3p0.idle_test_period">2000</property>        <property name="c3p0.timeout">2000</property>        <property name="c3p0.max_statements">10</property>        <!-- 设置JDBC的statement读取数据库的时候每次从数据库中读取记录条数，这个值100合适，太大会损耗内存，小了速度慢 -->        <property name="jdbc.fetch_size">100</property>        <!-- 设置数据库进行批量删除，更新，插入的时候批次的大小，30比较合适 -->        <property name="jdbc.batch_size">30</property>        <!-- 指定关联的hbm.xml文件 -->        <mapping resource="com/hibernate/entities/Department.hbm.xml"/>             <mapping resource="com/hibernate/entities/Employee.hbm.xml"/>           </session-factory></hibernate-configuration>

三、测试代码

import static org.junit.Assert.*;import java.nio.channels.SeekableByteChannel;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import java.util.Set;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.Transaction;import org.hibernate.cfg.Configuration;import org.hibernate.service.ServiceRegistry;import org.hibernate.service.ServiceRegistryBuilder;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.hibernate.entities.Department;import com.hibernate.entities.Employee;import javassist.expr.NewArray;public class HibernateTest {    private SessionFactory sessionFactory;    private Session session;    private Transaction transaction;    @Test    public void testSave() {        List<Department> departments = new ArrayList<>();        for (int i = 0; i < 26; i++) {            Department department = new Department();            department.setName("DEPT_" + ("A" + i));            departments.add(department);            Employee employee1 = new Employee();            employee1.setName("EMP1_" + ("a" + i));            employee1.setEmail("EMAIL1_" + ("a" + i));            employee1.setSalary(1000 + i);            employee1.setDept(departments.get(i));            Employee employee2 = new Employee();            employee2.setName("EMP2_" + ("b" + i));            employee2.setEmail("EMAIL2_" + ("b" + i));            employee2.setSalary(2000 + i);            employee2.setDept(departments.get(i));            department.getEmployees().add(employee1);            department.getEmployees().add(employee2);            employee1.setDept(department);            employee2.setDept(department);            session.save(department);            session.save(employee1);            session.save(employee2);        }    }    /***     * 基于位置的参数     */    @Test    public void testHQL() {        // 1、创建Query对象        // Employee-对象而不是表名        String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? AND e.dept = ? ORDER BY e.salary DESC";        Query query = session.createQuery(hql);        // 2、绑定参数        Department dept = new Department();        dept.setId(27);        query.setFloat(0, 100f).setString(1, "%a0%").setEntity(2, dept);        // 3、查询        List<Employee> employees = query.list();        System.out.println(employees.size());    }    /***     * 基于命名参数     */    @Test    public void testHQLNameParam() {        // 1、创建Query对象        // Employee-对象而不是表名        String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email";        Query query = session.createQuery(hql);        // 2、绑定参数        query.setFloat("sal", 1500f).setString("email", "%b5%");        // 3、查询        List<Employee> employees = query.list();        System.out.println(employees.size());    }    /***     * 分页查询     */    @Test    public void testPageQuery() {        String hql = "FROM Employee";        Query query = session.createQuery(hql);        int numPage = 3;// 第3页        int pageSize = 5;// 每一页5条        List<Employee> employees = query.setFirstResult((numPage - 1) * pageSize).setMaxResults(pageSize).list();        System.out.println(employees);    }    /***     * 命名查询     */    @Test    public void testNameQuery() {        //salaryEmps-<query name="salaryEmps">        Query query = session.getNamedQuery("salaryEmps");        //查询工资大于1000小于1020 的员工        List<Employee> employees = query.setFloat("minSalart", 1000f).setFloat("maxSalary", 1020f).list();        System.out.println(employees.size());    }    /***     * 投影查询     */    @Test    public void testFieldQuery() {        //Employee类中要有对应的构造器        String hql = "SELECT new Employee(e.email, e.salary, e.dept) "                + "FROM Employee e "                + "WHERE e.dept =:dept";        Query query = session.createQuery(hql);        Department dept = new Department();        dept.setId(28);//      List<Object[]> employees = query.setEntity("dept", dept).list();//      for(Object[] objs : employees){//          System.out.println(Arrays.asList(objs));//      }        List<Employee> employees = query.setEntity("dept", dept).list();        for(Employee employee : employees){            System.out.println(employee.getSalary()+ " "+employee.getDept().getName());        }    }    @Test    public void testGroupBy(){        //查询每个部门 最低工资 > minsal,和最高工资        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);        List<Object[]> objects = query.setFloat("minsal", 1000).list();        for(Object[] objs : objects){            System.out.println(Arrays.asList(objs));        }    }    /***     * 迫切左外链接  推荐使用     * employees被初始化     */    @Test    public void testLeftJoinFetch(){        //SELECT DISTINCT -去除重复的元素        String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.employees";//      String hql2 = "FROM Department d LEFT JOIN FETCH d.employees";        Query query = session.createQuery(hql);        List<Department> departments = query.list();        System.out.println(departments.size());    }    /***     * 左外链接     * employees没有被初始化，使用的时候会再去查     */    @Test    public void testLeftJoin(){        //SELECT DISTINCT -去除重复的元素//      String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.employees";//      Query query = session.createQuery(hql);        String hql2 = "FROM Department d LEFT JOIN d.employees";        Query query = session.createQuery(hql2);        List<Object[]> departments = query.list();        for(Object[] objects : departments){            System.out.println(Arrays.asList(objects));        }    }    /***     * 内链接     * employees没有被初始化，使用的时候会再去查     */    @Test    public void testInnerJoin(){        //SELECT DISTINCT -去除重复的元素//      String hql = "SELECT DISTINCT d FROM Department d INNER JOIN d.employees";//      Query query = session.createQuery(hql);        String hql2 = "FROM Department d INNER JOIN d.employees";        Query query = session.createQuery(hql2);        List<Object[]> departments = query.list();        for(Object[] objects : departments){            System.out.println(Arrays.asList(objects));        }    }    @Before    public void init() {        System.out.println("HibernateTest.init()");        // 1.创建一个sessionFactory对象        Configuration configuration = new Configuration().configure();        ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties())                .buildServiceRegistry();        sessionFactory = configuration.buildSessionFactory(serviceRegistry);        // 2.创建一个session对象        session = sessionFactory.openSession();        // 3.开启事务        transaction = session.beginTransaction();    }    @After    public void destroy() {        System.out.println("HibernateTest.destroy()");        // 5.提交事务        transaction.commit();        // 6.关闭session        session.close();        // 7.关闭SessionFactory        sessionFactory.close();    }}