hibernate学习(7)——HQL查询

1、HQL查询定义

Hibernate查询分类:
1. get/load 根据OID检索
2. 对象视图检索 c.getOrders
3. Sql语句 createSqlQuery
4. Hql语句 createQuery
5. Criteria查询 createCriteria
HQL 详解
QBC 详解

2、Hql查询所有

@Test
	public void test01() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

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

		System.out.println(list);

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

　　

3、 Hql选择查询所有

@Test
    public void test02() {
        Session session = HibernateUtil.openSession();
        Transaction tran = session.beginTransaction();

        Query query = session
                .createQuery("select c.id, c.name from Customer c");

        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println(Arrays.toString(obj));
        }

        System.out.println(list);

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

4、投影查询

// 投影查询
	// 选择查询的基础上,想把查询结果封装到对象中
	@Test
	public void test03() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

		Query query = session
				.createQuery("select new Customer(c.id,c.name) from Customer c");

		List<Customer> list = query.list();

		System.out.println(list);

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

5、排序查询

// 排序
    @Test
    public void test04() {
        Session session = HibernateUtil.openSession();
        Transaction tran = session.beginTransaction();

        Query query = session.createQuery("from Customer c order by c.id desc");
        List<Customer> list = query.list();

        System.out.println(list);

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

6、分页查询

@Test
	public void test05() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

		Query query = session.createQuery("from Customer c order by c.id desc");

		// limit ?,? setFirstResult,setMaxResults
		// setFirstResult: (当前页数-1)*每页最大记录数

		// 从哪个索引开始取数据.包裹索引本身的记录
		query.setFirstResult(0);
		// 查询出多少条数据
		query.setMaxResults(3);

		List<Customer> list = query.list();

		System.out.println(list);
		tran.commit();
		session.close();
	}

7、聚合函数查询

// 聚合函数
	@Test
	public void test07c() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

		// ------------------------------------------------
		// Query query =
		// session.createQuery(" select count(*) from  Customer c ");
		// Query query =
		// session.createQuery(" select avg(c.id) from  Customer c ");
		// Query query =
		// session.createQuery(" select sum(c.id) from  Customer c ");
		// Query query =
		// session.createQuery(" select max(c.id) from  Customer c ");
		Query query = session.createQuery("select count(*) from Customer c");
		Object count = query.uniqueResult();

		System.out.println(count);
		tran.commit();
		session.close();
	}

　　8、绑定查询

// 绑定参数
	@Test
	public void test06() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

		// 参数1:?占位符的索引 ,第一个问好索引为0
		Query query = session.createQuery("from Customer c where c.id= ?");

		// 参数1:?占位符的索引 ,第一个问好索引为0
		query.setInteger(0, 6);
		Customer cust = (Customer) query.uniqueResult();
		System.out.println(cust);
		tran.commit();
		session.close();
	}

　　9、分组查询

// 分组
	// group by .. having..
	@Test
	public void test08() {
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();

		Query query =session.createQuery(" select o.customer, count(o)  " +
				"					from Order o " +
				"					group by o.customer " +
				"					having  count(o) > 2 ");
		List<Object[]> list = query.list();

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

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

10、 连接查询

1.交叉连接 ，等效 sql 笛卡尔积（不常用）

2.隐式内连接，等效 sql 隐式内连接

3.内连接，等效sql内连接

4.迫切内连接，hibernate底层使用 内连接。

5.左外连接，等效sql左外连接

6.迫切左外连接，hibernate底层使用 左外连接

7.右外连接，等效sql右外连接

 

package com.alice.hibernate02.hql;



import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.alice.hibernate02.util.HibernateUtil;

//HQL详解2-表连接
public class Demo02 {
	//交叉连接 => 笛卡尔积
		//开发时要避免出现笛卡尔积
	@Test
	public void test01(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c,Order o");
		
		List<Object[]> list = query.list();
		for(Object[] obj:list){
			System.out.println(Arrays.toString(obj));
		}
		
		tran.commit();
		session.close();
	}
	//内连接 
		//隐式内连接 => 在笛卡尔积基础上过滤无效数据
	@Test
	public void test02(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c,Order o where o.customer = c");
		
		List<Object[]> list = query.list();
		for(Object[] obj:list){
			System.out.println(Arrays.toString(obj));
		}
		
		tran.commit();
		session.close();
	}
	
	//内连接
		//显式内连接( 非迫切)=> inner join
		// List<Object[]>
		// Object[] => [Customer,Order]
		// 将父 与 子 对象装入数组中分别 返回
	@Test
	public void test03(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c inner join c.orders");
		
		List<Object[]> list = query.list();
		for(Object[] obj:list){
			System.out.println(Arrays.toString(obj));
		}
		
		tran.commit();
		session.close();
	}
	//内连接
		//显式内连接(迫切)=> inner join
		// List<Customer> 
		// 迫切连接会将 子装入父中,组装成一个对象
	@Test
	public void test04(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c inner join fetch c.orders");
		
		List<Object> list = query.list();
		for(Object obj:list){
			System.out.println(obj);
		}
		
		tran.commit();
		session.close();
	}
	
	//左外连接
	//left [outer] join
	//右外连接
		//right [outer] join
	@Test
	public void test05(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c left outer  join c.orders");
		
		List<Object[]> list = query.list();
		for(Object[] obj:list){
			System.out.println(Arrays.toString(obj));
		}
		
		tran.commit();
		session.close();
	}
	
	//左外连接 迫切
		//left [outer] join fetch
	@Test
	public void test06(){
		Session session = HibernateUtil.openSession();
		Transaction tran = session.beginTransaction();
		
		Query query = session.createQuery("from Customer c left outer  join fetch c.orders");
		
		List<Object> list = query.list();
		for(Object obj:list){
			System.out.println(obj);
		}
		
		tran.commit();
		session.close();
	}
}