查询一个对象(实体类必须有一个不带参数的构造方法)
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 ]);
} |