Hibernate框架（四）----查询进阶之多表查询

经过前三篇博客的详解，相信对Hibernate有一定的认识了吧？

下面具体讲解一下Hiernate的查询语句的进阶部分

一.HQL语句

    1.基本查询

String hql1 = " from com.auicyh.domain.Customer ";//完整写法
		String hql2 = " from Customer";//简单写法
		String hql3 = " from java.lang.Object";
		Query query = session.createQuery(hql3);
		List<Customer> list = query.list();
		System.out.println(list);

    2.排序查询

String hql1 = " from com.auicyh.domain.Customer order by cust_id asc";//完整写法 升序
		String hql2 = " from Customer order by cust_id desc";//简单写法 降序
		String hql3 = " from java.lang.Object";
		Query query = session.createQuery(hql2);
		List<Customer> list = query.list();
		System.out.println(list);

    3.条件查询

String hql1 = " from com.auicyh.domain.Customer where cust_id =?";//完整写法
		String hql2 = " from Customer where cust_id=:id";//简单写法
		String hql3 = " from java.lang.Object";
		Query query = session.createQuery(hql2);
		//query.setParameter(0, 1l);
		query.setParameter("id", 2l);
		List<Customer> list = query.list();
		System.out.println(list);

    4.分页查询

String hql1 = " from com.auicyh.domain.Customer ";//完整写法
		String hql2 = " from Customer ";//简单写法
		String hql3 = " from java.lang.Object";
		Query query = session.createQuery(hql2);
		query.setFirstResult(1);
		query.setMaxResults(3);
		List<Customer> list = query.list();
		System.out.println(list);

    5.统计查询

String hql1 = " select count(*) from com.auicyh.domain.Customer ";//完整写法
		String hql2 = " select count(*) from Customer ";//简单写法 求个数
		String hql3 = " select sum(cust_id) from Customer ";//简单写法 求和
		String hql4 = " select avg(cust_id) from Customer ";//简单写法 平均值
		String hql5 = " select max(cust_id) from Customer ";//简单写法 最大值
		String hql6 = " select min(cust_id) from Customer ";//简单写法 最小值
		Query query = session.createQuery(hql6);
		Number uniqueResult = (Number) query.uniqueResult();
		//List<Customer> list = query.list();
		System.out.println(uniqueResult);

    6.投影查询

String hql1 = " select cust_name from com.auicyh.domain.Customer ";//完整写法
		String hql2 = " select cust_name,cust_id from Customer ";//简单写法
		//需要在实体类中创建构造方法
		String hql3 = " select new Customer(cust_id,cust_name) from Customer";
		Query query = session.createQuery(hql3);
		
		List<Object[]> list = query.list();
		System.out.println(list);

    7.多表查询 内连接返回数组

String hql = " from Customer c inner join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for(Object[] obj:list){
			System.out.println(Arrays.toString(obj));
		}

    8.多表查询  迫切内连接返回一个对象

String hql = " from Customer c inner join fetch c.linkMens";
		Query query = session.createQuery(hql);
		List<Customer> list = query.list();
		System.out.println(list);

    9.多表查询 左外连接 将两端的对象分别返回放到数组中

	String hql = " from Customer c left join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for(Object[] obj :list){
			System.out.println(Arrays.toString(obj));
		}

    10.多表查询 右外连接

String hql = " from Customer c right join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for(Object[] obj :list){
			System.out.println(Arrays.toString(obj));
		}

二.Criteria语句

    1.基本查询

Criteria criteria = session.createCriteria(Customer.class);
		List<Customer> list = criteria.list();
		System.out.println(list);

    2.条件查询

Criteria criteria = session.createCriteria(Customer.class);
		//criteria.add(Restrictions.idEq(2l));//添加条件
		criteria.add(Restrictions.eq("cust_id", 2l));
		List<Customer> list = criteria.list();
		System.out.println(list);

    3.分页查询

Criteria criteria = session.createCriteria(Customer.class);
		criteria.setFirstResult(0);
		criteria.setMaxResults(2);
		List<Customer> list = criteria.list();
		System.out.println(list);

    4.排序查询

Criteria criteria = session.createCriteria(Customer.class);
			//criteria.addOrder(Order.asc("cust_id"));
			criteria.addOrder(Order.desc("cust_id"));
			List<Customer> list = criteria.list();
			System.out.println(list);

    5.统计查询

Criteria criteria = session.createCriteria(Customer.class);
		criteria.setProjection(Projections.rowCount());
		List<Customer> list = criteria.list();
		System.out.println(list);

    6.离线查询

        6.1 非离线查询图例

        6.2 离线查询图例

        6.3 查询代码

@Test
	//Criteria---离线查询---将到层的代码固定
	public void fun1(){
		//——————————————在web或者service层获取离线的criteria对象———————
		DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);
		dc.add(Restrictions.idEq(4l));
		
		
		
		//——————————————————————————————————————————————————————————
		//1. 获取session对象
		Session session = HibernateUtils.openSession();
		//2.获取事务对象
		Transaction tx = session.beginTransaction();
		//--------------------------------------------
		
		
		Criteria criteria = dc.getExecutableCriteria(session);//将离线查询对象和session对象结合
		List<Customer> list = criteria.list();
		System.out.println(list);
		
		//--------------------------------------------
		//3.提交事务
		tx.commit();
		//4.关闭session
		session.close();
	}

三.原生SQL

    1.内连接与外连接语法

	/**
	 * 知识回固MySql
	 * 内连接
	 *   |-隐式内连接   select * from A,B where b.aid = a.id;
	 *   |-显式内连接   select * from A inner join B on b.aid = a.id;
	 *   
	 * 外连接
	 * |- 左外连接  select * from A left [outer] join B on b.aid = a.id;
	 * |- 右外连接   select * from A right[outer] join B on b.aid = a.id;
	 */

    2.图例

四.查询优化

    1.类级别查询

    结论 （1.）get方法:没有任何策略.调用即立即查询数据库加载数据；

            （2.）load方法当lazy属性设置为TRUE时，查询类时会返回代理对象 在使用时根据关联的session查询数据库加载数据；

            （3.）lazy=false load方法与get方法没有任何区别。调用时既加载数据。

             （4.）为了提高效率建议使用延迟加载 使用懒加载时要确保,调用属性加载数据时,session还是打开的.不然会抛出异常。

    2.关联级别

        （1）集合策略

        （2） 关联属性策略

        （3）结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.

    3.no-session问题解决策略

        

    4.批量抓取