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());
}
}
}
显示结果