写点什么

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());		}
复制代码


综上所述,使用级联查询非常麻烦,所以,能够不使用,就不要使用。


用户头像

andy

关注

还未添加个人签名 2019-11-21 加入

还未添加个人简介

评论

发布
暂无评论
MyBatis之关系映射(下)_andy_InfoQ写作社区