Hibernate查询--HQL

最新推荐文章于 2019-03-03 09:55:18 发布

原创最新推荐文章于 2019-03-03 09:55:18 发布 · 175 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍Hibernate查询语言(HQL)的基本用法，包括简单查询、条件查询、聚合查询、排序及分组等功能，并演示如何通过HQL实现更新和删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Hibernate语言查询( Hibernate Query Language，HQL) ：它是完全面向对象的查询语句，查询功能非常强大，具备继承、多态和关联等特性。Hibernate官方推荐使用HQL进行查询。

Hibernate查询语言（HQL）是面向对象的查询语言，其结构与SQL查询相似，不同的是，HQL查询的主体是持久化类而不是数据表。

HQL提供了一种应用和数据库之间的抽象，提高了可移植性。

HQL子句本身大小写无关，但是其中出现的类名和属性名必须注意大小写。

1、使用HQL查询最简单的例子，就是查询指定类对应表格的所有记录

@Test 
	public void testQueryUser(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User");
            List<User> users = query.list();
            for(User u:users)
            {
            	System.out.println(u.getUsername() + " " + u.getAge());
            }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

2、针对某个属性作查询

@Test 
	public void testQueryUser2(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User as u where u.username='bb'");
            List<User> users = query.list();
            for(User u:users)
            {
            	System.out.println(u.getUsername() + " " + u.getAge());
            }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

3、查询两个以上的属性，查询的结果会以数组的方式返回

@Test
	public void testQueryUser3(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select u.username,u.password,u.age from User as u");
            List users = query.list();
            for(int i=0;i<users.size();i++) {
            	Object obj[]=(Object[])users.get(i);
            	System.out.println(obj[0]+"的密码为:"+obj[1]);
            }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

4、使用distinct去除资料重复的记录

@Test
	public void testQueryUser4(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select distinct u.username from User as u");
            List usernames = query.list();
            for(int i=0;i<usernames.size();i++) {
            	String username = (String)usernames.get(i);
            	System.out.println(i+"--"+username);
            }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

5、取得记录集的大小：

@Test
	public void countUser(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select count(*) from User");
			Object count  =  (Object)query.uniqueResult();
			System.out.println("共有"+count+"条记录");
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

6、使用avg()取得属性的平均值

@Test
	public void avgAgeUser(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select avg(u.age) from User u");
			//Object average =(Object)query.uniqueResult();
			Number average =(Number)query.uniqueResult();
			System.out.println("平均年龄为："+average);
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

7、使用where子句来限定查询的条件，除了 = 运算之外，还有 >、>=、<、<=、!= 或 <>等比较运算

@Test
	public void selectUsername(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User u where u.username='aaa'");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

8、在where子句上进行表达式

@Test
	public void selectUsername1(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User as u where (u.age / 10 = 3)");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

9、在where子句上使用and、or

@Test
	public void selectUsername2(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User as u where u.age > 20 and u.username='aaa'");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

10、is not nullL与is null则可以测试字段值是否为空值

@Test
	public void selectUsername3(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user where user.username is not null");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

11、between可以测试字段值是否在指定的范围之内

@Test
	public void selectUsername4(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user where user.age between 30 and 40");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

12、使用in或not in来测试字段值是否在您指定的集合中

@Test
	public void selectUsername5(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User u where u.username in('rere','大大')");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

13、like或not like可以让您进行模糊条件搜寻

@Test
	public void selectUsername6(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user where user.username like 'a%'");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

14、对于支持子查询的数据库来说，Hibernate支持在查询中嵌套子查询

@Test
	public void selectUsername7(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User u where u.age>(select avg(age) from User)");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

15、对查询结果使用order by进行排序

@Test
	public void selectUsername8(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user order by user.age");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

16、可使用desc反排序

@Test
	public void selectUsername9(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user order by user.age desc");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

17、同时指定两个以上的排序方式

@Test
	public void selectUsername10(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User user order by user.age desc,user.username");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

18、使用GROUP BY子句，自动将指定的字段依相同的内容群组
例如依字段“password"分组并作平均：

@Test
	public void selectUsername11(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select user.password, avg(user.age) from User user group by user.password having avg(user.age) > 20");
			List list=query.list();
			Iterator it = list.iterator();
	        while(it.hasNext()){
	            Object[] results = (Object[]) it.next();
	            System.out.println(results[0]+","+results[1]);
	        }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		}
	}

19、结合having子句,对查询出来的组进行限制

@Test
	public void selectUsername12(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("select user.password, avg(user.age) from User user group by user.password having avg(user.age) > 20");
			List list=query.list();
			Iterator it = list.iterator();
	        while(it.hasNext()){
	            Object[] results = (Object[]) it.next();
	            System.out.println(results[0]+","+results[1]);
	        }
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

20、在HQL语句中使用“?”占位符，使用query的setXXX方法给“?”绑定参数

@Test
	public void selectUsername13(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User u where u.username=?");
			query.setString(0,"aaa");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

21、“？”参数以外，还可以使用命名参数来表示参数。同一个命名参数可以在一个查询中出现多次。

@Test
	public void selectUsername14(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User u where u.username=:name");
			query.setString("name","aaa");
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

22、控制分页Query有两个方法
一是：setFirstResult(int firstResult):设定从那个对象开始检索。

二是：setMaxResults(int maxResult):设置一次最多能检索出对象的数目。

@Test
	public void selectUsername15(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("from User");
			query.setFirstResult(0);
			query.setMaxResults(5);
			List users=query.list();
			for(int i=0;i<users.size();i++) {
				User user=(User)users.get(i);
				System.out.println(user.getUsername()+"的密码为："+user.getPassword());
			}
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}

23、在Hibernate 3中，HQL新增了update与delete语句，可以直接使用HQL指定更新或删除

@Test
	public void update1(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("update User set username='ah' where username='aaa'");
			query.executeUpdate();
			
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}
	@Test
	public void delete1(){
		// TODO Auto-generated method stub
		SessionFactory sf = null;
		Session s = null;
		Transaction ts = null;
		
		try {
			sf = HibernateUtils.getSession();
			s = sf.getCurrentSession();
			ts = s.beginTransaction();
			Query query = s.createQuery("delete User u where u.username='she'");
			query.executeUpdate();
			
			ts.commit();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			if(ts != null)
			{
				ts.rollback();
			}
			e.printStackTrace();
		} 
	}