今天主要學習的關聯關系是一對一關系與一對多關系。
一、一對一關系
還是通過例子來解釋說明。（一個妻子對應一個丈夫）。
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();}} } |
看一下項目的整體：
每件事都需要堅持！
