(16)hql的连接查询

1.SELECT * FROM classroom t1 LEFT JOIN student t2 ON (t1.`id`=t2.`c_id`)

左连接:左边有的，右边没有的也连接上去

2.SELECT * FROM classroom t1 RIGHT JOIN student t2 ON (t1.`id`=t2.`c_id`)

右连接

3.SELECT t2.id,t2.`name`,COUNT(t1.`id`) FROM student t1 LEFT JOIN classroom t2 ON (t1.`c_id`=t2.`id`) GROUP BY t2.`id`

统计每一个班人数

4.SELECT t1.`name`,t2.`sex`,COUNT(t2.`id`) FROM classroom t1 LEFT JOIN student t2 ON (t1.`id`=t2.`c_id`) GROUP BY t1.`id`,t2.`sex`

统计每一个班男女人数

test14：

void test14() {
   		Session s=null;
   		try {
   			s=HibernateUtil.getSession();
   			//List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id=2").list(); 
   			List<Student> objs=s.createQuery("select stu from Student stu left join stu.classroom cla where cla.id=2").list();
   			for(Student obj:objs) {
   				System.out.println(obj.getId()+":"+obj.getName());
   			}
   		} catch (Exception e) {
   			// TODO 自动生成的 catch 块
   			e.printStackTrace();
   			s.getTransaction().rollback();
   		}finally {
   			HibernateUtil.closeSession(s);
   		}
   	}

注意:（1）.连接stu和cla，只需要 select xx from Student stu left join stu.classroom cla

test15：

 	void test15() {
   		Session s=null;
   		try {
   			s=HibernateUtil.getSession();
   			//List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id=2").list(); 
   			List<Object[]> objs=s.createQuery("select cla.name,count(stu.id) from Student stu right join stu.classroom cla group by cla.id").list();
   			for(Object[] obj:objs) {
   				System.out.println(obj[0]+":"+obj[1]);
   			}
   		} catch (Exception e) {
   			// TODO 自动生成的 catch 块
   			e.printStackTrace();
   			s.getTransaction().rollback();
   		}finally {
   			HibernateUtil.closeSession(s);
   		}
   	}

 

test16:

public class StudentDto {
	private int stuid;
	private String stuname;
	private String stusex;
	private String claname;
	private String spename;
	
public StudentDto(int stuid, String stuname, String stusex, String claname, String spename) {
		super();
		this.stuid = stuid;
		this.stuname = stuname;
		this.stusex = stusex;
		this.claname = claname;
		this.spename = spename;
	}

public int getStuid() {
		return stuid;
	}

	public void setStuid(int stuid) {
		this.stuid = stuid;
	}

	public String getStuname() {
		return stuname;
	}

	public void setStuname(String stuname) {
		this.stuname = stuname;
	}

	public String getStusex() {
		return stusex;
	}

	public void setStusex(String stusex) {
		this.stusex = stusex;
	}

	public String getClaname() {
		return claname;
	}

	public void setClaname(String claname) {
		this.claname = claname;
	}

	public String getSpename() {
		return spename;
	}

	public void setSpename(String spename) {
		this.spename = spename;
	}

public StudentDto() {}
}

注意：（1）. 该类需要一个字段生成的构造函数以及一个无参数的构造函数，以及生成getter，setter

	void test16() {
   		Session s=null;
   		try {
   			s=HibernateUtil.getSession();
   			//List<Student> objs=s.createQuery("select stu from Student stu where stu.classroom.id=2").list(); 
   			List<StudentDto> objs=s.createQuery("select new model.StudentDto(stu.id as stuid,stu.name as stuname,stu.sex as stusex,cla.name as claname,spe.name as spename) from Student stu left join stu.classroom cla left join cla.special spe").list();
   			for(StudentDto obj:objs) {
   				System.out.println(obj.getStuid()+","+obj.getStuname()+","+obj.getStusex()+","+obj.getClaname()+","+obj.getSpename());
   			}
   		} catch (Exception e) {
   			// TODO 自动生成的 catch 块
   			e.printStackTrace();
   			s.getTransaction().rollback();
   		}finally {
   			HibernateUtil.closeSession(s);
   		}
   	}

注意:(1).使用dto对象，在select中用 new model.StudentDto(stu.name as stuname等)，注意是完整路径，包括包名

(2).返回的是List<dto对象>，遍历每一个对象只需要使用getter即可取到相应属性值