Hibernate---HQL

1、HQL简介
完全的HQL语法结构
[select/update/delete...] [form...]
[where...] {group by ...] [having ...]
[order by ...]
其中Java的类名和属性名区分大小写的；

2、实体的查询
（1）from子句
- 简单的from
String hql = "from User";
Query query = session.createQuery(hql);
List list = query.list();//将查询的数据以对象形式放在集合中；
如果有相同的类名可以使用包名称区分,即：String hql = "from com.my.bean.User";
（2）where子句
- as 别名(可以省略)；
String hql = "from User as user where user.name='tom'";
String hql = "from User user where user.name='tom'";
- 通过比较操作符，指定筛选条件
hql = "from User as user where user.age > 20";
hql = "from User as user where user.age between 18 and 30"; 两边都是闭区间[]；
hql = "from User as user where user.age in(18,28)"; 只包含18、28这两个点；
hql = "from User as user where user.name is null";
hql = "from User as user where user.name is not null";
hql = "from User as user where user.name like t%"; 查询名字以t开头的记录；

3、属性的查询
- 查询一个属性,返回的list中存放的当前的属性；
String hql = "select user.name from User as user";
Query query = session.createQuery(hql);
List list = query.list();
for(int i = 0; i < list.size(); i++)
{
System.out.println(list.get(i));
}
- 查询多个属性，返回对象数组；
hql = "select user.name,user.age from User as user";
Query query = session.createQuery(hql);
Iterator itor = query.iterate();
while(itor.hasNext())
{
Object[] result = (Object[]) itor.next();
System.out.println(result[0]);
System.out.println(result[1]);
}

- 查询多个属性，封装成对象（要有适当的构造方法）
String hql = "select new User(user.name,user.age) from User as user";
Query query = session.createQuery(hql);
List list = query.list();
for(int i = 0; i < list.size(); i++)
{
User user = (User) list.get(i);
System.out.println(user.getName());
System.out.println(user.getAge());
}

- 使用统计函数
String hql = "select count(*),min(user.age) from User as user";
Query query = session.createQuery(hql);
Iterator itor = query.iterate();
while(itor.hasNext())
{
Object[] result = (Object[]) itor.next();
System.out.println(result[0]);
System.out.println(result[1]);
}
- sql原生函数， 如：upper,distinct
String hql = "select upper(user.name) from User as user";
Query query = session.createQuery(hql);
List list = query.list();
for(int i = 0; i < list.size(); i++)
{
System.out.println(list.get(i));
}

4、实体的批量查询、更新、删除
比较session.update和session.delete
- 删除
批量删除："delete from User as u where u.age > 30";
String hql = "delete User as user where user.id=3";
session.beginTransaction();//开启事务
Query query = session.createQuery(hql);
query.executeUpdate();
session.getTransaction().commit();
- 更新，有条件更新
批量更新："update User as user set user.age=18";
String sql = "update User as user set user.age=18 where user.id=2";
session.beginTransaction();//开启事务
Query query = session.createQuery(hql);
query.executeUpdate();
session.getTransaction().commit();

5、分组与排序
- order by子句（排序）
String hql = "select user.name from User as user order by user.name";
String hql = "select user.name from User as user order by user.name desc"; //降序排列
String hql = "select user.name from User as user order by user.name asc"; //升序排列
String hql = "select user.name from User as user order by user.name,user.id  asc";

- group by子句（分组）
String hql = "select count(user),user.age from User as user group by user.age";
session.beginTransaction();//开启事务
Query query = session.createQuery(hql);
Iterator itor = query.iterate();
while(itor.hasNext())
{
Object[] result = (Object[]) itor.next();
System.out.println(result[0]);
System.out.println(result[1]);
}
- having 子句
String hql = "select count(user),user.age from User as user group by user.age having count(user) > 3";

6、参数绑定
- 顺序占位符
String hql = "from User as user where user.name=? and user.age=?";
Query query = session.createQuery(hql);
query.setString(0, "tom");
query.setInteger(1, new Integer(18));
List list = query.list();
for(int i = 0; i < list.size(); i++)
{
User user = (User) list.get(i);
System.out.println(user.getName());
}

- 引用占位符
String hql = "from User as user where user.name=:name and user.age=:age";
Query query = session.createQuery(hql);
query.setString("name", "tom");
query.setInteger("age", new Integer(18));

7、引用的查询
配置文件：User.hbm.xml文件
<hibernate-mapping>
<query name="queryByNameAndAge">
from User as user where user.name=:name and user.age=:age
</query>
</hibernate-mapping>
程序代码：
Query query = session.getNamedQuery("queryByNameAndAge");
query.setString("name", "tom");
query.setInteger("age", new Integer(18));
List list = query.list();
for(int i = 0; i < list.size(); i++)
{
User user = (User) list.get(i);
System.out.println(user.getName());
}

8、联合查询
- HQL提供了联合查询方式
Inner join
Left outer join
Right outer join
Full join

- Inner join
返回所有关联条件的记录组合
From User as user inner join fetch user.address;
fetch关键字表明Address对象读出后立即填充到对应的User对象中；如果忽略，返回的是一个Object数组，其中包含User对象和Address对象；
- Left outer join
返回包含左侧表的所有记录
From User as user left join fetch user.address;
- Right outer join
返回包含右侧表的所有记录
From User as user right join user.address;
- Full join (避免使用)
返回两张表的笛卡儿积
From User as user full join user.address;

9、子查询

From User as user where (select count(*) from user.address) > 1;