立即检索
类级别的立即加载关联对象
关联级别的立即加载指定对象的关联对象
可以设定批量检索数量
延时检索
类级别的延时加载关联对象
关联级别的延时加载指定对象的关联对象
可以设定批量检索数量
迫切左外连接检索 类级别不适用 关联级别通过左外连接加载与检索方法指定对象关联的对象
涉及到的元素属性
<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 指定批量检索的数量
迫切左外连接检索 类级别不适用 关联级别通过左外连接加载与检索方法指定对象关联的对象
涉及到的元素属性
<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() 方法加载的对象时实际相关数据 不是代理类
' 但是仅仅初始化了 OID 其他都为 null
' 只有当访问除 ID 外的属性 或者修改时才会加载数据库的数据
' 有可能在你关闭 session 后仍没有初始化对象 也就没有完整的游离对象
' 只有在 session 范围内才能被初始化
' get() 方法加载的对象时实际相关数据 不是代理类
一对多和多对多的检索策略
<set> 的 lazy 和 out-join
都为 false 立即检索
lazy 为 false out-join 为 true 是迫切左外连接检索 ( 当有多个 <set> 只允许一个用 )
相反时 用延时检索策略
<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();
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;
}
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();
...
List result=session.createCriteria(Customer.class).list();
...
'
指定查询和命名别名
HQL
...
List result=session.createQuery("from Customer as c where name=:name").setString("name", "Tom").list();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
List result=session.createCriteria(Customer.class).addOrder(Order.asc("name")).setFirstResult(0).setMaxResults(3).list();
...
'
检索单个对象
uniqueResult()
方法
如果不确定返回的时单个对象先用 Query 和 Criteria 的 serMaxResult(1) 方法 设定只返回一个对象
如果不确定返回的时单个对象先用 Query 和 Criteria 的 serMaxResult(1) 方法 设定只返回一个对象
如果确定只返回一个对象
比如
from Customer c where c.id
=
1
' HQL
的两种绑定查询
1
session.createQuery("from Customer c where c.name=:name and c.age=:age").setString("name", "tom").setInteger("age", 20)
2
session.createQuery("from Customer c where c.name=? and c.age=?").setString(0, "tom").setInteger(1, 20)
'
特殊绑定方法
1
setEntity() 可以绑定实体对象
setEntity() 可以绑定实体对象
session.createQuery("from Order o where o.customer=:customer").setEntity("customer", customer)
2
setParameter 绑定任意类型的参数
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) ;
query.setParameter("customer", customer, Hibernate.entity(Customer.class));
query.setParameter("orderNumber", orderNumber, Hibernate.STRING) ;
3
setProperties() 与一个对象的属性绑定
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();
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
HQL
...
List result=session.createQuery("from Customer c where c.age>23").list();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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();
...
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%"))
HQL like 'T%'
BQC add(Restrictions.like("name","T%"))
以
T
结尾
HQL like '%T'
BQC add(Restrictions.like("name","%T"))
HQL like '%T'
BQC add(Restrictions.like("name","%T"))
包含
T
HQL like '%T%'
BQC add(Restrictions.like("name","%T%"))
HQL like '%T%'
BQC add(Restrictions.like("name","%T%"))
控制长度为
3
HQL like 'T_ _'
BQC add(Restrictions.like("name","T_ _"))
HQL like 'T_ _'
BQC add(Restrictions.like("name","T_ _"))
另外
Restrictions 还有这样的方法 like(java.lang.String propertyName, java.lang.String value, MatchMode matchMode)
有一些定义好的 field 具体查看 API
Restrictions 还有这样的方法 like(java.lang.String propertyName, java.lang.String value, MatchMode matchMode)
有一些定义好的 field 具体查看 API
'
逻辑运算
检索
复杂条件
条件结合
HQL
HQL
...
List result=session.createQuery("from Customer c where c.name like 'T%' and c.name like '%m'").list();
...
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();
...
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
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();
...
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();
...
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 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 来让结果更清晰
也可以建立一个 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();
......
}
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.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, 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 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);
}
...
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());
}
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());
}
总的来说就是将显示时间
和
数据库的时间都进行转化
然后进行比较