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

川长思鸟来 2022-05-31 01:59 256阅读 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" />
    		
    		<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

RoleMapper.xml

RolePrivilegeMapper.xml

PrivilegeMapper.xml

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

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>
    		
    		<setting name="useGeneratedKeys" value="true"/>
    		
    		<setting name="useColumnLabel" value="true"/>
    		
    		<setting name="lazyLoadingEnabled" value="true"/>
    		
    		<setting name="mapUnderscoreToCamelCase" value="true"/>
    		
            <setting name="logImpl" value="STDOUT_LOGGING" />
    	</settings>
    </configuration>

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

![70][]

[70]: /images/20220531/c260347a157b4806b67d27603bf5d446.png