一、连接查询
与SQL一样,HQL与QBC也支持各种各样的连接查询。如下表,
1、迫切左外连接查询
显式指定与Customer关联的Order对象采取迫切左外连接检索策略:
//HQL
List result=session.createQuery("from Customer c left join fetch c.orders o where c.name like 'T%'").list();
for(Iterator it=result.iterator;it.hasNext();){
Customer customer=(Customer)it.next;}
//QBC
List result=session.createCriteria(Customer.class).setFetchMode("orders",FetchMode.JOIN).add(Restrictions.like("name","T",MatchMode.START)).list();
for(Iterator it=result.iterator;it.hasNext();){
Customer customer=(Customer)it.next;}
以上代码生成的SQL查询语句为:
select c.ID C_ID,c.NAME,c.AGE,o.ID O_ID,o.ORDER_NUMBER,o.CUTOMER_ID from CUSTOMERS c left outer join
OEDERS o on c.ID=o.CUSTOMER_ID where (c.NAME like 'T%');
查询结果如下,
2、左外连接
查询语句为:
//HQL
List result=session.createQuery("from Customer c left join c.orders where c.name like 'T%'").list();
for(Iterator pairs=result.iterator;pairs.hasNext();)
{
Object[] pair=(Object[])pairs.next();
Customer customer=(Customer)pair[0];
Order order=(Order)pair[1];
//如果orders集合采用延时检索策略,以下代码会初始化Customer对象的orders集合
customer.getOrders().iterator();
}
使用左外连接查询时,根据映射文件的配置来决定orders集合的检索策略。不过,即使在Customer.hbm.xml文件中对orders集合设置了延迟检索策略,在运行以上的Query的list()方法时,Hibernate执行的SQL查询语句仍然和迫切左外连接查询生成的SQL查询语句相同。
3、内连接
inner join表示内连接。
//HQL
List result=session.createQuery("from Customer c inner join c.orders where c.name like 'T%'").list();
for(Iterator pairs=result.iterator;pairs.hasNext();)
{
Object[] pair=(Object[])pairs.next();
Customer customer=(Customer)pair[0];
Order order=(Order)pair[1];
//如果orders集合采用延时检索策略,以下代码会初始化Customer对象的orders集合
customer.getOrders().iterator();
}
假定在Customer.hbm.xml文件中对orders集合设置了延时检索策略,那么运行list()时,SQL语句为:
select c.ID C_ID,c.NAME,c.AGE,o.ID O_ID,o.ORDER_NUMBER,o.CUTOMER_ID from CUSTOMERS c inner join
OEDERS o on c.ID=o.CUSTOMER_ID where (c.NAME like 'T%');
以上查询结果为:
Select * from ORDERS where CUSTOMER_ID=1;
QBC也支持内连接查询,如:
Criteria customerCriteria=session.createCriteria(Customer.class);
customerCriteria.add(Restrictions.like("name","T",MatchMode.START));
Criteria orderCriteria=session.createCriteria(Order.class);
orderCriteria.add(Restrictions.like("orderNumber","T",MatchMode.START));
List result=orderCriteria.list();
//支持链编程方式
List result=session.createCriteria(Customer.class)
.add(Restrictions.like("name","T",MatchMode.START))
.createCriteria(Order.class)
.add(Restrictions.like("orderNumber","T",MatchMode.START))
.list();
在默认情况下只检索出Customer对象,因此可采用以下方式访问result集合中所有Customer对象,
for(Iterator pairs=result.iterator();pairs.hasNext();)
{
Customer customer=(Customer)pairs.next();
//如果orders集合采用延时检索策略,以下代码会初始化Customer对象的orders集合
Iterator orders=customer.getOrders().iterator();
.......
}
此外,Criteria的createAlias()方法也可以完成相同的功能,
List result=session.createCriteria(Customer.class)
.createAlias("orders","o")
.add(Restrictions.like("name","T",MatchMode.START))
.add(Restrictions.like("o.orderNumber","T",MatchMode.START))
.list();
createAlias()为orders集合赋予别名o,因此可以以o.orderNumber俩访问Order类中的orderNumber属性。在以上的程序中,Customer类的默认别名为this,因此上述代码也可以改成,
List result=session.createCriteria(Customer.class)
.createAlias("orders","o")
.add(Restrictions.like("this.name","T",MatchMode.START))
.add(Restrictions.like("o.orderNumber","T",MatchMode.START))
.list();
采用内连接查询时,HQL与QBC用不同的默认行为,HQL默认检索出成对的Customer对象和Order对象,而QBC只检索出Customer对象。如果希望QBC返回Customer和Order对象,可以调用Criteria的setResultTransform()方法,
List result=session.createCriteria(Customer.class)
.createAlias("orders","o")
.add(Restrictions.like("name","T",MatchMode.START))
.add(Restrictions.like("o.orderNumber","T",MatchMode.START))
.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
.list();
for(Iterator pairs=result.iterator;pairs.hasNext();)
{
Map pair=(Map)pairs.next();
Customer customer=(Customer)map.get("this");
Order order=(Order)map.get("o");
......
}