IDEA Mybatis复杂结果映射

1.一对一结果映射

①数据库表

t_user表

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`password`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`role_id`  int(11) NULL DEFAULT NULL ,
`createtime`  timestamp NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=13
ROW_FORMAT=DYNAMIC

t_role表

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`rolekey`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`rolename`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=DYNAMIC

②基础数据类User及Role

User类

public class User {
    private Integer id;
    private String username;
    private String password;
    private Role role;
    // set/get,构造函数及toString省略...
}

Role类

public class Role {
    private Integer id;
    private String key;
    private String name;  
    // set/get,构造函数及toString省略...
}

③映射类

UserMapper接口映射类

	/**
     * 查看所有User记录
     * @return
     */
    List<User> getUsers();

④配置文件

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- 不写会报错 -->
<!DOCTYPE mapper PUBLIC
        "-//mybatis.org/DTD Mapper 3.0"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cx.mapper.UserMapper">
	<!--
        property表示基本类中的属性名称
        column表示对应数据库生成表中字段名称
    -->
    <resultMap id="userResult" type="User">
        <!--普通字段,映射简单数据类型(如String,int,double,Date等)-->
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <!--复杂字段(如自定义的类Role,User等)-->
        <association property="role" javaType="Role">
            <id property="id" column="role_id"/>
            <result property="key" column="rolekey"/>
            <result property="name" column="rolename"/>
        </association>
    </resultMap>
    <select id="getUsers" resultMap="userResult">
        SELECT
            a.id,
            a.username,
            a.password,
            b.id role_id,
            b.rolekey,
            b.rolename
        FROM
            t_user a
        LEFT JOIN t_role b ON a.role_id = b.id
    </select>
</mapper>

⑤测试功能

MybatisTest测试类

public class MybatisTest {
    /**
     * 获取通道
     */
    SqlSession sqlSession = DataBaseTools.getSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

	@Test
    public void test07(){
        List<User> userList = userMapper.getUsers();
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

显示结果

2.一对多结果映射

①数据库表

t_role表

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`rolekey`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`rolename`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=DYNAMIC

t_role_permission表

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`roleid`  int(11) NULL DEFAULT NULL ,
`permissionid`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=28
ROW_FORMAT=DYNAMIC

t_permission表

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
`permissionkey`  varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=15
ROW_FORMAT=DYNAMIC

②基础数据类Role及Permission

Role类

public class Role {
    private Integer id;
    private String key;
    private String name;

    /**
     * 一对多关系集合
     */
    private List<Permission> permissionList;
    // set/get,构造函数及toString省略...
}

Permission类

public class Permission {
    private Integer id;
    private String name;
    private String key;
    // set/get,构造函数及toString省略...
}

③映射类

RoleMapper接口映射类

public interface RoleMapper {
    /**
     * 根据ID获取Role
     * @param id
     * @return
     */
    Role getRoleById(@Param("id") Integer id);
}

④配置文件

RoleMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- 不写会报错 -->
<!DOCTYPE mapper PUBLIC
        "-//mybatis.org/DTD Mapper 3.0"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cx.mapper.RoleMapper">

        <resultMap id="rolePermissionMap" type="Role">
            <id property="id" column="id"/>
            <result property="key" column="rolekey"/>
            <result property="name" column="rolename"/>
            <!--复杂类型集合-->
            <!--ofType复杂类型集合中的类名称-->
            <collection property="permissionList" ofType="Permission">
                <id property="id" column="permissionid"/>
                <result property="name" column="name"/>
                <result property="key" column="permissionkey"/>
            </collection>
        </resultMap>
        <select id="getRoleById" resultMap="rolePermissionMap">
            SELECT
                a.*, c.id permissionid,
                c.name,
                c.permissionkey
            FROM
                t_role a
            LEFT JOIN t_role_permission b ON a.id = b.roleid
            LEFT JOIN t_permission c ON b.permissionid = c.id
            WHERE
                a.id = #{id}
        </select>

</mapper>

⑤测试功能

MybatisTest测试类

public class MybatisTest {
    /**
     * 获取通道
     */
    SqlSession sqlSession = DataBaseTools.getSession();
    RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
    @Test
    public void test08(){
        Role role = roleMapper.getRoleById(1);

        System.out.print(role.getId() + "\t");
        System.out.print(role.getName() + "\t");
        System.out.println(role.getKey());
        System.out.println("拥有的权限:");
        for (Permission permission : role.getPermissionList()) {
            System.out.println(permission.getName());
        }
    }
}

显示结果