hibernate ---hql

* uniqueResult方法

如果我们检索一个对象，明确知道最多只有一个对象，则建议使用该方法:

具体用法如下:

Student s=(Student) session.createQuery("from Student where sid='20050003'").uniqueResult();

                     System.out.println(s.getSname());

*distinct的用法

过滤重复的记录

//比如，显示所有学生的性别和年龄.

                     List list=session.createQuery("select distinct sage,ssex from Student").list();

                     for(int i=0;i<list.size();i++){

                            Object []  objs=(Object[]) list.get(i);

                            System.out.println(objs[0].toString()+" "+objs[1].toString());

                     }

*between and..

List list=session.createQuery("select distinct sage,ssex,sname from Student where sage between 20 and 22").list();

                     for(int i=0;i<list.size();i++){

                            Object []  objs=(Object[]) list.get(i);

                            System.out.println(objs[0].toString()+" "+objs[1].toString()+objs[2].toString());

                     }

*in /not in

//查询计算机系和外语系的学生信息

                     List<Student> list=session.createQuery("from Student where sdept in ('计算机系','外语系')").list();

                     //取出1. for 增强

                     for(Student s:list){

                            System.out.println(s.getSname()+" "+s.getSaddress()+" "+s.getSdept());

                     }

*group by使用

//显示各个系的学生的平均年龄

List<Object[]> list=session.createQuery("select avg(sage),sdept from  Student group by sdept").list();

                     //取出1. for 增强

                     for(Object[] obj:list){

                            System.out.println(obj[0].toString()+" "+obj[1].toString());

                     }

//having的使用

                     //1.对分组查询后的结果，进行筛选:比如请显示人数大于3的系名称

                     //a. 查询各个系分别有多少学生.

                     List<Object[]> list=session.createQuery("select count(*) as c1,sdept from  Student group by sdept having count(*)>3").list();

                     //取出1. for 增强

                     for(Object[] obj:list){

                            System.out.println(obj[0].toString()+" "+obj[1].toString());

                     }

//2查询女生少于200人的系

                     //a.查询各个系的女生有多个个

                     List<Object[]> list=session.

                     createQuery("select count(*) as c1,sdept from  Student where ssex='F' group by sdept").list();

                     //取出1. for 增强

                     for(Object[] obj:list){

                            System.out.println(obj[0].toString()+" "+obj[1].toString());

                     }

//1.查询计算机系共多少人?->如果我们返回的是一列数据

                     //这时我们的取法是直接取出list->object 而不是 list->Object[]

                     List<Object[]> list=session.

                     createQuery("select sage from  Student where sdept='计算机系'").list();

                     //取出1. for 增强

                     for(Object obj:list){

                            System.out.println(obj.toString());

                     }

3.查询选修11号课程的最高分和最低分.

                     List<Object[]> list=session.

                     createQuery("select 11,max(grade),min(grade) from Studcourse where course.cid=11").list();

                     //取出1. for 增强

                     for(Object[] obj:list){

                            System.out.println(obj[0].toString()+" max="+obj[1].toString()+" min="+obj[2].toString());

                     }

//计算各个科目不及格的学生数量.(学生练习!)

                     List<Object[]> list=session.

                     createQuery("select count(*),student.sdept from Studcourse where grade<60 group by student.sdept").list();

                     //取出1. for 增强

                     for(Object[] obj:list){

                            System.out.println(obj[0].toString()+" "+obj[1].toString());

                     }

<!--[if !supportLists]-->u  <!--[endif]-->参数绑定案例 （jdbc->PreparedStatement setXXX）

使用参数绑定的好处有3:

<!--[if !supportLists]-->1.       <!--[endif]-->可读性提高, 2 效果高 3,防止 sql注入漏洞

? 面试题:如果不使用参数绑定，怎样防止登录时， sql注入?

name  password  

思路: 1. 通过用户名，查询出该用户名在数据库中对应的密码，然后再与用户输入的秘密比较，如果相等，则用户和法，否则，非法.

参数绑定有两种形式

Query q=session.createQuery(from Student where sdept=:dept and sage>:age)

如果我们的参数是 :冒号形式给出的，则我们的参数绑定应当这样:

List<Student> list=session.createQuery("from Student where sdept=:a1 and sage>:sage")

                     .setString("a1", "计算机系").setString("sage", "2").list();

还有一种形式:

Query q=session.createQuery(from Student where sdept=? and sage>?)

如果我们的参数是以 ? 形式给出的则，参数绑定应当:

List<Student> list=session.createQuery("from Student where sdept=? and sage>?")

                     .setString(0, "计算机系").setString(1, "2").list();

参数的绑定，可以分开写：形式如下:

Query query=session.createQuery("from Student where sdept=? and sage>?");

                     query.setString(0, "计算机系");

                     query.setString(1, "2");

                     List <Student> list=query.list();

                     for(int i=0;i<list.size();i++){

                            Student s= list.get(i);

                            System.out.println(s.getSname()+" "+s.getSage());

                     }