Hibernate 利用HQL完成表的查询操作
1. 在Navicat中创建hibdb数据库并设计department表和user表
1)department表
2)users表
外键
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");
}
}