11.hibernate的查询--QBC查询与SQL查询

QBC查询

QBC（Query By Criteria）是hibernate提供的一套查询API，跨数据库。

1.基本查询

查询User的对象

	@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			
			查询Emp类对象的列表
			List<Emp> empList = c.list();
			for(Emp emp :empList){
				System.out.println(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

单个查询

	@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			PropertyProjection p = Projections.property("ename");
			c.setProjection(p);
			List<String> emplist = c.list();
			for(String emp :emplist){
				System.out.println(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

多个查询

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//创建要查询的列的集合
			ProjectionList pp = Projections.projectionList();
			//获得具体要查询的属性
			PropertyProjection p1 = Projections.property("ename");
			PropertyProjection p2 = Projections.property("ename");
			//把要查询的属性加入到集合中
			pp.add(p1);
			pp.add(p2);
			//设置具体要查询的列
			c.setProjection(pp);

			List<Object [] > objList = c.list();
			for(Object[] obj :objList){
				System.out.println(""+obj[0]+obj[1]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

2.统计查询

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//设置要查询的项
			c.setProjection(Projections.rowCount());
			Object obj = c.uniqueResult();
			System.out.println("总记录数"+obj);
			
			c.setProjection(Projections.max("empNo"));
			Object obj1 = c.uniqueResult();
			System.out.println("最大id"+obj1);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

3.分组统计查询

/*
 * 这里我按照性别查id最大值
 * */
	@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//创建查询项的集合
			ProjectionList pp = Projections.projectionList();
			//创建查询项
			PropertyProjection p1 = Projections.property("gender");
			//创建统计项
			AggregateProjection max = Projections.max("empNo");
			//设置按照那一项分组
			PropertyProjection fz = Projections.groupProperty("gender");
			//把要查询的项加入集合
			pp.add(p1);
			pp.add(max);
			pp.add(fz);
			//把查询集合设置给qbc接口
			c.setProjection(pp);
			List<Object []> objArr = c.list();
			for(Object[] obj : objArr){
				System.out.println(" "+obj[0]+obj[1]);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

4.排序

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//设置排序字段
			c.addOrder(Order.desc("empNo"));
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

5.分页

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//设置排序字段
			c.addOrder(Order.desc("empNo"));
			//设置开始行号 5代表从第2页开始 我这里11条数据
			c.setFirstResult(5);
			//设置每页记录数
			c.setMaxResults(5);
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

6.限定查询

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//创建查询条件 id=5的
			Criterion ct = Restrictions.eq("empNo",5);
			//把查询条件设置给qbc的查询接口
			c.add(ct);
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

我们通过Restrictions来设置限定查询的条件

短语	含义
Restrictions.eq	等于=
Restrictions.allEq	使用Map,使用key/value进行多个等于的判断
Restrictions.gt	大于>
Restrictions.ge	大于等于>=
Restrictions.lt	小于<
Restrictions.le	小于等于<=
Restrictions.between	对应sql的between子句
Restrictions.like	对应sql的like子句
Restrictions.in	对应sql的in子句
Restrictions.and	and 关系
Restrictions.or	or关系
Restrictions.sqlRestriction	Sql限定查询

in的使用

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//定义要查询的in的集合（也可以是数组）
			Integer [] empids = new Integer[]{1,2,3};
			//设置查询条件
			Criterion ct = Restrictions.in("empNo", empids);
			c.add(ct);
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

 and的使用

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//and的条件的使用 
			Criterion ct = Restrictions.and(Restrictions.ge("empNo", 3), 
											Restrictions.eq("ename", "zhangyan6"));

			c.add(ct);
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

or也类似。

7.模糊查询

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			//创建qbc的查询接口的实现类
			Criteria c = session.createCriteria(Emp.class);
			//like的模糊查询like的第二个参数和sql语法一致，只是不需要单引号
			Criterion ct = Restrictions.like("ename", "%zh%");

			c.add(ct);
			List<Emp> list = c.list();
			for(Emp id : list){
				System.out.println(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}

 

SQL查询

@Test
	public void testQuery1(){
		Session session = HibernateUtils.getSession();
		try {
			String sql = "select ename,gender from t_emp";
			SQLQuery s = session.createSQLQuery(sql);
			List<Object [] > listArr = s.list();
			for(Object []  obj : listArr){
				System.out.println(""+obj[0]+obj[1]  );
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateUtils.closeResource(session);
		}
	}