Hibernate查询—利用HQL完成表的查询操作

╰+攻爆jí腚メ 2022-06-16 08:51 241阅读 0赞

# **Hibernate 利用HQL完成表的查询操作** #

1. 在Navicat中创建hibdb数据库并设计department表和user表

![Center][]

1）department表

![Center 1][]

![Center 2][]

2）users表

![Center 3][]

外键

![Center 4][]

![Center 5][]

2. 在myeclipse的DB Browser窗格中创建与数据库hibdb的连接；

3. 为创建的Java工程添加hibernate的支持；

4. 在MyEclipse的DB Browser窗格中利用Hivernate反向工程（Hibernate Reverse Engineering）生成class表和student表的映射文件及POJO类。

（注第2、3步骤可以参考我的其它几篇文章）

5. 创建测试类

1）HqlDao.java

import java.util.Iterator;
    import java.util.List;
    import java.util.Set;
    
    import org.hibernate.Query;
    import org.hibernate.Session;
    
    import com.HibernateSessionFactory;
    import com.entity.Department;
    import com.entity.Users;
    
    public class HqlDao {
    	
    	public void seDname(String dname){
    		Session session = HibernateSessionFactory.getSession();
    		//根据班级名称查询department表select d.depId 
    		String hql1 = "from Department d where d.depName=:dname";
    		Query query = session.createQuery(hql1);
    		query.setString("dname", dname);
    		List result = query.list();
    		Iterator it = result.iterator();
    			while(it.hasNext()){
    				Department dep = (Department)it.next();
    				System.out.println("班级id："+dep.getDepId()+"    "+"班级名称:"+dep.getDepName());
    			}
    			session.close();
    	}
    	
    	public void seSname(String sname){
    		//根据学生姓名模糊查询user表
    		Session session = HibernateSessionFactory.getSession();
    		String hql2 = "from Users as u where u.usrDesc like :sname";
    		Query query2 = session.createQuery(hql2);
    		query2.setString("sname", sname);
    		List result2 = query2.list();
    		Iterator it2 = result2.iterator();
    			while(it2.hasNext()){
    				Users user = (Users)it2.next();
    				System.out.println("姓名："+user.getUsrDesc()+"  年龄："+user.getAge()+"  班级："+user.getDepartment().getDepName());
    			}
    			session.close();
    	}
    	
    	public void seExact(String sname){
    		//根据学生姓名精确查询user表
    		Session session = HibernateSessionFactory.getSession();
    		String hql3 = "from Users as u where u.usrDesc=:sname";
    		Query query3 = session.createQuery(hql3);
    		query3.setString("sname", sname);
    		List result3 = query3.list();
    		Iterator it3 = result3.iterator();
    			while(it3.hasNext()){
    				Users user = (Users)it3.next();
    				System.out.println("姓名："+user.getUsrDesc()+"  年龄："+user.getAge()+"  班级："+user.getDepartment().getDepName());
    			}
    			session.close();
    	}
    	
    	public void seCombine(String cname,int age){
    		//组合查询
    		Session session = HibernateSessionFactory.getSession();
    		String hql4 = "from Users as u where u.usrDesc=:cname and u.age=:age";
    		Query query4 = session.createQuery(hql4);
    		query4.setString("cname", cname);
    		query4.setInteger("age", age);
    		List result4 = query4.list();
    		Iterator it4 = result4.iterator();
    			while(it4.hasNext()){
    				Users user = (Users)it4.next();
    				System.out.println("姓名："+user.getUsrDesc()+"  年龄："+user.getAge()+"  班级："+user.getDepartment().getDepName());
    			}
    			session.close();
    	}
    	
    	public void seDep(String depName){
    		//查询某个班级的学生
    		Session session = HibernateSessionFactory.getSession();
    		String hql5 = "from Department d where d.depName=:depName";
    		Query query5 = session.createQuery(hql5);
    		query5.setString ("depName",depName);
    		List result5 = query5.list();
    		Iterator it5 = result5.iterator();
    
    			while(it5.hasNext()){
    				Department d = (Department)it5.next();
    				Set set = d.getUserses();
    				Iterator its = set.iterator();
    				while(its.hasNext()){
    					System.out.println(
    							"学生姓名："+((Users) its.next()).getUsrDesc());
    				}
    				
    			}
    			session.close();
    	}
    	
    	public void seUsr(String usrDesc){
    		//查询一个学生所在的班级
    		Session session = HibernateSessionFactory.getSession();
    		String hql6 = "select u.department,u from Users u where u.usrDesc=:usrDesc";
    		Query query6 = session.createQuery(hql6);
    		query6.setString ("usrDesc",usrDesc);
    		List result6 = query6.list();
    		Iterator it6 = result6.iterator();
    			while(it6.hasNext()){			
    				Object[] obj = (Object[])it6.next();
    				System.out.println("班级："+((Department)obj[0]).getDepName()
    						+"   学生姓名："+((Users)obj[1]).getUsrDesc());
    			}
    			session.close();
    	}
    
    }

2）Test1.java

public class Test1 {
    	public static void main(String[] args) {
    		// TODO Auto-generated method stub
    		HqlDao hq = new HqlDao();
    		
    		//根据班级名称查询department表
    		hq.seDname("101");
    		
    		//根据学生姓名模糊查询user表
    		hq.seSname("li%");
    		
    		//根据学生姓名精确查询user表
    		hq.seExact("lili");
    		
    		//组合查询
    		hq.seCombine("lili", 21);
    		
    		//查询某个班级的学生
    		hq.seDep("101");
    		
    		//查询一个学生所在的班级
    		hq.seUsr("lili");
    
    	}
    
    }

[Center]: /images/20220616/768360944f5448a2bdc53d555dcc5c3c.png
[Center 1]: /images/20220616/cbb9f2c2c206409a8e6b9a7eee092eb7.png
[Center 2]: /images/20220616/1f8bc9232e7c471894085ad1a6ef3d1b.png
[Center 3]: /images/20220616/0397d672d4d64595890c15dda7b02efb.png
[Center 4]: /images/20220616/79fe82c923ba428b9a383fae43b29cb6.png
[Center 5]: /images/20220616/95db84fcc0034acba4ea333ccee10a3f.png