Hibernate中使用HQL查询数据

HQL(Hibernate Query Language)是面向对象的查询语言，这一点也和Java语言的特性比较契合。它和SQL查询语言有些相像，但它使用的是类、对象和属性的概念，而没有表和字段的概念。在Hibernate提供的各种检索方式中，HQL是官方推荐的查询语言，也是使用最广泛的一种检索方式，具体功能有很多，Gd花了好几个小时的功夫实现了其中的大部分，还有一小部分留作日后更新，下面是Gd的代码以及测试结果：

1.

//查询指定类对应表格的所有记录
	public void FindAll()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		User user = new User();
		Query query = session.createQuery("from User");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			user = (User)users.get(i);
			System.out.println(i+"---"+user.getName());
		}
	}

2.

//针对某个属性做查询
	public void FindbyAttribute()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select u.age from User u");
		List userages = query.list();
		for (int i = 0; i < userages.size(); i++) {
			int userage = (int)userages.get(i);
			System.out.println(i+"---"+userage);
		}
	}

3.

//查询两个以上的属性，查询的结果会以数组的方式返回
	public void FindToGroup()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select u.age,u.gender 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]);
		}
	}

这里需要注意的是，查询两个一上的属性会以数组的方式返回，两个属性的数据类型不确定，所以定义一个Object类型的数组来接收数据。

4.

//使用distinct去除资料重复的记录
	public void TestDistinct()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select distinct u.age from User as u");
		List userages = query.list();
		for (int i = 0; i < userages.size(); i++) {
			int userage = (int)userages.get(i);
			System.out.println(i+"---"+userage);
		}
	}

这里需要注意的是需要在User类中提供适当的（也就是在此方法中所要查询的属性）的构造方法，因为实现有参构造函数后，隐式的无参构造函数不再存在，此时在User类中要显式提供无参构造函数，便于创建新的User对象。

5.

//如果User类提供有适当的构建方法(构造方法),则可以在使用HQL时直接指定新建一个对象传回
	public void BackToObject()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select new User(u.name,u.password)from User as u");
		List users = query.list();
		for (int i = 0; i < users.size(); i++) {
			User user = (User)users.get(i);
			System.out.println(user.getName()+"的密码为:"+user.getPassword());
		}
	}

6.

//取得记录集的大小
	public void TestGetCount()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select count(*) from User");
		Object count = (Object)query.uniqueResult();
		System.out.println("共有:"+count+"条记录");
	}

7.

//使用avg()取得属性的平均值
	public void TestGetAvg()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select avg(u.age) from User u");
		Number average = (Number)query.uniqueResult();
		System.out.println("平均年龄为:"+average);
	}

同样的，这里也需要注意参数类型。

8.

//使用where子句限定查询的条件
	public void TestWhere()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("from User u where u.name='张三'");
		//在where子句中使用表达式
		//Query query = session.createQuery("from User u where (u.age/10 = 3)");
		//在where子句上使用and、or
		//Query query = session.createQuery("from User u where (u.age>20) and (u.name = '张三')");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			User user = (User)users.get(i);
			System.out.println(user.getName()+"的密码为:"+user.getPassword());
		}
	}

这里需要注意的是使用where子句进行查询时，HQL语句中是没有select关键词的，若是粗心大意加上select关键词，便会报出：

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: from near line 1, column 8 [select from org.User.User u where u.name='张三']
此类的错误！

9.

//对查询结果使用order by进行排序
	public void TestOrderBy()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("from User u order by u.age");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			User user = (User) users.get(i);
			System.out.println("一个比一个老!"+user);
		}
	}

10.

//使用GROUP　BY子句，自动将指定字段依相同的内容群组
	public void TestGroupBy()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select avg(u.age) from User u group by u.gender");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			double usesavg= (double)users.get(i);
			System.out.println(usesavg);
		}
	}

11.

//结合having子句，对查询出来的组进行限制
	public void TestHaving()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("select u.password, avg(u.age) from User u group by u.password having avg(u.age) > 20");
		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]);
		}
	}

这里的HQL语句比较长，写起来容易出错，所以要细心一些，最后的结果中包含两个属性，所以也要用数组接收。

12.

//在HQL中使用"?"占位符，使用Query的setXXX方法给"?"绑定参数
	public void TestSet()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("from User u where u.name=?");
		query.setString(0 , "赵四");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			User user = (User)users.get(i);
			System.out.println(user);
		}
	}

13.

//使用命名参数表示参数
	public void TestName()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("from User u where u.name=:赵六");
		query.setString("赵六", "赵四");
		List users = query.list();
		for(int i=0;i<users.size();i++)
		{
			User user = (User)users.get(i);
			System.out.println(user);
		}
	}
	

14.

//控制Query分页
	public void TestQuery()
	{
		StaSingleton ss = new StaSingleton();
		Session session = ss.getSessionFactory().openSession();
		Transaction ts = session.beginTransaction();
		Query query = session.createQuery("from User");
		query.setFirstResult(0);
		query.setMaxResults(5);
		List result = query.list();
		for(int i=0;i<result.size();i++)
		{
			User user = (User)result.get(i);
			System.out.println(user);
		}
	}