hibernate 学习之QBC和本地SQL查询

QBC查询是通过使用hibernate提供的QUeryByCriteria API 来查询对象，
这种API封装了SQL语句的动态拼装，对查询提供了更加面向对象的功能接口。

本地SQL查询来完善HQL不能覆盖所有的查询特性

马哥私房菜博客地址：https://github.com/mageSFC/myblog

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98



@Test
public void testQBC() {
    //1.创建一个criteria 对象
    Criteria criteria = session.createCriteria(Employee.class);
    //2. 添加查询条件，在qbc中查询条件使用criterion来表示
    //criterion可以通过Restrictions类的静态方法得到
    criteria.add(Restrictions.eq("email", "SKUMAR"));
    criteria.add(Restrictions.gt("salary", 5000f));

    Employee employee = (Employee) criteria.uniqueResult();

    System.out.println(employee);


}


@Test
public void testQBC2() {
    //1.创建一个criteria 对象
    Criteria criteria = session.createCriteria(Employee.class);

    //and  的使用  Conjunction 本身就是一个criterion对象，其中还可以添加criterion对象
    Conjunction conjunction = Restrictions.conjunction();
    conjunction.add(Restrictions.like("name", "a", MatchMode.ANYWHERE));

    Department dept = new Department();
    dept.setId(20);
    conjunction.add(Restrictions.eq("dept", dept));
    System.out.println(conjunction);


    Disjunction disjunction = Restrictions.disjunction();
    disjunction.add(Restrictions.ge("salary", 6000f));
    disjunction.add(Restrictions.isNull("email"));

    criteria.add(disjunction).add(conjunction);

    List list = criteria.list();
    System.out.println(list);


}

@Test
public void testQBC3() {
    //1.创建一个criteria 对象
    Criteria criteria = session.createCriteria(Employee.class);

    //统计查询
    criteria.setProjection(Projections.max("salary"));

    Float o = (Float) criteria.uniqueResult();
    System.out.println(o);


}


@Test
public void testQBC4() {
    //1.创建一个criteria 对象
    Criteria criteria = session.createCriteria(Employee.class);

    //排序
    criteria.addOrder(Order.asc("salary"));
    criteria.addOrder(Order.desc("email"));

    int pageSize = 5;
    int pageNo = 3;

    criteria.setFirstResult((pageNo - 1) * pageSize)
            .setMaxResults(pageSize);
    List list = criteria.list();
    System.out.println(list);

}


@Test
public void testNativeSql() {
    String sql = "INSERT INTO HB_DEPARTMENT VALUES(?, ?)";
    Query sqlQuery = session.createSQLQuery(sql);

    sqlQuery.setInteger(0, 280).setString(1, "马哥私房菜").executeUpdate();

}

@Test
public void testHQLUpdate(){
    String sql = "delete from Department  d where d.id = :id";
    Query query = session.createQuery(sql);
    query.setInteger("id", 280).executeUpdate();
}