首页 > 代码库 > 常用HQL(Hibernate Query Language)查询
常用HQL(Hibernate Query Language)查询
查询一个对象(实体类必须有一个不带参数的构造方法)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@Test public void test01() { Session session = null ; try { session = HibernateUtil.openSession(); /** * 对于HQL而言,都是基于对象进行查询的 */ Query query = session.createQuery( "from Special" ); List<Special> spes = query.list(); for (Special spe:spes) { System.out.println(spe.getName()); } } catch (Exception e) { e.printStackTrace(); } finally { HibernateUtil.close(session); } } |
使用select查询
1
2
3
4
5
6
7
8
9
|
//不能使用select *进行查询 //Query query = session.createQuery("select * from Special"); /** * 可以使用链式查询的方式 */ List<Special> spes = session.createQuery( "select spe from Special spe" ).list(); for (Special spe:spes) { System.out.println(spe.getName()); } |
条件查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
List<Student> stus = session .createQuery( "from Student where name like ‘%张%‘" ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } ------------------------------------------------- /** * 基于?的条件的查询 * 特别注意:jdbc设置参数的最小下标是1,hibernate是0 */ List<Student> stus = session .createQuery( "from Student where name like ?" ) .setParameter( 0 , "%李%" ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } ------------------------------------------------ /** * 还可以基于别名进行查询,使用:xxx来说明别名的名称 */ List<Student> stus = session .createQuery( "from Student where name like :name and sex=:sex" ) .setParameter( "name" , "%刘%" ) .setParameter( "sex" , "男" ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
使用uniqueResult可以返回唯一的一个值/对象
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Long stus = (Long)session .createQuery( "select count(*) from Student " + "where name like :name and sex=:sex" ) .setParameter( "name" , "%刘%" ) .setParameter( "sex" , "男" ) .uniqueResult(); System.out.println(stus); ------------------------------------------------------ Student stu = (Student)session .createQuery( "select stu from Student stu where id=:id" ) .setParameter( "id" , 1 ) .uniqueResult(); System.out.println(stu.getName()); |
基于投影的查询,通过在列表中存储一个对象的数组
1
2
3
4
5
6
7
|
List<Object[]> stus = session .createQuery( "select stu.sex,count(*) from Student stu " + "group by stu.sex" ) .list(); for (Object[] obj:stus) { System.out.println(obj[ 0 ]+ ":" +obj[ 1 ]); } |
如果对象中相应的导航对象,可以直接导航完成查询
1
2
3
4
5
6
7
8
|
List<Student> stus = session .createQuery( "select stu from Student stu " + "where stu.classroom.name=? and stu.name like ?" ) .setParameter( 0 , "计算机教育班" ).setParameter( 1 , "%张%" ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
可以使用in来设置基于列表的查询,此处的查询需要使用别名进行查询
特别注意,使用in的查询必须在其他的查询之后
1
2
3
4
5
6
7
8
9
|
List<Student> stus = session .createQuery( "select stu from Student stu " + "where stu.name like ? and stu.classroom.id in (:clas)" ) .setParameter( 0 , "%张%" ) .setParameterList( "clas" , new Integer[]{ 1 , 2 }) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
使用setFirstResult和setMaxResult可以完成分页的offset和pageSize的设置
1
2
3
4
5
6
7
8
9
|
List<Student> stus = session .createQuery( "select stu from Student stu " + "where stu.classroom.id in (:clas)" ) .setParameterList( "clas" , new Integer[]{ 1 , 2 }) .setFirstResult( 0 ).setMaxResults( 15 ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
可以通过is null来查询为空的对象,和sql一样不能使用=来查询null的对象
1
2
3
4
5
6
7
8
|
List<Student> stus = session .createQuery( "select stu from Student stu " + "where stu.classroom is null" ) .setFirstResult( 0 ).setMaxResults( 15 ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
连接查询
1
2
3
4
5
6
7
8
9
10
11
12
13
|
/** * 使用对象的导航可以完成连接,但是是基于Cross JOIN,效率不高, * 可以直接使用JOIN来完成连接 */ List<Student> stus = session .createQuery( "select stu from Student stu " + "left join " + "stu.classroom cla where cla.id=2" ) .setFirstResult( 0 ).setMaxResults( 15 ) .list(); for (Student stu:stus) { System.out.println(stu.getName()); } |
统计班级学生的人数
1
2
3
4
5
6
7
|
List<Object[]> stus = session .createQuery( "select cla.name,count(stu.classroom.id) from Student stu " + "right join stu.classroom cla group by cla.id" ) .list(); for (Object[] stu:stus) { System.out.println(stu[ 0 ]+ "," +stu[ 1 ]); } |
把查询出来的数据封装成一个对象。
定义一个学生对象DTO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
package org.zttc.itat.model; /** * DTO对象没有存储的意义,仅仅是用来进行数据的传输的 * */ public class StudentDto { private int sid; private String sname; private String sex; private String cname; private String spename; //省去set get方法 public StudentDto( int sid, String sname, String sex, String cname,String spename) { super (); this .sid = sid; this .sname = sname; this .sex = sex; this .cname = cname; this .spename = spename; } public StudentDto() { } } |
查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
/** * 直接可以使用new XXObject完成查询, * 注意,一定要加上Object的完整包名 * 这里使用的new XX,必须在对象中加入相应的构造函数 */ List<StudentDto> stus = session .createQuery( "select new org.zttc.itat.model.StudentDto" + "(stu.id as sid,stu.name as sname,stu.sex as sex," + "cla.name as cname,spe.name as spename) " + "from Student stu left join stu.classroom cla " + "left join cla.special spe" ) .list(); for (StudentDto stu:stus) { System.out.println(stu.getSid()+ "," +stu.getSname()+ "," + stu.getSex()+ "," +stu.getCname()+ "," +stu.getSpename()); } |
having是为group来设置条件的
1
2
3
4
5
6
7
8
|
List<Object[]> stus = session.createQuery( "select spe.name," + "(count(stu.classroom.special.id)) from Student stu right join " + "stu.classroom.special spe group by spe " + "having count(stu.classroom.special.id)>150" ) .list(); for (Object[] obj:stus) { System.out.println(obj[ 0 ]+ ":" +obj[ 1 ]); } |
常用HQL(Hibernate Query Language)查询
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。