HQL检索方式

 Hibernate Query Language
依赖接口:Query
数据模型:
tb_user(userId,userName)
tb_addr(addrId,addrName,userId)
Entity:
class Addr{
        private int addrId;
        private String addrName;
}
class User{
        private int userId;
        private String userName;
        private Set addrs = new HashSet();
}
备注:省略geters 和 seters
用 ? 版本
String hSQL = "from User as u where u.userId=?";  //一般都取一个别名 as u
Query q = session.createQuery(hSQL);             
q.setInt(0,1);                                    //第一各参数为 '?' 的位置,从 0 开始,第二个参数为 ? 的值;
List users = q.list();
执行为:select * from tb_user where userId = 1;
用 :Field 版本
String hSQL = "from User as u where u.userId=:userId";  //一般都取一个别名 as u
Query q = session.createQuery(hSQL);             
q.setInt("userId",1);                                 
List users = q.list();    //注意：String hql = "from User u where u.id=? and u.name=:name";hibernate不报错，String hql = "from User u where  u.name=:name
        and  u.id=?";hibernate会报
严重:  cannot define positional parameter after any named parameters have been defined
2009-4-16 15:00:33 org.hibernate.hql.ast.ErrorCounter reportError
严重:  right-hand operand of a binary operator was null

hSQL语句例子:
以下都假以调用 q.list();即返回list
from:
form User as u -> select * from tb_user  //返回的list里存放的是User
select:
select u.userName from User as u where u.userId=? and u.userName like 'ab%' 返回的list里存放的是User
select new List(u.userId,u.userName) from User as u   //返回的list里存的是 list(此list存放的对应的是userId,userName)
select new com.pannuo.entity.Test(u.userId,u.userName) from User as u  //返回的list里存的是Test
Test说明:
class Test{
        private int userId;
        private String userName; //必须有两个对应属性
        public Test(int id,String name){    //必须有个对应构造方法
                this.userId = id;
                this.userName = name;
        }
}//省略geters 和 seters
select u.userName as name from User u //对属性名别名
select new map(u.userName as name) from User u //返回的list里存放的是map(以userName为key 实际选出的值为value)

聚集函数:(5种)
以下调用Object o = q.uniqueResult()
avg    :计算属性平均值    ->select avg(u.userId) from User u  //返回Double 包装类                         
count   :统计选择对象个数 ->select count(*) from User         //返回Integer 或其他的
max     :属性值最大值     ->select max(u.userId) from User u  //返回Integer 或其他的
min     :属性值最小值     ->select min(u.userId) from User u  //返回Integer 或其他的
sum     :属性值总和       ->select sum(u.userId) from User u  //返回Integer 或其他的                                         
order by
from User u order by u.userName asc,u.userId desc  //默认升序asc

group by
select a.userId,sum(a.userAge),count(a) from User u group by u.groupId

子查询
from User as u where u.age>(select avg(b.age) from UserTmp as b)
from User as u where not(u.userName,u.age) in(select b.userName,b.age from UserTmp as b)

分页
//从第100条开始,检索20条数据
Query.setFirstResult(100);
Query.setMaxResult(20);

SQL查询
SQLQuery q = (SQLQuery) session.createSQLQuery("select * from tb_user where userId=?"); //String 就为SQL语句
q.setInt(0,1);
List list = q.list();

其他
distinct //不重复
select distinct u.userName form User as u;

内联
多对一(Addr中有一个User即:多个Addr有一个User)
以前可以用级联操作来检索Addr是同时检索User
现在用内联:
from Addr ad a inner join fetch a.user as u where c.addrId=?  //内联,同时拿Addr中的User

HQL的更新
update User u set u.userName=? where u.userId=?

例句：
select new List(d.goods,count(*)) from OrderDetail as d group by d.goods.goodsId order by count(*) desc