Hibernate --- 检索方式

一、Hibernate检索数据的五种方式：

1.1 导航对象图检索方式 --- 根据已加载的对象导航到其他对象

1.2 OID检索方式 --- 按照对象的OID来检索对象

1.3 HQL检索方式 --- 使用面向对象的HQL查询语句

1.4 QBC检索方式 --- 使用QBC(Query By Criteria)API来检索对象，这种API封装了基于字符串形式的查询语句，提供了更加面向对象的查询接口

1.5 本地SQL检索方式 --- 使用本地数据库的SQL查询语句

二、导航对象图检索方式

如果说我们在对象-关系映射文件上配置了它们得到一对多的关系，那么我们就可以使用

School school  = (School)session.get(School.class,1);  
List<Student> students = school.getStudents();  

三、OID检索方式

主要是指用Session中的的get()和load()方法加载数据：
(1) get() 

• 不支持延迟加载即执行get()方法的时候就发Select语句 
• 当在数据库中查不到记录的时候不会抛出异常，会返回一个null

(2) load()

• 支持延迟加载即需要用到记录数据的时候在发Select语句
• 当在数据库中查不到记录的时候会抛出ObjectNotFoundException异常

四、HQL检索方式

HQL(Hibernate Query Language) 是面向对象的查询语言，它和 SQL 查询语言有些相似。在 Hibernate 提供的各种检索方式中，HQL 是使用最广的一种检索方式。

实体类：

Customer类：

package com.hibernate.domain;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;

import org.hibernate.annotations.NamedNativeQuery;
import org.hibernate.annotations.Proxy;

@Entity
@Table(name = "t_customer")
@NamedQuery(name = "myHql", query = "from Customer")

@SqlResultSetMapping(name = "customerSetMapping", entities = { @EntityResult(entityClass = Customer.class, fields = {
		@FieldResult(name = "id", column = "id"), @FieldResult(name = "name", column = "name") }) })

@NamedNativeQuery(name = "findCustomer", query = "select * from t_customer", resultSetMapping = "customerSetMapping")
@Proxy(lazy = true)
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id; // 主键

	private String name; // 姓名

	@OneToMany(targetEntity = Order.class, mappedBy = "c")
	
	private Set<Order> orders = new HashSet<Order>();

	public Customer() {

	}

	public Customer(Integer id, String name) {
		super();
		this.id = id;
		this.name = name;
	}

	public Set<Order> getOrders() {
		return orders;
	}

	public void setOrders(Set<Order> orders) {
		this.orders = orders;
	}

	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;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + "]";
	}

}

Order类：

package com.hibernate.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.Cascade;
import org.hibernate.annotations.CascadeType;
import org.hibernate.annotations.NamedQuery;

@Entity
@Table(name = "t_order")
@NamedQuery(name="findOrderByCustomer",query="from Order where c=:c")
public class Order {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	private Double money;

	private String receiverInfo; // 收货地址
	// 订单与客户关联
	@ManyToOne(targetEntity = Customer.class)
	@JoinColumn(name = "c_customer_id")
	@Cascade(CascadeType.SAVE_UPDATE)
	
	private  Customer c;

	public Customer getC() {
		return c;
	}

	public void setC(Customer c) {
		this.c = c;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Double getMoney() {
		return money;
	}

	public void setMoney(Double money) {
		this.money = money;
	}

	public String getReceiverInfo() {
		return receiverInfo;
	}

	public void setReceiverInfo(String receiverInfo) {
		this.receiverInfo = receiverInfo;
	}

	@Override
	public String toString() {
		return "Order [id=" + id + ", money=" + money + ", receiverInfo=" + receiverInfo + "]";
	}

}

hibernate.cfg.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
	"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

	<session-factory>
		<!-- 配置关于数据库连接的四个项 driverClass url username password -->
		<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="hibernate.connection.url">jdbc:mysql:///hibernateTest</property>
		<property name="hibernate.connection.username">root</property>
		<property name="hibernate.connection.password">root</property>

		<!-- 设置连接提供者 -->
		<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
		<!-- c3p0连接池的配置 -->
		<property name="hibernate.c3p0.max_size">20</property> <!-- 最大连接池 -->
		<property name="hibernate.c3p0.min_size">5</property> <!-- 最小连接数 -->
		<property name="hibernate.c3p0.timeout">120</property> <!-- 超时 -->
		<property name="hibernate.c3p0.idle_test_period">3000</property> <!-- 空闲连接 -->

		<!-- 可以将向数据库发送的sql显示出来 -->
		<property name="hibernate.show_sql">true</property>
		<!-- 格式化sql -->
		<property name="hibernate.format_sql">true</property>

		<!-- hibernate的方言 -->
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

		<!-- 自动创建表 -->
		<property name="hibernate.hbm2ddl.auto">update</property>


		<!-- 设置事务隔离级别 -->
		<property name="hibernate.connection.isolation ">4</property>


		<property name="hibernate.current_session_context_class">thread</property>


		<mapping class="com.hibernate.domain.Customer" />
		<mapping class="com.hibernate.domain.Order" />


	</session-factory>

</hibernate-configuration>	

4.1 HQL基本检索 

// 基本检索
	@Test
	public void test2() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 1.编写HQL
		String hql = "from Customer"; // from是关键字，后面是类名,关键字是不区分大小写，但是类名是区分
		// 2.通过session.createQuery(hql)
		// Query query = session.createQuery(hql);
		// 3.通过list方法得到数据
		// List<Customer> list = query.list();

		List<Customer> list = session.createQuery(hql).list();

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

		session.getTransaction().commit();
		session.close();
	}

4.2 HQL排序检索 

// 排序检索--//查询订单，根据订单的价格进行排序
	@Test
	public void test3() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 1.定义hql
		String hql = "from Order order by money desc"; // desc 降序 默认是asc 升序
		// 2.执行hql查询订单，根据价格进行排序
		List<Order> list = session.createQuery(hql).list();

		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

4.3 HQL条件查询检索 

// 条件查询
	@Test
	public void test4() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 1.根据位置来绑定参数
		// 1.1创建hql
		// String hql = "from Order where money>? ";

		// 1.2.执行hql
		// List<Order> list = session.createQuery(hql).setParameter(0,
		// 2000d).list();
		// 可以使用例如setString() setDouble这样的方法去添加参数,参数的序号是从0开始.

		// 2.根据名称来绑定
		// 1.1创建hql
		String hql = "from Order where money>:mymoney ";

		// 1.2.执行hql
		List<Order> list = session.createQuery(hql).setParameter("mymoney", 2000d).list();
		// 可以使用例如setString() setDouble这样的方法去添加参数

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

	}

4.4 HQL分页检索 

// 分页检索
	@Test
	public void test5() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		Query query = session.createQuery("from Order");
		// 每页显示6条件 ，我们要得到第二页数据
		query.setFirstResult((2 - 1) * 6); // 设定开始位置
		query.setMaxResults(6); // 设置条数

		List<Order> list = query.list();
		System.out.println(list);

		session.getTransaction().commit();
		session.close();

	}

4.5 HQL分组统计检索 

// 分组统计操作
	@Test
	public void test6() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 统计操作----统计一共有多少订单 count
		// String hql="select count(*) from Order";

		// Object count = session.createQuery(hql).uniqueResult();
		// System.out.println(count);

		// 分组统计----每一个人的订单总价
		String hql = "select sum(money) from Order group by c";
		List list = session.createQuery(hql).list();
		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

4.6 HQL投影查询检索 

// 投影查询
	@Test
	public void test7() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 1.查询出所有的Customer的name
		// String hql = "select name from Customer";

		// List list = session.createQuery(hql).list();

		// System.out.println(list); // [张龙, 张三丰]

		// 如果只查询一个列,得到的结果List<Object>

		// 2.查询所有的Customer的id,name
		// String hql = "select id,name from Customer";

		// List<Object[]> list = session.createQuery(hql).list();

		// for(Object[] objs:list){
		// for(Object obj:objs){
		// System.out.print(obj+" ");
		// }
		// System.out.println();
		// }
		// 如果是查询多列,得到的结果是List<Object[]>

		// 3.使用投影将查询的结果封装到Customer对象
		String hql = "select new Customer(id,name) from Customer"; // 必须在PO类中提供对应的构造方法
		List<Customer> cs = session.createQuery(hql).list();
		System.out.println(cs);

		session.getTransaction().commit();
		session.close();
	}

4.7 HQL命名查询检索 

// 命名查询
	@Test
	public void test9() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 1.我要查询张龙这个客户的订单
		Customer c = session.get(Customer.class, 1);

		Query query = session.getNamedQuery("findOrderByCustomer"); // from Order  where c=:c
		// 2.现在hql它的参数是一个实体
		List<Order> list = query.setEntity("c", c).list();

		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

	// 命名查询
	@Test
	public void test8() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		Query query = session.getNamedQuery("myHql");
		List<Customer> list = query.list();
		System.out.println(list);
		session.getTransaction().commit();
		session.close();
	}

4.8 HQL显示内连接检索

// 测试显示内连接
	@Test
	public void test1() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// sql连接 select * from t_customer inner join t_order on 条件

		String hql = "from Customer c inner join c.orders with c.id=1";

		Query query = session.createQuery(hql);
		List<Object[]> list = query.list(); // 结果是List<Object[]>
											// 而Object[]中装入的是Customer与Order对象。

		for (Object[] objs : list) {
			for (Object obj : objs) {
				System.out.print(obj + "\t");
			}
			System.out.println();
		}

		session.getTransaction().commit();
		session.close();
	}

4.9 HQL隐式内连接检索

// 测试隐式内连接
	@Test
	public void test2() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// sql连接 select * from t_customer,t_order where 条件
		String hql = "from Order o where o.c.id=1";
		Query query = session.createQuery(hql);
		List list = query.list();
		System.out.println(list);
		session.getTransaction().commit();
		session.close();
	}

4.10 HQL迫切内连接检索

// 迫切内连接
	@Test
	public void test3() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 迫切内连接 inner join fetch 注意：使用迫切连接结果可能出现重复，所以要使用distinct来去重复
		String hql = "select distinct c from Customer c inner join fetch c.orders";
		// 底层也是执行的inner join 只不过结果封装到对象中。

		Query query = session.createQuery(hql);
		List<Customer> list = query.list(); // 结果是List<>,集合中装入的From后面的对象。

		for (Customer o : list) {
			System.out.println(o);
		}

		session.getTransaction().commit();
		session.close();
	}

4.11 HQL外连接检索

// 外连接
	@Test
	public void test4() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		List<Object[]> list = session.createQuery("from Customer c left outer join c.orders").list(); // 左外连接
		for (Object[] objs : list) {
			for (Object obj : objs) {
				System.out.print(obj + "\t");
			}
			System.out.println();
		}
		session.getTransaction().commit();
		session.close();

4.12 HQL迫切左外连接检索

// 迫切左外连接
	@Test
	public void test5() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 注意:fetch不可以与单独条件的with一起使用
		List<Customer> list = session
				.createQuery("select distinct c from Customer c left outer join fetch c.orders where c.id=1").list(); // 左外连接

		for (Customer c : list) {
			System.out.println(c);
		}

		session.getTransaction().commit();
		session.close();
	}

五、QBC检索方式

QBC 查询就是通过使用 Hibernate 提供的 Query By Criteria API 来查询对象，这种 API 封装了 SQL 语句的动态拼装，对查询提供了更加面向对象的功能接口

QBC查询对照表：

5.1 QBC基本检索

// 基本检索
	@Test
	public void test1() {
		// 查询所有Customer
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 1.得到一个Criteria对象
		Criteria criteria = session.createCriteria(Customer.class);

		// 2.调用list方法
		List<Customer> list = criteria.list();

		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

5.2 QBC排序检索

// 排序检索
	@Test
	public void test2() {
		// 查询订单信息 根据订单的价格进行排序
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		Criteria criteria = session.createCriteria(Order.class);
		// 指定排序
		// criteria.addOrder(org.hibernate.criterion.Order.desc("money")); // 降序
		criteria.addOrder(org.hibernate.criterion.Order.asc("money")); // 升序

		List<Order> list = criteria.list();

		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

5.3 QBC条件检索

// 条件检索
	@Test
	public void test3() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 1.查询名称叫张某客户 张_
		Criteria criteria = session.createCriteria(Customer.class);
		Criterion like = Restrictions.like("name", "张_"); // 其它的条件 lt < gt > le
															// <= ge>= eq==
		criteria.add(like);// 添加条件
		Customer c = (Customer) criteria.uniqueResult();
		System.out.println(c);

		// 2.查询订单价格在1050以上的，并且它的客户是张某
		Criteria cri = session.createCriteria(Order.class);
		SimpleExpression lt = Restrictions.gt("money", 1050d); // >1050
		SimpleExpression eq = Restrictions.eq("c", c); // 与c客户相同
		LogicalExpression and = Restrictions.and(lt, eq);
		cri.add(and);
		// List<Order> orders =
		// cri.add(Restrictions.and(Restrictions.gt("money", 1050d),
		// Restrictions.eq("c", c))).list();
		List<Order> orders = cri.list();
		System.out.println(orders);

		session.getTransaction().commit();
		session.close();

	}

5.4 QBC分页检索

// 分页检索
	@Test
	public void test4() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		Criteria criteria = session.createCriteria(Order.class);
		criteria.setFirstResult((2 - 1) * 6);
		criteria.setMaxResults(6);
		List<Order> list = criteria.list();

		System.out.println(list);

		session.getTransaction().commit();
		session.close();
	}

5.5 QBC统计检索

// 统计检索
	@Test
	public void test5() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();

		// 1.统计订单总数
		Criteria criteria = session.createCriteria(Order.class);
		// Object obj =
		// criteria.setProjection(Projections.rowCount()).uniqueResult();
		// //统计总行数 count(id)
		// System.out.println(obj);

		// 2.订单的总价格----分组统计根据客户
		// criteria.setProjection(Projections.sum("money")); //统计总金额

		criteria.setProjection(
				Projections.projectionList().add(Projections.sum("money")).add(Projections.groupProperty("c")));

		List<Object[]> list = criteria.list(); // 在这个集合中保存的是Object[money的统计信息,客户信息]

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

		session.getTransaction().commit();
		session.close();
	}

5.6 QBC离线检索

// 离线的检索
	@Test
	public void test6() {
		// 1.得到一个DetachedCriteria
		DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);
		dc.add(Restrictions.like("name", "张_"));

		// 2.生成Criteria执行操作
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		Criteria criteria = dc.getExecutableCriteria(session);
		List<Customer> list = criteria.list();

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

	}

六、SQL检索方式

6.1 SQL基本检索

// 测试执行本地sql
	@Test
	public void test1() {
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		// 执行本地sql
		SQLQuery sqlQuery = session.createSQLQuery("select * from t_customer");
		// List<Object[]> list = sqlQuery.list();
		sqlQuery.addEntity(Customer.class); // 将查询结果绑定到指定对象
		// sqlQuery.setParameter(position, val) //给参数赋值
		List list = sqlQuery.list();
		System.out.println(list);
		session.getTransaction().commit();
		session.close();
	}

6.2 SQL命名查询检索

//测试本地sql命名查询
	@Test
	public void test2(){
		Session session = HibernateUtils.openSession();
		session.beginTransaction();
		
		Query query = session.getNamedQuery("findCustomer");
		List list = query.list();
		System.out.println(list);
		session.getTransaction().commit();
		session.close();
	}

4.11 HQL外连接检索