query 中Hql和sql

1 一 基本语法：

Configuration cfg = new Configuration();
cfg.configure("/hibernate.cfg.xml");
SessionFactory sf = cfg.buildSessionFactory();
Session session = sf.openSession();

1 返回所有对象
Query query = session.createQuery("from People");//from 接类名，不需select
List list = query.list();

2 返回某些属性：
Query query = session.createQuery("select name,age from People");
List list = query.list();

3 where 条件:
Query query = session.createQuery("from People p where p.age>20");//p.age对应类属性，而不是表字段
List list = query.list();

4 重复过滤：
Query query = session.createQuery("select distinct name from People");
List list = query.list();

5 between..and 和not between..and
Query query = session.createQuery("select age from People p where p.age between 10 and 20");
List list = query.list();

6 in 和not in
Query query = session.createQuery("select name from People p where p.name in('jack','tom')");
List list = query.list();

7 like
Query query = session.createQuery("from People p where p.name like '%jac%'");
List list = query.list();

8 order by
Query query = session.createQuery("from People p where p.age asc");
List list = query.list();

9 group by 分组
Query query = session.createQuery("select t.type,count(id) from BookInfo t group by t.type");
List list = query.list();

10 having 为group by 设置分组条件
Query query = session.createQuery("select t.type,count(id) from BookInfo t group by t.type having count(id) >3");
List list = query.list();

11 聚集函数：
count()计算符合记录条数
sum()计算符合条件和
avg()计算符合条平均值
max()计算符合条最大值
min()计算符合条最小值

12 使用sql函数
upper() lower()

13 any 和 all
any:
select * from BookInfo t where t.price > any(select b.price from BookInfo b where type=0)
结果集中的某个
all：
select * from BookInfo t where t.price > all(select b.price from BookInfo b where type=0)
结果集中的全部

14 更新和删除：
Query query = session.createQuery("update BookInfo b set b.price = 10 where b.id=1");
query.excuteUpdate();
注意：HQL是不能实现插入的。但可以用session.save();或sql实现

15 表连接查询：
...
--------------
二 在HQL语句中绑定参数：
1. 按参数的名字绑定：
Query query = session.createQuery("from BookInfo b where b.bookName=:bookName"+"and b.price=:price");
query.setString("bookName","3dsMax");
query.setDouble("price",56.0);
List list = query.list();
2. 按参数位置绑定：
注意：是从0位置开始的 ,jdbc 从1开始
Query query = session.createQuery("from BookInfo b where b.bookName=?"+"and b.price=?");
query.setString(0,"3dsMax");
query.setDouble(1,56.0);
List list = query.list();

3 几种特殊的参数绑定函数：
(1) setEntity();绑定持久化类
eg:
Person 中有持久化类Dog作为其属性
calss Person{
  private Dog dog;
  //...
}

Dog d1 = new Dog();
Dog d2 = (Gog)session.load(Dog.class,new Integer(0));
Query query = session.createQuery("from Person where dog=?");
query.setEntity(0,d1);//error(unsaved transient instance ...)
query.setEntity(0,d2);//ok
Person p = (Person)query.list().get(0);

(2) setParameter();可以绑定任何类型参数
eg:
Query query = session.createQuery("from BookInfo b where b.bookName=?"+"and b.creatDate=?");
query.setParameter(0,"3dsMax");
query.setParameter(1,"1999-03-04",Hibernate.DATE);

(3) setParameters(Object[] values,Type[] types);可以按位置设置对应的值和类型
eg:
Query query = session.createQuery("from BookInfo b where b.bookName=?"+"and b.price=?");
Object[] ob = {"3dsMax",67.0};
Type[] types = {Hibernate.STRING,Hibernate.DOUBLE};
List list = query.list();

(4) setParameterList() 选定集合然后属性值在这个集合中的对象
eg:
Query query = session.createQuery("from BookInfo b where b.id in(:id)");
Set ids = new HashSet(0);//哈希表的初始容量 0
ids.add(new Long(2));
ids.add(new Long(3));
ids.add(new Long(5));
query.setParameterList("id",ids);
List list = query.list();

-----------------------------
三 限制查询返回的数目
 setFirstResult(int firstResult);//设置从哪个对象查询
 setMaxResult(int maxResult);//设置每次查询返回的最大数目

与mysql中的limit 0,10
postgresql 的 limit 10 offset 0 相似

----------------------------
四 在映射文件中命名HQL语句：
eg：
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.huawei.m2m.People" table="SA_PEOPLE">
        <!--省略-->
    </class>
    <query name="serchPeople">
        <![CDATA[
            from People        
        ]]>
    </query>
    <sql-query name="deletePeople">
        <![CDATA[
            delete from People as p where p.id in (:id)         
        ]]>
    </sql-query>
</hibernate-mapping>
//无参的
Query query = session.getNamedQuery("serchPeople");
List list = query.list();
//有参的
Set<Long> ids = new HashSet<Long>();
ids.add(new Long(2));
ids.add(new Long(3));
ids.add(new Long(5));
Query query = super.getSession().getNamedQuery("deletePeople");
//向正常一样将参数绑定进去
query.setParameterList("id", ids);