Hibernate HQL示例九：连接查询

inner join

left out join

right out join

可简写

join ,left join,right join

 

先回忆一下连接：

inner join: 返回的是两个表中关联字段值相同的数据集

select t.name from t_student t inner join t_classes c on t.classid=c.id where c.id<2;

 内连接不使用inner join关键字的情况：

select t.name from t_student t ,t_classes c where t.classid = c.id and c.id<2

 

 

left out join:返回的是两个表中，关联左边的那个表的所有数据和右边表中关联字段值与左边相同的数据集。

select * from t_student t left join t_classes c on t.classid = c.id;

 right out join:与left out join相似。

 

 

如下示例演示：

package com.bjsxt.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import junit.framework.TestCase;

public class JionQueryTest extends TestCase {
	
	/**
	 * 内连接，从Student连接到Classes
	 *
	 */
	public void testQuery1() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			List students = session.createQuery("select c.name, s.name from Student s join s.classes c ").list();
			//Hibernate: select classes1_.name as col_0_0_, student0_.name as col_1_0_ from t_student student0_ inner join t_classes classes1_ on student0_.classid=classes1_.id
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}	
	}
	
	public void testQuery1Object() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//查询出来的obj数组中包含的是两个对象。。
			List students = session.createQuery("from Student s join s.classes  ").list();
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
			//	System.out.println(obj[0] + ", " + obj[1]);
				Student stu = (Student)obj[0];
				Classes cls = (Classes)obj[1];
				System.out.println(stu.getName() + " : " + stu.getCreateTime() + " : " + cls.getName());
			
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}	
	}
	
	/**
	 * 左外连接
	 *
	 */
	public void testQuery2() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			List students = session.createQuery("select c.name, s.name from Classes c left join c.students s ").list();
			
			//List students = session.createQuery("select s.name,c.name from Student s left join s.classes c").list();
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}	
	}

	/**
	 * 右外连接
	 *
	 */	
	public void testQuery3() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			List students = session.createQuery("select c.name, s.name from Classes c right join c.students s ")
			.list();
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}	
	}
	
	/**
	 * 内连接，从Classes连接到Student
	 *
	 */
	public void testQuery4() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			List students = session.createQuery("select c.name, s.name from Classes c join c.students s ").list();
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}	
	}
}