三套查询之Criteria查询
完全面向对象的，不需要写任可查询语句.
1.查询所有的学生
1 //1.查询所有的学生
2 @Test
3 public void test1(){
4 Criteria criteria = session.createCriteria(Student.class);
5 List<Student> list = criteria.list();
6 for (Student student : list) {
7 System.out.println(student.getId()+student.getName());
8 }
9
10 Criteria criteria2 = session.createCriteria("com.rong.entity.group.Student");
11 List<Student> list2 = criteria2.list();
12 for (Student student : list2) {
13 System.out.println(student.getId()+student.getName());
14 }
15 }
2.添加查询条件
add(Criterion criterion) : 添加查询条件.
Criterion : 代表一个查询条件.
Restrictions工具类，专门负责生成查询条件对象，它把where部分都改成了静态方法。
sqlRestriction(String sql) : 生成查询条件的万能方法.
Property工具类，专门负责生成查询条件对象.
Propery.forName("属性名").xxx()
1 //2.添加查询条件
2 @Test
3 public void test2(){
4 //1.添加一个
5 Criteria criteria = session.createCriteria(Student.class);
6 criteria=criteria.add(Restrictions.like("name", "%ab%"));
7 List<Student> list = criteria.list();
8 for (Student student : list) {
9 System.out.println(student.getId()+student.getName());
10 }
11 //2.添加多个
12 Criteria criteria2 = session.createCriteria(Student.class);
13 criteria2.add(Restrictions.like("name", "%a%"));
14 criteria2.add(Restrictions.between("java", 60, 100));
15 List<Student> list2 = criteria2.list();
16 for (Student student : list2) {
17 System.out.println(student.getId()+student.getName());
18 }
19 System.out.println("===================================");
20 Criteria criteria3 = session.createCriteria(Student.class);
21 criteria3.add(Property.forName("name").like("%p%"));
22 criteria3.add(Property.forName("math").between(85, 100));
23 List<Student> list3 = criteria3.list();
24 for (Student student : list3) {
25 System.out.println(student.getId()+student.getName()
26 +student.getMath());
27 }
28 }
3.添加自己的sql
1 //3.添加自己的sql
2 @Test
3 public void test3(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.add(Restrictions.sqlRestriction("length(name)>4 and name like '%a%'"));
6 List<Student> list = criteria.list();
7 for (Student student : list) {
8 System.out.println(student.getId()+student.getName());
9 }
10 }
4.排序
addOrder(Order order) : 添加排序.
Order.asc("属性名") | Order.desc("属性名")
Property.forName("age").asc()|Property.forName("age").desc()
1 //4.排序
2 @Test
3 public void test4(){
4 //第一种方式
5 Criteria criteria = session.createCriteria(Student.class);
6 criteria.add(Restrictions.lt("id", 10));
7 criteria.addOrder(Order.asc("java"));//升序
8 List<Student> list = criteria.list();
9 for (Student student : list) {
10 System.out.println(student.getId()+student.getName()+student.getJava());
11 }
12 Criteria criteria2 = session.createCriteria(Student.class);
13 criteria2.add(Restrictions.gt("id", 90));
14 criteria2.addOrder(Order.desc("math"));//降序
15 List<Student> list2 = criteria2.list();
16 for (Student student : list2) {
17 System.out.println(student.getId()+student.getName()+student.getMath());
18 }
19 //第二种方式
20 Criteria criteria3 = session.createCriteria(Student.class);
21 List<Student> list3 = criteria3.addOrder(Property.forName("java").asc()).list();
22 for (Student student : list3) {
23 System.out.println(student.getId()+student.getName()+student.getJava());
24 }
25 Criteria criteria4 = session.createCriteria(Student.class);
26 List<Student> list4 = criteria4.addOrder(Property.forName("java").desc()).list();
27 for (Student student : list4) {
28 System.out.println(student.getId()+student.getName()+student.getJava());
29 }
30 }
5.查询一列
setProjection(Projection projection): 查询哪些列.
Projection : 代表一列
ProjectionList : 代表多列
Projections : 专门创建Projection | ProjectionList
1 //5.查询一列
2 @Test
3 public void test5(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.setProjection(Projections.property("name"));
6 List<String> list = criteria.list();
7 for (String string : list) {
8 System.out.println(string);
9 }
10 }
6.查询多列
1 //6.查询多列
2 @Test
3 public void test6(){
4 Criteria criteria = session.createCriteria(Student.class);
5 ProjectionList pl = Projections.projectionList();
6 pl.add(Projections.property("java"));
7 pl.add(Projections.property("math"));
8 criteria.setProjection(pl);
9 //criteria.setProjection(Projections.projectionList().add(Projections.property("java"))
10 // .add(Projections.property("math")));
11 List<Object[]> list = criteria.list();
12 for (Object[] objects : list) {
13 for (Object object : objects) {
14 System.out.print(object+" ");
15 }
16 System.out.println();
17 }
18 }
7.聚集函数
1 //7.聚集函数
2 @Test
3 public void test7(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.setProjection(Projections.rowCount());
6 criteria.add(Restrictions.between("id", 10, 20));
7 Object rowCount = criteria.uniqueResult();
8 System.out.println("id在10到20之间的数量为："+rowCount);
9 Criteria criteria2 = session.createCriteria(Student.class);
10 criteria2.setProjection(Projections.sum("java"));
11 criteria2.add(Property.forName("id").between(1, 3));
12 Object sum = criteria2.uniqueResult();
13 System.out.println("id为1到3的java成绩总和为："+sum);
14 }
8.分组
1 //8.分组
2 @Test
3 public void test8(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.setProjection(Projections.projectionList().add(Projections.rowCount()).add(Projections.groupProperty("teacher.id")));
6 List<Object[]> list = criteria.list();
7 for (Object[] objects : list) {
8 for (Object object : objects) {
9 System.out.println(object);
10 }
11 }
12 }
9.通过老师id查询学生(使用别名查询)
setFetchMode(String associationPath, FetchMode mode): 抓取连接(join fetch)
FetchMode: 抓取模式
FetchMode.JOIN FetchMode.EAGER 立即查询
FetchMode.SELECT FetchMode.LAZY 延迟查询
1 //9.通过老师id查询学生(使用别名查询)
2 @Test
3 public void test9(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.add(Restrictions.eq("teacher.tid", 1));
6 List<Student> list = criteria.list();
7 for (Student student : list) {
8 System.out.println(student.getName()+student.getTeacher().getTid());
9 }
10 System.out.println("=============以下是通过别名查询================");
11 Criteria criteria2 = session.createCriteria(Student.class);
12 criteria2.createAlias("teacher", "t");
13 criteria2.add(Restrictions.eq("t.id", 1));
14 List<Student> list2 = criteria2.list();
15 for (Student student : list2) {
16 System.out.println(student.getName()+student.getTeacher().getTid());
17 }
18 }
1 //setFetchMode: 抓取延迟的属性
2 Criteria criteria3 = session.createCriteria(Student.class);
3 criteria3.setFetchMode("teacher", FetchMode.JOIN);
4 List<Student> list3 = criteria3.list();
5 /*for (Student student : list3) {
6 System.out.println(student.getName()+student.getTeacher().getTid());
7 }*/
10.离线查询
离线查询 DetachedCriteria.
DetachedCriteria ：用它就可以定义一条查询语句. select * from stu_info.
用得时候需要与Session关联起来.
离线查询的主要作用就是为了做子查询. in 、not in(离线查询对象)
1 //10.离线查询
2 @Test
3 public void test10(){
4 DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Student.class);
5 Criteria criteria = detachedCriteria.getExecutableCriteria(session);
6 List<Student> list = criteria.list();
7 for (Student student : list) {
8 System.out.println(student.getId()+student.getName());
9 }
10 }
11.子查询
1 //11.子查询
2 @Test
3 public void test11(){
4 Criteria criteria = session.createCriteria(Student.class);
5 criteria.add(Restrictions.in("id", new Object[]{1,2,3}));
6 List<Student> list = criteria.list();
7 for (Student student : list) {
8 System.out.println(student.getId()+student.getName());
9 }
10 System.out.println("==================================");
11 //离线查询作为子查询的一部分
12 DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Student.class);
13 detachedCriteria.setProjection(Projections.property("id"));
14 detachedCriteria.add(Restrictions.between("id", 60, 90));
15 Criteria criteria2 = session.createCriteria(Student.class);
16 criteria2.add(Property.forName("id").in(detachedCriteria));
17 List<Student> list2 = criteria2.list();
18 for (Student student : list2) {
19 System.out.println(student.getId()+student.getName());
20 }
21 }