13.3.2 定义DAO接口

发表于 分类于 ， ， Waline： 本文字数： 30k 阅读时长 ≈ 27 分钟

13.3.2 定义DAO接口

UserDao接口

下面是UserDao接口的源代码。

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
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.fkit.hrm.dao.provider.UserDynaSqlProvider;
import org.fkit.hrm.domain.User;
// 静态导入数据库表常量
import static org.fkit.hrm.util.common.HrmConstants.USERTABLE;

public interface UserDao
{
    // 根据登录名和密码查询员工
    @Select("select * from " + USERTABLE
            + " where loginname = #{loginname} and password = #{password}")
    User selectByLoginnameAndPassword(@Param("loginname") String loginname,
            @Param("password") String password);

    // 根据id查询用户
    @Select("select * from " + USERTABLE + " where ID = #{id}")
    User selectById(Integer id);

    // 根据id删除用户
    @Delete(" delete from " + USERTABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 动态修改用户
    @SelectProvider(
        type = UserDynaSqlProvider.class,
        method = "updateUser")
    void update(User user);

    // 动态查询
    @SelectProvider(
        type = UserDynaSqlProvider.class,
        method = "selectWhitParam")
    List<User> selectByPage(Map<String, Object> params);

    // 根据参数查询用户总数
    @SelectProvider(
        type = UserDynaSqlProvider.class,
        method = "count")
    Integer count(Map<String, Object> params);

    // 动态插入用户
    @SelectProvider(
        type = UserDynaSqlProvider.class,
        method = "insertUser")
    void save(User user);
}

在Userdao接口中使用了动态SQL提供类:UserDynasqlProvider。

UserDynaSqlProvider.java

D:\Desktop\随书源码\Spring+Mybatis企业应用实战(第2版)\codes\13\hrmapp\src\org\fkit\hrm\dao\provider\UserDynaSqlProvider.java
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package org.fkit.hrm.dao.provider;

import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.fkit.hrm.domain.User;
import static org.fkit.hrm.util.common.HrmConstants.USERTABLE;

public class UserDynaSqlProvider
{
    // 分页动态查询
    public String selectWhitParam(Map<String, Object> params)
    {
        String sql = new SQL()
        {
            {
                SELECT("*");
                FROM(USERTABLE);
                if (params.get("user") != null)
                {
                    User user = (User) params.get("user");
                    if (user.getUsername() != null
                            && !user.getUsername().equals(""))
                    {
                        WHERE("  username LIKE CONCAT ('%',#{user.username},'%') ");
                    }
                    if (user.getUserstatus() != null
                            && !user.getUserstatus().equals(""))
                    {
                        WHERE(" userstatus LIKE CONCAT ('%',#{user.userstatus},'%') ");
                    }
                }
            }
        }.toString();

        if (params.get("pageModel") != null)
        {
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }
        return sql;
    }
    // 动态查询总数量
    public String count(Map<String, Object> params)
    {
        return new SQL()
        {
            {
                SELECT("count(*)");
                FROM(USERTABLE);
                if (params.get("user") != null)
                {
                    User user = (User) params.get("user");
                    if (user.getUsername() != null
                            && !user.getUsername().equals(""))
                    {
                        WHERE(" username LIKE CONCAT ('%',#{user.username},'%') ");
                    }
                    if (user.getUserstatus() != null
                            && !user.getUserstatus().equals(""))
                    {
                        WHERE(" userstatus LIKE CONCAT ('%',#{user.userstatus},'%') ");
                    }
                }
            }
        }.toString();
    }

    // 动态插入
    public String insertUser(User user)
    {
        return new SQL()
        {
            {
                INSERT_INTO(USERTABLE);
                if (user.getUsername() != null
                        && !user.getUsername().equals(""))
                {
                    VALUES("username", "#{username}");
                }
                if (user.getUserstatus() != null
                        && !user.getUserstatus().equals(""))
                {
                    VALUES("userstatus", "#{userstatus}");
                }
                if (user.getLoginname() != null
                        && !user.getLoginname().equals(""))
                {
                    VALUES("loginname", "#{loginname}");
                }
                if (user.getPassword() != null
                        && !user.getPassword().equals(""))
                {
                    VALUES("password", "#{password}");
                }
            }
        }.toString();
    }
    // 动态更新
    public String updateUser(User user)
    {
        return new SQL()
        {
            {
                UPDATE(USERTABLE);
                if (user.getUsername() != null)
                {
                    SET(" username = #{username} ");
                }
                if (user.getLoginname() != null)
                {
                    SET(" loginname = #{loginname} ");
                }
                if (user.getPassword() != null)
                {
                    SET(" password = #{password} ");
                }
                if (user.getUserstatus() != null)
                {
                    SET(" userstatus = #{userstatus} ");
                }
                if (user.getCreateDate() != null)
                {
                    SET(" create_date = #{createDate} ");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}

通过上面的DAO接口完成数据库的操作,这种简单的实现较之传统的JDBC持久化访问方便。

DeptDao接口

下面是DeptDao接口的源代码:

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
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.fkit.hrm.dao.provider.DeptDynaSqlProvider;
import org.fkit.hrm.domain.Dept;
import static org.fkit.hrm.util.common.HrmConstants.DEPTTABLE;

public interface DeptDao
{
    // 动态查询
    @SelectProvider(
        type = DeptDynaSqlProvider.class,
        method = "selectWhitParam")
    List<Dept> selectByPage(Map<String, Object> params);

    @SelectProvider(
        type = DeptDynaSqlProvider.class,
        method = "count")
    Integer count(Map<String, Object> params);

    @Select("select * from " + DEPTTABLE + " ")
    List<Dept> selectAllDept();

    @Select("select * from " + DEPTTABLE + " where ID = #{id}")
    Dept selectById(int id);

    // 根据id删除部门
    @Delete(" delete from " + DEPTTABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 动态插入部门
    @SelectProvider(
        type = DeptDynaSqlProvider.class,
        method = "insertDept")
    void save(Dept dept);

    // 动态修改用户
    @SelectProvider(
        type = DeptDynaSqlProvider.class,
        method = "updateDept")
    void update(Dept dept);
}

JobDao接口

下面是JobDao接口的源代码。

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
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.fkit.hrm.dao.provider.JobDynaSqlProvider;
import org.fkit.hrm.domain.Job;
import static org.fkit.hrm.util.common.HrmConstants.JOBTABLE;

public interface JobDao
{
    @Select("select * from " + JOBTABLE + " where ID = #{id}")
    Job selectById(int id);

    @Select("select * from " + JOBTABLE + " ")
    List<Job> selectAllJob();

    // 动态查询
    @SelectProvider(
        type = JobDynaSqlProvider.class,
        method = "selectWhitParam")
    List<Job> selectByPage(Map<String, Object> params);

    @SelectProvider(
        type = JobDynaSqlProvider.class,
        method = "count")
    Integer count(Map<String, Object> params);

    // 根据id删除部门
    @Delete(" delete from " + JOBTABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 动态插入部门
    @SelectProvider(
        type = JobDynaSqlProvider.class,
        method = "insertJob")
    void save(Job job);

    // 动态修改用户
    @SelectProvider(
        type = JobDynaSqlProvider.class,
        method = "updateJob")
    void update(Job job);
}

在JobDao接口中使用了动态SQL提供类JobDynaSqlProvider。

JobDynaSqlProvider.java

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
package org.fkit.hrm.dao.provider;

import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.fkit.hrm.domain.Job;
import static org.fkit.hrm.util.common.HrmConstants.JOBTABLE;

public class JobDynaSqlProvider
{
    // 分页动态查询
    public String selectWhitParam(Map<String, Object> params)
    {
        String sql = new SQL()
        {
            {
                SELECT("*");
                FROM(JOBTABLE);
                if (params.get("job") != null)
                {
                    Job job = (Job) params.get("job");
                    if (job.getName() != null && !job.getName().equals(""))
                    {
                        WHERE("  name LIKE CONCAT ('%',#{job.name},'%') ");
                    }
                }
            }
        }.toString();

        if (params.get("pageModel") != null)
        {
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }

        return sql;
    }
    // 动态查询总数量
    public String count(Map<String, Object> params)
    {
        return new SQL()
        {
            {
                SELECT("count(*)");
                FROM(JOBTABLE);
                if (params.get("job") != null)
                {
                    Job job = (Job) params.get("job");
                    if (job.getName() != null && !job.getName().equals(""))
                    {
                        WHERE("  name LIKE CONCAT ('%',#{job.name},'%') ");
                    }
                }
            }
        }.toString();
    }
    // 动态插入
    public String insertJob(Job job)
    {

        return new SQL()
        {
            {
                INSERT_INTO(JOBTABLE);
                if (job.getName() != null && !job.getName().equals(""))
                {
                    VALUES("name", "#{name}");
                }
                if (job.getRemark() != null && !job.getRemark().equals(""))
                {
                    VALUES("remark", "#{remark}");
                }
            }
        }.toString();
    }
    // 动态更新
    public String updateJob(Job job)
    {

        return new SQL()
        {
            {
                UPDATE(JOBTABLE);
                if (job.getName() != null)
                {
                    SET(" name = #{name} ");
                }
                if (job.getRemark() != null)
                {
                    SET(" remark = #{remark} ");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}

EmployeeDao.java

下面是EmployeeDao接口的源代码。

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.mapping.FetchType;
import org.fkit.hrm.dao.provider.EmployeeDynaSqlProvider;
import org.fkit.hrm.domain.Employee;
import static org.fkit.hrm.util.common.HrmConstants.EMPLOYEETABLE;

public interface EmployeeDao
{
    // 根据参数查询员工总数
    @SelectProvider(
        type = EmployeeDynaSqlProvider.class,
        method = "count")
    Integer count(Map<String, Object> params);

    // 根据参数动态查询员工
    @SelectProvider(
        type = EmployeeDynaSqlProvider.class,
        method = "selectWhitParam")
    @Results(
    {@Result(
        id = true,
        column = "id",
        property = "id"),
            @Result(
                column = "CARD_ID",
                property = "cardId"),
            @Result(
                column = "POST_CODE",
                property = "postCode"),
            @Result(
                column = "QQ_NUM",
                property = "qqNum"),
            @Result(
                column = "BIRTHDAY",
                property = "birthday",
                javaType = java.util.Date.class),
            @Result(
                column = "CREATE_DATE",
                property = "createDate",
                javaType = java.util.Date.class),
            @Result(
                column = "DEPT_ID",
                property = "dept",
                one = @One(
                    select = "org.fkit.hrm.dao.DeptDao.selectById",
                    fetchType = FetchType.EAGER)),
            @Result(
                column = "JOB_ID",
                property = "job",
                one = @One(
                    select = "org.fkit.hrm.dao.JobDao.selectById",
                    fetchType = FetchType.EAGER))})
    List<Employee> selectByPage(Map<String, Object> params);

    // 动态插入员工
    @SelectProvider(
        type = EmployeeDynaSqlProvider.class,
        method = "insertEmployee")
    void save(Employee employee);

    // 根据id删除员工
    @Delete(" delete from " + EMPLOYEETABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 根据id查询员工
    @Select("select * from " + EMPLOYEETABLE + " where ID = #{id}")
    @Results(
    {@Result(
        id = true,
        column = "id",
        property = "id"),
            @Result(
                column = "CARD_ID",
                property = "cardId"),
            @Result(
                column = "POST_CODE",
                property = "postCode"),
            @Result(
                column = "QQ_NUM",
                property = "qqNum"),
            @Result(
                column = "BIRTHDAY",
                property = "birthday",
                javaType = java.util.Date.class),
            @Result(
                column = "CREATE_DATE",
                property = "createDate",
                javaType = java.util.Date.class),
            @Result(
                column = "DEPT_ID",
                property = "dept",
                one = @One(
                    select = "org.fkit.hrm.dao.DeptDao.selectById",
                    fetchType = FetchType.EAGER)),
            @Result(
                column = "JOB_ID",
                property = "job",
                one = @One(
                    select = "org.fkit.hrm.dao.JobDao.selectById",
                    fetchType = FetchType.EAGER))})
    Employee selectById(Integer id);

    // 动态修改员工
    @SelectProvider(
        type = EmployeeDynaSqlProvider.class,
        method = "updateEmployee")
    void update(Employee employee);
}

在EmployeeDao接口中使用了动态SQL提供类EmployeeDynaSqlProvider。

EmployeeDynaSqlProvider.java

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
package org.fkit.hrm.dao.provider;

import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.fkit.hrm.domain.Employee;
import static org.fkit.hrm.util.common.HrmConstants.EMPLOYEETABLE;

public class EmployeeDynaSqlProvider
{
    // 分页动态查询
    public String selectWhitParam(Map<String, Object> params)
    {
        String sql = new SQL()
        {
            {
                SELECT("*");
                FROM(EMPLOYEETABLE);
                if (params.get("employee") != null)
                {
                    Employee employee = (Employee) params.get("employee");
                    if (employee.getDept() != null
                            && employee.getDept().getId() != null
                            && employee.getDept().getId() != 0)
                    {
                        WHERE(" DEPT_ID = #{employee.dept.id} ");
                    }
                    if (employee.getJob() != null
                            && employee.getJob().getId() != null
                            && employee.getJob().getId() != 0)
                    {
                        WHERE(" JOB_ID = #{employee.job.id} ");
                    }
                    if (employee.getName() != null
                            && !employee.getName().equals(""))
                    {
                        WHERE("  NAME LIKE CONCAT ('%',#{employee.name},'%') ");
                    }
                    if (employee.getPhone() != null
                            && !employee.getPhone().equals(""))
                    {
                        WHERE(" phone LIKE CONCAT ('%',#{employee.phone},'%') ");
                    }
                    if (employee.getCardId() != null
                            && !employee.getCardId().equals(""))
                    {
                        WHERE(" card_id LIKE CONCAT ('%',#{employee.cardId},'%') ");
                    }
                    if (employee.getSex() != null && employee.getSex() != 0)
                    {
                        WHERE("sex = #{employee.sex}");
                    }
                }
            }
        }.toString();

        if (params.get("pageModel") != null)
        {
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }

        return sql;
    }
    // 动态查询总数量
    public String count(Map<String, Object> params)
    {
        return new SQL()
        {
            {
                SELECT("count(*)");
                FROM(EMPLOYEETABLE);
                if (params.get("employee") != null)
                {
                    Employee employee = (Employee) params.get("employee");
                    if (employee.getDept() != null
                            && employee.getDept().getId() != null
                            && employee.getDept().getId() != 0)
                    {
                        WHERE(" DEPT_ID = #{employee.dept.id} ");
                    }
                    if (employee.getJob() != null
                            && employee.getJob().getId() != null
                            && employee.getJob().getId() != 0)
                    {
                        WHERE(" JOB_ID = #{employee.job.id} ");
                    }
                    if (employee.getName() != null
                            && !employee.getName().equals(""))
                    {
                        WHERE("  NAME LIKE CONCAT ('%',#{employee.name},'%') ");
                    }
                    if (employee.getPhone() != null
                            && !employee.getPhone().equals(""))
                    {
                        WHERE(" phone LIKE CONCAT ('%',#{employee.phone},'%') ");
                    }
                    if (employee.getCardId() != null
                            && !employee.getCardId().equals(""))
                    {
                        WHERE(" card_id LIKE CONCAT ('%',#{employee.cardId},'%') ");
                    }
                    if (employee.getSex() != null && employee.getSex() != 0)
                    {
                        WHERE("sex = #{employee.sex}");
                    }
                }
            }
        }.toString();
    }

    // 动态插入
    public String insertEmployee(Employee employee)
    {
        return new SQL()
        {
            {
                INSERT_INTO(EMPLOYEETABLE);
                if (employee.getName() != null)
                {
                    VALUES("name", "#{name}");
                }
                if (employee.getCardId() != null)
                {
                    VALUES("card_id", "#{cardId}");
                }
                if (employee.getAddress() != null)
                {
                    VALUES("address", "#{address}");
                }
                if (employee.getPostCode() != null)
                {
                    VALUES("post_code", "#{postCode}");
                }
                if (employee.getTel() != null)
                {
                    VALUES("tel", "#{tel}");
                }
                if (employee.getPhone() != null)
                {
                    VALUES("phone", "#{phone}");
                }
                if (employee.getQqNum() != null)
                {
                    VALUES("qq_num", "#{qqNum}");
                }
                if (employee.getEmail() != null)
                {
                    VALUES("email", "#{email}");
                }
                if (employee.getSex() != null)
                {
                    VALUES("sex", "#{sex}");
                }
                if (employee.getParty() != null)
                {
                    VALUES("party", "#{party}");
                }
                if (employee.getBirthday() != null)
                {
                    VALUES("birthday", "#{birthday}");
                }
                if (employee.getRace() != null)
                {
                    VALUES("race", "#{race}");
                }
                if (employee.getEducation() != null)
                {
                    VALUES("education", "#{education}");
                }
                if (employee.getSpeciality() != null)
                {
                    VALUES("speciality", "#{speciality}");
                }
                if (employee.getHobby() != null)
                {
                    VALUES("hobby", "#{hobby}");
                }
                if (employee.getRemark() != null)
                {
                    VALUES("remark", "#{remark}");
                }
                if (employee.getCreateDate() != null)
                {
                    VALUES("create_Date", "#{createDate}");
                }
                if (employee.getDept() != null)
                {
                    VALUES("dept_id", "#{dept.id}");
                }
                if (employee.getJob() != null)
                {
                    VALUES("job_id", "#{job.id}");
                }
            }
        }.toString();
    }
    // 动态更新
    public String updateEmployee(Employee employee)
    {
        return new SQL()
        {
            {
                UPDATE(EMPLOYEETABLE);
                if (employee.getName() != null)
                {
                    SET(" name = #{name} ");
                }
                if (employee.getCardId() != null)
                {
                    SET(" card_id = #{cardId} ");
                }
                if (employee.getAddress() != null)
                {
                    SET(" address = #{address} ");
                }
                if (employee.getPostCode() != null)
                {
                    SET(" post_code = #{postCode} ");
                }
                if (employee.getTel() != null)
                {
                    SET(" tel = #{tel} ");
                }
                if (employee.getPhone() != null)
                {
                    SET(" phone = #{phone} ");
                }
                if (employee.getQqNum() != null)
                {
                    SET(" qq_num = #{qqNum} ");
                }
                if (employee.getEmail() != null)
                {
                    SET(" email = #{email} ");
                }
                if (employee.getSex() != null)
                {
                    SET(" sex = #{sex} ");
                }
                if (employee.getParty() != null)
                {
                    SET(" party = #{party} ");
                }
                if (employee.getBirthday() != null)
                {
                    SET(" birthday = #{birthday} ");
                }
                if (employee.getRace() != null)
                {
                    SET(" race = #{race} ");
                }
                if (employee.getEducation() != null)
                {
                    SET(" education = #{education} ");
                }
                if (employee.getSpeciality() != null)
                {
                    SET(" speciality = #{speciality} ");
                }
                if (employee.getHobby() != null)
                {
                    SET(" hobby = #{hobby} ");
                }
                if (employee.getRemark() != null)
                {
                    SET(" remark = #{remark} ");
                }
                if (employee.getCreateDate() != null)
                {
                    SET(" create_Date = #{createDate} ");
                }
                if (employee.getDept() != null)
                {
                    SET(" dept_id = #{dept.id} ");
                }
                if (employee.getJob() != null)
                {
                    SET(" job_id = #{job.id} ");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}

NoticeDao接口

下面是NoticeDao接口的源代码。

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
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import org.fkit.hrm.dao.provider.NoticeDynaSqlProvider;
import org.fkit.hrm.domain.Notice;
import static org.fkit.hrm.util.common.HrmConstants.NOTICETABLE;

public interface NoticeDao
{
    // 动态查询
    @SelectProvider(
        type = NoticeDynaSqlProvider.class,
        method = "selectWhitParam"
    )
    @Results({
        @Result(
            id = true,
            column = "id",
            property = "id"),
        @Result(
            column = "CREATE_DATE",
            property = "createDate",
            javaType = java.util.Date.class),
        @Result(
            column = "USER_ID",
            property = "user",
            one = @One(
                select = "org.fkit.hrm.dao.UserDao.selectById",
                fetchType = FetchType.EAGER)
        )
     })
    List<Notice> selectByPage(Map<String, Object> params);

    @SelectProvider(
        type = NoticeDynaSqlProvider.class,
        method = "count")
    Integer count(Map<String, Object> params);

    @Select("select * from " + NOTICETABLE + " where ID = #{id}")
    Notice selectById(int id);

    // 根据id删除公告
    @Delete(" delete from " + NOTICETABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 动态插入公告
    @SelectProvider(
        type = NoticeDynaSqlProvider.class,
        method = "insertNotice")
    void save(Notice notice);

    // 动态修改公告
    @SelectProvider(
        type = NoticeDynaSqlProvider.class,
        method = "updateNotice")
    void update(Notice notice);
}

在Noticedao接口中使用了动态SQL提供类NoticeDynaSqlProvider：

NoticeDynaSqlProvider.java

下面是DocumentDao接口的源代码。

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
package org.fkit.hrm.dao.provider;

import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.fkit.hrm.domain.Notice;
import static org.fkit.hrm.util.common.HrmConstants.NOTICETABLE;

public class NoticeDynaSqlProvider
{
    // 分页动态查询
    public String selectWhitParam(Map<String, Object> params)
    {
        String sql = new SQL()
        {
            {
                SELECT("*");
                FROM(NOTICETABLE);
                if (params.get("notice") != null)
                {
                    Notice notice = (Notice) params.get("notice");
                    if (notice.getTitle() != null
                            && !notice.getTitle().equals(""))
                    {
                        WHERE("  title LIKE CONCAT ('%',#{notice.title},'%') ");
                    }
                    if (notice.getContent() != null
                            && !notice.getContent().equals(""))
                    {
                        WHERE("  content LIKE CONCAT ('%',#{notice.content},'%') ");
                    }
                }
            }
        }.toString();

        if (params.get("pageModel") != null)
        {
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }

        return sql;
    }
    // 动态查询总数量
    public String count(Map<String, Object> params)
    {
        return new SQL()
        {
            {
                SELECT("count(*)");
                FROM(NOTICETABLE);
                if (params.get("notice") != null)
                {
                    Notice notice = (Notice) params.get("notice");
                    if (notice.getTitle() != null
                            && !notice.getTitle().equals(""))
                    {
                        WHERE("  title LIKE CONCAT ('%',#{notice.title},'%') ");
                    }
                    if (notice.getContent() != null
                            && !notice.getContent().equals(""))
                    {
                        WHERE("  content LIKE CONCAT ('%',#{notice.content},'%') ");
                    }
                }
            }
        }.toString();
    }
    // 动态插入
    public String insertNotice(Notice notice)
    {

        return new SQL()
        {
            {
                INSERT_INTO(NOTICETABLE);
                if (notice.getTitle() != null && !notice.getTitle().equals(""))
                {
                    VALUES("title", "#{title}");
                }
                if (notice.getContent() != null
                        && !notice.getContent().equals(""))
                {
                    VALUES("content", "#{content}");
                }
                if (notice.getUser() != null
                        && notice.getUser().getId() != null)
                {
                    VALUES("user_id", "#{user.id}");
                }
            }
        }.toString();
    }
    // 动态更新
    public String updateNotice(Notice notice)
    {

        return new SQL()
        {
            {
                UPDATE(NOTICETABLE);
                if (notice.getTitle() != null && !notice.getTitle().equals(""))
                {
                    SET(" title = #{title} ");
                }
                if (notice.getContent() != null
                        && !notice.getContent().equals(""))
                {
                    SET(" content = #{content} ");
                }
                if (notice.getUser() != null
                        && notice.getUser().getId() != null)
                {
                    SET(" user_id = #{user.id} ");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}

DocumentDao接口

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
package org.fkit.hrm.dao;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import org.fkit.hrm.dao.provider.DocumentDynaSqlProvider;
import org.fkit.hrm.domain.Document;
import static org.fkit.hrm.util.common.HrmConstants.DOCUMENTTABLE;

public interface DocumentDao{

    // 动态查询
    @SelectProvider(
        type = DocumentDynaSqlProvider.class,
        method = "selectWhitParam"
    )
    @Results(
        {@Result(
            id = true,
            column = "id",
            property = "id"
        ), @Result(
            column = "CREATE_DATE",
            property = "createDate",
            javaType = java.util.Date.class
        ), @Result(
            column = "USER_ID",
            property = "user",
            one = @One(
                select = "org.fkit.hrm.dao.UserDao.selectById",
                fetchType = FetchType.EAGER
            )
        )}
    )
    List<Document> selectByPage(Map<String, Object> params);

    @SelectProvider(
        type = DocumentDynaSqlProvider.class,
        method = "count"
    )
    Integer count(Map<String, Object> params);

    // 动态插入文档
    @SelectProvider(
        type = DocumentDynaSqlProvider.class,
        method = "insertDocument"
    )
    void save(Document document);

    @Select("select * from " + DOCUMENTTABLE + " where ID = #{id}")
    Document selectById(int id);

    // 根据id删除文档
    @Delete(" delete from " + DOCUMENTTABLE + " where id = #{id} ")
    void deleteById(Integer id);

    // 动态修改文档
    @SelectProvider(
        type = DocumentDynaSqlProvider.class,
        method = "updateDocument"
    )
    void update(Document document);
}

在DocumentDao接口中使用了动态SQL提供类DocumentDynaSqlProvider。

DocumentDynaSqlProvider.java

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
99
100
101
102
103
104
105
106
package org.fkit.hrm.dao.provider;

import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.fkit.hrm.domain.Document;
import static org.fkit.hrm.util.common.HrmConstants.DOCUMENTTABLE;

public class DocumentDynaSqlProvider{
    // 分页动态查询
    public String selectWhitParam(Map<String, Object> params)
    {
        String sql = new SQL(){
            {
                SELECT("*");
                FROM(DOCUMENTTABLE);
                if(params.get("document") != null)
                {
                    Document document = (Document) params.get("document");
                    if(document.getTitle() != null && !document.getTitle().equals(""))
                    {
                        WHERE("  title LIKE CONCAT ('%',#{document.title},'%') ");
                    }
                }
            }
        }.toString();

        if(params.get("pageModel") != null)
        {
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }

        return sql;
    }
    // 动态查询总数量
    public String count(Map<String, Object> params)
    {
        return new SQL(){
            {
                SELECT("count(*)");
                FROM(DOCUMENTTABLE);
                if(params.get("document") != null)
                {
                    Document document = (Document) params.get("document");
                    if(document.getTitle() != null && !document.getTitle().equals(""))
                    {
                        WHERE("  title LIKE CONCAT ('%',#{document.title},'%') ");
                    }
                }
            }
        }.toString();
    }
    // 动态插入
    public String insertDocument(Document document)
    {

        return new SQL(){
            {
                INSERT_INTO(DOCUMENTTABLE);
                if(document.getTitle() != null && !document.getTitle().equals(""))
                {
                    VALUES("title", "#{title}");
                }
                if(document.getFileName() != null && !document.getFileName().equals(""))
                {
                    VALUES("filename", "#{fileName}");
                }
                if(document.getRemark() != null && !document.getRemark().equals(""))
                {
                    VALUES("remark", "#{remark}");
                }
                if(document.getUser() != null && document.getUser().getId() != null)
                {
                    VALUES("user_id", "#{user.id}");
                }
            }
        }.toString();
    }

    // 动态更新
    public String updateDocument(Document document)
    {

        return new SQL(){
            {
                UPDATE(DOCUMENTTABLE);
                if(document.getTitle() != null && !document.getTitle().equals(""))
                {
                    SET(" title = #{title} ");
                }
                if(document.getFileName() != null && !document.getFileName().equals(""))
                {
                    SET(" filename = #{fileName} ");
                }
                if(document.getRemark() != null && !document.getRemark().equals(""))
                {
                    SET("remark = #{remark}");
                }
                if(document.getUser() != null && document.getUser().getId() != null)
                {
                    SET("user_id = #{user.id}");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}