Hibernate查询(1)--HQL

布满荆棘的人生 2022-09-22 03:42 120阅读 0赞

一，基本查询

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

1，查询所有对象

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());
 			}

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

2，对查询结果进行排序

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());
 			}

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

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

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]);
 			}

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

4，聚集函数

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

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

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);

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

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

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]);

\*(4)sum()计算和

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);

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

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);

5，分组查询

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]);
 			}

二，动态实例查询

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

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());
 			}

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

public Student(String name,int age){
 		this.name = name;
 		this.age = age;
 	}

三，分页查询

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());
 			}

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

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();
 		}
 	
 	}

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

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());
 			}

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

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

2，按照参数名字查询

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());
 			}

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

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

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

逻辑运算符：and,or,not

模式匹配：like