六、hql语句
一、选课系统
1.建表
2.填充数据
INSERT INTO `student` VALUES ('20040001', '林青霞', 'F', '计算机系', '22', '上海'); INSERT INTO `student` VALUES ('20040002', '刘德华', 'M', '外语系', '23', '南京'); INSERT INTO `student` VALUES ('20050006', '周润发', 'M', '数学家', '20', '湖北'); INSERT INTO `student` VALUES ('200050003', '成龙', 'M', '化学系', '21', '山东'); INSERT INTO `student` VALUES ('200050005', '周华健', 'M', '生物系', '24', '山东'); INSERT INTO `student` VALUES ('200500004', '林可欣', 'F', '计算机系', '22', '北京'); INSERT INTO `course` VALUES ('11', 'java编程', '6'); INSERT INTO `course` VALUES ('21', 'c++课程', '4'); INSERT INTO `course` VALUES ('31', 'oracle', '3'); INSERT INTO `course` VALUES ('41', 'javaEE', '100'); INSERT INTO `course` VALUES ('51', 'linux', '1'); INSERT INTO `studcourse` VALUES ('1', '20040001', '11', '90'); INSERT INTO `studcourse` VALUES ('2', '20040001', '21', '19'); INSERT INTO `studcourse` VALUES ('3', '20050003', '21', '45'); INSERT INTO `studcourse` VALUES ('4', '20050004', '41', '99'); INSERT INTO `studcourse` VALUES ('5', '20050006', '11', '39');
3.逆向工程(优先生成主表，即student表和course表，再生成studcourse表)
// Course表 private Integer cid; private String cname; private Integer ccredit; private Set studcourses = new HashSet(0); // Studcourse表 private Integer stuCourseId; private Student student; private Course course; private Integer grade; //Student表 private Long sid; private String sname; private String ssex; private String sdept; private Integer sage; private String saddress; private Set studcourses = new HashSet(0);
这里我们可以看出，由于表之间的主外键关系，一个学生可以选多门课，一门课可以被多个人选，于是就生成了如上的domain对象。而且，在hbm.xml文件中还有<set>标签和<one-to-many>等标签。
二、hql查询
//查询学生的所有属性 Session session=HibernateUtil.getCurrentSession(); Transaction ts=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); //查询所有属性可以不用select * from List<Student> list = session.createQuery("from Student").list(); for(Student s:list){ System.out.println(s.getSname()+" "+s.getSaddress()); } ts.commit(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); if(ts!=null) ts.rollback(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } }
//当然list还有另一种遍历方式 Iterator<Student> iterator = list.iterator(); while(iterator.hasNext()){ Student s=iterator.next(); System.out.println(s.getSname()+" "+s.getSage()); }
//查询部分属性 //在jdbc里，建议需要什么字段就查询什么字段，但在hibernate里，建议把整个对象都查出来 //但是作为了解，我们还是需要知道怎么查询部分字段 List list = session.createQuery("select sname,sage from Student").list(); for(int i=0;i<list.size();i++){ Object[] obj = (Object[]) list.get(i); System.out.println(obj[0]+" "+obj[1]); } List<Object[]> list = session.createQuery("select sname,sage from Student").list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//注意，查询两个字段以上就是对象数组，查询一个字段就是对象 List list = session.createQuery("select sage from Student").list(); for(int i=0;i<list.size();i++){ System.out.println(list.get(i)); }
//查询每个人选择的课程数 List<Student> list = session.createQuery("from Student").list(); for(Student s:list){ System.out.println(s.getSname()+"选择了"+s.getStudcourses().size()+"门课"); }
//查询每个人选择的课程 List<Student> list = session.createQuery("from Student").list(); for(Student s:list){ if(s.getStudcourses().size()==0) System.out.println(s.getSname()+"没有选课"); else{ Set<Studcourse> set = s.getStudcourses(); for(Studcourse sc:set){ System.out.println(s.getSname()+"选择了"+sc.getCourse().getCname()); } } }
//uniqueResult(当你确定查询结果至多只有一组时使用,如果有多条会报错) Student s= (Student) session.createQuery("from Student where sid='20050003'").uniqueResult(); System.out.println(s.getSname());
//distinct关键字取消重复值 List list = session.createQuery("select distinct ssex from Student").list(); for(int i=0;i<list.size();i++){ System.out.println(list.get(i)); }
//between...and List list = session.createQuery("select sname,sage from Student where sage between 20 and 22").list(); for(int i=0;i<list.size();i++){ Object[] obj=(Object[]) list.get(i); System.out.println(obj[0]+" "+obj[1]); }
//in和not in List list = session.createQuery("select sname,sdept from Student where sdept in('计算机系','外语系')").list(); for(int i=0;i<list.size();i++){ Object[] obj=(Object[]) list.get(i); System.out.println(obj[0]+" "+obj[1]); }
//group by显示各个系的平均年龄 List list = session.createQuery("select avg(sage),sdept from Student group by sdept").list(); for(int i=0;i<list.size();i++){ Object[] obj=(Object[]) list.get(i); System.out.println(obj[0]+" "+obj[1]); }
//having对分组查询后的结果再进行筛选 //人数大于1的系名称 List<Object[]> list = session.createQuery("select count(*),sdept from Student group by sdept having count(*)>1").list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//聚集函数--count(),avg(),max(),min(),sum() List<Object[]> list = session.createQuery("select max(grade),min(grade) from Studcourse where cid=11").list(); for(Object[] obj:list){ System.out.println("最高分:"+obj[0]+" 最低分："+obj[1]); }
//查询所有分数低于60分的学生名、课程名、分数 List<Object[]> list = session.createQuery("select student.sname,course.cname,grade from Studcourse where grade<60").list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]+" "+obj[2]); } --在Studcourse表里有属性student和属性course，所以能够这样访问
//查询每个系不及格的人数 List<Object[]> list = session.createQuery("select student.sdept,count(*) from Studcourse where grade<60 group by student.sdept").list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//按年龄从小到大排序，取出前三个学生名字、年龄 List<Object[]> list = session.createQuery("select sname,sage from Student order by sage") .setFirstResult(0) //表示从第一个开始取 .setMaxResults(3) //最多取出3个 .list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//分页显示所有数据 public static void showResultByPage(int pageSize){ int pageNow=1;//当前是第几页 int pageCount=1;//总共需要分几页 int rowCount=1;//总共有多少条数据 Session session=HibernateUtil.getCurrentSession(); Transaction ts=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); rowCount = Integer.parseInt(session.createQuery("select count(*) from Student").uniqueResult().toString()); pageCount=(rowCount-1)/pageSize+1;
//循环显示每页 for(int i=1;i<=pageCount;i++){ System.out.println("************第"+i+"页************"); List<Student> list = session.createQuery("from Student") .setFirstResult((i-1)*pageSize) .setMaxResults(pageSize) .list(); for(Student s:list){ System.out.println(s.getSname()+" "+s.getSdept()); } } ts.commit(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); if(ts!=null) ts.rollback(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } } }
//参数绑定 //使用setParameter()的方法来确定变量的值，使sql语句可读性好，程序性能提高，防止sql注入 ①替代问号 Query query = session.createQuery("select sname,sage from Student where sid=?"); query.setString(0, "20040001"); List<Object[]> list = query.list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); } ②替代冒号加字符串 Query query = session.createQuery("select sname,sage from Student where sid=:ss"); query.setString("ss", "20040001"); List<Object[]> list = query.list(); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//根据参数绑定的方法提供同一的查询方法 public static List executeQuery(String hql,String[] parameters){ Session session=null; Transaction ts=null; List list=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); Query query = session.createQuery(hql); if(parameters!=null&¶meters.length>0){ for(int i=0;i<parameters.length;i++){ query.setString(i, parameters[i]); } } list = query.list(); ts.commit(); } catch (Exception e) { // TODO: handle exception if(ts!=null) ts.rollback(); e.printStackTrace(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } } return list; }
//这里由于sessionFactory、session等都已经在HibernateUtil.executeQuery处执行了 public static void main(String[] args) { // TODO Auto-generated method stub String hql="select sname,sage from Student where sid=?"; String[] parameters={"20040001"}; List<Object[]> list = HibernateUtil.executeQuery(hql,parameters); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); } }
//统一的分页查询方法 public static List excuteQueryByPage(String hql,String[] parameters,int pageSize,int pageNow){ Session session=null; Transaction ts=null; List list=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); Query query = session.createQuery(hql); if(parameters!=null&¶meters.length>0){ for(int i=0;i<parameters.length;i++){ query.setString(i, parameters[i]); } } query.setFirstResult((pageNow-1)*pageSize); query.setMaxResults(pageSize); list = query.list(); ts.commit(); } catch (Exception e) { // TODO: handle exception if(ts!=null) ts.rollback(); e.printStackTrace(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } } return list; }
//统一的添加方法 public static void save(Object obj){ Session session=null; Transaction ts=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); session.save(obj); ts.commit(); } catch (Exception e) { // TODO: handle exception if(ts!=null) ts.rollback(); e.printStackTrace(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } } }
//统一的修改和删除 public static void executeUpdate(String hql,String[] parameters){ Session session=null; Transaction ts=null; try { session=HibernateUtil.getCurrentSession(); ts=session.beginTransaction(); Query query = session.createQuery(hql); if(parameters!=null&¶meters.length>0){ for(int i=0;i<parameters.length;i++){ query.setString(i, parameters[i]); } } query.executeUpdate(); ts.commit(); } catch (Exception e) { // TODO: handle exception if(ts!=null) ts.rollback(); e.printStackTrace(); }finally{ if(session!=null&&session.isOpen()){ session.close(); } } }
//复杂查询 //显示所有选择了21号课程的学生信息 String hql="select student.sname,student.sdept from Studcourse where course.cid=21"; List<Object[]> list = HibernateUtil.executeQuery(hql,null); for(Object[] obj:list){ System.out.println(obj[0]+" "+obj[1]); }
//hql不能直接查询对象，查询的必须是能够得到的字段 //例如下面hql，执行都将会报错 String hql="select student from Studcourse where course.cid=21"; List<Student> list = HibernateUtil.executeQuery(hql,null); for(Student s:list){ System.out.println(s.getSname()+" "+s.getSdept()); } String hql="select student.* from Studcourse where course.cid=21"; List<Object[]> list = HibernateUtil.executeQuery(hql,null); for(Object[] s:list){ System.out.println(s[0]+" "+s[1]); }
//子查询 String hql="from Studcourse where course.cid=21"; List<Studcourse> list = HibernateUtil.executeQuery(hql,null); for(Studcourse sc:list){ System.out.println(sc.getStuCourseId()+" "+sc.getGrade()); } //session关闭后，依然能够得到list中直接属性的值 //如果想得到学生名等，将会报错could not initialize proxy - no Session for(Studcourse sc:list){ System.out.println(sc.getStudent().getSname()); } //因为sc.getStudent()是需要向SQL发送sql语句的，而此时session已经关闭了，懒加载机制就不会再打开session去查询。 //禁用懒加载即可再查询到
三、在映射文件中得到hql语句
在Student.hbm.xml中配置如下hql语句:
<hibernate-mapping> <query name="query1"> from Student where sage>22 </query> </hibernate-mapping>
执行时候执行如下代码：
List<Student> list = session.getNamedQuery("query1").list();
for(Student s:list){
System.out.println(s.getSname()+" "+s.getSage());
}
