Hibernate的查询体系及数据过滤

Hibernate查询体系分4种过滤数据的方式

 

 

一、HQL(Hibernate Query Language)是面向对象的查询语言。

HQL操作的对象是类、实例、属性等

步骤

1.获取Hibernate Session对象

2.编写HQL语句

3.以HQL语句作为参数，调用Session的CreateQuery方法创建查询对象

4.如果HQL包含参数，调用Query的setXXX方法为参数赋值

5.调用Query对象的list等方法返回查询结果

 

注意分页的

setFirstResult(int  firstResult)//返回结果集从第几条数据开始

setMaxResults(int maxResults)//返回本次查询的结果数

 

下面简单介绍下HQL语句的语法

 

1.from字句（表明从持久化类中选择出全部的属性）

2.select 字句（用于确定选择出的属性）

select p.name from Person as p
select p.name.firstName from Person as p
select new list(p.name, p.address) from Person as p
select new ClassTest(p.name, p.address) from Person as p (有前提)
select p.name as personName from Person as p
select new map(p.name as personName) from Person as p (与new map()结合更普遍)

3.聚集函数
avg,count,max,min,sum
select count(*) from Person
select max(p.age) from Person as p
select p.name || "" || p.address from Person as p

4.多态查询
from Person as p
from java.lang.Object o
from Named as n

5.where子句
from Person where name like "tom%"
from Person as p where p.name like "tom%"
from Cat cat where cat.mate.name like "kit%"
     select * from cat_table as table1 cat_table as table2 where table1.mate =
     table2.id and table1.name like "kit%"
from Foo foo where foo.bar.baz.customer.address.city like "fuzhou%"
from Cat cat, Cat rival where cat.mate = rival.mate
select cat, mate
from Cat cat, Cat mate
where cat.mate = mate
from Cat as cat where cat.id = 123
from Cat as cat where cat.mate.id = 69
from Person as person
where person.id.country = 'AU'
     and person.id.medicareNumber = 123456
from Account as account
where account.owner.id.country = 'AU'
     and account.owner.id.medicareNumber = 123456
from Cat cat where cat.class = DomesticCat
from Account as a where a.person.name.firstName like "dd%" // 正确
from Account as a where a.person.name like "dd%" // 错误

6.表达式
from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ('Foo', 'Bar', 'Baz')
from DomesticCat cat where cat.name not between 'A' and 'B'
from DomesticCat cat where cat.name not in ('Foo', 'Bar', 'Baz')
from DomesticCat cat where cat.name is null
from Person as p where p.address is not null
<property name="hibernate.query.substitutions">true 1, false 0</property>
from Cat cat where cat.alive = true
from Cat cat where cat.kittens.size > 0
from Cat cat where size(cat.kittens) > 0
from Calendar cal where maxelement(cal.holidays) > current date
from Order order where maxindex(order.items) > 100
from Order order where minelement(order.items) > 10000
//操作集合元素
select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
//p的name属性等于集合中某个元素的name属性
select p from NameList list, Person p
where p.name = some elements(list.names)
//操作集合元素
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)
//items是有序集合属性，items[0]代表第一个元素
from Order order where order.items[0].id = 1234
//holidays是map集合属性，holidays[national day]是代表其中第一个元素
select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
     and person.nationality.calendar = calendar
//下面同时使用list集合和map集合属性
select item from Item item, Order order
where order.items[order.deliveredItemIndices[0]] = item and order.id = 11
select item from Item item, Order order
where order.items[maxindex(order.items)] = item and order.id = 11

select item from Item item, Order order
where order.items[size(order.items) - 1] = item

select cust
from Product prod,
     Store store
     inner join store.customers cust
where prod.name = 'widget'
     and store.location.name in ['Melbourne', 'Sydney']
     and prod = all elements(cust.currentOrder.lineItems)

SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
FROM customers cust,
     stores store,
     locations loc,
     store_customers sc,
     product prod
WHERE prod.name = 'widget'
     AND store.loc_id = loc.id
     AND loc.name IN ('Melbourne', 'Sydney')
     AND sc.store_id = store.id
     AND sc.cust_id = cust.id
     AND prod.id = ALL(
         SELECT item.prod_id
         FROM line_items item, orders o
         WHERE item.order_id = o.id
             AND cust.current_order = o.id
     )

7.order by子句
from Person as p
order by p.name, p.age
from Person as p
order by p.name asc, p.age desc

8.group by子句
select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
//select后出现的id处出现在group by之后，而name属性则出现在聚集函数中
select foo.id, avg(name), max(name)
from Foo foo join foo.names name
group by foo.id

select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)

select cat
from Cat cat
join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc

9.子查询
from Cat as fatcat
where fatcat.weight > (select avg(cat.weight) from DomesticCat cat)

from Cat as cat
where not (cat.name, cat.color) in (
     select cat.name, cat.color from DomesticCat cat
)

10.fetch关键字
from Person as p join p.scores

from Document fetch all properties order by name
from Document doc fetch all properties where lower(doc.name) like '%cat%'

11.命名查询
<!--定义命名查询-->
<query name="myNamedQuery">
     <!--此处确定命名查询的HQL语句-->
     from Person as p where p.age > ?
</query>

调用命名查询的示例代码如下：
  private void findByNamedQuery() throws Exception {
     Session session = HibernateUtil.currentSession();
     Transaction tx = session.beginTransaction();
     System.out.println("执行命名查询");
     //调用命名查询
     List pl = sess.getNamedQuery("myNamedQuery")
                                     //为参数赋值
                                     .setInteger(0, 20)
                                     //返回全部结果
                                     .list();
     //遍历结果集
     for (Integer pit = pl.iterator(); pit.hasNext(); )
      {
         Person p = (Person)pit.next();
         System.out.println(p.getName());
     }
     tx.commit();
     HibernateUtil.closeSession();
}

 

二、条件查询

 

通过如下3个类完成

1.Criteria 代表一次查询

2.Criterion 代表产生一个查询条件

3.Restrictions 产生查询条件工具类

 

三、sql查询

与query类似

多了个addEntity和addScalar

 

 

四、数据过滤

 

1.配置过滤设置 filter

Session.EnableFilter("XXX").setParameter(name,value);

 拦截器

2.继承自Interceptor

session.openSession(Interceptor  in);

3. 事件系统

继承自DefaultLoadEventListener

使用

(1)new Configuration().getSessionEventListenerConfig().setLoadEventListener(new MyLoadListener());

(2)在Hibernate.cfg.xml文件中配置

<listener type="load" class="lee.MyLoadListener"/>

<listener type="save" class="lee.MySaveListener"/>