Hibernate查询(1)--HQL

一，基本查询

基本步骤：获得Session->编写HQL语句->创建Query对象->执行查询获得结果

1，查询所有对象

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "from Student";
			Query query = sess.createQuery(hql);
			List list = query.list();
			Iterator itor = list.iterator();
			while (itor.hasNext()) {
				Student s = (Student) itor.next();
				System.out.println(s.getAge());
			}
</span>

注意：HQL语句"from Student"中，Student是类名，而不是表名，因此需要区分大小写，关键字From不需要区分大小写；

2，对查询结果进行排序

<span style="font-size:18px;">			sess = sf.openSession();
                        tx = sess.beginTransaction();
			String hql = "froM Student as b order by b.age asc";//asc是升序 desc是降序
			Query query = sess.createQuery(hql);
			List<Student> list = query.list();
			for(Student st:list){
				System.out.println(st.getAge());
			}</span>

注意：HQL语句也可以使用别名，关键字是as，也可以省略，order by是排序，asc是升序，desc是降序，查询排序后默认是升序；

3，查询对象的部分属性(也叫属性查询或者投影查询)

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select b.name,b.age froM Student as b";//asc是升序 desc是降序
			Query query = sess.createQuery(hql);
		        List list = query.list();
			for(int i=0;i<list.size();i++){
 			    Object obj[] = (Object[])list.get(i);
			    System.out.println(obj[0]);
			}</span>

注意：属性查询时返回的是List，list中是一个Object数组，不再是一个对象，是该条记录的查询的属性组成的一个Object数组，有多少条记录，就有多少个数组被查询到；

4，聚集函数

在HQL语句中，可以使用下列聚集函数

*(1)count()统计记录总数

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select count (b) from Student as b";
			Query query = sess.createQuery(hql);
			long count = (long)query.uniqueResult();
			System.out.println("总数为："+count);</span>

*(2)min()计算最小值

*(3)max()计算最大值

<span style="font-size:18px;">               		sess = sf.openSession();
 			tx = sess.beginTransaction();
			String hql = "select min(b.age),max(b.age) from Student as b";
			Query query = sess.createQuery(hql);
			Object obj[] = (Object[])query.uniqueResult();
			System.out.println("最小值："+obj[0]+"==最大值："+obj[1]);</span>

*(4)sum()计算和

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select sum(b.age) from Student as b";
			Query query = sess.createQuery(hql);
			long sum = (long)query.uniqueResult();
			System.out.println("和："+sum);</span>

*(5)avg()计算平均数

<span style="font-size:18px;">		        sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select avg(b.age) from Student as b";
			Query query = sess.createQuery(hql);
			double avg = (double)query.uniqueResult();
			System.out.println("平均数："+avg);</span>

5，分组查询

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select b.type,count(*) from Student as b group by b.type";
			Query query = sess.createQuery(hql);
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Object obj[] = (Object[])list.get(i);
				System.out.println(obj[0]+"--"+obj[1]);
			}</span>

二，动态实例查询

在属性查询(投影查询)时，返回的查询结果是一个对象数组，不易操作，为了提高检索效率，可将检索出来的属性封装到一个实体类的对象中；

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "select new Student(b.name,b.age) from Student as b";
			Query query = sess.createQuery(hql);
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Student st = (Student)list.get(i);
				System.out.println(st.getName()+"--"+st.getAge());
			}</span>

注意：在HQL语句中使用了Student类的两个属性，这样做必须在实体类Student中添加相对应的构造方法，即含有这两个查找的属性的构造器

<span style="font-size:18px;">public Student(String name,int age){
		this.name = name;
		this.age = age;
	}</span>

三，分页查询

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "from Student as b order by b.age asc";
			Query query = sess.createQuery(hql);
			query.setFirstResult(0);//设定从哪个对象开始查询，这个参数表示这个对象在查询结果中的索引，起始索引为0；
			query.setMaxResults(3);//设定一次返回多少个对象，默认时，返回查询结果中的多有对象
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Student st = (Student)list.get(i);
				System.out.println(st.getName()+"--"+st.getAge());
			}</span>

一般在实际应用中如果需要，下面是模版方法：

<span style="font-size:18px;">public void pagedSearch(int pageIndex,int pageSize){
		conf = new Configuration().configure();
		st = new StandardServiceRegistryBuilder().applySettings(conf.getProperties()).build();
		sf = conf.buildSessionFactory(st);
		try {
			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "from Student as b order by b.age asc";
			Query query = sess.createQuery(hql);
			query.setFirstResult((pageIndex-1)*pageSize);//设定从哪个对象开始查询，这个参数表示这个对象在查询结果中的索引，起始索引为0；
			query.setMaxResults(pageSize);//设定一次返回多少个对象，默认时，返回查询结果中的多有对象
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Student st = (Student)list.get(i);
				System.out.println(st.getName()+"--"+st.getAge());
			}
			
		} catch (Exception e) {
			if (tx != null) {
				tx.rollback();
			}
		} finally {
			sess.close();
			sf.close();
		}
	
	}</span>

四，条件查询
1，按照参数位置查询

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "from Student as b where b.name like ?";
			Query query = sess.createQuery(hql);
			query.setString(0, "%test%");//第一个参数位置为0 ，like关键字试用了%这个通配符，记住，是百分号
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Student st = (Student)list.get(i);
				System.out.println(st.getName()+"--"+st.getAge());
			}</span>

注意：除了setString()方法外，还有如下赋值类型

setDate(),setDouble(),setBoolean(),setInteger(),setTime()

2，按照参数名字查询

<span style="font-size:18px;">			sess = sf.openSession();
			tx = sess.beginTransaction();
			String hql = "from Student as b where b.name like :aa";//参数名字是随便起，但是要以:开头
			Query query = sess.createQuery(hql);
			query.setString("aa","%te%");
			List list = query.list();
			for(int i=0;i<list.size();i++){
				Student st = (Student)list.get(i);
				System.out.println(st.getName()+"--"+st.getAge());
			}</span>

在这两种条件查询时，HQL语句可以使用如下运算符

比较运算符：=,<>,>,>=,<,<=,is null,is not null

范围运算符：in,not in,between,not between

逻辑运算符：and,or,not

模式匹配：like