一、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 {/*** 使用等值連接查詢，查詢客戶姓名，訂單編號，訂單價格*/@Testpublic 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();}}/***使用左外連接，按客戶分組，查詢每個客戶的姓名和訂單數*/@Testpublic 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*/@Testpublic 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();}} }

?

總結

以上是生活随笔為你收集整理的Hibernate进阶之如何使用Hql内连接，外连接以及自连接的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。