首页 > 代码库 > 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学习笔记(二) 关联关系
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。