hibernate检索方式（HQL 检索方式，QBC 检索方式，本地 SQL 检索方式）
hibernate有五种检索方式，这儿用 单向的一对多的映射关系 例子，这儿有后三种的方式；
导航对象图检索方式: 根据已经加载的对象导航到其他对象
OID 检索方式: 按照对象的 OID 来检索对象
HQL 检索方式: 使用面向对象的 HQL 查询语言
QBC 检索方式: 使用 QBC(Query By Criteria) API 来检索对象. 这种 API 封装了基于字符串形式的查询语句, 提供了更加面向对象的查询接口.
本地 SQL 检索方式: 使用本地数据库的 SQL 查询语句
建立封装数据库中数据属性的封装类：n-1;
n的一端封装类：
package com.atguigu.hibernate.entities; import java.util.HashSet; import java.util.Set; public class Department { private Integer id; private String name; private Set<Employee> emps=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> getEmps() { return emps; } public void setEmps(Set<Employee> emps) { this.emps = emps; } @Override public String toString() { return "Department [id=" + id + "]"; } }
1的一端封装的类：
package com.atguigu.hibernate.entities; 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, float salary, String email, Department dept) { super(); this.name = name; this.salary = salary; this.email = email; this.dept = dept; } 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 + "]"; } }
在该包下自动生成对用的关系映射文件，只需进行一些修改；
单向的n-1映射关联关系，1的一端，关系映射文件；
<?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 2016-9-26 8:08:56 by Hibernate Tools 3.4.0.CR1 --> <hibernate-mapping package="com.atguigu.hibernate.entities"> <class name="Employee" table="LL_EMPLOYEE"> <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="Department"> <column name="DEPT_id" /> </many-to-one> </class> <!-- 在映射文件中定义命名查询语句 ,书写的位置不在class范围内，注意书写的位置--> <query name="salaryemp"><![CDATA[from Employee e where e.salary >:minSal and e.salary <:maxSal]]></query> </hibernate-mapping>
单向的n-1映射关联关系，n的一端，关系映射文件；
<?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 2016-9-26 8:08:56 by Hibernate Tools 3.4.0.CR1 --> <hibernate-mapping package="com.atguigu.hibernate.entities"> <class name="Department" table="LL_DEPARTMENT"> <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> <set name="emps" table="LL_EMPLOYEE" inverse="true" lazy="true"> <key> <column name="DEPT_ID" /> </key> <one-to-many class="Employee" /> </set> </class> </hibernate-mapping>
在src目录下建立hibernate的配置文件：
<?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> <!-- Hibernate 连接数据库的基本信息 --> <property name="connection.username">lxn123</property> <property name="connection.password">lxn123</property> <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="connection.url">jdbc:oracle:thin:@localhost:1521:orcl1</property> <!-- --> <!-- Hibernate 的基本配置 --> <!-- Hibernate 使用的数据库方言 ,是oracle书籍库的基本方言--> <property name="dialect">org.hibernate.dialect.Oracle10gDialect</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 数据源，不是开发 这儿就不配置了 --> <!-- 设定 JDBC 的 Statement 读取数据的时候每次从数据库中取出的记录条数 --> <property name="hibernate.jdbc.fetch_size">100</property> <!-- 设定对数据库进行批量删除，批量更新和批量插入的时候的批次大小 --> <property name="jdbc.batch_size">30</property> <!-- 需要关联的 hibernate 映射文件 .hbm.xml --> <mapping resource="com/atguigu/hibernate/entities/Department.hbm.xml"/> <mapping resource="com/atguigu/hibernate/entities/Employee.hbm.xml"/> </session-factory> </hibernate-configuration>
建立测试类，进行测试方法；
package com.atguigu.hibernate.test; import java.util.ArrayList; import java.util.Arrays; import java.util.LinkedHashSet; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import org.hibernate.cfg.Configuration; import org.hibernate.criterion.Conjunction; import org.hibernate.criterion.Disjunction; import org.hibernate.criterion.MatchMode; import org.hibernate.criterion.Order; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.hibernate.service.ServiceRegistry; import org.hibernate.service.ServiceRegistryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.atguigu.hibernate.entities.Department; import com.atguigu.hibernate.entities.Employee; public class HqlTest { private static SessionFactory sessionFactory; private static Session session; private static Transaction transaction; @Before public void init(){ Configuration configuration=new Configuration().configure(); ServiceRegistry serviceRegistry= new ServiceRegistryBuilder() .applySettings(configuration.getProperties()) .buildServiceRegistry(); sessionFactory=configuration.buildSessionFactory(serviceRegistry); session=sessionFactory.openSession(); transaction=session.beginTransaction(); } @After public void destroy(){ transaction.commit(); session.close(); sessionFactory.close(); } @Test //hql也可以进行曾删改的操作 public void testHQLUpdate(){ String hql = "DELETE FROM Department d WHERE d.id = :id"; session.createQuery(hql).setInteger("id", 280) .executeUpdate(); } @Test //本地 SQL 检索 public void testNativeSQL(){ String sql = "INSERT INTO ll_department VALUES(?, ?)"; Query query = session.createSQLQuery(sql); query.setInteger(0, 280) .setString(1, "ATGUIGU") .executeUpdate(); } @Test //QBC检索, 添加排序,添加翻页方法 public void testQBC4(){ Criteria criteria = session.createCriteria(Employee.class); //1. 添加排序 criteria.addOrder(Order.asc("salary")); criteria.addOrder(Order.desc("email")); List<Employee> empss=criteria.list(); System.out.println(empss); //2. 添加翻页方法 int pageSize = 5; int pageNo = 3; List<Employee> emps=criteria.setFirstResult((pageNo - 1) * pageSize) .setMaxResults(pageSize) .list(); System.out.println(emps); } @Test //QBC检索,统计查询 public void testQBC3(){ Criteria criteria = session.createCriteria(Employee.class); //统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到 criteria.setProjection(Projections.max("salary")); System.out.println(criteria.uniqueResult()); } @Test //QBC检索,AND OR public void testQBC2(){ Criteria criteria = session.createCriteria(Employee.class); //1. AND: 使用 Conjunction 表示 //Conjunction 本身就是一个 Criterion 对象 //且其中还可以添加 Criterion 对象 Conjunction conjunction = Restrictions.conjunction(); conjunction.add(Restrictions.like("name", "a", MatchMode.ANYWHERE)); Department dept = new Department(); dept.setId(80); conjunction.add(Restrictions.eq("dept", dept)); System.out.println(conjunction); //2. OR Disjunction disjunction = Restrictions.disjunction(); disjunction.add(Restrictions.ge("salary", 6000F)); disjunction.add(Restrictions.isNull("email")); criteria.add(disjunction); criteria.add(conjunction); criteria.list(); System.out.println(criteria); } @Test //QBC检索 public void testQBC(){ //1. 创建一个 Criteria 对象 Criteria criteria = session.createCriteria(Employee.class); //2. 添加查询条件: 在 QBC 中查询条件使用 Criterion 来表示 //Criterion 可以通过 Restrictions 的静态方法得到 //eq,是等于，gt是大于 criteria.add(Restrictions.eq("email", "SKUMAR")); criteria.add(Restrictions.gt("salary", 5000F)); //3. 执行查询 Employee employee = (Employee) criteria.uniqueResult(); System.out.println(employee); } @Test //内连接 public void testinnerjoin(){ String hql = "SELECT e FROM Employee e INNER JOIN e.dept"; Query query = session.createQuery(hql); List<Employee> emps = query.list(); emps=new ArrayList<>(new LinkedHashSet<>(emps)); System.out.println(emps.size()); for(Employee emp: emps){ System.out.println(emp.getName() + ", " + emp.getDept().getName()); } } @Test //迫切内连接 public void testinnerjoinfetch(){ String hql = "FROM Department d INNER JOIN FETCH d.emps"; Query query = session.createQuery(hql); List<Department> depts = query.list(); //取消重复的行 depts = new ArrayList<>(new LinkedHashSet(depts)); System.out.println(depts.size()); for(Department dept: depts){ System.out.println(dept.getName() + "-" + dept.getEmps().size()); } } @Test //左外连接，DISTINCT取消重复的行 public void testLeftJoin(){ String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps"; Query query = session.createQuery(hql); List<Department> depts = query.list(); System.out.println(depts.size()); for(Department dept: depts){ System.out.println(dept.getName() + ", " + dept.getEmps()); } // List<Object []> result = query.list(); // result = new ArrayList<>(new LinkedHashSet<>(result));，取消重复的行 // System.out.println(result); // // for(Object [] objs: result){ // System.out.println(Arrays.asList(objs)); // } } //迫切左外连接LEFT JOIN FETCH,DISTINCT是取消重复的行 public void testLeftJoinFetch(){ String hql="select distinct d from Department d left join fetch d.emps"; Query query=session.createQuery(hql); List<Department> depts=query.list(); //取消重复的行,set集合中没有重复的值 depts=new ArrayList<>(new LinkedHashSet<>(depts)); System.out.println(depts.size()); for(Department dept:depts){ System.out.println(dept.getName()+"--"+dept.getEmps()); } } //报表查询,即在sql是分组查询，只查询部分属性 public void testGroupBy(){ String hql="select min(e.salary),max(e.salary) " + "from Employee e group by e.dept having min(salary)>:de"; Query query=session.createQuery(hql).setFloat("de", 5000); //数组的形式 List<Object []> result=query.list(); for(Object [] emp:result){ System.out.println(Arrays.asList(emp)); } } //投影查询，即查询部分属性 public void testFieldQuery2(){ String hql="select new Employee(e.name,e.salary,e.email,e.dept) " + "from Employee e where e.dept=:dept"; Query query=session.createQuery(hql); Department dept=new Department(); dept.setId(80); //封装类形式的 List<Employee> result=query.setEntity("dept", dept).list(); for(Employee emp:result){ System.out.println(emp.getName()+"，"+emp.getSalary()+"，"+emp.getEmail()+"，"+emp.getDept()); } } @Test //投影查询，即查询部分属性 public void testFieldQuery(){ String hql="select e.email,e.salary,e.dept from Employee e where e.dept=:dept"; Query query=session.createQuery(hql); Department dept=new Department(); dept.setId(80); //数组形式的 List<Object[]> result=query.setEntity("dept", dept).list(); for(Object [] emps:result){ System.out.println(Arrays.asList(emps)); } } //在映射文件中定义命名查询语句方式的命名查询,在映射文件中要配置的。。。 //此查询方法，只能查询到在此范围内的个数，不能查询到具体的属性值 public void testNamedQuery(){ Query query =session.getNamedQuery("salaryemp"); List<Employee> emps= query.setFloat("minSal", 5000) .setFloat("maxSal", 10000) .list(); System.out.println(emps.size()); } //分页查询,此查询只能查询到，对应的id @Test public void testPageQuery(){ String hql="from Employee"; Query query=session.createQuery(hql); //第三页 int pageNo=3; //每页的大小为5 int pageSize=5; /* * setFirstResult(int firstResult): 设定从哪一个对象开始检索, * 参数 firstResult 表示这个对象在查询结果中的索引位置, 索引位置的起始值为 0. * 默认情况下, Query 从查询结果中的第一个对象开始检索 * * setMaxResults(int maxResults): 设定一次最多检索出的对象的数目. * 在默认情况下, Query 和 Criteria 接口检索出查询结果中所有的对象 * */ List<Employee> emps= query.setFirstResult((pageNo-1)*pageSize) .setMaxResults(pageSize).list(); System.out.println(emps); } //基于命名参数的查询 //此查询方法，只能查询到在此范围内的个数，不能查询到具体的属性值 public void testHQLNamedParameter(){ //1. 创建 Query 对象 //基于命名参数. String hql="from Employee e where e.salary > :sal and e.email like :ema"; Query query = session.createQuery(hql); //2. 绑定参数。setFloat,setString,都为设置属性的类型 query.setFloat("sal", 7000).setString("ema", "%A%"); //3. 执行查询 List<Employee> emp=query.list(); System.out.println(emp.size()); } //此查询方法，只能查询到在此范围内的个数，不能查询到具体的属性值 public void testHql(){ //1. 创建 Query 对象 //基于位置的参数.hql是面向对象的，所以，书写表名时 为封装属性的类名； String hql="FROM Employee e WHERE e.salary > ? AND e.email LIKE ? AND e.dept = ? " + "ORDER BY e.salary"; Query query=session.createQuery(hql); //2. 绑定参数 //Query 对象调用 setXxx 方法支持方法链的编程风格. Department dept=new Department(); dept.setId(80); query.setFloat(0, 5000).setString(1, "%A%").setEntity(2, dept); //3. 执行查询 List<Employee> emp=query.list(); System.out.println(emp.size()); } }
