MyBatis 之关系映射（下）
作者：andy
- 2022-10-27 北京
本文字数：3042 字
阅读完需：约 10 分钟
一、多对多关系映射
对于多对多的数据库表操作，关键在于多对多关系中的中间表。同时，需要注意的是，在多对多关系之中，一定要找到关系主控方。如何去寻找，可以这样思考，如果没有了一方，另一方会如何。
1.1、建立数据库表
role 表：
create table role( rid int auto_increment, name varchar(255) not null, constraint pk_rid primary key(rid))engine innodb;复制代码
groups 表：
create table groups( gid int auto_increment, name varchar(255) not null, constraint pk_gid primary key(gid))engine innodb;复制代码
role_groups 表：
create table role_groups( rid int, gid int, constraint fk_rid foreign key(rid) references role(rid)on delete cascade, constraint fk_gid foreign key(gid) references groups(gid)on delete cascade)engine innodb;复制代码
1.2、定义 VO 类
Role 类：
package org.fuys.owndb.vo;import java.io.Serializable;import java.util.List;@SuppressWarnings("serial")public class Role implements Serializable { private Integer rid; private String name; private List<Groups> groupses;}复制代码
Groups 类：
package org.fuys.owndb.vo;import java.io.Serializable;import java.util.List;@SuppressWarnings("serial")public class Groups implements Serializable { private Integer gid; private String name; private List<Role> roles;}复制代码
RoleGroups 类：
package org.fuys.owndb.vo;import java.io.Serializable;@SuppressWarnings("serial")public class RoleGroups implements Serializable { private Integer rid; private Integer gid;}复制代码
1.3、增加配置文件
Role.xml 文件：
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="org.fuys.owndb.vo.mapping.Role"> <resultMap type="Role" id="RoleMap"> <id column="rid" property="rid"/> <result column="name" property="name"/> <collection property="groupses" column="rid" javaType="java.util.List" ofType="Groups" select="org.fuys.owndb.vo.mapping.Groups.selectByCid"/> </resultMap> <insert id="insert" parameterType="Role" keyProperty="rid" useGeneratedKeys="true"> INSERT INTO role(name) VALUES(#{name}) </insert> <insert id="insertRoleGroups" parameterType="RoleGroups"> INSERT INTO role_groups(rid,gid) VALUES(#{rid},#{gid}) </insert> <update id="updateByRid" parameterType="Role"> UPDATE role SET name = #{name} WHERE rid = #{rid} </update> <delete id="deleteRoleGroupsByRid" parameterType="int"> DELETE FROM role_groups WHERE rid = #{rid} </delete> <select id="selectByRid" parameterType="int" resultMap="RoleMap"> SELECT rid,name FROM role WHERE rid = #{rid} </select> </mapper>复制代码
Groups.xml 文件
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="org.fuys.owndb.vo.mapping.Groups"> <resultMap type="Groups" id="GroupsMap"> <id column="gid" property="gid"/> <result column="name" property="name"/> </resultMap> <insert id="insert" parameterType="Groups" keyProperty="gid" useGeneratedKeys="true"> INSERT INTO groups(name) VALUES(#{name}) </insert> <select id="selectByCid" parameterType="int" resultMap="GroupsMap"> SELECT gid,name FROM groups WHERE gid in ( SELECT gid FROM role_groups WHERE rid = #{rid} ) </select></mapper>复制代码
1.4、修改核心配置文件
<typeAliases> <typeAlias type="org.fuys.owndb.vo.Role" alias="Role"/> <typeAlias type="org.fuys.owndb.vo.Groups" alias="Groups"/> </typeAliases> <mappers> <mapper resource="org/fuys/owndb/vo/mapping/Role.xml"/> <mapper resource="org/fuys/owndb/vo/mapping/Groups.xml"/> </mappers>复制代码
1.5、测试数据增加程序
@Test public void testInsert() throws Exception { Role role = new Role(); role.setName("family"); int lenR = MyBatisSqlSessionFactory.getSqlSession().insert("org.fuys.owndb.vo.mapping.Role.insert", role); logger.info("role --> " + role.toString() + " --> " + lenR); List<Integer> groupses = new ArrayList<>(); Groups groups = new Groups(); groups.setName("life"); int lenG = MyBatisSqlSessionFactory.getSqlSession().insert("org.fuys.owndb.vo.mapping.Groups.insert",groups); logger.info("groups --> " + groups.toString() + " --> " + lenG); groupses.add(groups.getGid()); groups.setName("gift"); lenG = MyBatisSqlSessionFactory.getSqlSession().insert("org.fuys.owndb.vo.mapping.Groups.insert",groups); logger.info("groups --> " + groups.toString() + " --> " + lenG); groupses.add(groups.getGid()); RoleGroups roleGroups = new RoleGroups(); for(int x=0;x<groupses.size();x++){ roleGroups.setRid(role.getRid()); roleGroups.setGid(groupses.get(x)); int lenRG = MyBatisSqlSessionFactory.getSqlSession().insert("org.fuys.owndb.vo.mapping.Role.insertRoleGroups",roleGroups); logger.info("groups --> " + roleGroups + " --> " + lenRG); } MyBatisSqlSessionFactory.getSqlSession().commit(); MyBatisSqlSessionFactory.closeSqlSession(); }复制代码
1.6、测试数据修改
@Test public void testUpdate(){ Role role = new Role(); role.setRid(1); role.setName("better friend"); int len = MyBatisSqlSessionFactory.getSqlSession().update("org.fuys.owndb.vo.mapping.Role.updateByRid", role); logger.info("role --> " + len); int delRG = MyBatisSqlSessionFactory.getSqlSession().delete("org.fuys.owndb.vo.mapping.Role.deleteRoleGroupsByRid", 1); logger.info("roleGroups --> " + delRG); List<Integer> groupses = new ArrayList<>(); groupses.add(1); groupses.add(2); RoleGroups roleGroups = new RoleGroups(); for(int x=0;x<groupses.size();x++){ roleGroups.setRid(role.getRid()); roleGroups.setGid(groupses.get(x)); int lenRG = MyBatisSqlSessionFactory.getSqlSession().insert("org.fuys.owndb.vo.mapping.Role.insertRoleGroups",roleGroups); logger.info("groups --> " + roleGroups + " --> " + lenRG); } MyBatisSqlSessionFactory.getSqlSession().commit(); MyBatisSqlSessionFactory.closeSqlSession(); }复制代码
1.7、测试数据查询
@Test public void testMoreVsMore(){ Role role = MyBatisSqlSessionFactory.getSqlSession().selectOne("org.fuys.owndb.vo.mapping.Role.selectByRid",1); logger.info(role.toString()); }复制代码
综上所述，使用级联查询非常麻烦，所以，能够不使用，就不要使用。
划线
评论
复制
发布于: 刚刚阅读数: 5
andy
关注
还未添加个人签名 2019-11-21 加入
还未添加个人简介
评论