Hibernate 3.2 学习笔记查询和检索_hibernate-3.2.2 查询-优快云博客

本文链接：https://blog.youkuaiyun.com/RainyTooo/article/details/1147074

Hibernate的检索策略
-------------------------------------------------------------------------------------------------------------------------------------------------

检索策略的作用域可用策略默认运行时受影响的方法

--------------------------------------------------------------------------------------------------------------------------------------------------

类级别立即检索立即 load()
延时检索

--------------------------------------------------------------------------------------------------------------------------------------------------

关联级别                                   立即检索                                      多对一和一对一                   load() get() 等
                                                   延时检索                                       为外连接检索
                                                   迫切左外连接检索                          -------------
                                                                                                         一对多和多对多
                                                                                                          为立即检索
--------------------------------------------------------------------------------------------------------------------------------------------------

立即检索类级别的立即加载关联对象关联级别的立即加载指定对象的关联对象可以设定批量检索数量

延时检索   类级别的延时加载关联对象关联级别的延时加载指定对象的关联对象可以设定批量检索数量
迫切左外连接检索类级别不适用关联级别通过左外连接加载与检索方法指定对象关联的对象

涉及到的元素属性
<class>和<set>的lazy 默认是true 表示延时检索
<many-to-one><one-to-one><set> 的 outer-join             <many-to-one><one-to-one>默认auto    <set> 默认   false
<class>和<set> 的batch-size 默认1 指定批量检索的数量

' 延时检索时会产生代理类
' 但是仅仅初始化了OID 其他都为null
' 只有当访问除ID外的属性或者修改时才会加载数据库的数据
' 有可能在你关闭session后仍没有初始化对象也就没有完整的游离对象
' 只有在session范围内才能被初始化
' get()方法加载的对象时实际相关数据不是代理类

一对多和多对多的检索策略
<set> 的lazy和out-join
都为false 立即检索
lazy为false out-join为true是迫切左外连接检索(当有多个<set>只允许一个用)
相反时用延时检索策略

Hibernate的检索方式

HQL检索方式：
      1 通过Session的createQuery()方法创建一个Query对象包含一HQL查询语句
      2 可以动态绑定参数
      3 可以调用list()方法返回List类型的结果
exp :
      List result = session.createQuery(".......").setString("name",name).setInteger("age", 21).list();

QBC检索方式：
      1 用session的才createCriteria()方法创建一个Criteria对象
      2 可以设定查询的条件用Expression的一些静态方法如Expression.like("name", "T%")
               Expression.eq("age", new Integer(21)
      3 可以使用方法链也可以调用list()方法返回List类型的结果
exp :
      List result = session.createCriteria(Customer.class).add(Restrictions.like("name", "T%")).add(Restrictions.eq("age", new Integer(21)).list();
      4 还可以使用QBE模板检索方式
exp :
      Customer exp = new Customer();
      exp.setName("tom");

List result =session.createCriteria(Customer.class).add(Example.create(exp)).list();

SQL检索方式：

Query query=session.createSQLQuery("..............");

由于检索和HQL比较杂乱分别举例说明

' 最简单的查询

HQL

Session session = sessionFactory.openSession();

Transaction tx = null ;

try

{

tx=session.beginTransaction();

List result=session.createQuery("from Customer ").list();

for(Iterator it=result.iterator(); it.hasNext();)

{

Customer c =(Customer)it.next();

System.out.println("Customer's name :" + c.getName());

System.out.println("Customer's age :" + c.getAge());

}

tx.commit();

}

catch (Exception e)

{

if(tx!=null)

{

tx.rollback();

}

throw e;

}

BQC

...

List result = session.createCriteria(Customer. class ).list();

...

' 指定查询和命名别名

HQL

...

List result = session.createQuery( " from Customer as c where name=:name " ).setString( " name " , " Tom " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).add(Restrictions.eq( " name " , " Tom " )).list();

...

' 对查询结果排序

desc是降序

HQL

...

List result = session.createQuery( " from Customer as c order by c.name " ).list();

List result = session.createQuery( " from Customer as c order by c.name asc, c.age desc " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).addOrder(Order.asc( " name " )).addOrder(Order.desc( " age " )).list();

...

' 在指定时间范围内查询结果

...

tx = session.beginTransaction();

Date beginDate = java.sql.Date.valueOf( " 2006-8-1 " );

Date endDate = java.sql.Date.valueOf( " 2006-9-1 " );

List result = session.createQuery( " from Customer c where c.retime <:endTime and c.retime >=:beginTime " ).setTimestamp( " endTime " , endDate).setTimestamp( " beginTime " , beginDate).list();

...

' 分页查询

HQL

...

List result = session.createQuery( " from Customer c Order by c.name asc " ).setFirstResult( 0 ).setMaxResults( 3 ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).addOrder(Order.asc( " name " )).setFirstResult( 0 ).setMaxResults( 3 ).list();

...

' 检索单个对象

uniqueResult() 方法
如果不确定返回的时单个对象先用Query和Criteria的serMaxResult(1)方法设定只返回一个对象

如果确定只返回一个对象比如from Customer c where c.id＝1

' HQL的两种绑定查询

session.createQuery( " from Customer c where c.name=:name and c.age=:age " ).setString( " name " , " tom " ).setInteger( " age " , 20 )

session.createQuery( " from Customer c where c.name=? and c.age=? " ).setString( 0 , " tom " ).setInteger( 1 , 20 )

' 特殊绑定方法

1
setEntity() 可以绑定实体对象

session.createQuery( " from Order o where o.customer=:customer " ).setEntity( " customer " , customer)

2
setParameter 绑定任意类型的参数

Query query = session.createQuery( " from Order o where o.customer=:customer and o.orderNumber like: orderNumber " );

query.setParameter( " customer " , customer, Hibernate.entity(Customer. class ));

query.setParameter( " orderNumber " , orderNumber, Hibernate.STRING)；

3
setProperties() 与一个对象的属性绑定

Customer customer = new Customer();

customer.setName( " Tom " );

customer.setAge( 21 );

List result = session.createQuery( " from Customer c where c.name=:name and c.age=:age " ).setProperties(customer).list();

' 设定查询条件

' 比较运算大于某个条件
HQL

...

List result = session.createQuery( " from Customer c where c.age>23 " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).add(Restrictions.gt( " age " , new Integer( 23 ))).list();

...

' 比较运算不等于某个条件

HQL

...

List result = session.createQuery( " from Customer c where c.age<>24 " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).add(Restrictions.not(Restrictions.eq( " age " , new Integer( 23 )))).list();

...

' 比较运算条件为null的某个对象

HQL

...

List result = session.createQuery( " from Order o where o.customer is null " ).list();

...

BQC

...

List result = session.createCriteria(Order. class )

.add(Restrictions.isNull( " customer " )).list();

...

' 范围运算检索在某个范围内String

HQL

...

List result = session.createQuery( " from Customer c where c.name in('Tom','Mike','Jack') " ).list();

...

BQC

...

String[] names = {"Tom", "Mike", "Jack"} ;

List result = session.createCriteria(Customer. class )

.add(Restrictions.in( " name " , names)).list();

...

' 范围运算检索在某个范围内Integer

HQL

...

List result = session.createQuery( " from Customer c where c.age between 24 and 30 " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class )

.add(Restrictions.between( " age " , new Integer( 24 ), new Integer( 30 ))).list();

...

不在某个范围内

HQL

...

List result = session.createQuery( " from Customer c where c.age not between 24 and 30 " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class )

.add(Restrictions.not(Restrictions.between( " age " , new Integer( 24 ), new Integer( 30 )))).list();

...

' 范围运算字符串通配符

以 T 开头
HQL like 'T%'
BQC add(Restrictions.like("name","T%"))

以 T 结尾
HQL like '%T'
BQC add(Restrictions.like("name","%T"))

包含 T
HQL like '%T%'
BQC add(Restrictions.like("name","%T%"))

控制长度为3
HQL like 'T_ _'
BQC add(Restrictions.like("name","T_ _"))

另外
Restrictions 还有这样的方法like(java.lang.String propertyName, java.lang.String value, MatchMode matchMode)
有一些定义好的field 具体查看API

' 逻辑运算检索复杂条件

条件结合
HQL

...

List result = session.createQuery( " from Customer c where c.name like 'T%' and c.name like '%m' " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class )

.add(Restrictions.like( " name " , " T% " )).add(Restrictions.like( " name " , " %m " )).list();

...

逻辑与
Restriction有如下方法
or(Criterion lhs, Criterion rhs)
and(Criterion lhs, Criterion rhs

HQL

...

List result = session.createQuery( " from Customer c where (c.name like 'T%' and c.name like '%m') or (c.age not between 20 and 25) " ).list();

...

BQC

...

List result = session.createCriteria(Customer. class ).add(Restrictions.or(Restrictions.and(Restrictions.like( " name " , " T% " ), Restrictions.like( " name " , " %m " )), Restrictions.not(Restrictions.between( " age " , new Integer( 20 ), new Integer( 25 ))))).list();

...

' 报表查询

from 关键字是必须 select group by having 用于报表查询

投影查询只检索出需要的字段

HQL

...

List result = session.createQuery( " select c.id, c.name, o.orderNumber from Customer c join c.orders o where o.orderNumber like 'T%' " ).list();

Iterator it = result.iterator();

while (it.hasNext())

{

Object[] row=(Object[])it.next();

Long id=(Long)row[0];

String name=(String)row[1];

String orderNumber=(String)row[2];

System.out.println(id+" "+name+" "+orderNumber);

}

...

这个list返回的结果是集合存放的是关系数据
也可以建立一个javabean来让结果更清晰

...

select new ergal.CustomerRow(c.id, c.name, o.orderNumber) from Customer c join c.orders o where o.orderNumber like ' T% '

...

while (it.hasNest())

{

CustomerRow cr=(CustomerRow)it.next();

Long id=(Long)cr.getId();

......

}

' 分组查询

查询相同姓名的的记录

...

List result = session.createQuery( " select c.name, count(c) from Customer c group by c.name " ).list();

Iterator it = result.iterator();

while (it.hasNext())

{

Object[] row=(Object[])it.next();

String name=(String)row[0];

Long id=(Long)row[1];

System.out.println(id+" "+name);

}

...

统计订单数目

...

List result = session.createQuery( " select c.id, c.name, count(o) from Customer c join c.orders o group by c.id " ).list();

Iterator it = result.iterator();

while (it.hasNext())

{

Object[] row=(Object[])it.next();

Long id=(Long)row[0];

String name=(String)row[1];

Long num=(Long)row[2];

System.out.println(num+" "+name+" "+id);

}

...

统计每个客户的订单总价-右外连接

...

List result = session.createQuery( " select c.id, c.name, sum(o.price) from Customer c right outer join c.orders o group by c.id " ).list();

Iterator it = result.iterator();

while (it.hasNext())

{

Object[] row=(Object[])it.next();

Long id=(Long)row[0];

String name=(String)row[1];

Double num=(Double)row[2];

System.out.println(num+" "+name+" "+id);

}

...

统计每个客户的订单总价-加条件订单大于一的

...

List result = session.createQuery( " select c.id, c.name, sum(o.price) from Customer c join c.orders o group by c.id having(count(o)>1) " ).list();

Iterator it = result.iterator();

while (it.hasNext())

{

Object[] row=(Object[])it.next();

Long id=(Long)row[0];

String name=(String)row[1];

Double num=(Double)row[2];

System.out.println(num+" "+name+" "+id);

}

...

查询时间段

tx = session.beginTransaction();

Date beginDate = java.sql.Date.valueOf( " 2006-8-1 " );

Date endDate = java.sql.Date.valueOf( " 2006-9-1 " );

List result = session.createQuery( " from Customer c where c.retime <:endTime and c.retime >=:beginTime " ).setTimestamp( " endTime " , endDate).setTimestamp( " beginTime " , beginDate).list();

for (Iterator it = result.iterator(); it.hasNext();)

{

Customer c =(Customer)it.next();

System.out.println("Customer's name :" + c.getName());

System.out.println("Customer's regiestertime :" + c.getRetime());

}

总的来说就是将显示时间和数据库的时间都进行转化然后进行比较

还有别的方式