本笔记继续使用dept部门表,emp员工表,一对多多对一双向映射。

1 条件查询

1.1    查询 员工表emp中 年龄eage小于30,月薪esal大于20000的员工姓名ename

sql:select ename from emp where eage<? and esal >?;

hql: select ename from Emp where eage<? and esal >?

1.2 问号的设置与别名

 问号(?)的设置使用.setParameter(位置, 属性值) 在sql语句中问号的位置是从1开始,在hql中从零开始。

 在hql中,问号(?)也可以起别用,:后面紧跟别名,setParameter("age", 30)方法也随之改变,将原先的数字改成"别名",如下所示

             String hql="select ename from Emp where eage<:age and esal >:sal";
             Query query= session.createQuery(hql).setParameter("age", 30).setParameter("sal", 20000f).list();   

1.3   一组问号的设置

       对于一组问号也可以设置别名:如 查询在1,2部门的员工姓名

             hql="select ename from Emp where did in (?,?)";

             hql="select ename from Emp where did in (:d)";

             Query query= session.createQuery(hql).setParameter("d", new Object[]{1,2}).list();

 

public void Test9() throws Exception{ 
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();
String hql="select ename from Emp where eage<? and esal >?";
Query query= session.createQuery(hql).setParameter(0, 30).setParameter(1, 20000f); //设置问号,从0开始。
List<String> list = query.list();
for(String message:list){
System.out.println(message);
}
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}

结果:

Hibernate: select emp0_.ename as col_0_0_ from emp emp0_ where emp0_.eage<? and emp0_.esal>?
刘亦菲
白百何
文章
林月如

 

2 条件查询的常用关键字

    2.1  distinct  过滤重复的值

            查询员工表中的所有员工姓名,并去掉重复值

            hql="select distinct  ename from Emp ";  

    2.2  delete   删除

             删除年龄大于25的员工

     hql="delete Emp where eage>25"; 删除年龄大于25岁的用户

            Query query=session.createQuery(hql);

         query.executeUpdate();           //执行  executeUpdate 方法返回int类型。

       tx.commit();            //成功,则提交,对数据库操作

    2.3  update 更新

           更新员工编号11的年龄为22

    String hql="update Emp s set s.eage='22' where s.eid=11";   //更新语句

       Query query=session.createQuery(hql);

           query.executeUpdate();           //执行

           tx.commit();           //成功,则提交

    2.4  between...and...和not between... and...确定查询范围

          查找员工表中年龄在20到30之间的员工姓名

          hql="select ename from Emp where eage between 20 and 30"

  2.5  in和not in确定查询集合

           查询员工属于低1,2部门的员工姓名

          hql="select ename from Emp where did in(1,2)"

    2.6  like进行模糊查询

          用like进行模糊查询时有两个可用的通配符:“%”和“_”。“%”代表长度大于等于0的字符,“_”代表长度为1的单个字符。

          查询员工表中姓名中带有刘的员工

          hql="select ename from Emp where ename  like '%刘%'"

     2.7  逻辑与 and 逻辑或 or

          查询员工中薪水大于2000 同时年龄小于30的员工姓名

          hql="select ename from Emp where esal>2000 and eage<30";

          查询员工中,年龄大于40或者年龄小于30的员工

          hql="select ename  from Emp where eage>40 or eage<30";

     2.8  order by对结果进行排序

         对薪水进行排序,从小到大

         hql="select esal from Emp where order by esal asc";

         对年龄进行排序,倒叙,

         hql="select ename from Emp where order by eage desc";

     2.9  group by对记录进行分组

        根据部门分组,求出各组的平均薪水

        hql=" select avg(esal) from Emp group by did";

     2.10 having 对分组进行筛选

          根据部门分组,查出员工所在组平均薪水大于10000的员工姓名

          hql=" select ename from Emp group by did having  avg(esal)>10000";

 

3 聚集函数

查询emp员工表中有多少个员工。

sql:select count(ename) from emp;

hql: select count(ename) from Emp;

聚集函数及含义:

hql: select avg(esal) from Emp;   薪水平均值

hql: select max(esal) from Emp;  薪水最大值

hql: select sum(esal) from Emp;  薪水最小值

    public void Test91() throws Exception{ 
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();
String hql="select count(ename) from Emp";
Object count=session.createQuery(hql).uniqueResult(); //uniqueResult()方法返回Object类型 
System.out.println(count);

tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}

 

结果:

Hibernate: select count(emp0_.ename) as col_0_0_ from emp emp0_
15

 

 4 子查询

Hibernate 支持子查询,所谓子查询就是,要查询的字段及信息在A表,条件在B表。语法与sql语句相似。

public void Test9() throws Exception{ 
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();

String hql="select ename from Emp where did in (select did from Dept where daddress = 302)";
Query query= session.createQuery(hql);
List<String> list = query.list();
for(String message:list){
System.out.println(message);
}
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}

结果:

Hibernate: select emp0_.ename as col_0_0_ from emp emp0_ where did in (select dept1_.did from dept dept1_ where dept1_.daddress=302)
李世民
曹操
和珅
刘诗诗

 

5 导航查询

 e.dept.daddress=301

员工所在部门的地址是301

public void Test9() throws Exception{ 
Configuration config=new Configuration().configure();
SessionFactory sessionFactory= config.buildSessionFactory();
Session session=null;
Transaction tr=null;
try{
session= sessionFactory.openSession();
tr=session.beginTransaction();

String hql="select ename from Emp e where e.dept.daddress=301 ";
Query query= session.createQuery(hql);
List<String> list = query.list();
for(String message:list){
System.out.println(message);
}
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
if(session!=null){
session.close();
}
if(sessionFactory!=null){
sessionFactory.close();
}
}
}

结果:

Hibernate: select emp0_.ename as col_0_0_ from emp emp0_, dept dept1_ where emp0_.did=dept1_.did and dept1_.daddress=301
刘德华
贺龙
白百何
文章
林月如