Hibernate中的多表连查询

title: Hibernate中的多表连查询
date: 2018-2-14 23:03:40
categories: Hibernate
tags: Hibernate

---

xl_echo编辑整理，欢迎转载，转载请声明文章来源。更多案例、资料请联系QQ：1280023003

Hibernate中的多表连查询

HQL多表连接查询Java代码

package com.echo.hibernate.hqljoinTest;

import java.util.List;

import org.hibernate.Session;
import org.junit.Test;

import com.echo.hibernate.domain.Customer;
import com.echo.hibernate.utils.HibernateUtils;

public class HQLTest {

    @Test
    /**
     * HQL显示内连接
     * 注意：在HQL中显示内连接会自动去除笛卡尔积
     */
    public void Test1(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "from Orders o inner join o.c";
        List<Object[]> list = session.createQuery(hql).list();

        for(Object[] objs : list){
            for(Object obj : objs){
                System.out.print(obj);
            }
            System.out.println();
        }

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL显示内连接，加条件检索，查询出ID为1的客户的所有订单
     */
    public void Test2(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "from Orders o inner join o.c with o.c.id = 1";
        List<Object[]> list = session.createQuery(hql).list();

        for(Object[] objs : list){
            for(Object obj : objs){
                System.out.print(obj + "\t");
            }
            System.out.println();
        }

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL隐式内连接
     */
    public void Test3(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "from Orders o where o.c.id = 1";
        List list = session.createQuery(hql).list();

        System.out.println(list);

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL迫切内连接
     * 注意使用迫切内连接，结果list中全部装入的是from后面的对象
     */
    public void Test4(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "from Orders o inner join fetch o.c";
        List list = session.createQuery(hql).list();

        System.out.println(list);

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL迫切内连接，查询所有客户
     * 注意：使用迫切内连接查询所有客户信息，有多少个订单就会将订单对应的客户全部打印出来，出现大量重复，所有需要使用distinct去重
     */
    public void Test5(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "select distinct c from Customer c inner join fetch c.orders";
        List list = session.createQuery(hql).list();

        System.out.println(list);

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL左外连接，查询所有客户，没有订单的客户也需要查询出来
     * 注意：左外连接，会将没有订单的客户也查询出来，左外连接以left的左边的表为准，左边的即是左表
     */
    public void Test6(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        String hql = "from Customer c left outer join c.orders";//左外连接
        //String hql = "from Customer c right outer join c.orders"//右外连接
        List<Object[]> list = session.createQuery(hql).list();
        for(Object[] objs : list){
            for(Object obj : objs){
                System.out.print(obj + "\t");
            }
            System.out.println();
        }

        System.out.println(list);

        session.beginTransaction().commit();
        session.close();
    }

    @Test
    /**
     * HQL迫切左外连接
     * 注意：
     *  没有迫切右外连接
     *  使用迫切左外连接需要使用distinct消除笛卡尔积
     *  同时fetch不能跟with同时存在，如果需要加条件那么，就需要使用where
     */
    public void Test7(){

        Session session = HibernateUtils.getSession();
        session.beginTransaction();

        //String hql = "select distinct c from Customer c left outer join fetch c.orders";
        String hql = "select distinct c from Customer c left outer join fetch c.orders where c.id=1";
        List<Customer> list = session.createQuery(hql).list();
        System.out.println(list);

        session.beginTransaction().commit();
        session.close();
    }
}

实体类注解代码

package com.echo.hibernate.domain;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.NamedQuery;
/**
 * 客户类，注解开发一对多
 * @author XL-sorry
 * catalog可以省略，默认直接从核心配置文件中加载
 */
@Entity
@Table(name="t_customer",catalog="myxlsorry")
/*@NamedQuery(name="myhql",query="from Customer")*/
public class Customer {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;//主键
    private String name;//客户姓名

    /**
     * 在关系属性上使用OnetoMany，代表的就是该类对另外一个类是一对多的映射关系
     * targetEntity=Orders.class声明多的那个类是哪一个
     * mappedBy="c"，在orders中的属性，定义属于那个客户就是c，所以配置它，代表着由orders维护外键
     */
    @OneToMany(targetEntity=Orders.class,mappedBy="c",orphanRemoval=true)
    /*@Cascade(CascadeType.SAVE_UPDATE)*/
    private Set<Orders> orders = new HashSet<>();

    public Customer() {
        super();
    }
    public Customer(Integer id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    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 Set<Orders> getOrders() {
        return orders;
    }
    public void setOrders(Set<Orders> orders) {
        this.orders = orders;
    }
    @Override
    public String toString() {
        return "Customer [id=" + id + ", name=" + name + "]";
    }

}


package com.echo.hibernate.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import org.hibernate.annotations.Cascade;
import org.hibernate.annotations.CascadeType;
import org.hibernate.annotations.NamedQuery;

/**
 * 订单类，注解开发多对一
 * @author XL-sorry
 *
 */
@Entity
@Table(name="t_orders",catalog="myxlsorry")
@NamedQuery(name="findOrdersByCustomer",query="from Orders where c = :c")
public class Orders {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;//订单编号
    private String address;//收货地址
    private Double money;//订单金额

    /**
     * 指定关联哪一个类
     */
    @ManyToOne(targetEntity=Customer.class)
    //指定级联
//  @Cascade(CascadeType.SAVE_UPDATE)
    //生成外键的名字叫什么
    @JoinColumn(name = "c_customer_id")
    @Cascade(CascadeType.ALL)
    private Customer c;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Double getMoney() {
        return money;
    }
    public void setMoney(Double money) {
        this.money = money;
    }
    public Customer getC() {
        return c;
    }
    public void setC(Customer c) {
        this.c = c;
    }
    @Override
    public String toString() {
        return "Orders [id=" + id + ", address=" + address + ", money=" + money + ", c=" + c + "]";
    }
}