(16)hql的连接查询

淡淡的烟草味﹌ 2022-05-03 14:30 89阅读 0赞

**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即可取到相应属性值**