mybatis一对多映射

发表于 | 分类于 ，

注意事项

  • 启动类加上MapperScan
  • 映射文件路径配置要对应到生成的Mapper.xml文件

    application.yml配置文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    spring:
      datasource:
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        username: sa
        password: longqi.123
        url: jdbc:sqlserver://localhost:8888;database=shiro
    mybatis:
      mapper-locations: classpath:mappers/*Mapper.xml
      type-aliases-package: com.howard.shiro.shiro.mappers

mybatis可以根据ResultMap映射一对多

自动生成实体类后,在实体类里添加关联表实体

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Data
public class UserInfoEntity {
    private Integer userId;

    private String userName;

    private String password;

    private String salt;

    private String email;

    private Integer status;

    private Date lastLoginTime;

    private Date createTime;

    private Date modifyTime;

    private String activeCode;

    private Integer deptId;

    private List<RoleEntity> roles;
	}

在UserMapper.xml的ResultMap标签里配置collection标签

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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="com.howard.shiro.shiro.mapper.UserInfoEntityMapper">

  <resultMap id="BaseResultMap" type="com.howard.shiro.shiro.entity.UserInfoEntity">
    <id column="user_id" jdbcType="INTEGER" property="userId" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="salt" jdbcType="VARCHAR" property="salt" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="last_login_time" jdbcType="TIMESTAMP" property="lastLoginTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime" />
    <result column="active_code" jdbcType="VARCHAR" property="activeCode" />
    <result column="dept_id" jdbcType="INTEGER" property="deptId" />
  </resultMap>
  
  <resultMap id="BaseRoleResultMap" type="com.howard.shiro.shiro.entity.RoleEntity">
    <id column="role_id" jdbcType="INTEGER" property="roleId" />
    <result column="role_name" jdbcType="VARCHAR" property="roleName" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime" />
  </resultMap>
  
  <resultMap id="UserRolePermResultMap" type="com.howard.shiro.shiro.entity.UserInfoEntity" extends="BaseResultMap">
    <collection property="roles" ofType="com.howard.shiro.shiro.entity.RoleEntity"
                select="selectAllRoleByUserId" column="user_id"/>
				<!--extend标识继承 select标签对应子查询语句,column标签对应传递的值 -->
  </resultMap>
  <sql id="Base_Column_List">
    user_id, user_name, password, salt, email, status, last_login_time, create_time, modify_time,
    active_code, dept_id
  </sql>

  <sql id="Base_Role_List">
    role.role_id, role.role_name, role.remark, role.create_time, role.modify_time
  </sql>

  <select id="selectAllRoleByUserId" resultMap="BaseRoleResultMap" parameterType="java.lang.Integer">
    select
    <include refid="Base_Role_List"/>
    from
    user_role
    inner join role ON user_role.role_id  = role.role_id
    where
    user_role.user_id = #{user_id}
  </select>
    <select id="selectUserInfoRoleByUserName" parameterType="java.lang.String" resultMap="UserRolePermResultMap">
    select
    <include refid="Base_Column_List"/>
    from
    user_info
    where
    user_name=#{userName}
  </select>
</mapper>