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