一、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();
}
}
}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/114712.html原文链接：https://javaforall.net
