Hibernate的八大类HQL查询集合: 一:属性查询(SimplePropertyQuery) 1,单一属性查询 *返回结果集属性列表,元素类型和实体类中相应的属性一致 2, 多个属性查询, * 返回集合中的元素是object数组, * 其中数组元素的类型和对应属性在实体类中的类型一致 代码: for(Iterator iterator=list.iterator();iterator.hasNext();) { Object[] obj=(Object[])iterator.next(); System.out.println("sid:="+obj[0].toString()+" sname:="+obj[1]); } 3, 通过hql来动态实例化对象 代码: List list=session.createQuery("select new Student(sid,sname) from Student").list(); for (Iterator iterator=list.iterator();iterator.hasNext();){ Student student=(Student) iterator.next(); System.out.println("sid:="+student.getSid()+" sname:="+student.getSname()); } 4,采用别名查询 代码: //List list=session.createQuery("select s.sid, s.sname from Student as s").list(); List list=session.createQuery("select s.sid, s.sname from Student s").list(); 二:简单对象查询(SimplyObjectQuery) 1,返回对象集合(可以忽略select) 代码: //List list=session.createQuery("from Student").list(); //List list=session.createQuery("from Student as s").list(); List list=session.createQuery("from Student s").list(); 2,采用select查询对象 * 必须要使用别名 代码: //List list=session.createQuery("select s from Student as s").list(); List list=session.createQuery("select s from Student s").list(); 3,查询单一对象的操作 代码: Object obj=session.createQuery("select s from Student s where s.sid=:mysid") .setParameter("mysid", 12) .uniqueResult(); 4,分页查询 * setFirstResult(int firstResult) * Set the first row to retrieve. * If not set, rows will be retrieved beginnning from row 0. * firstResult - a row number, numbered from 0 代码: List list=session.createQuery("select s from Student s") .setFirstResult(5) .setMaxResults(5) .list(); 三:简单条件查询(SimpleConditionQuery) 1,可以拼凑字符串 代码: List stuList=session.createQuery("select s.sid, s.sname from Student as s where s.sname like '%1%'").list(); 2,注意:可以采用?占位的方式来传递参数 *参数的索引从0开始 *传递的参数值不能用''号括起来 *注意方法链编程 代码: /* *Query query=ession.createQuery("select s.sid, s.sname from Student as s where s.sname like ?"); *query.setParameter(0, "%1%"); *List stuList=query.list(); */ List stuList=session.createQuery("select s.sid, s.sname from Student as s where s.sname like ?") .setParameter(0, "%1%") .list(); 3,通过参数名(:参数名)传参的方式进行查询 代码: /*Object obj=session.createQuery("select s.sid, s.sname from Student as s where s.sname like :myname and s.sid=:myid") .setParameter("myname", "%1%") .setParameter("myid", 98) .uniqueResult();//.list(); Object[] objs=(Object[])obj; System.out.println("s.sid:="+objs[0].toString()+" s.sname:="+objs[1].toString()); */ List stuList=session.createQuery("select s.sid, s.sname from Student as s where s.sname like :myname and s.sid=:myid") .setParameter("myname", "%1%") .setParameter("myid", 98) .list(); 4,传递多个参数的查询 * 支持in查询,需要setParameterList("myids", new Object[]{12,13,14})的方式进行参数传递 代码: List stuList=session.createQuery("select s.sid, s.sname from Student as s where s.id in(:myids)") .setParameterList("myids", new Object[]{12,13,14}) .list(); 5, 查询2009年2月入学的学员信息 *支持Sql函数查询 代码: List stuList=session.createQuery("select s.sid, s.sname ,s.schoolDate from Student as s where year(s.schoolDate)=:myyear and month(s.schoolDate)=:mymonth") .setParameter("myyear", 2009) .setParameter("mymonth", 2) .list(); 6,查询2009-02-10到2009-02-15号入学的学员信息 *注意:在进行日期查询时 *setParameter("startDate", formatter.parse("2009-02-10 00:00:00")) *方法中第二个参数是一个Object对象(Date类型对象) 代码: SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); List stuList=session.createQuery("select s.sid, s.sname ,s.schoolDate from Student as s where s.schoolDate between :startDate and :endDate") .setParameter("startDate", formatter.parse("2009-02-10 00:00:00")) .setParameter("endDate", formatter.parse("2009-02-15 23:59:59")) .list(); 四:原生SQL语句查询(SqlQueryTest_4) *这里用的是createSQLQuery().list(); 代码: List stuList=session.createSQLQuery("select * from Student").list(); 五:外置命名查询(NamedQueryTest_5) * 1.在映射文件中采用<query>标签来定义HQL语句,不必限定文件!!!注意:映射文件中的HQL语句不可以出错,否则会导致 QuerySyntaxException,特别是要查询的实体类名写错的情况。(Error in named query: selectAllStudents org.hibernate.hql.ast.QuerySyntaxException: Students is not mapped)。 * 2.在程序中通过session.getNamedQuery(hql_name)得到查询语句 代码: List list=session.getNamedQuery("selectAllStudents") .setParameter("mysid",12).list(); 六:对象导航查询(ObjectNavQueryTest_6) 代码: List list= session.createQuery("select s.sname from Student as s where s.clsInfo.cname like :myname") .setParameter("myname", "%1%") .list(); 七:联合查询(JoinQueryTest_7) 1,联合查询* 内连接查询 * 获取已经参加班级报名的所有学生名号和其所在班级名称 代码: List list=session.createQuery("select s.sname,c.cname from Student as s join s.clsInfo as c").list(); 2,联合查询* 左连接查询 * 获取所有班级名称和其学员姓名 * 以班级信息表为主表,进行左外连接查询 代码: List list=session.createQuery("select c.cname ,s.sname from ClassInfo as c left join c.studentSet as s").list(); 3,联合查询 * 右连接查询 * 获取所有学员姓名和其所属班级名称 * 以学员信息表为主表,进行右外连接查询 代码: List list=session.createQuery("select s.sname, c.cname from ClassInfo as c right join c.studentSet as s").list(); 八:(StateQueryTest_8) 1,聚合函数使用 代码: //List list=session.createQuery("select count(*) from Student").list(); //long count=(Long) list.get(0); long count=(Long) session.createQuery("select count(*) from Student").uniqueResult(); 2,分组查询 代码: List list=session.createQuery("select c.cname, count(s) from Student as s inner join s.clsInfo as c group by c.cname order by c.cname").list();