Hibernate进阶之如何使用Hql内连接，外连接以及自连接

一、sql语句中的 内连接、自连接和外连接：

1、使用等值连接/内连接查询，查询客户姓名，订单编号，订单价格
等值连接/内连接：只能查询出符合条件的记录：
select c.name,o.orderno,o.price
from customers c,orders o
where c.id = o.customers_id; 

2、使用左外连接，按客户分组，查询每个客户的订单数：
select c.name,count(o.orderno)
from customers c left join orders o
on c.id = o.customers_id
group by c.name;

3、使用右外连接，按客户分组，查询每个客户的订单数：
select c.name,count(o.orderno)
from orders o right join customers c
on c.id = o.customers_id
group by c.name;

外连接：既能查询出符合条件的记录，同时不符合条件的记录也能查询出

4、等值连接语法：
select    字段
from      表名,表名 
where     等值连接条件;

外连接语法：
select    字段
from      表名1 left/right join 表名2
on        等值连接条件;
group by  字段

二、HQL实战：

1、使用等值连接查询，查询客户姓名，订单编号，订单价格：
    select c.name,o.orderno,o.price
    from Customer c join c.orderSet o
    where c.id = o.customer.id
2、使用左外连接，按客户分组，查询每个客户的姓名和订单数：
             select c.name,count(o.orderno)
             from Customer c left join c.orderSet o

             group  by c.name 

这里需要注意的是：左外链接中left join on后面跟着的条件省略不写，否则会报错，Hibernate会自动加上去，如果要添加条件可以使用with。

3、使用自连接，求出xx的老板是yy
    select a.name,b.name
    from  Emp a,Emp b 

    where a.mgr = b.id     

三、具体小案例：

首先写配置文件（CustomerOrder.hbm.xml，Emp.hbm.xml）：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="example.hql">
	<class name="Customer" table="customers">
		<id name="id" column="id" type="int">
			<generator class="native"></generator>
		</id>
		<property name="name" column="name" type="string"></property>
		<property name="age" column="age" type="int"></property>
		<!-- set标签用于映射单向一对多
			name表示单方的关联属性
			table表示多方对应表的名字
			key-cloumn表示多方对应表的外键
			one-to-many-class表示单方关联属性中的每个元素的类型
		 -->
		 <set name="orderSet" table="orders" cascade="all" inverse="true">
		 	<key column="customers_id"></key>
		 	<one-to-many class="Order"/>
		 </set>
	</class>
	<!-- 映射类的多方 -->
	<class name="Order" table="Orders">
		<id name="id" column="id">
			<generator class="native"></generator>
		</id>
		<property name="orderNo" column="orderNo"></property>
		<property name="price" column="price"></property>
		<many-to-one name="customer" column="customers_id"></many-to-one>
	</class>
	<query name="findCustomerByAge">
		<![CDATA[
			from Customer c where c.age>?
		]]>
	</query>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="example.hql">
	<class name="Emp" table="emps">
		<id name="id" column="id" type="int">
			<generator class="native"></generator>
		</id>
		<property name="name" column="name" ></property>
		<property name="age" column="sal" ></property>
		<property name="age" column="mgr" ></property>
	</class>
</hibernate-mapping>

接下来写实体类Customer，Order，Emp员工类：

Customer：

package example.hql;
 
import java.util.LinkedHashSet;
import java.util.Set;
 
/**
 * 客户（一方）
 * @author Administrator
 *
 */
public class Customer {
 
	private Integer id;//对应表的主键
	private String name;
	private Integer age;
	private Set<Order> orderSet=new LinkedHashSet<Order>();//关联属性
	public Customer() {
	}
	public Customer(String name, Integer age) {
		super();
		this.name = name;
		this.age = age;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public Set<Order> getOrderSet() {
		return orderSet;
	}
	public void setOrderSet(Set<Order> orderSet) {
		this.orderSet = orderSet;
	}
	
}

Order类：

package example.hql;
/**
 * 订单（多的一方）
 * @author Administrator
 *
 */
public class Order {
 
	private Integer id;
	private String orderNo;//订单编号
	private Integer price;//价格
	private Customer customer;//关联的属性
	
	
	public Order(String orderNo, Integer price, Customer customer) {
		super();
		this.orderNo = orderNo;
		this.price = price;
		this.customer = customer;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getPrice() {
		return price;
	}
	public void setPrice(Integer price) {
		this.price = price;
	}
	
	public String getOrderNo() {
		return orderNo;
	}
	public void setOrderNo(String orderNo) {
		this.orderNo = orderNo;
	}
	public Order() {
	}
	public Customer getCustomer() {
		return customer;
	}
	public void setCustomer(Customer customer) {
		this.customer = customer;
	}
	
}

Emp类：

package example.hql;
 
/**
 * 员工表
 * @author Administrator
 *
 */
public class Emp {
 
	private Integer id;
	private String name;
	private Integer sal;//薪水
	private Integer mgr;//直属领导编号
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getSal() {
		return sal;
	}
	public void setSal(Integer sal) {
		this.sal = sal;
	}
	public Integer getMgr() {
		return mgr;
	}
	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}
	public Emp() {
	}
	
	
}

最后实现内连接，外连接和自连接：

package example.hql;
 
import java.util.List;
 
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;
 
import example.utils.HibernateUtils;
 
public class CustomerOrderDao2 {
 
	/**
	 * 使用等值连接查询，查询客户姓名，订单编号，订单价格
	 */
	@Test
	public void test01(){
		Session session=HibernateUtils.getSession();
		Transaction t=session.getTransaction();
		try{
			t.begin();
			String hql="select c.name,o.orderNo,o.price from Customer c join c.orderSet o where c.id=o.customer.id";
			Query query=session.createQuery(hql);
			List<Object[]> list= (List<Object[]>) query.list();
			//对象导航查询
			for(Object[] o:list){
				System.out.println(o[0]+","+o[1]);
			}
			t.commit();
		}catch (Exception e) {
			e.printStackTrace();
			t.rollback();
		}finally{
			HibernateUtils.closeSession();
		}
	}
	
	/**
	 *使用左外连接，按客户分组，查询每个客户的姓名和订单数
	 */
	@Test
	public void test02(){
		Session session=HibernateUtils.getSession();
		Transaction t=session.getTransaction();
		try{
			t.begin();
			String hql="select c.name,count(o.orderNo) from Customer c left join c.orderSet o group by c.name";
			Query query=session.createQuery(hql);
			List<Object[]> list= (List<Object[]>) query.list();
			//对象导航查询
			for(Object[] o:list){
				System.out.println(o[0]+","+o[1]);
			}
			t.commit();
		}catch (Exception e) {
			e.printStackTrace();
			t.rollback();
		}finally{
			HibernateUtils.closeSession();
		}
	}
	/**
	 *使用自连接，求出xx的老板是yy
	 */
	@Test
	public void test03(){
		Session session=HibernateUtils.getSession();
		Transaction t=session.getTransaction();
		try{
			t.begin();
			String hql="select e1.name,e2.name from Emp e1,Emp e2 where e1.mgr=e2.id";
			Query query=session.createQuery(hql);
			List<Object[]> list= (List<Object[]>) query.list();
			//对象导航查询
			for(Object[] o:list){
				System.out.println(o[0]+","+o[1]);
			}
			t.commit();
		}catch (Exception e) {
			e.printStackTrace();
			t.rollback();
		}finally{
			HibernateUtils.closeSession();
		}
	}
}