Hibernate4学习笔记(四)： 使用HQL语句从数据库检索数据(带检索条件)

日期： 2016-7-19

内容： 使用HQL语句从数据库检索数据(带检索条件)

1、数据库操作方法：

@Test
	public void queryById()
	{
		//获得session
		Session session = hibernate.getSession();
		
		//获得事务
		Transaction tx = hibernate.getTransaction();
		
		//hql查询语句：模糊查询
		String hql = "from Users users where users.email like ? ";
		
		//创建query对象
		Query query = session.createQuery(hql);
		
		//HQl语句中的“？”代表查询的传入值
		query.setString(0, "%l%");//对邮箱进行模糊查询
		
		//执行HQL语句
		List<Users> list = query.list();
		
		//遍历查询结果
		for(Users user: list){
			//System.out.println("UserName: "+user.getUsername()+"  Password:"+user.getPassword() +" Email:"+user.getEmail());
			System.out.println(user);
		}
		
	}

带检索ID的检索：

检索结果：

Hibernate: 
    select
        users0_.id as id1_,
        users0_.address as address1_,
        users0_.birthday as birthday1_,
        users0_.email as email1_,
        users0_.password as password1_,
        users0_.phone as phone1_,
        users0_.sex as sex1_,
        users0_.username as username1_ 
    from
        user_info users0_ 
    where
        users0_.email like ?
Users [id=1, username=yuashanshan, password=yuashanshan, email=ldayanlong@163.com, phone=18888888888, address=云端城市！, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=2, username=luoting, password=luoting..123, email=lusing@163.com, phone=1381723113, address=上海市金山号, sex=女, birthday=2016-07-19 15:16:13.0]+

Users [id=3, username=刘德华, password=liudehjua..123, email=liudehua@163.com, phone=88888888888, address=香港特别行政区浅水湾8888号, sex=男, birthday=2016-07-19 15:16:13.0]+

按照参数的位置进行查询：

@Test
	public void queryById2()
	{
		//hql查询语句：模糊查询
		String hql = "from Users users where users.email like ? ";
		
		Query query = hibernate.getQuery(hql);
		//HQl语句中的“？”代表查询的传入值
		query.setString(0, "%l%");//对邮箱进行模糊查询
		
		Users user = new Users();
		
		//遍历查询结果
		hibernate.printQueryInfo(query, user);
	}
	

检索结果：

Hibernate: 
    select
        users0_.id as id1_,
        users0_.address as address1_,
        users0_.birthday as birthday1_,
        users0_.email as email1_,
        users0_.password as password1_,
        users0_.phone as phone1_,
        users0_.sex as sex1_,
        users0_.username as username1_ 
    from
        user_info users0_ 
    where
        users0_.email like ?
Users [id=1, username=yuashanshan, password=yuashanshan, email=ldayanlong@163.com, phone=18888888888, address=云端城市！, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=2, username=luoting, password=luoting..123, email=lusing@163.com, phone=1381723113, address=上海市金山号, sex=女, birthday=2016-07-19 15:16:13.0]+

Users [id=3, username=刘德华, password=liudehjua..123, email=liudehua@163.com, phone=88888888888, address=香港特别行政区浅水湾8888号, sex=男, birthday=2016-07-19 15:16:13.0]+

按照参数的名称进行查询：

@Test
	public void queryByNane()
	{
		//HQL查询语句
		String hql = "from Users user where user.address=:userAddress";
		
		//获得query对象
		Query query = hibernate.getQuery(hql);
		//设置参数的信息
		query.setString("userAddress", "云南省昭通市镇雄县塘房镇白鸟村水沟组64号");
		
		//获得对象实例
		Users user = new Users();
		
		//遍历打印执行结果
		hibernate.printQueryInfo(query, user);
	}

检索结果：

Hibernate: 
    select
        users0_.id as id1_,
        users0_.address as address1_,
        users0_.birthday as birthday1_,
        users0_.email as email1_,
        users0_.password as password1_,
        users0_.phone as phone1_,
        users0_.sex as sex1_,
        users0_.username as username1_ 
    from
        user_info users0_ 
    where
        users0_.address=?

使用QBC操作：

@Test
	public void queryByCriteria()
	{
		//获得session对象
		Session session = hibernate.getSession();
		
		//创建Criteria对象：查询所有的User对象
		Criteria criteria = session.createCriteria(Users.class);
		
		//对查询结果按照出生日期的降序排列
		criteria.addOrder(Order.desc("birthday"));
		
		//执行查询获得结果
		List list = criteria.list();
		
		//遍历查询结果
		Iterator itor = list.iterator();
		
		while(itor.hasNext()){
			Users user = (Users)itor.next();
			System.out.println(user);
		}
	}

操作结果：

Hibernate: 
    select
        this_.id as id1_0_,
        this_.address as address1_0_,
        this_.birthday as birthday1_0_,
        this_.email as email1_0_,
        this_.password as password1_0_,
        this_.phone as phone1_0_,
        this_.sex as sex1_0_,
        this_.username as username1_0_ 
    from
        user_info this_ 
    order by
        this_.birthday desc
Users [id=1, username=yuashanshan, password=yuashanshan, email=ldayanlong@163.com, phone=18888888888, address=云端城市！, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=2, username=luoting, password=luoting..123, email=lusing@163.com, phone=1381723113, address=上海市金山号, sex=女, birthday=2016-07-19 15:16:13.0]+

Users [id=3, username=刘德华, password=liudehjua..123, email=liudehua@163.com, phone=88888888888, address=香港特别行政区浅水湾8888号, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=4, username=张学友, password=zhangxueyou, email=zhangxueyou@163.com, phone=99999999999, address=香港特别行政区湾仔码头, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=5, username=feiyuqing, password=feiyuqing..123, email=feiyuqing@163.com, phone=12345678910, address=大陆行货, sex=男, birthday=2016-07-19 15:16:13.0]+

Users [id=6, username=wangbaoqiang, password=wangbaoqiang..456, email=wangbaoqiang@163.com, phone=1235689723, address=上海市浦江镇大十字123号, sex=男, birthday=2016-07-19 15:16:13.0]+