今天主要学习的关联关系是一对一关系与一对多关系。
一、一对一关系
还是通过例子来解释说明。（一个妻子对应一个丈夫）。
1)数据库信息
|
1
2
3
4
5
6
7
8
9
10
11
|
create table t_wife(id int primary key auto_increment,wife_name varchar(),fk_husband_id int);create table t_husband(id int primary key auto_increment,husband_name varchar());insert into t_husband values (null,'hello');insert into t_wife values(null,'kitty',) |
2）对应的JavaBean代码
虽然在数据库里只有一方配置的外键，但是这个一对一是双向的关系。
HusbandBean.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
|
package com.cy.mybatis.beans;import java.io.Serializable;/*** one to one* @author acer**/public class HusbandBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String name;private WifeBean wife;public HusbandBean() {super();}public HusbandBean(Integer id, String name, WifeBean wife) {super();this.id = id;this.name = name;this.wife = wife;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public WifeBean getWife() {return wife;}public void setWife(WifeBean wife) {this.wife = wife;}@Overridepublic String toString() {return "Husband [id=" + id + ", name=" + name + ", wife=" + wife + "]";}} |
WifeBean.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
|
package com.cy.mybatis.beans;import java.io.Serializable;/*** one to one* @author acer**/public class WifeBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String name;private HusbandBean husband;public WifeBean() {super();}public WifeBean(Integer id, String name, HusbandBean husband) {super();this.id = id;this.name = name;this.husband = husband;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public HusbandBean getHusband() {return husband;}public void setHusband(HusbandBean husband) {this.husband = husband;}@Overridepublic String toString() {return "Wife [id=" + id + ", name=" + name + ", husband=" + husband+ "]";}} |
3）接下来建立两个接口，HusbandMapper,WifeMapper.
HusbandMapper
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
package com.cy.mybatis.mapper;import com.cy.mybatis.beans.HusbandBean;public interface HusbandMapper {/*** 根据id查询丈夫信息* @param id* @return* @throws Exception*/public HusbandBean selectHusbandById (int id) throws Exception;/*** 根据id查询丈夫与妻子信息* @param id* @return* @throws Exception*/public HusbandBean selectHusbandAndWife(int id) throws Exception;} |
4）定义HusbandMapper.xml文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.HusbandMapper"><resultMap type="HusbandBean" id="husbandAndWife"><id property="id" column="id" javaType="java.lang.Integer"/><result property="name" column="name" javaType="java.lang.String"/><!-- association – 一个复杂的类型关联；许多结果将包成这种类型嵌入结果映射 – 结果映射自身的关联，或者参考一个column="id" 这里的id指的是在t_wife表来的主键id 这个查询妻子，所以在妻子mapper里有个方法 --><association property="wife" column="id" javaType="WifeBean" select="com.cy.mybatis.mapper.WifeMapper.selectWifeByHusbandId" ></association></resultMap><!-- resultType 返回类型 从这条语句中返回的期望类型的类的完全限定名或别名。--><select id="selectHusbandById" resultType="HusbandBean">select * from t_husband where id=#{id}</select> <!-- resultMap 命名引用外部的 resultMap。返回的是一个集合。--><select id="selectHusbandAndWife" resultMap="husbandAndWife">select * from t_husband where id=#{id}</select></mapper> |
在WifeMapper.xml里有个方法
|
1
2
3
4
5
6
7
|
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.WifeMapper"><select id="selectWifeByHusbandId" resultType="WifeBean">select * from t_wife where fk_husband_id = #{id}</select> </mapper> |
5）写个实现
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
package com.cy.mybatis.service;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.HusbandBean;import com.cy.mybatis.mapper.HusbandMapper;import com.cy.mybatis.tools.DBTools;public class OneToOneService {public static void main(String[] args) {selectHusbandAndWife();}private static void selectHusbandAndWife() {SqlSession session = DBTools.getSession();HusbandMapper hm = session.getMapper(HusbandMapper.class);try {HusbandBean husband = hm.selectHusbandAndWife();System.out.println(husband);session.commit();} catch (Exception e) {e.printStackTrace();}}} |
注意：那个工具类还是前一章那样写的，就相当与在昨天的基础上建立的。
注意：
mybatis实际是对XML进行操作，我们所有的方法都直接定义在XML中，写个接口只是为了更好的符合我们3层的思想，如果不写接口，直接通过session也可以直接操作xml中的方法 ，
XML中只要有方法，就可以使用，而调用的方式就是：namespace+方法名；
例外使用resultType时，一定要保证，你属性名与字段名相同；
如果不相同，就使用resultMap 。
二、一对多关系
还是通过例子来解释说明。（一把锁对应多把钥匙）。
2.1）数据库信息 这里没有添加数据了，我们用批量添加数据
|
1
2
3
4
5
6
7
8
9
|
create table t_key(id int primary key auto_increment,key_name varchar(),fk_lock_id int);create table t_lock(id int primary key auto_increment,lock_name varchar()); |
2.2) 实体类
KeyBean.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
|
package com.cy.mybatis.beans;import java.io.Serializable;/*** manyTOone* **/public class KeyBean implements Serializable {private static final long serialVersionUID = L;private Integer id;private String key;private LockBean lock;public KeyBean() {super();}public KeyBean(Integer id, String key, LockBean lock) {super();this.id = id;this.key = key;this.lock = lock;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getKey() {return key;}public void setKey(String key) {this.key = key;}public LockBean getLock() {return lock;}public void setLock(LockBean lock) {this.lock = lock;}@Overridepublic String toString() {return "KeyBean [id=" + id + ", key=" + key + ", lock=" + lock + "]";}} LockBean.javapackage com.cy.mybatis.beans;import java.io.Serializable;import java.util.List;/*** oneTOmany* **/public class LockBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String lock;private List<KeyBean> keys;public LockBean() {super();}public LockBean(Integer id, String lock, List<KeyBean> keys) {super();this.id = id;this.lock = lock;this.keys = keys;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getLock() {return lock;}public void setLock(String lock) {this.lock = lock;}public List<KeyBean> getKeys() {return keys;}public void setKeys(List<KeyBean> keys) {this.keys = keys;}@Overridepublic String toString() {return "LockBean [id=" + id + ", keys=" + keys + ", lock=" + lock + "]";}} |
2.3) 建立接口
|
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
|
KeyMapper.javapackage com.cy.mybatis.mapper;import java.util.List;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.KeyBean;public interface KeyMapper {/*** 批量添加钥匙* @return* 提倡 这样使用 @Param("keys")*/public int batchSaveKeys(@Param("keys")List<KeyBean> keys);} LockMapper.javapackage com.cy.mybatis.mapper;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.LockBean;public interface LockMapper {/*** 添加锁* @param lock* @return*/public int saveLock(@Param("lock")LockBean lock);/*** 根据ID查询锁的资料* @param id* @return*/public LockBean findLockById(int id);/*** 根据ID查询锁与钥匙的资料* onemany* @param id* @return*/public LockBean findLockAndKeys(int id);} |
2.4) 建立xml文件
KeyMapper.xml
|
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
|
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.KeyMapper"><resultMap id="keyMap" type="KeyBean"><id property="id" column="id" javaType="java.lang.Integer"/><result property="key" column="key_name" javaType="java.lang.String"/></resultMap><!--collection 为用于遍历的元素（必选），支持数组、List、Set --><!-- item 表示集合中每一个元素进行迭代时的别名. --><!--separator表示在每次进行迭代之间以什么符号作为分隔 符. --><insert id="batchSaveKeys">insert into t_key values <foreach collection="keys" item="key" separator=",">(null,#{key.key},#{key.lock.id})</foreach></insert><select id="findKeysByLockId" resultMap="keyMap">select * from t_key where fk_lock_id = #{id}</select></mapper> LockMapper.xml<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.LockMapper"><!--自定义返回类型 --><resultMap id="lockMap" type="LockBean"><id property="id" column="id" javaType="java.lang.Integer"/><result property="lock" column="lock_name" javaType="java.lang.String"/></resultMap><!--自定义返回类型 --><resultMap id="lockAndKeysMap" type="LockBean"><id property="id" column="id" javaType="java.lang.Integer"/><result property="lock" column="lock_name" javaType="java.lang.String"/><collection property="keys" column="id" select="com.cy.mybatis.mapper.KeyMapper.findKeysByLockId"></collection></resultMap><insert id="saveLock">insert into t_lock values (null,#{lock.lock}) </insert><select id="findLockById" resultMap="lockMap">select * from t_lock where id= #{id}</select><select id="findLockAndKeys" resultMap="lockAndKeysMap">select * from t_lock where id= #{id}</select></mapper> |
2.5 ） 实现
|
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
|
package com.cy.mybatis.service;import java.util.ArrayList;import java.util.List;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.KeyBean;import com.cy.mybatis.beans.LockBean;import com.cy.mybatis.mapper.KeyMapper;import com.cy.mybatis.mapper.LockMapper;import com.cy.mybatis.tools.DBTools;public class OneToManyService {public static void main(String[] args) {// saveLock();// batchSaveKeys();findLockAndKeys();}private static void findLockAndKeys() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);LockBean lock = lm.findLockAndKeys();System.out.println(lock);}private static void batchSaveKeys() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);KeyMapper km = session.getMapper(KeyMapper.class);LockBean lock = lm.findLockById();List<KeyBean> keys = new ArrayList<KeyBean>();for(int i = ; i < ; i++){KeyBean key = new KeyBean(null, "钥匙"+i, lock);keys.add(key);}km.batchSaveKeys(keys);session.commit();}private static void saveLock() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);LockBean lock = new LockBean(null, "锁", null);lm.saveLock(lock);session.commit();}} |
结果显示：
三 、批量操作与分页
这里就使用前一章的User.就写出主要的代码。
首先定义分页对象。
|
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
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
|
package com.cy.mybatis.beans;import java.util.List;/*** 定义一个分页对象* * @author* */public class Pager {private int pageNo;// 当前页码private int pageTotal;// 总页码private int rowsTotal;// 总条数private int pageSize;// 每页显示条数private List<Object> list;// 返回的数据集合public int getPageNo() {return pageNo;}public void setPageNo(int pageNo) {this.pageNo = pageNo;}public int getPageTotal() {return pageTotal;}public void setPageTotal(int pageTotal) {this.pageTotal = pageTotal;}public int getRowsTotal() {return rowsTotal;}public void setRowsTotal(int rowsTotal) {this.rowsTotal = rowsTotal;pageTotal = rowsTotal % pageSize == ? rowsTotal / pageSize : rowsTotal / pageSize + ;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public List<?> getList() {return list;}public void setList(List<Object> list) {this.list = list;}@Overridepublic String toString() {return "Pager [pageNo=" + pageNo + ", pageTotal=" + pageTotal+ ", rowsTotal=" + rowsTotal + ", pageSize=" + pageSize+ ", list=" + list + "]";}} UserMapper.java接口。package com.cy.mybatis.mapper;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.UserBean;public interface UserMapper {/*** 新增用戶* @param user* @return* @throws Exception*/public int insertUser(@Param("user")UserBean user) throws Exception;/*** 修改用戶* @param user* @param id* @return* @throws Exception*/public int updateUser (@Param("u")UserBean user,@Param("id")int id) throws Exception;/*** 刪除用戶* @param id* @return* @throws Exception*/public int deleteUser(int id) throws Exception;/*** 根据id查询用户信息* @param id* @return* @throws Exception*/public UserBean selectUserById(int id) throws Exception;/*** 查询所有的用户信息* @return* @throws Exception*/public List<UserBean> selectAllUser() throws Exception;/*** 批量增加* @param user* @return* @throws Exception*/public int batchInsertUser(@Param("users")List<UserBean> user) throws Exception;/*** 批量删除* @param list* @return* @throws Exception*/public int batchDeleteUser(@Param("list")List<Integer> list) throws Exception;/*** 分页查询数据* @param parma* @return* @throws Exception*/public List<UserBean> pagerUser(Map<String, Object> parmas) throws Exception;/*** * 分页统计数据* @param parma* @return* @throws Exception*/public int countUser(Map<String, Object> parmas) throws Exception;} xml文件<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.UserMapper"><!-- 自定义返回结果集 --><resultMap id="userMap" type="UserBean"><id property="id" column="id" javaType="java.lang.Integer"></id><result property="username" column="username" javaType="java.lang.String"></result><result property="password" column="password" javaType="java.lang.String"></result><result property="account" column="account" javaType="java.lang.Double"></result></resultMap><!-- 在各种标签中的id属性必须和接口中的方法名相同 ， id属性值必须是唯一的，不能够重复使用。parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型--> <!-- useGeneratedKeys：（ 仅 对 insert 有 用 ） 这 会 告 诉 MyBatis 使 用 JDBC 的getGeneratedKeys 方法来取出由数据（比如：像 MySQL 和 SQLServer 这样的数据库管理系统的自动递增字段）内部生成的主键。默认值： false。 --> <!--keyProperty： （仅对 insert有用）标记一个属性， MyBatis 会通过 getGeneratedKeys或者通过 insert 语句的 selectKey 子元素设置它的值。默认：不设置。 --><!--#{}中的内容，为占位符，当参数为某个JavaBean时，表示放置该Bean对象的属性值 --><insert id="insertUser" useGeneratedKeys="true" keyProperty="user.id">insert into t_user (username,password,account) values (#{user.username},#{user.password},#{user.account})</insert><update id="updateUser">update t_user set username=#{u.username},password=#{u.password},account=#{u.account} where id=#{id}</update><delete id="deleteUser" parameterType="int">delete from t_user where id=#{id} </delete><select id="selectUserById" parameterType="int" resultMap="userMap">select * from t_user where id=#{id}</select><select id="selectAllUser" resultMap="userMap">select * from t_user</select><!-- 批量操作和foreach标签 --><insert id="batchInsertUser" parameterType="java.util.List">insert into t_user values <foreach collection="users" item="users" separator=",">(null,#{users.username},#{users.password},#{users.account})</foreach></insert><delete id="batchDeleteUser">delete from t_user where id in (<foreach collection="list" item="list" separator=",">#{id}</foreach>)</delete><!--collection 为用于遍历的元素（必选），支持数组、List、Set --><!-- item 表示集合中每一个元素进行迭代时的别名. --><!--separator表示在每次进行迭代之间以什么符号作为分隔 符. --><select id="pagerUser" parameterType="java.util.Map" resultMap="userMap">select * from t_user where =<if test="username!=null">and username like '%${username}%'</if>limit ${index},${pageSize} </select><select id="countUser" parameterType="java.util.Map" resultType="int">select count(*) from t_user where = <if test="username != null">and username like '%${username}%'</if></select></mapper> #在生成SQL时，对于字符类型参数，会拼装引号$在生成SQL时，不会拼装引号，可用于order by之类的参数拼装测试类package com.cy.mybatis.service;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.UserBean;import com.cy.mybatis.tools.DBTools;import com.cy.mybatis.mapper.UserMapper;public class UserService {/*** @param args*/public static void main(String[] args) {// insertUser();// deleteUser();// updateUser();// selectUserById();// selectAllUser();// batchInsertUser();// batchDeleteUser();// countUser();pagerUser();}private static void countUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);Map<String,Object> params = new HashMap<String,Object>();params.put("username", "kitty");int index = ;params.put("index", index);//从第几页开始。mysql是从开始的params.put("pageSize", );//每页显示的数据条数int count;try {count = mapper.countUser(params);System.out.println(count);} catch (Exception e) {e.printStackTrace();}}private static void pagerUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);Map<String,Object> params = new HashMap<String,Object>();params.put("username", "kitty");params.put("index", );//从第几页开始。mysql是从开始的params.put("pageSize", );//每页显示的数据条数try {List<UserBean> u = mapper.pagerUser(params);for (UserBean userBean : u) {System.out.println("--------"+userBean);}} catch (Exception e) {e.printStackTrace();}}private static void batchDeleteUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);List<Integer> ids = new ArrayList<Integer>();for(int i = ; i < ; i ++){ids.add(i);}try {mapper.batchDeleteUser(ids);session.commit();} catch (Exception e) {e.printStackTrace();}}private static void batchInsertUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);List<UserBean> users = new ArrayList<UserBean>();for(int i = ; i < ; i ++){UserBean user = new UserBean("kitty"+i, "", .);users.add(user);}try {mapper.batchInsertUser(users);session.commit();} catch (Exception e) {e.printStackTrace();}}/*** 新增用户*/private static void insertUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);UserBean user = new UserBean("懿", "", .);try {mapper.insertUser(user);System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** 删除用户*/private static void deleteUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {mapper.deleteUser();session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** 修改用户数据*/private static void updateUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);UserBean user =new UserBean("小明", "",.);try {mapper.updateUser(user, );session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** 根据id查询用户*/private static void selectUserById(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {UserBean user= mapper.selectUserById();System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** 查询所有的用户*/private static void selectAllUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {List<UserBean> user=mapper.selectAllUser();System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}} } |
看一下项目的整体：
每件事都需要坚持！
