首页 > 代码库 > MyBatis学习笔记(二) 关联关系

MyBatis学习笔记(二) 关联关系

今天主要学习的关联关系是一对一关系与一对多关系。

 

一、一对一关系

还是通过例子来解释说明。(一个妻子对应一个丈夫)。

   1)数据库信息

 

 1 create table t_wife( 2  id int primary key auto_increment, 3  wife_name varchar(20), 4  fk_husband_id int 5 ); 6  7 create table t_husband( 8 id int primary key auto_increment, 9 husband_name varchar(20)10 );11 12 insert into t_husband values (null,‘hello‘);13 insert into t_wife values(null,‘kitty‘,1)

 

 2)对应的JavaBean代码

虽然在数据库里只有一方配置的外键,但是这个一对一是双向的关系。

HusbandBean.java

 1 package com.cy.mybatis.beans; 2  3 import java.io.Serializable; 4 /** 5  * one to one 6  * @author acer 7  * 8  */ 9 public class HusbandBean implements Serializable{10 11     12     private static final long serialVersionUID = 1L;13     14     private Integer id;15     private String name;16     private WifeBean wife;17     public HusbandBean() {18         super();19     }20     public HusbandBean(Integer id, String name, WifeBean wife) {21         super();22         this.id = id;23         this.name = name;24         this.wife = wife;25     }26     public Integer getId() {27         return id;28     }29     public void setId(Integer id) {30         this.id = id;31     }32     public String getName() {33         return name;34     }35     public void setName(String name) {36         this.name = name;37     }38     public WifeBean getWife() {39         return wife;40     }41     public void setWife(WifeBean wife) {42         this.wife = wife;43     }44     @Override45     public String toString() {46         return "Husband [id=" + id + ", name=" + name + ", wife=" + wife + "]";47     }48     49     50 51 }

WifeBean.java

技术分享 View Code

 

 

 3)接下来建立两个接口,HusbandMapper,WifeMapper.

HusbandMapper

技术分享 View Code

 

 4)定义HusbandMapper.xml文件

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.HusbandMapper"> 4  5 <resultMap type="HusbandBean" id="husbandAndWife"> 6   <id property="id" column="id" javaType="java.lang.Integer"/> 7   <result property="name" column="name" javaType="java.lang.String"/> 8    9   <!--  association – 一个复杂的类型关联;许多结果将包成这种类型10                                                         嵌入结果映射 – 结果映射自身的关联,或者参考一个11                        column="id" 这里的id指的是在t_wife表来的主键id  12                       这个查询妻子,所以在妻子mapper里有个方法 -->13   <association property="wife" column="id" javaType="WifeBean" select="com.cy.mybatis.mapper.WifeMapper.selectWifeByHusbandId" ></association>14 </resultMap>15 16  <!-- resultType 返回类型 从这条语句中返回的期望类型的类的完全限定名或别名17                                             。-->18  <select id="selectHusbandById" resultType="HusbandBean">19    select * from t_husband where id=#{id}20  </select> 21 22   <!-- resultMap  命名引用外部的 resultMap。返回的是一个集合。-->23  <select id="selectHusbandAndWife" resultMap="husbandAndWife">24   select * from t_husband where id=#{id}25  </select>26 27 28 29 30 </mapper>

 

    在WifeMapper.xml里有个方法

1 <?xml version="1.0" encoding="UTF-8"?>2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">3 4 <mapper namespace="com.cy.mybatis.mapper.WifeMapper">5    <select id="selectWifeByHusbandId" resultType="WifeBean">6         select * from t_wife where fk_husband_id = #{id}7 8     </select>    9 </mapper>

 

 

 

5)写个实现

 1 package com.cy.mybatis.service; 2  3 import org.apache.ibatis.session.SqlSession; 4  5 import com.cy.mybatis.beans.HusbandBean; 6 import com.cy.mybatis.mapper.HusbandMapper; 7 import com.cy.mybatis.tools.DBTools; 8  9 10 11 12 public class OneToOneService {13     14     public static void main(String[] args) {15         selectHusbandAndWife();16         17     }18 19     20     private static void selectHusbandAndWife() {21         SqlSession session = DBTools.getSession();22         HusbandMapper hm = session.getMapper(HusbandMapper.class);23         try {24             HusbandBean    husband = hm.selectHusbandAndWife(1);25             System.out.println(husband);26             session.commit();27         } catch (Exception e) {28             e.printStackTrace();29         }30     }31 32 }

 

 注意:那个工具类还是前一章那样写的,就相当与在昨天的基础上建立的。

技术分享

注意:

 mybatis实际是对XML进行操作,我们所有的方法都直接定义在XML中,写个接口只是为了更好的符合我们3层的思想,如果不写接口,直接通过session也可以直接操作xml中的方法 ,
 XML中只要有方法,就可以使用,而调用的方式就是:namespace+方法名;
 例外使用resultType时,一定要保证,你属性名与字段名相同;
 如果不相同,就使用resultMap 。

二、一对多关系

还是通过例子来解释说明。(一把锁对应多把钥匙)。

 

2.1)数据库信息 这里没有添加数据了,我们用批量添加数据

 1 create table t_key( 2 id int primary key auto_increment, 3 key_name varchar(20), 4 fk_lock_id int  5 ); 6  7  8 create table t_lock( 9 id int primary key auto_increment,10 lock_name varchar(20)11 );

 

 

 

2.2) 实体类

KeyBean.java

技术分享 View Code

LockBean.java

技术分享 View Code

 

 

 

2.3) 建立接口

KeyMapper.java

 1 package com.cy.mybatis.mapper; 2  3 import java.util.List; 4  5 import org.apache.ibatis.annotations.Param; 6  7 import com.cy.mybatis.beans.KeyBean; 8  9 public interface KeyMapper {10     /**11      * 批量添加钥匙12      * @return13      * 提倡 这样使用 @Param("keys")14      */15     public int batchSaveKeys(@Param("keys")List<KeyBean> keys);16 }

 

LockMapper.java

 1 package com.cy.mybatis.mapper; 2  3 import org.apache.ibatis.annotations.Param; 4  5 import com.cy.mybatis.beans.LockBean; 6  7 public interface LockMapper { 8     /** 9      * 添加锁10      * @param lock11      * @return12      */13     public int saveLock(@Param("lock")LockBean lock);14     15     /**16      * 根据ID查询锁的资料17      * @param id18      * @return19      */20     public LockBean findLockById(int id);21     22     /**23      * 根据ID查询锁与钥匙的资料24      * one2many25      * @param id26      * @return27      */28     public LockBean findLockAndKeys(int id);29     30 }

 

 

2.4) 建立xml文件

KeyMapper.xml

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.KeyMapper"> 4      5     <resultMap id="keyMap" type="KeyBean"> 6         <id property="id" column="id" javaType="java.lang.Integer"/> 7         <result property="key" column="key_name" javaType="java.lang.String"/> 8     </resultMap> 9     10     11     <!--collection 为用于遍历的元素(必选),支持数组、List、Set  -->12     <!-- item 表示集合中每一个元素进行迭代时的别名. -->13     <!--separator表示在每次进行迭代之间以什么符号作为分隔 符.  -->14     <insert id="batchSaveKeys">15         insert into t_key values 16         <foreach collection="keys" item="key" separator=",">17             (null,#{key.key},#{key.lock.id})18         </foreach>19     </insert>20     21     <select id="findKeysByLockId" resultMap="keyMap">22         select * from t_key where fk_lock_id = #{id}23     </select>24 25 </mapper>

 

LockMapper.xml

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.LockMapper"> 4     <!--自定义返回类型  --> 5     <resultMap id="lockMap" type="LockBean"> 6         <id property="id" column="id" javaType="java.lang.Integer"/> 7         <result property="lock" column="lock_name" javaType="java.lang.String"/> 8     </resultMap> 9     10     <!--自定义返回类型  -->11     <resultMap id="lockAndKeysMap" type="LockBean">12         <id property="id" column="id" javaType="java.lang.Integer"/>13         <result property="lock" column="lock_name" javaType="java.lang.String"/>14         15         <collection property="keys" column="id" select="com.cy.mybatis.mapper.KeyMapper.findKeysByLockId"></collection>16     </resultMap>17     18     <insert id="saveLock">19         insert into t_lock values (null,#{lock.lock})    20     </insert>21     22     <select id="findLockById" resultMap="lockMap">23         select * from t_lock where id= #{id}24     </select>25     26     <select id="findLockAndKeys" resultMap="lockAndKeysMap">27         select * from t_lock where id= #{id}28     </select>29 30 </mapper>

2.5 ) 实现

 1 package com.cy.mybatis.service; 2  3 import java.util.ArrayList; 4 import java.util.List; 5  6 import org.apache.ibatis.session.SqlSession; 7  8 import com.cy.mybatis.beans.KeyBean; 9 import com.cy.mybatis.beans.LockBean;10 import com.cy.mybatis.mapper.KeyMapper;11 import com.cy.mybatis.mapper.LockMapper;12 import com.cy.mybatis.tools.DBTools;13 14 public class OneToManyService {15     public static void main(String[] args) {16 //        saveLock();17 //        batchSaveKeys();18         findLockAndKeys();19     }20 21     private static void findLockAndKeys() {22     23         SqlSession session = DBTools.getSession();24         LockMapper lm = session.getMapper(LockMapper.class);25         LockBean lock = lm.findLockAndKeys(1);26         System.out.println(lock);27     }28 29     private static void batchSaveKeys() {30         31         SqlSession session = DBTools.getSession();32         LockMapper lm = session.getMapper(LockMapper.class);33         KeyMapper km = session.getMapper(KeyMapper.class);34         35         LockBean lock = lm.findLockById(1);36         List<KeyBean> keys = new ArrayList<KeyBean>();37         for(int i = 0; i < 5; i++){38             KeyBean key = new KeyBean(null, "钥匙"+i, lock);39             keys.add(key);40         }41         km.batchSaveKeys(keys);42         session.commit();43     }44 45     private static void saveLock() {46         SqlSession session = DBTools.getSession();47         LockMapper lm = session.getMapper(LockMapper.class);48         LockBean lock = new LockBean(null, "锁1", null);49         lm.saveLock(lock);50         session.commit();51     }52 }

 

结果显示:

技术分享

 

 

 三 、批量操作与分页

 这里就使用前一章的User.就写出主要的代码。

首先定义分页对象。

 1 package com.cy.mybatis.beans; 2  3 import java.util.List; 4  5  6  7 /** 8  * 定义一个分页对象 9  * 10  * @author11  * 12  */13 public class Pager {14 15     private int pageNo;// 当前页码16     private int pageTotal;// 总页码17     private int rowsTotal;// 总条数18     private int pageSize;// 每页显示条数19      private List<Object> list;// 返回的数据集合20 21     public int getPageNo() {22         return pageNo;23     }24 25     public void setPageNo(int pageNo) {26         this.pageNo = pageNo;27     }28 29     public int getPageTotal() {30         return pageTotal;31     }32 33     public void setPageTotal(int pageTotal) {34         this.pageTotal = pageTotal;35     }36 37     public int getRowsTotal() {38         return rowsTotal;39     }40 41     public void setRowsTotal(int rowsTotal) {42         this.rowsTotal = rowsTotal;43         pageTotal = rowsTotal % pageSize == 0 ? rowsTotal / pageSize : rowsTotal / pageSize + 1;44     }45 46     public int getPageSize() {47         return pageSize;48     }49 50     public void setPageSize(int pageSize) {51         this.pageSize = pageSize;52     }53 54     public List<?> getList() {55         return list;56     }57 58     public void setList(List<Object> list) {59         this.list = list;60     }61 62 63     @Override64     public String toString() {65         return "Pager [pageNo=" + pageNo + ", pageTotal=" + pageTotal66                 + ", rowsTotal=" + rowsTotal + ", pageSize=" + pageSize67                 + ", list=" + list + "]";68     }69 70 }

 

UserMapper.java接口。

 1 package com.cy.mybatis.mapper; 2  3 import java.util.List; 4 import java.util.Map; 5  6 import org.apache.ibatis.annotations.Param; 7  8 import com.cy.mybatis.beans.UserBean; 9 10 public interface UserMapper {11     /**12      * 新增用戶13      * @param user14      * @return15      * @throws Exception16      */17     public int insertUser(@Param("user")UserBean user) throws Exception;18     /**19      * 修改用戶20      * @param user21      * @param id22      * @return23      * @throws Exception24      */25     public int updateUser (@Param("u")UserBean user,@Param("id")int id) throws Exception;26      /**27       * 刪除用戶28       * @param id29       * @return30       * @throws Exception31       */32     public int deleteUser(int id) throws Exception;33     /**34      * 根据id查询用户信息35      * @param id36      * @return37      * @throws Exception38      */39     public UserBean selectUserById(int id) throws Exception;40      /**41       * 查询所有的用户信息42       * @return43       * @throws Exception44       */45     public List<UserBean> selectAllUser() throws Exception;46     47     48     /**49      * 批量增加50      * @param user51      * @return52      * @throws Exception53      */54    public int batchInsertUser(@Param("users")List<UserBean> user) throws Exception;55    56    /**57     * 批量删除58     * @param list59     * @return60     * @throws Exception61     */62    public int batchDeleteUser(@Param("list")List<Integer> list) throws Exception;63    64    65    /**66     * 分页查询数据67     * @param parma68     * @return69     * @throws Exception70     */71    public List<UserBean> pagerUser(Map<String, Object> parmas) throws Exception;72    73    /**74     * 75     * 分页统计数据76     * @param parma77     * @return78     * @throws Exception79     */80     public int countUser(Map<String, Object> parmas) throws Exception;81     82  83     84 }

 

xml文件

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.UserMapper"> 4 <!-- 自定义返回结果集 --> 5    <resultMap id="userMap" type="UserBean"> 6         <id property="id" column="id" javaType="java.lang.Integer"></id> 7         <result property="username" column="username" javaType="java.lang.String"></result> 8         <result property="password" column="password" javaType="java.lang.String"></result> 9         <result property="account" column="account" javaType="java.lang.Double"></result>10     </resultMap>11 <!-- 在各种标签中的id属性必须和接口中的方法名相同 , id属性值必须是唯一的,不能够重复使用。parameterType属性指明查询时使用的参数类型,resultType属性指明查询返回的结果集类型-->    12 <!-- useGeneratedKeys:( 仅 对 insert 有 用 ) 这 会 告 诉 MyBatis 使 用 JDBC 的getGeneratedKeys 13             方法来取出由数据(比如:像 MySQL 和 SQLServer 这样的数据库管理系统的自动递增字段)内部生成的主键。默认值: false。 -->    14 <!--keyProperty: (仅对 insert有用)标记一个属性, MyBatis 会通过 getGeneratedKeys或者通过 insert 语句的 selectKey 子元素设置它的值。默认:不设置。 -->15 <!--#{}中的内容,为占位符,当参数为某个JavaBean时,表示放置该Bean对象的属性值  -->16 17 18     <insert id="insertUser" useGeneratedKeys="true" keyProperty="user.id">19         insert into t_user (username,password,account) values (#{user.username},#{user.password},#{user.account})20     </insert>21     22     <update id="updateUser">23       update t_user set username=#{u.username},password=#{u.password},account=#{u.account} where id=#{id}24     </update>25     26     <delete id="deleteUser" parameterType="int">27      delete from t_user where id=#{id}  28     </delete>29     30     <select id="selectUserById" parameterType="int" resultMap="userMap">31      select * from t_user where id=#{id}32     </select>33     34     <select id="selectAllUser" resultMap="userMap">35      select * from t_user36     </select>37     38     39     40     41     <!-- 批量操作和foreach标签 -->42     43     <insert id="batchInsertUser" parameterType="java.util.List">44        insert into t_user values 45         <foreach collection="users" item="users" separator=",">46          (null,#{users.username},#{users.password},#{users.account})47         </foreach>48     </insert>49     50     51     <delete id="batchDeleteUser">52        delete from t_user where id in (53          <foreach collection="list" item="list" separator=",">54           #{id}55          </foreach>56        )57     </delete>58     59     <!--collection 为用于遍历的元素(必选),支持数组、List、Set  -->60     <!-- item 表示集合中每一个元素进行迭代时的别名. -->61     <!--separator表示在每次进行迭代之间以什么符号作为分隔 符.  -->62     63     64     <select id="pagerUser" parameterType="java.util.Map" resultMap="userMap">65       select * from t_user where 1=166 67       <if test="username!=null">68        and username like ‘%${username}%‘69       </if>70        limit ${index},${pageSize} 71     </select>72     73     <select id="countUser" parameterType="java.util.Map" resultType="int">74         select count(*) from t_user where 1=1 75         <if test="username != null">76             and username like ‘%${username}%‘    77         </if>78     </select>79     80     81 </mapper>    

 

#在生成SQL时,对于字符类型参数,会拼装引号
$在生成SQL时,不会拼装引号,可用于order by之类的参数拼装

 测试类

  1 package com.cy.mybatis.service;  2   3 import java.util.ArrayList;  4 import java.util.HashMap;  5 import java.util.List;  6 import java.util.Map;  7   8 import org.apache.ibatis.session.SqlSession;  9  10 import com.cy.mybatis.beans.UserBean; 11 import com.cy.mybatis.tools.DBTools; 12 import com.cy.mybatis.mapper.UserMapper; 13  14 public class UserService { 15  16     /** 17      * @param args 18      */ 19     public static void main(String[] args) { 20 //        insertUser(); 21 //        deleteUser(); 22 //        updateUser(); 23 //        selectUserById(); 24 //        selectAllUser(); 25          26 //        batchInsertUser(); 27 //        batchDeleteUser(); 28 //        countUser(); 29         pagerUser(); 30     } 31  32      33     private static void countUser() { 34         SqlSession session = DBTools.getSession(); 35         UserMapper mapper = session.getMapper(UserMapper.class); 36         Map<String,Object> params = new HashMap<String,Object>(); 37         params.put("username", "kitty"); 38         int index = 0; 39         params.put("index", index);//从第几页开始。mysql是从0开始的 40         params.put("pageSize", 5);//每页显示的数据条数 41         int count; 42         try { 43             count = mapper.countUser(params); 44             System.out.println(count); 45         } catch (Exception e) { 46             e.printStackTrace(); 47         } 48          49     } 50  51  52     private static void pagerUser() { 53         SqlSession session = DBTools.getSession(); 54         UserMapper mapper = session.getMapper(UserMapper.class); 55         Map<String,Object> params = new HashMap<String,Object>(); 56         params.put("username", "kitty"); 57         params.put("index", 0);//从第几页开始。mysql是从0开始的 58         params.put("pageSize", 5);//每页显示的数据条数 59         try { 60              List<UserBean> u = mapper.pagerUser(params); 61             for (UserBean userBean : u) { 62                 System.out.println("--------"+userBean); 63             } 64         } catch (Exception e) { 65             e.printStackTrace(); 66         } 67          68     } 69  70  71     private static void batchDeleteUser() { 72         SqlSession session = DBTools.getSession(); 73         UserMapper mapper = session.getMapper(UserMapper.class); 74         List<Integer> ids = new ArrayList<Integer>(); 75         for(int i = 4; i < 10; i ++){ 76             ids.add(i); 77         } 78         try { 79             mapper.batchDeleteUser(ids); 80             session.commit(); 81         } catch (Exception e) { 82             e.printStackTrace(); 83         } 84          85     } 86  87  88     private static void batchInsertUser() { 89         SqlSession session = DBTools.getSession(); 90         UserMapper mapper = session.getMapper(UserMapper.class); 91          92         List<UserBean> users = new ArrayList<UserBean>(); 93         for(int i = 0; i < 10; i ++){ 94             UserBean user = new UserBean("kitty"+i, "123456", 6000.0); 95             users.add(user); 96         } 97         try { 98             mapper.batchInsertUser(users); 99             session.commit();100         } catch (Exception e) {101             e.printStackTrace();102         }103     }104 105 106     /**107      * 新增用户108      */109     private static void insertUser() {110         SqlSession session = DBTools.getSession();111         UserMapper mapper = session.getMapper(UserMapper.class);112         UserBean user = new UserBean("懿", "1314520", 7000.0);113         try {114             mapper.insertUser(user);115             System.out.println(user.toString());116              session.commit();117         } catch (Exception e) {118             e.printStackTrace();119             session.rollback();120         }121     }122     123     124     /**125      * 删除用户126      */127     private static void deleteUser(){128         SqlSession session=DBTools.getSession();129         UserMapper mapper=session.getMapper(UserMapper.class);130         try {131             mapper.deleteUser(1);132             session.commit();133         } catch (Exception e) {134             e.printStackTrace();135             session.rollback();136         }137     }138     139     /**140      * 修改用户数据141      */142     private static void updateUser(){143         SqlSession session=DBTools.getSession();144         UserMapper mapper=session.getMapper(UserMapper.class);145         UserBean user =new UserBean("小明", "111",6000.0);146         try {147             mapper.updateUser(user, 3);148             session.commit();149         } catch (Exception e) {150             e.printStackTrace();151             session.rollback();152         }153     }154     155     /**156      * 根据id查询用户157      */158     private static void selectUserById(){159         SqlSession session=DBTools.getSession();160         UserMapper mapper=session.getMapper(UserMapper.class);161         try {162         UserBean user=    mapper.selectUserById(2);163         System.out.println(user.toString());164             165             session.commit();166         } catch (Exception e) {167             e.printStackTrace();168             session.rollback();169         }170     }171     172     /**173      * 查询所有的用户174      */175     private static void selectAllUser(){176         SqlSession session=DBTools.getSession();177         UserMapper mapper=session.getMapper(UserMapper.class);178         try {179         List<UserBean> user=mapper.selectAllUser();180         System.out.println(user.toString());181         session.commit();182         } catch (Exception e) {183             e.printStackTrace();184             session.rollback();185         }186     }    187 188 }

 

 

 

 

 看一下项目的整体:

技术分享

 

MyBatis学习笔记(二) 关联关系