HIbernate--HQL举例+详解(三)

傷城~ 2021-08-14 01:37 381阅读 0赞

## 多表查询 ##

**准备数据**  
增加一个LinkMAN实体类，与Customer形成多对一关系。

public class Customer { 
    	
    	private Long cust_id;
    	private String cust_name;
    	private String cust_source;
    	private String cust_industry;
    	private String cust_level;
    	private String cust_linkman;
    	private String cust_phone;
    	private String cust_mobile;
    	//使用set集合,表达一对多关系
    	private Set<LinkMan> linkMens = new HashSet<LinkMan>();
    	
    	public Customer() { 
    	}
    	public Set<LinkMan> getLinkMens() { 
    		return linkMens;
    	}
    	public void setLinkMens(Set<LinkMan> linkMens) { 
    		this.linkMens = linkMens;
    	}
    	public Long getCust_id() { 
    		return cust_id;
    	}
    	public void setCust_id(Long cust_id) { 
    		this.cust_id = cust_id;
    	}
    	public String getCust_name() { 
    		return cust_name;
    	}
    	public void setCust_name(String cust_name) { 
    		this.cust_name = cust_name;
    	}
    	public String getCust_source() { 
    		return cust_source;
    	}
    	public void setCust_source(String cust_source) { 
    		this.cust_source = cust_source;
    	}
    	public String getCust_industry() { 
    		return cust_industry;
    	}
    	public void setCust_industry(String cust_industry) { 
    		this.cust_industry = cust_industry;
    	}
    	public String getCust_level() { 
    		return cust_level;
    	}
    	public void setCust_level(String cust_level) { 
    		this.cust_level = cust_level;
    	}
    	public String getCust_linkman() { 
    		return cust_linkman;
    	}
    	public void setCust_linkman(String cust_linkman) { 
    		this.cust_linkman = cust_linkman;
    	}
    	public String getCust_phone() { 
    		return cust_phone;
    	}
    	public void setCust_phone(String cust_phone) { 
    		this.cust_phone = cust_phone;
    	}
    	public String getCust_mobile() { 
    		return cust_mobile;
    	}
    	public void setCust_mobile(String cust_mobile) { 
    		this.cust_mobile = cust_mobile;
    	}
    	public Customer(Long cust_id, String cust_name, String cust_level) { 
    		super();
    		this.cust_id = cust_id;
    		this.cust_name = cust_name;
    		this.cust_level = cust_level;
    	}
    }

public class LinkMan { 
    
    	private Long lkm_id;
    	private Character lkm_gender;
    	private String lkm_name;
    	private String lkm_phone;
    	private String lkm_email;
    	private String lkm_qq;
    	private String lkm_mobile;
    	private String lkm_memo;
    	private String lkm_position;
    	//表达多对一关系
    	private Customer customer ;
    	
    	public Customer getCustomer() { 
    		return customer;
    	}
    	public void setCustomer(Customer customer) { 
    		this.customer = customer;
    	}
    	public Long getLkm_id() { 
    		return lkm_id;
    	}
    	public void setLkm_id(Long lkm_id) { 
    		this.lkm_id = lkm_id;
    	}
    	public Character getLkm_gender() { 
    		return lkm_gender;
    	}
    	public void setLkm_gender(Character lkm_gender) { 
    		this.lkm_gender = lkm_gender;
    	}
    	public String getLkm_name() { 
    		return lkm_name;
    	}
    	public void setLkm_name(String lkm_name) { 
    		this.lkm_name = lkm_name;
    	}
    	public String getLkm_phone() { 
    		return lkm_phone;
    	}
    	public void setLkm_phone(String lkm_phone) { 
    		this.lkm_phone = lkm_phone;
    	}
    	public String getLkm_email() { 
    		return lkm_email;
    	}
    	public void setLkm_email(String lkm_email) { 
    		this.lkm_email = lkm_email;
    	}
    	public String getLkm_qq() { 
    		return lkm_qq;
    	}
    	public void setLkm_qq(String lkm_qq) { 
    		this.lkm_qq = lkm_qq;
    	}
    	public String getLkm_mobile() { 
    		return lkm_mobile;
    	}
    	public void setLkm_mobile(String lkm_mobile) { 
    		this.lkm_mobile = lkm_mobile;
    	}
    	public String getLkm_memo() { 
    		return lkm_memo;
    	}
    	public void setLkm_memo(String lkm_memo) { 
    		this.lkm_memo = lkm_memo;
    	}
    	public String getLkm_position() { 
    		return lkm_position;
    	}
    	public void setLkm_position(String lkm_position) { 
    		this.lkm_position = lkm_position;
    	}
    }

表中数据如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

#### 内连接和迫切内连接 ####

*  内连接  
    SQL语句：select \* from cst\_customer cus,cst\_linkman lin where cus.cust\_id=lin.lkm\_cust\_id  
    hql语句：from Customer c inner join c.linkMens  
    query.list()返回的list里面每部分是数组形式
 *  迫切内连接  
    迫切内连接和内连接底层实现一样  
    区别：使用内连接返回list每部分是数组，使用迫切内连接返回list每部分是对象。  
    hql语句：from Customer c inner join fetch c.linkMens

例子：

//内连接和迫切内连接
    @Test
    	public void fun1(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		String hql1 = " from Customer c inner join c.linkMens ";//内连接
    		String hql2 = "from Customer c inner join fetch c.linkMens";//迫切内连接
    		
    		Query query1 = session.createQuery(hql1);
    		Query query2 = session.createQuery(hql2);
    		
    		
    		List<Object[]> list1 = query1.list();
    		List<Customer> list2 = query2.list();
    		
    		for(Object[] arr : list1){ 
    			System.out.println(Arrays.toString(arr));
    		}
    		
    		System.out.println(list2);
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_id as cust_id1_0_0_,
            linkmens1_.lkm_id as lkm_id1_1_1_,
            customer0_.cust_name as cust_nam2_0_0_,
            customer0_.cust_source as cust_sou3_0_0_,
            customer0_.cust_industry as cust_ind4_0_0_,
            customer0_.cust_level as cust_lev5_0_0_,
            customer0_.cust_linkman as cust_lin6_0_0_,
            customer0_.cust_phone as cust_pho7_0_0_,
            customer0_.cust_mobile as cust_mob8_0_0_,
            linkmens1_.lkm_gender as lkm_gend2_1_1_,
            linkmens1_.lkm_name as lkm_name3_1_1_,
            linkmens1_.lkm_phone as lkm_phon4_1_1_,
            linkmens1_.lkm_email as lkm_emai5_1_1_,
            linkmens1_.lkm_qq as lkm_qq6_1_1_,
            linkmens1_.lkm_mobile as lkm_mobi7_1_1_,
            linkmens1_.lkm_memo as lkm_memo8_1_1_,
            linkmens1_.lkm_position as lkm_posi9_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_1_ 
        from
            cst_customer customer0_ 
        inner join
            cst_linkman linkmens1_ 
                on customer0_.cust_id=linkmens1_.lkm_cust_id
    Hibernate: 
        select
            customer0_.cust_id as cust_id1_0_0_,
            linkmens1_.lkm_id as lkm_id1_1_1_,
            customer0_.cust_name as cust_nam2_0_0_,
            customer0_.cust_source as cust_sou3_0_0_,
            customer0_.cust_industry as cust_ind4_0_0_,
            customer0_.cust_level as cust_lev5_0_0_,
            customer0_.cust_linkman as cust_lin6_0_0_,
            customer0_.cust_phone as cust_pho7_0_0_,
            customer0_.cust_mobile as cust_mob8_0_0_,
            linkmens1_.lkm_gender as lkm_gend2_1_1_,
            linkmens1_.lkm_name as lkm_name3_1_1_,
            linkmens1_.lkm_phone as lkm_phon4_1_1_,
            linkmens1_.lkm_email as lkm_emai5_1_1_,
            linkmens1_.lkm_qq as lkm_qq6_1_1_,
            linkmens1_.lkm_mobile as lkm_mobi7_1_1_,
            linkmens1_.lkm_memo as lkm_memo8_1_1_,
            linkmens1_.lkm_position as lkm_posi9_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_0__,
            linkmens1_.lkm_id as lkm_id1_1_0__ 
        from
            cst_customer customer0_ 
        inner join
            cst_linkman linkmens1_ 
                on customer0_.cust_id=linkmens1_.lkm_cust_id
    //内连接，两部分，以数组形式返回
    [per.zhang.domain.Customer@48e64352, per.zhang.domain.LinkMan@200606de]
    [per.zhang.domain.Customer@48e64352, per.zhang.domain.LinkMan@750fe12e]
    [per.zhang.domain.Customer@f8908f6, per.zhang.domain.LinkMan@3e587920]
    [per.zhang.domain.Customer@2ef8a8c3, per.zhang.domain.LinkMan@24f43aa3]
    [per.zhang.domain.Customer@2ef8a8c3, per.zhang.domain.LinkMan@63fd4873]
    [per.zhang.domain.Customer@1e11bc55, per.zhang.domain.LinkMan@7544a1e4]
    [per.zhang.domain.Customer@1e11bc55, per.zhang.domain.LinkMan@70e0accd]
    [per.zhang.domain.Customer@7957dc72, per.zhang.domain.LinkMan@6ab72419]
    //迫切内连接 以对象形式返回，linkMan封装到了Customer的Set集合中
    [per.zhang.domain.Customer@48e64352, per.zhang.domain.Customer@48e64352, per.zhang.domain.Customer@f8908f6, per.zhang.domain.Customer@2ef8a8c3, per.zhang.domain.Customer@2ef8a8c3, per.zhang.domain.Customer@1e11bc55, per.zhang.domain.Customer@1e11bc55, per.zhang.domain.Customer@7957dc72]

#### 外连接和迫切外连接 ####

@Test
    	//外连接和迫切外连接
    	public void fun2(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		String hql1 = " from Customer c right join c.linkMens ";//右外连接
    		String hql2 = " from Customer c right join fetch c.linkMens ";//迫切右外连接
    		
    		Query query1 = session.createQuery(hql1);
    		Query query2 = session.createQuery(hql2);
    		
    		
    		List<Object[]> list1 = query1.list();
    		List<Customer> list2 = query2.list();
    		
    		for(Object[] arr : list1){ 
    			System.out.println(Arrays.toString(arr));
    		}
    		
    		System.out.println(list2);
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_id as cust_id1_0_0_,
            linkmens1_.lkm_id as lkm_id1_1_1_,
            customer0_.cust_name as cust_nam2_0_0_,
            customer0_.cust_source as cust_sou3_0_0_,
            customer0_.cust_industry as cust_ind4_0_0_,
            customer0_.cust_level as cust_lev5_0_0_,
            customer0_.cust_linkman as cust_lin6_0_0_,
            customer0_.cust_phone as cust_pho7_0_0_,
            customer0_.cust_mobile as cust_mob8_0_0_,
            linkmens1_.lkm_gender as lkm_gend2_1_1_,
            linkmens1_.lkm_name as lkm_name3_1_1_,
            linkmens1_.lkm_phone as lkm_phon4_1_1_,
            linkmens1_.lkm_email as lkm_emai5_1_1_,
            linkmens1_.lkm_qq as lkm_qq6_1_1_,
            linkmens1_.lkm_mobile as lkm_mobi7_1_1_,
            linkmens1_.lkm_memo as lkm_memo8_1_1_,
            linkmens1_.lkm_position as lkm_posi9_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_1_ 
        from
            cst_customer customer0_ 
        right outer join
            cst_linkman linkmens1_ 
                on customer0_.cust_id=linkmens1_.lkm_cust_id
    Hibernate: 
        select
            customer0_.cust_id as cust_id1_0_0_,
            linkmens1_.lkm_id as lkm_id1_1_1_,
            customer0_.cust_name as cust_nam2_0_0_,
            customer0_.cust_source as cust_sou3_0_0_,
            customer0_.cust_industry as cust_ind4_0_0_,
            customer0_.cust_level as cust_lev5_0_0_,
            customer0_.cust_linkman as cust_lin6_0_0_,
            customer0_.cust_phone as cust_pho7_0_0_,
            customer0_.cust_mobile as cust_mob8_0_0_,
            linkmens1_.lkm_gender as lkm_gend2_1_1_,
            linkmens1_.lkm_name as lkm_name3_1_1_,
            linkmens1_.lkm_phone as lkm_phon4_1_1_,
            linkmens1_.lkm_email as lkm_emai5_1_1_,
            linkmens1_.lkm_qq as lkm_qq6_1_1_,
            linkmens1_.lkm_mobile as lkm_mobi7_1_1_,
            linkmens1_.lkm_memo as lkm_memo8_1_1_,
            linkmens1_.lkm_position as lkm_posi9_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_1_,
            linkmens1_.lkm_cust_id as lkm_cus10_1_0__,
            linkmens1_.lkm_id as lkm_id1_1_0__ 
        from
            cst_customer customer0_ 
        right outer join
            cst_linkman linkmens1_ 
                on customer0_.cust_id=linkmens1_.lkm_cust_id
                //外连接，两部分，以数组形式返回
    [pers.zhang.domain.Customer@48e64352, pers.zhang.domain.LinkMan@200606de]
    [pers.zhang.domain.Customer@48e64352, pers.zhang.domain.LinkMan@750fe12e]
    [pers.zhang.domain.Customer@f8908f6, pers.zhang.domain.LinkMan@3e587920]
    [pers.zhang.domain.Customer@2ef8a8c3, pers.zhang.domain.LinkMan@24f43aa3]
    [pers.zhang.domain.Customer@2ef8a8c3, pers.zhang.domain.LinkMan@63fd4873]
    [pers.zhang.domain.Customer@1e11bc55, pers.zhang.domain.LinkMan@7544a1e4]
    [pers.zhang.domain.Customer@1e11bc55, pers.zhang.domain.LinkMan@70e0accd]
    [pers.zhang.domain.Customer@7957dc72, pers.zhang.domain.LinkMan@6ab72419]
    [null, pers.zhang.domain.LinkMan@3aacf32a]
    //外连接，以对象形式返回，linkMan封装到了Customer的Set集合中
    [cn.itcast.domain.Customer@48e64352, pers.zhang.domain.Customer@48e64352, cn.itcast.domain.Customer@f8908f6, pers.zhang.domain.Customer@2ef8a8c3, cn.itcast.domain.Customer@2ef8a8c3, pers.zhang.domain.Customer@1e11bc55, cn.itcast.domain.Customer@1e11bc55, pers.zhang.domain.Customer@7957dc72, null]

#### 子查询 ####

对于子查询的数据库，Hibernate支持在查询中使用子查询。一个子查询必须被圆括号包围起来（经常是SQL聚集函数的圆括号）。甚至相互关联的子查询（引用到外部查询中的别名的子查询）也是允许的。  
select c.cust\_id,c.cust\_name,c.cust\_level  
from Customer c  
where c.cust\_id = (  
select l.customer.cust\_id  
from LinkMan l  
where l.lkm\_name like ‘联想02’)

例子：

@Test
    	//子查询
    	public void fun4(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		//查询linkman的名字为‘联想02’的customer对象的id，名字，和客户等级
    		String hql = "select c.cust_id,c.cust_name,c.cust_level "
    						+ "from Customer c "
    						+ "where c.cust_id = "
    							+ "(select l.customer.cust_id "
    								+ "from LinkMan l "
    								+ "where l.lkm_name like '联想02')";
    		
    		Query query = session.createQuery(hql);
    		
    		List<Object[]> list = query.list();
    		
    		for(Object[] arr : list){ 
    			System.out.println(Arrays.toString(arr));
    		}
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_id as col_0_0_,
            customer0_.cust_name as col_1_0_,
            customer0_.cust_level as col_2_0_ 
        from
            cst_customer customer0_ 
        where
            customer0_.cust_id=(
                select
                    linkman1_.lkm_cust_id 
                from
                    cst_linkman linkman1_ 
                where
                    linkman1_.lkm_name like '联想02'
            )
    [2, 联想, 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/4d419383dbe64ad78d0b2266955a17ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/3c5aa7c9fe4f456c9dd30ca030b1ca21.png