mybatis映射关系查询（一对一，一对多）

最新推荐文章于 2023-09-04 12:24:43 发布

JC. Lin

最新推荐文章于 2023-09-04 12:24:43 发布

阅读量980

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/qq_31803503/article/details/79248787

版权

mybatis 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

初学mybatis的同学们都会觉得mybatis比较麻烦，修改sql语句不能热部署，映射关系配置困难。这些问题都会阻碍同学们学习的步伐，为了让同学们更快的了解mybatis映射关系配置，我特意做了个demo让同学们去参考，话不多说直接上源码：

数据表：

-- ----------------------------
-- Table structure for jdrole
-- ----------------------------
DROP TABLE IF EXISTS `jdrole`;
CREATE TABLE `jdrole` (
  `eid` bigint(20) NOT NULL AUTO_INCREMENT,
  `detail` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `roleCode` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for jduser
-- ----------------------------
DROP TABLE IF EXISTS `jduser`;
CREATE TABLE `jduser` (
  `eid` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `headImg` bigint(20) DEFAULT NULL,
  `memo` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for privilege
-- ----------------------------
DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege` (
  `eid` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for roleprivilege
-- ----------------------------
DROP TABLE IF EXISTS `roleprivilege`;
CREATE TABLE `roleprivilege` (
  `eid` bigint(20) NOT NULL AUTO_INCREMENT,
  `privilege` bigint(20) DEFAULT NULL,
  `role` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`eid`),
  KEY `FK6E0EABB56BFB671` (`role`),
  KEY `FK6E0EABB50471437` (`privilege`),
  CONSTRAINT `FK6E0EABB50471437` FOREIGN KEY (`privilege`) REFERENCES `privilege` (`eid`),
  CONSTRAINT `FK6E0EABB56BFB671` FOREIGN KEY (`role`) REFERENCES `jdrole` (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for userrole
-- ----------------------------
DROP TABLE IF EXISTS `userrole`;
CREATE TABLE `userrole` (
  `eid` bigint(20) NOT NULL AUTO_INCREMENT,
  `role` bigint(20) DEFAULT NULL,
  `user` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`eid`),
  KEY `FKF3F7670156BFB671` (`role`),
  KEY `FKF3F767014C46FFB0` (`user`),
  CONSTRAINT `FKF3F767014C46FFB0` FOREIGN KEY (`user`) REFERENCES `jduser` (`eid`),
  CONSTRAINT `FKF3F7670156BFB671` FOREIGN KEY (`role`) REFERENCES `jdrole` (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

javaBean

public class User implements Serializable{
  private static final long serialVersionUID = -3355898752454617959L;
  private Long eid;
  private String name;
  private String password;
  private String dept;//部门
  private String account;//账户
  
  private Long headImg;//头像保存Files的ID
  private Boolean gender;//性别
  private String state;//状态
  private String mobile;//手机号码
  private String email;//邮箱
  private Date birthday;//生日
  private String memo;//备忘录
  private List<UserRole> userRoles = new ArrayList<UserRole>(0);
  
  //用户状态 
  public static String USER_STATE_VALID = "1";//有效
  public static String USER_STATE_INVALID = "0";//无效
}
public class UserRole implements Serializable {
  private static final long serialVersionUID = 987951779822832066L;
  private Long eid;
  private User user;
  private Role role;
}
public class Role implements Serializable {
  private static final long serialVersionUID = -5948865302742232919L;
  private Long eid;
  private String name;//角色名
  private String roleCode;//角色编码
  private String detail;//详细
  private String state;//状态
  private List<RolePrivilege> rolePrivileges = new ArrayList<RolePrivilege>(0);
  private List<UserRole> userRoles = new ArrayList<UserRole>(0);
  
  public static String ROLE_STATE_VALID = "1";//有效
  public static String ROLE_STATE_INVALID = "0";//无效
}
public class Privilege implements Serializable {
  private static final long serialVersionUID = -7358970078992523810L;
  
  private Long eid;
  private String name;//权限名
  private String code;
}
public class RolePrivilege implements Serializable {
  private static final long serialVersionUID = -5659074190267257788L;
  private Long eid;
  private Role role;
  private Privilege privilege;
}

最后放上*mapper.xml文件

UserMapper.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="cn.lin.nsfw.user.dao.UserMapper">
	<resultMap id="BaseResultMap" type="cn.lin.nsfw.user.entity.User">
		<id column="user_eid" jdbcType="BIGINT" property="eid" />
		<result column="user_account" jdbcType="VARCHAR" property="account" />
		<result column="user_birthday" jdbcType="TIMESTAMP" property="birthday" />
		<result column="user_dept" jdbcType="VARCHAR" property="dept" />
		<result column="user_email" jdbcType="VARCHAR" property="email" />
		<result column="user_gender" jdbcType="BIT" property="gender" />
		<result column="user_headImg" jdbcType="BIGINT" property="headImg" />
		<result column="user_memo" jdbcType="VARCHAR" property="memo" />
		<result column="user_mobile" jdbcType="VARCHAR" property="mobile" />
		<result column="user_name" jdbcType="VARCHAR" property="name" />
		<result column="user_password" jdbcType="VARCHAR" property="password" />
		<result column="user_state" jdbcType="VARCHAR" property="state" />
		<!-- 在 UserRole 类型的 ArrayList 中的 userRoles 的集合。 -->
		<collection column="user_eid" javaType="ArrayList" property="userRoles" ofType="cn.lin.nsfw.user.entity.UserRole"  
			select="getUserRoles" fetchType="lazy"/>
	</resultMap>
	<sql id="Base_Column_List">
		${alias}.eid as user_eid, 
		${alias}.account as user_account, 
		${alias}.birthday as user_birthday, 
		${alias}.dept as user_dept, 
		${alias}.email as user_email, 
		${alias}.gender as user_gender, 
		${alias}.headImg as user_headImg, 
		${alias}.memo as user_memo, 
		${alias}.mobile as user_mobile, 
		${alias}.name as user_name, 
		${alias}.password as user_password, 
		${alias}.state as user_state
	</sql>
	<select id="getUserRoles" parameterType="java.lang.Long" resultMap="cn.lin.nsfw.user.dao.UserRoleMapper.BaseResultMap">
		select 
			ur.eid as userRole_eid
		from UserRole ur 
		where ur.user=#{eid}
	</select>
	<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"><property name="alias" value="u"/></include>
		from jduser u
		where u.eid = #{eid,jdbcType=BIGINT}
	</select>
	<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
		delete from jduser
		where eid = #{eid,jdbcType=BIGINT}
	</delete>
	<insert id="insert" parameterType="cn.lin.nsfw.user.entity.User">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into jduser (account, birthday, dept, email, gender, headImg, memo, mobile, name, password, state)
		values (#{account,jdbcType=VARCHAR}, #{birthday,jdbcType=TIMESTAMP},
		#{dept,jdbcType=VARCHAR},
		#{email,jdbcType=VARCHAR}, #{gender,jdbcType=BIT}, #{headImg,jdbcType=BIGINT},
		#{memo,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR},
		#{name,jdbcType=VARCHAR},
		#{password,jdbcType=VARCHAR}, #{state,jdbcType=VARCHAR})
	</insert>
	<insert id="insertSelective" parameterType="cn.lin.nsfw.user.entity.User">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into jduser
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="account != null">
				account,
			</if>
			<if test="birthday != null">
				birthday,
			</if>
			<if test="dept != null">
				dept,
			</if>
			<if test="email != null">
				email,
			</if>
			<if test="gender != null">
				gender,
			</if>
			<if test="headImg != null">
				headImg,
			</if>
			<if test="memo != null">
				memo,
			</if>
			<if test="mobile != null">
				mobile,
			</if>
			<if test="name != null">
				name,
			</if>
			<if test="password != null">
				password,
			</if>
			<if test="state != null">
				state,
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="account != null">
				#{account,jdbcType=VARCHAR},
			</if>
			<if test="birthday != null">
				#{birthday,jdbcType=TIMESTAMP},
			</if>
			<if test="dept != null">
				#{dept,jdbcType=VARCHAR},
			</if>
			<if test="email != null">
				#{email,jdbcType=VARCHAR},
			</if>
			<if test="gender != null">
				#{gender,jdbcType=BIT},
			</if>
			<if test="headImg != null">
				#{headImg,jdbcType=BIGINT},
			</if>
			<if test="memo != null">
				#{memo,jdbcType=VARCHAR},
			</if>
			<if test="mobile != null">
				#{mobile,jdbcType=VARCHAR},
			</if>
			<if test="name != null">
				#{name,jdbcType=VARCHAR},
			</if>
			<if test="password != null">
				#{password,jdbcType=VARCHAR},
			</if>
			<if test="state != null">
				#{state,jdbcType=VARCHAR},
			</if>
		</trim>
	</insert>
	<update id="updateByPrimaryKeySelective" parameterType="cn.lin.nsfw.user.entity.User">
		update jduser
		<set>
			<if test="account != null">
				account = #{account,jdbcType=VARCHAR},
			</if>
			<if test="birthday != null">
				birthday = #{birthday,jdbcType=TIMESTAMP},
			</if>
			<if test="dept != null">
				dept = #{dept,jdbcType=VARCHAR},
			</if>
			<if test="email != null">
				email = #{email,jdbcType=VARCHAR},
			</if>
			<if test="gender != null">
				gender = #{gender,jdbcType=BIT},
			</if>
			<if test="headImg != null">
				headImg = #{headImg,jdbcType=BIGINT},
			</if>
			<if test="memo != null">
				memo = #{memo,jdbcType=VARCHAR},
			</if>
			<if test="mobile != null">
				mobile = #{mobile,jdbcType=VARCHAR},
			</if>
			<if test="name != null">
				name = #{name,jdbcType=VARCHAR},
			</if>
			<if test="password != null">
				password = #{password,jdbcType=VARCHAR},
			</if>
			<if test="state != null">
				state = #{state,jdbcType=VARCHAR},
			</if>
		</set>
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<update id="updateByPrimaryKey" parameterType="cn.lin.nsfw.user.entity.User">
		update jduser
		set account = #{account,jdbcType=VARCHAR},
		birthday = #{birthday,jdbcType=TIMESTAMP},
		dept = #{dept,jdbcType=VARCHAR},
		email = #{email,jdbcType=VARCHAR},
		gender = #{gender,jdbcType=BIT},
		headImg = #{headImg,jdbcType=BIGINT},
		memo = #{memo,jdbcType=VARCHAR},
		mobile = #{mobile,jdbcType=VARCHAR},
		name = #{name,jdbcType=VARCHAR},
		password = #{password,jdbcType=VARCHAR},
		state = #{state,jdbcType=VARCHAR}
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<insert id="insertBatchSelective" parameterType="java.util.List">
		insert into jduser
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="list[0].eid!=null">
				eid,
			</if>
			<if test="list[0].account!=null">
				account,
			</if>
			<if test="list[0].birthday!=null">
				birthday,
			</if>
			<if test="list[0].dept!=null">
				dept,
			</if>
			<if test="list[0].email!=null">
				email,
			</if>
			<if test="list[0].gender!=null">
				gender,
			</if>
			<if test="list[0].headImg!=null">
				headImg,
			</if>
			<if test="list[0].memo!=null">
				memo,
			</if>
			<if test="list[0].mobile!=null">
				mobile,
			</if>
			<if test="list[0].name!=null">
				name,
			</if>
			<if test="list[0].password!=null">
				password,
			</if>
			<if test="list[0].state!=null">
				state,
			</if>
		</trim>
		values
		<foreach collection="list" index="index" item="item"
			separator=",">
			<trim prefix=" (" suffix=")" suffixOverrides=",">
				<if test="item.eid!=null">
					#{item.eid,jdbcType=BIGINT},
				</if>
				<if test="item.account!=null">
					#{item.account,jdbcType=VARCHAR},
				</if>
				<if test="item.birthday!=null">
					#{item.birthday,jdbcType=TIMESTAMP},
				</if>
				<if test="item.dept!=null">
					#{item.dept,jdbcType=VARCHAR},
				</if>
				<if test="item.email!=null">
					#{item.email,jdbcType=VARCHAR},
				</if>
				<if test="item.gender!=null">
					#{item.gender,jdbcType=BIT},
				</if>
				<if test="item.headImg!=null">
					#{item.headImg,jdbcType=BIGINT},
				</if>
				<if test="item.memo!=null">
					#{item.memo,jdbcType=VARCHAR},
				</if>
				<if test="item.mobile!=null">
					#{item.mobile,jdbcType=VARCHAR},
				</if>
				<if test="item.name!=null">
					#{item.name,jdbcType=VARCHAR},
				</if>
				<if test="item.password!=null">
					#{item.password,jdbcType=VARCHAR},
				</if>
				<if test="item.state!=null">
					#{item.state,jdbcType=VARCHAR},
				</if>
			</trim>
		</foreach>
	</insert>
	<update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
		<foreach collection="list" index="index" item="item"
			separator=";">
			update jduser
			<set>
				<if test="item.account!=null">
					account=#{item.account,jdbcType=VARCHAR},
				</if>
				<if test="item.birthday!=null">
					birthday=#{item.birthday,jdbcType=TIMESTAMP},
				</if>
				<if test="item.dept!=null">
					dept=#{item.dept,jdbcType=VARCHAR},
				</if>
				<if test="item.email!=null">
					email=#{item.email,jdbcType=VARCHAR},
				</if>
				<if test="item.gender!=null">
					gender=#{item.gender,jdbcType=BIT},
				</if>
				<if test="item.headImg!=null">
					headImg=#{item.headImg,jdbcType=BIGINT},
				</if>
				<if test="item.memo!=null">
					memo=#{item.memo,jdbcType=VARCHAR},
				</if>
				<if test="item.mobile!=null">
					mobile=#{item.mobile,jdbcType=VARCHAR},
				</if>
				<if test="item.name!=null">
					name=#{item.name,jdbcType=VARCHAR},
				</if>
				<if test="item.password!=null">
					password=#{item.password,jdbcType=VARCHAR},
				</if>
				<if test="item.state!=null">
					state=#{item.state,jdbcType=VARCHAR},
				</if>
			</set>
			where
			eid = #{item.eid,jdbcType=BIGINT}
		</foreach>
	</update>
</mapper>

UserRoleMapper.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="cn.lin.nsfw.user.dao.UserRoleMapper">
	<resultMap id="BaseResultMap" type="cn.lin.nsfw.user.entity.UserRole">
		<id column="userRole_eid" jdbcType="BIGINT" property="eid" />
		<association column="user" property="user" jdbcType="BIGINT" javaType="cn.lin.nsfw.user.entity.User" 
			resultMap="cn.lin.nsfw.user.dao.UserMapper.BaseResultMap"/>
		<association column="role" property="role" jdbcType="BIGINT" javaType="cn.lin.nsfw.role.entity.Role" 
			resultMap="cn.lin.nsfw.role.dao.RoleMapper.BaseResultMap"/>
	</resultMap>
	<sql id="Base_Column_List">
		eid, role, user
	</sql>
	<select id="getUser" parameterType="java.lang.Long" resultType="cn.lin.nsfw.user.entity.User">
		select
		eid as user_eid, 
		account as user_account, 
		birthday as user_birthday, 
		dept as user_dept, 
		email as user_email, 
		gender as user_gender, 
		headImg as user_headImg, 
		memo as user_memo, 
		mobile as user_mobile, 
		name as user_name, 
		password as user_password, 
		state as user_state
		from jdUser u where u.eid=#{eid}
	</select>
	<select id="getRole" parameterType="java.lang.Long" resultType="cn.lin.nsfw.role.entity.Role">
		select
		eid as role_eid,
		name as role_name,
		roleCode as role_roleCode,
		detail as role_detail,
		state as role_state
		from jdRole r where r.eid=#{eid}
	</select>
	<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
		select
		ur.eid as userRole_eid,
		<include refid="cn.lin.nsfw.user.dao.UserMapper.Base_Column_List"><property name="alias" value="u"/></include>,
		<include refid="cn.lin.nsfw.role.dao.RoleMapper.Base_Column_List"><property name="alias" value="r"/></include>
		from userrole ur 
		left join jduser u on u.eid = ur.user
		left join jdrole r on r.eid = ur.role
		where ur.eid = #{eid,jdbcType=BIGINT}
	</select>
	<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
		delete from userrole
		where eid = #{eid,jdbcType=BIGINT}
	</delete>
	<insert id="insert" parameterType="cn.lin.nsfw.user.entity.UserRole">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into userrole (role, [user])
		values (#{role.eid,jdbcType=BIGINT}, #{user.eid,jdbcType=BIGINT})
	</insert>
	<insert id="insertSelective" parameterType="cn.lin.nsfw.user.entity.UserRole">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into userrole
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="role.eid != null">
				role,
			</if>
			<if test="user.eid != null">
				[user],
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="role.eid != null">
				#{role.eid,jdbcType=BIGINT},
			</if>
			<if test="user.eid != null">
				#{user.eid,jdbcType=BIGINT},
			</if>
		</trim>
	</insert>
	<update id="updateByPrimaryKeySelective" parameterType="cn.lin.nsfw.user.entity.UserRole">
		update userrole
		<set>
			<if test="role.eid != null">
				role = #{role.eid,jdbcType=BIGINT},
			</if>
			<if test="user.eid != null">
				[user] = #{user.eid,jdbcType=BIGINT},
			</if>
		</set>
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<update id="updateByPrimaryKey" parameterType="cn.lin.nsfw.user.entity.UserRole">
		update userrole
		set role = #{role.eid,jdbcType=BIGINT},
		[user] = #{user.eid,jdbcType=BIGINT}
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<insert id="insertBatchSelective" parameterType="java.util.List">
		insert into userrole
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="list[0].eid!=null">
				eid,
			</if>
			<if test="list[0].role.eid!=null">
				role,
			</if>
			<if test="list[0].user.eid!=null">
				[user],
			</if>
		</trim>
		values
		<foreach collection="list" index="index" item="item"
			separator=",">
			<trim prefix=" (" suffix=")" suffixOverrides=",">
				<if test="item.eid!=null">
					#{item.eid,jdbcType=BIGINT},
				</if>
				<if test="item.role.eid!=null">
					#{item.role.eid,jdbcType=BIGINT},
				</if>
				<if test="item.user.eid!=null">
					#{item.user.eid,jdbcType=BIGINT},
				</if>
			</trim>
		</foreach>
	</insert>
	<update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
		<foreach collection="list" index="index" item="item"
			separator=";">
			update userrole
			<set>
				<if test="item.role.eid!=null">
					role=#{item.role.eid,jdbcType=BIGINT},
				</if>
				<if test="item.user.eid!=null">
					[user]=#{item.user.eid,jdbcType=BIGINT},
				</if>
			</set>
			where
			eid = #{item.eid,jdbcType=BIGINT}
		</foreach>
	</update>
</mapper>

RoleMapper.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="cn.lin.nsfw.role.dao.RoleMapper">
	<resultMap id="BaseResultMap" type="cn.lin.nsfw.role.entity.Role">
		<id column="role_eid" jdbcType="BIGINT" property="eid" />
		<result column="role_detail" jdbcType="VARCHAR" property="detail" />
		<result column="role_name" jdbcType="VARCHAR" property="name" />
		<result column="role_roleCode" jdbcType="VARCHAR" property="roleCode" />
		<result column="role_state" jdbcType="VARCHAR" property="state" />
		
		<collection column="role_eid" javaType="ArrayList" property="userRoles" ofType="cn.lin.nsfw.user.entity.UserRole" select="getUserRoles" fetchType="lazy"/>
		<collection column="role_eid" javaType="ArrayList" property="rolePrivileges" ofType="cn.lin.nsfw.role.entity.RolePrivilege" select="getRolePrivileges" fetchType="lazy"/>
	</resultMap>
	<sql id="Base_Column_List">
		${alias}.eid as role_eid, 
		${alias}.detail as role_detail, 
		${alias}.name as role_name, 
		${alias}.roleCode as role_roleCode, 
		${alias}.state as role_state
	</sql>
	<select id="getUserRoles"  parameterType="java.lang.Long" resultMap="cn.lin.nsfw.user.dao.UserRoleMapper.BaseResultMap">
		select 
			ur.eid as userRole_eid
		from UserRole ur 
		where ur.role=#{eid}
	</select>
	<select id="getRolePrivileges"  parameterType="java.lang.Long" resultMap="cn.lin.nsfw.role.dao.RolePrivilegeMapper.BaseResultMap">
		select 
			rp.eid as rolePrivilege_eid,
			<include refid="cn.lin.nsfw.role.dao.PrivilegeMapper.Base_Column_List"><property name="alias" value="p"/></include>
		from RolePrivilege rp 
		left join Privilege p on p.eid = rp.privilege
		where rp.role=#{eid}
	</select>
	<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"><property name="alias" value="r"/></include>
		from jdrole r
		where r.eid = #{eid,jdbcType=BIGINT}
	</select>
	<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
		delete from jdrole
		where eid = #{eid,jdbcType=BIGINT}
	</delete>
	<insert id="insert" parameterType="cn.lin.nsfw.role.entity.Role">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into jdrole (detail, name, roleCode,
		state)
		values (#{detail,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},
		#{rolecode,jdbcType=VARCHAR},
		#{state,jdbcType=VARCHAR})
	</insert>
	<insert id="insertSelective" parameterType="cn.lin.nsfw.role.entity.Role">
		<selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into jdrole
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="detail != null">
				detail,
			</if>
			<if test="name != null">
				name,
			</if>
			<if test="rolecode != null">
				roleCode,
			</if>
			<if test="state != null">
				state,
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="detail != null">
				#{detail,jdbcType=VARCHAR},
			</if>
			<if test="name != null">
				#{name,jdbcType=VARCHAR},
			</if>
			<if test="rolecode != null">
				#{rolecode,jdbcType=VARCHAR},
			</if>
			<if test="state != null">
				#{state,jdbcType=VARCHAR},
			</if>
		</trim>
	</insert>
	<update id="updateByPrimaryKeySelective" parameterType="cn.lin.nsfw.role.entity.Role">
		update jdrole
		<set>
			<if test="detail != null">
				detail = #{detail,jdbcType=VARCHAR},
			</if>
			<if test="name != null">
				name = #{name,jdbcType=VARCHAR},
			</if>
			<if test="rolecode != null">
				roleCode = #{rolecode,jdbcType=VARCHAR},
			</if>
			<if test="state != null">
				state = #{state,jdbcType=VARCHAR},
			</if>
		</set>
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<update id="updateByPrimaryKey" parameterType="cn.lin.nsfw.role.entity.Role">
		update jdrole
		set detail = #{detail,jdbcType=VARCHAR},
		name = #{name,jdbcType=VARCHAR},
		roleCode = #{rolecode,jdbcType=VARCHAR},
		state = #{state,jdbcType=VARCHAR}
		where eid = #{eid,jdbcType=BIGINT}
	</update>
	<insert id="insertBatchSelective" parameterType="java.util.List">
		insert into jdrole
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="list[0].eid!=null">
				eid,
			</if>
			<if test="list[0].detail!=null">
				detail,
			</if>
			<if test="list[0].name!=null">
				name,
			</if>
			<if test="list[0].rolecode!=null">
				roleCode,
			</if>
			<if test="list[0].state!=null">
				state,
			</if>
		</trim>
		values
		<foreach collection="list" index="index" item="item"
			separator=",">
			<trim prefix=" (" suffix=")" suffixOverrides=",">
				<if test="item.eid!=null">
					#{item.eid,jdbcType=BIGINT},
				</if>
				<if test="item.detail!=null">
					#{item.detail,jdbcType=VARCHAR},
				</if>
				<if test="item.name!=null">
					#{item.name,jdbcType=VARCHAR},
				</if>
				<if test="item.rolecode!=null">
					#{item.rolecode,jdbcType=VARCHAR},
				</if>
				<if test="item.state!=null">
					#{item.state,jdbcType=VARCHAR},
				</if>
			</trim>
		</foreach>
	</insert>
	<update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
		<foreach collection="list" index="index" item="item"
			separator=";">
			update jdrole
			<set>
				<if test="item.detail!=null">
					detail=#{item.detail,jdbcType=VARCHAR},
				</if>
				<if test="item.name!=null">
					name=#{item.name,jdbcType=VARCHAR},
				</if>
				<if test="item.rolecode!=null">
					roleCode=#{item.rolecode,jdbcType=VARCHAR},
				</if>
				<if test="item.state!=null">
					state=#{item.state,jdbcType=VARCHAR},
				</if>
			</set>
			where
			eid = #{item.eid,jdbcType=BIGINT}
		</foreach>
	</update>
</mapper>

RolePrivilegeMapper.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="cn.lin.nsfw.role.dao.RolePrivilegeMapper">
  <resultMap id="BaseResultMap" type="cn.lin.nsfw.role.entity.RolePrivilege">
    <id column="rolePrivilege_eid" jdbcType="BIGINT" property="eid" />
    <association column="role" property="role" jdbcType="BIGINT" javaType="cn.lin.nsfw.role.entity.Role" 
    	resultMap="cn.lin.nsfw.role.dao.RoleMapper.BaseResultMap" />
    <association column="privilege" property="privilege" jdbcType="BIGINT" javaType="cn.lin.nsfw.role.entity.Privilege" 
    	resultMap="cn.lin.nsfw.role.dao.PrivilegeMapper.BaseResultMap" />
  </resultMap>
  <sql id="Base_Column_List">
    eid, privilege, role
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    	rp.eid as rolePrivilege_eid,
    	<include refid="cn.lin.nsfw.role.dao.RoleMapper.Base_Column_List"><property name="alias" value="r"/></include>,
    	<include refid="cn.lin.nsfw.role.dao.PrivilegeMapper.Base_Column_List"><property name="alias" value="p"/></include>
    from roleprivilege rp
    left join jdrole r on r.eid = rp.role 
    left join privilege p on p.eid = rp.privilege
    where rp.eid = #{eid,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from roleprivilege
    where eid = #{eid,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="cn.lin.nsfw.role.entity.RolePrivilege">
    <selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into roleprivilege (privilege, role)
    values (#{privilege.eid,jdbcType=BIGINT}, #{role.eid,jdbcType=BIGINT})
  </insert>
  <insert id="insertSelective" parameterType="cn.lin.nsfw.role.entity.RolePrivilege">
    <selectKey keyProperty="eid" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into roleprivilege
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="privilege.eid != null">
        privilege,
      </if>
      <if test="role.eid != null">
        role,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="privilege.eid != null">
        #{privilege.eid,jdbcType=BIGINT},
      </if>
      <if test="role.eid != null">
        #{role.eid,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cn.lin.nsfw.role.entity.RolePrivilege">
    update roleprivilege
    <set>
      <if test="privilege.eid != null">
        privilege = #{privilege.eid,jdbcType=BIGINT},
      </if>
      <if test="role.eid != null">
        role = #{role.eid,jdbcType=BIGINT},
      </if>
    </set>
    where eid = #{eid,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.lin.nsfw.role.entity.RolePrivilege">
    update roleprivilege
    set privilege = #{privilege.eid,jdbcType=BIGINT},
      role = #{role.eid,jdbcType=BIGINT}
    where eid = #{eid,jdbcType=BIGINT}
  </update>
  <select id="getRole" resultMap="cn.lin.nsfw.role.dao.RoleMapper.BaseResultMap">
    select eid,detail,name,roleCode,state from jdRole where role=#{eid}
  </select>
  <select id="getPrivilege" resultMap="cn.lin.nsfw.role.dao.PrivilegeMapper.BaseResultMap">
    select eid,code,name from Privilege where privilege=#{eid}
  </select>
  <insert id="insertBatchSelective" parameterType="java.util.List">
    insert into roleprivilege
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="list[0].eid!=null">
        eid,
      </if>
      <if test="list[0].privilege.eid!=null">
        privilege,
      </if>
      <if test="list[0].role.eid!=null">
        role,
      </if>
    </trim>
     values 
    <foreach collection="list" index="index" item="item" separator=",">
      <trim prefix=" (" suffix=")" suffixOverrides=",">
        <if test="item.eid!=null">
          #{item.eid,jdbcType=BIGINT},
        </if>
        <if test="item.privilege.eid!=null">
          #{item.privilege.eid,jdbcType=BIGINT},
        </if>
        <if test="item.role.eid!=null">
          #{item.role.eid,jdbcType=BIGINT},
        </if>
      </trim>
    </foreach>
  </insert>
  <update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
    <foreach collection="list" index="index" item="item" separator=";">
      update roleprivilege
      <set>
        <if test="item.privilege.eid!=null">
          privilege=#{item.privilege.eid,jdbcType=BIGINT},
        </if>
        <if test="item.role.eid!=null">
          role=#{item.role.eid,jdbcType=BIGINT},
        </if>
      </set>
      where 
      eid = #{item.eid,jdbcType=BIGINT}
    </foreach>
  </update>
</mapper>

PrivilegeMapper.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="cn.lin.nsfw.role.dao.PrivilegeMapper">
  <resultMap id="BaseResultMap" type="cn.lin.nsfw.role.entity.Privilege">
    <id column="privilege_eid" jdbcType="BIGINT" property="eid" />
    <result column="privilege_code" jdbcType="VARCHAR" property="code" />
    <result column="privilege_name" jdbcType="VARCHAR" property="name" />
  </resultMap>
  <sql id="Base_Column_List">
    ${alias}.eid as privilege_eid, 
    ${alias}.code as privilege_code, 
    ${alias}.name as privilege_name
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List"><property name="alias" value="p"/></include>
    from privilege p
    where p.eid = #{eid,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from privilege
    where eid = #{eid,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="cn.lin.nsfw.role.entity.Privilege">
    insert into privilege (eid, code, name
      )
    values (#{eid,jdbcType=BIGINT}, #{code,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="cn.lin.nsfw.role.entity.Privilege">
    insert into privilege
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="eid != null">
        eid,
      </if>
      <if test="code != null">
        code,
      </if>
      <if test="name != null">
        name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="eid != null">
        #{eid,jdbcType=BIGINT},
      </if>
      <if test="code != null">
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cn.lin.nsfw.role.entity.Privilege">
    update privilege
    <set>
      <if test="code != null">
        code = #{code,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
    </set>
    where eid = #{eid,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.lin.nsfw.role.entity.Privilege">
    update privilege
    set code = #{code,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR}
    where eid = #{eid,jdbcType=BIGINT}
  </update>
  <insert id="insertBatchSelective" parameterType="java.util.List">
    insert into privilege
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="list[0].eid!=null">
        eid,
      </if>
      <if test="list[0].code!=null">
        code,
      </if>
      <if test="list[0].name!=null">
        name,
      </if>
    </trim>
     values 
    <foreach collection="list" index="index" item="item" separator=",">
      <trim prefix=" (" suffix=")" suffixOverrides=",">
        <if test="item.eid!=null">
          #{item.eid,jdbcType=BIGINT},
        </if>
        <if test="item.code!=null">
          #{item.code,jdbcType=VARCHAR},
        </if>
        <if test="item.name!=null">
          #{item.name,jdbcType=VARCHAR},
        </if>
      </trim>
    </foreach>
  </insert>
  <update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
    <foreach collection="list" index="index" item="item" separator=";">
      update privilege
      <set>
        <if test="item.code!=null">
          code=#{item.code,jdbcType=VARCHAR},
        </if>
        <if test="item.name!=null">
          name=#{item.name,jdbcType=VARCHAR},
        </if>
      </set>
      where 
      eid = #{item.eid,jdbcType=BIGINT}
    </foreach>
  </update>
</mapper>

我说说在学映射关系的时候遇到的问题：

1、column其实可以指字段名，也可以指查找数据的别名，多级查询时mybatis是通过别名去给对象里面的字段设值。

2、ofType是指类名（最好写全类名）

3、启动是出现错误是，认真看清报的是什么错误，mybatis都会很清晰的反映出来

4、使用Id查询对象时，就一定为点到id，例如：“role=#{item.role.eid,jdbcType=BIGINT},”
5、如果自增主键时，可以不必重新查询主键（<selectKey>），可以通过配置全局属性来实现

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
  
<configuration>
	<settings>
		<!-- 使用jdbc的getGeneratedKeys 获取数据库自增主键值 -->
		<setting name="useGeneratedKeys" value="true"/>
		<!-- 使用列别名替换列名  默认：true -->
		<setting name="useColumnLabel" value="true"/>
		<!-- 全局懒加载 -->
		<setting name="lazyLoadingEnabled" value="true"/>
		<!-- 开启驼峰命名转换：Table{create_time} 》   Entity{createTime} -->
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<!-- 打印查询语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>
</configuration>

最后，还是地多看看别人的源码，文档。http://www.mybatis.org/mybatis-3/zh/index.html
感兴趣的朋友可以关注微信公众号（会定时推送新的知识）：

JC. Lin

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mybatis映射关系查询（一对一，一对多）

初学mybatis的同学们都会觉得mybatis比较麻烦，修改sql语句不能热部署，映射关系配置困难。这些问题都会阻碍同学们学习的步伐，为了让同学们更快的了解mybatis映射关系配置，我特意做了个demo让同学们去参考，话不多说直接上源码：数据表： -- ------------------------------ Table structure for jdrole-- --...
复制链接

扫一扫