HQL
来源：互联网 发布：勇士vs火箭季后赛数据 编辑：程序博客网 时间：2024/04/29 15:46
1、属性查询
1.1、单个属性查询
List<String> l=session.createQuery("select e.ename from Emp e").list();for (String ename : l) {System.out.println(ename);//每条记录都是一个字段对应的数据类型}1.2、多个属性查询
List l = session.createQuery("select e.empno,e.ename from Emp e").list();Iterator it = l.iterator();while (it.hasNext()) {Object[]obj=(Object[])it.next();System.out.println("empno:"+obj[0]+" ename:"+obj[1]);}每条记录对应的都是Ocject[]数组，每个Object数组中依次保存所获取的属性数据。1.3、动态构造对象实例,前提要有相应的构造函数
Query q = session.createQuery("select new Emp(e.empno,e.ename) from Emp e");List<Emp> l = q.list();for (Emp emp : l) {System.out.println(emp.getEmpno() + " " + emp.getEname());} 1.4、存放在一个 List 对象中
Query q=session.createQuery("select new list(e.empno,e.ename) from Emp e");Iterator it=q.list().iterator();while(it.hasNext()){List obj=(List)it.next();System.out.println("emono:"+obj.get(0)+" ename:"+obj.get(1));}1.5、Map
Query q = session.createQuery("select new map(count(*) as count,max(sal) as max,min(sal) as min,avg(sal) as avg,sum(sal) as sum) from Emp");Iterator it = q.list().iterator();Map obj = (Map) it.next();System.out.println(obj);该查询返回了一个 Map 的对象，内容是别名与被选择的值组成的名-值映射2、参数绑定
2.1、命名参数和？占位符
Query q = session.createQuery(" from Emp where empno=? and deptno=:deptno");q.setInteger(0, 7369);//占位符(?)参数q.setInteger("deptno", 20);//命名参数List<Emp> l = q.list();for (Emp emp : l) {System.out.println(emp.getEname());}Emp e=(Emp)session.createQuery("from Emp as e where e=?").setEntity(0, new Emp(7369)).uniqueResult();2.2、封装参数(对象属性必须与命名参数名一致)
public class QueryProperties {private Integer id;private String name;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public QueryProperties() {super();// TODO Auto-generated constructor stub}public QueryProperties(Integer id, String name) {super();this.id = id;this.name = name;}}实现：Query q = session.createQuery(" from Emp where empno>:id and ename like:name ");QueryProperties qp=new QueryProperties(100,"M%");q.setProperties(qp);//对象封装参数List<Emp> l = q.list();for (Emp emp : l) {System.out.println(emp.getEname());}3、聚合函数Query q=session.createQuery("select count(*),max(sal),min(sal),avg(sal),sum(sal) from Emp");Iterator it=q.list().iterator();while(it.hasNext()){Object[]obj=(Object[])it.next();System.out.println("count:"+obj[0]+" max:"+obj[1]+" min:"+obj[2]+" avg:"+obj[3]+" sum:"+obj[4]);}4、order by、group by
Query q=session.createQuery("from Emp e order by empno desc");List<Emp> list=q.list();for (Emp emp : list) {System.out.println(emp.getEmpno());}Query q=session.createQuery("select e.job from Emp e group by e.job");List list=q.list();Iterator it=list.iterator();while(it.hasNext()){System.out.println(it.next());}5、分页
Hibernate提供：
int pageSize = 3;int pageIndex = 2;Query q = session.createQuery("from Emp order by empno");System.out.println(q.list().size());ScrollableResults scroll = q.scroll();scroll.last();int count = scroll.getRowNumber() + 1;// 总记录数int totalpages = (count % pageSize == 0) ? (count / pageSize) : (count/ pageSize + 1);q.setFirstResult(pageSize * (pageIndex - 1));q.setMaxResults(pageSize);List<Emp> l = q.list();for (Emp emp : l) {System.out.println(emp.getEmpno());}或
int count = list.size();//总记录数6、HQL查询性能优化6.1、避免使用or操作，使用in替代;避免使用not、like的特殊形式、having子句、distinct6.2、所以在以下情况下失效：
①对字段使用函数；
②对字段进行计算,如price+10;
③某些情况下的like操作；
④某些情况下使用not,如not(price>1800);
⑤在where中使用or时，有一个字段没有索引，那么其它字段的索引将不起作用;
7、连接查询
7.1、内连接：inner join
Query q=session.createQuery("from Emp e inner join e.dept d with d.deptno=10");List l=q.list();Iterator it=l.iterator();while(it.hasNext()){Object[]obj=(Object[])it.next();Emp e=(Emp)obj[0];System.out.println(e.getEname());Dept d=(Dept)obj[1];System.out.println(d.getEmps());}Query q2=session.createQuery("from Emp e inner join fetch e.dept d");List l2=q2.list();Iterator it2=l2.iterator();while(it2.hasNext()){Emp e=(Emp)it2.next();System.out.println(e.getDept());}
fetch关键字表明右表对象读取后立即填充到对应的左表对象中。如果忽略fetch关键字，得到的结果集中每行数据都是一个Object数组。通过with可以提供获得额外的join条件7.2、外连接
7.2.1、右外连接
Query q=session.createQuery("from Emp e right join e.dept d");List l=q.list();Iterator it=l.iterator();while(it.hasNext()){Object[]obj=(Object[])it.next();Emp e=(Emp)obj[0];System.out.println(e.getEname());Dept d=(Dept)obj[1];System.out.println(d.getEmps());}Query q2=session.createQuery("from Emp e right join fetch e.dept d");List l2=q2.list();Iterator it2=l2.iterator();while(it2.hasNext()){Emp e=(Emp)it2.next();System.out.println(e.getDept());}7.2.2、左外连接from Emp e left join fetch e.dept d
8、命名查询
首先在class同级节点上配置
<query name="test"> <![CDATA[ from org.han.entity.Emp e where e.ename like:name ]]></query>通过Query q=session.getNamedQuery("test");方法获得该查询语句Query q = session.getNamedQuery("test");q.setString("name", "M%");List<Emp> l = q.list();for (Emp emp : l) {System.out.println(emp.getEname());}
9、本地SQL查询9.1查询
SQLQuery sq = session.createSQLQuery("select {e.*},{d.*} from emp e,dept d where e.deptno=d.deptno").addEntity("e", Emp.class).addEntity("d", Dept.class);List<Emp> l = sq.list();Iterator it = l.iterator();while (it.hasNext()) {Object[] obj = (Object[]) it.next();Emp e = (Emp) obj[0];Dept d = (Dept) obj[1];System.out.println("雇员：" + e.getEname() + " 部门:" + d.getDeptno());}9.2命名查询
XML配置<sql-query name="sqlquery"><![CDATA[select {e.*},{d.*} from emp e,dept d where e.deptno=d.deptno and mod(e.empno,?)=0]]><return alias="e" class="org.han.entity.Emp"></return><return alias="d" class="org.han.entity.Dept"></return></sql-query><return>是指定别名和实体类的联系，其中alias属性用于指定别名，class用于指定实体类Query q = session.getNamedQuery("sqlquery");q.setInteger(0, 3);List<Emp> l = q.list();Iterator it = l.iterator();while (it.hasNext()) {Object[] obj = (Object[]) it.next();Emp e = (Emp) obj[0];Dept d = (Dept) obj[1];System.out.println("雇员：" + e.getEname() + " 部门:" + d.getDeptno());}10、过滤集合
集合过滤器（filter）是一种用于一个持久化集合或者数组的特殊的查询。查询字符串中可以使用 "this" 来引用集合中的当前元素。
List<Dept> l=session.createQuery("from Dept").list();Collection<Emp> coll=session.createFilter(l.get(1).getEmps(), "").setMaxResults(3).setFirstResult(3).list();for (Emp dept : coll) {System.out.println(dept.getEmpno());}
