MyBatis學習筆記(二)之關聯關係

今天主要學習的關聯關係是一對一關係與一對多關係。

一、一對一關係

還是通過例子來解釋說明。 （一個妻子對應一個丈夫）。

1)數據庫信息

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

 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

 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

 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文件

<?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裡有個方法

<?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）寫個實現

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）數據庫信息這裡沒有添加數據了，我們用批量添加數據

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

 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) 建立接口

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

 <?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 ） 實現

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.就寫出主要的代碼。

首先定義分頁對象。

 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();}} }

看一下項目的整體：

每件事都需要堅持！