本文共 12809 字，大约阅读时间需要 42 分钟。

多表查询

准备数据

增加一个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
   
     linkMens = new HashSet
    
     ();		public Customer() {
   	}	public Set
     
       getLinkMens() {
   		return linkMens;	}	public void setLinkMens(Set
      
        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;	}}

表中数据如下：

内连接和迫切内连接

• 内连接
  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
   
     list1 = query1.list();		List
    
      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_idHibernate:     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
   
     list1 = query1.list();		List
    
      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_idHibernate:     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
   
     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]

转载地址：http://cjsqb.baihongyu.com/