MyBatis高级查询 一对一映射

drop database if exists simple;

create database simple;

use simple;

drop table if exists sys_user;

create table sys_user

(

    id bigint not null auto_increment comment '用户ID',

    user_name varchar(50) comment '用户名',

    user_password varchar(50) comment '密码',

    user_email varchar(50) comment '邮箱',

    user_info text comment '简介',

    head_img blob comment '头像',

    create_time datetime comment '创建时间',

    primary key (id)

);

alter table sys_user comment '用户表';

drop table if exists sys_role;

create table sys_role(

    id bigint not null auto_increment comment '角色ID',

    role_name varchar(50) comment '角色名',

    enabled int comment '有效标志',

    create_by bigint comment '创建人',

    create_time datetime comment '创建时间',

    primary key (id)

);

alter table sys_role comment '角色表';

drop table if exists sys_privilege;

create table sys_privilege

(

    id bigint not null auto_increment comment '权限ID',

    privilege_name varchar(50) comment '权限名称',

    privilege_url varchar(50) comment '权限URL',

    primary key (id)

);

alter table sys_privilege comment '权限表';

drop table if exists sys_user_role;

create table sys_user_role

(

    user_id bigint comment '用户ID',

    role_id bigint comment '角色ID'

);

alter table sys_user_role comment '用户角色关联表';

drop table if exists sys_role_privilege;

create table sys_role_privilege

(

    role_id bigint comment '用户ID',

    privilege_id bigint comment '角色ID'

);

alter table sys_role_privilege comment '角色权限关联表';

假如1个用户只能有1种角色sys_user和sys_role是通过sys_user_role一对一关联；

1.使用自动映射处理一对一关系；优点：当一定会使用到嵌套结果时使用。

　　1.在SysUser.class model中增加SysRole的对象。

private SysRole role;

　　2.在查询的xml Mapper文件中对查询出的role属性写出对应的属性名称。

 <select id="selectUserAndById" resultType="test.model.SysUser">

          select

              u.id,

              u.user_name userName,

              u.user_password userPassword,

              u.user_email userEmail,

              u.create_time createTime,

              u.user_info userInfo,

              u.head_img  headImg,

              r.id "role.id",

              r.role_name "role.roleName",

              r.enabled "role.enabled",

              r.create_by "role.createBy",

              r.create_time "role.createTime"

              from sys_user u inner join sys_user_role ur on u.id=ur.user_id

              inner join sys_role r on ur.user_id=r.id where u.id=#{id}

  </select>

2.使用resultMap配置一对一映射

<?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="test.dao.SysUserMapper">

  <resultMap id="BaseResultMap" type="test.model.SysUser">

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <id column="id" jdbcType="BIGINT" property="id" />

    <result column="user_name" jdbcType="VARCHAR" property="userName" />

    <result column="user_password" jdbcType="VARCHAR" property="userPassword" />

    <result column="user_email" jdbcType="VARCHAR" property="userEmail" />

    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

    <result column="user_info" jdbcType="LONGVARCHAR" property="userInfo" />

    <result column="head_img" jdbcType="LONGVARBINARY" property="headImg" />

  </resultMap>

  <resultMap type="test.model.SysUser" id="userRoleResultMap" extends="BaseResultMap">

      <!-- role相关属性 -->

    <result column="role_id" jdbcType="BIGINT" property="role.id" />

    <result column="role_name" jdbcType="VARCHAR" property="role.roleName" />

    <result column="enabled" jdbcType="INTEGER" property="role.enabled" />

    <result column="create_by" jdbcType="BIGINT" property="role.createBy" />

    <result column="role_create_time" jdbcType="TIMESTAMP" property="role.createTime" />

  </resultMap>

  
<!-- 注意返回 resultMap="userRoleResultMap"-->

  <select id="selectUserAndById" resultMap="userRoleResultMap">

          select

              u.id,

              u.user_name,

              u.user_password,

              u.user_email,

              u.create_time,

              u.user_info,

              u.head_img,

              r.id role_id,

              r.role_name role_name,

              r.enabled enabled,

              r.create_by create_by,

              r.create_time role_create_time

              from sys_user u inner join sys_user_role ur on u.id=ur.user_id

              inner join sys_role r on ur.user_id=r.id where u.id=#{id}

  </select>

</mapper>

3.使用resultMap的associaction标签配置一对一映射

<?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="test.dao.SysUserMapper">

  <resultMap id="BaseResultMap" type="test.model.SysUser">

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <id column="id" jdbcType="BIGINT" property="id" />

    <result column="user_name" jdbcType="VARCHAR" property="userName" />

    <result column="user_password" jdbcType="VARCHAR" property="userPassword" />

    <result column="user_email" jdbcType="VARCHAR" property="userEmail" />

    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

    <result column="user_info" jdbcType="LONGVARCHAR" property="userInfo" />

    <result column="head_img" jdbcType="LONGVARBINARY" property="headImg" />

  </resultMap>

  <!-- 继承上面的UserResultMap -->

  <resultMap type="test.model.SysUser" id="userRoleResultMap" extends="BaseResultMap">

      <!-- role相关属性 -->

      <!-- 对应的ResultMap在test.dao.SysRoleMapper这个命名控件中-->

    <association property="role" columnPrefix="role_" resultMap="test.dao.SysRoleMapper.BaseResultMap"></association>

  </resultMap>

  <select id="selectUserAndById" resultMap="userRoleResultMap">

          select

              u.id,

              u.user_name,

              u.user_password,

              u.user_email,

              u.create_time,

              u.user_info,

              u.head_img,

              r.id role_id,

              r.role_name role_role_name,

              r.enabled role_enabled,

              r.create_by role_create_by,

              r.create_time role_create_time

              from sys_user u inner join sys_user_role ur on u.id=ur.user_id

              inner join sys_role r on ur.user_id=r.id where u.id=#{id}

  </select>

<mapper/>

<mapper namespace="test.dao.SysRoleMapper">

  <resultMap id="BaseResultMap" type="test.model.SysRole">

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <id column="id" jdbcType="BIGINT" property="id" />

    <result column="role_name" jdbcType="VARCHAR" property="roleName" />

    <result column="enabled" jdbcType="INTEGER" property="enabled" />

    <result column="create_by" jdbcType="BIGINT" property="createBy" />

    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

  </resultMap>

</mapper>

4.association标签的嵌套查询 （子查询）

fetchType="lazy"；还需要在配置文件中加

<settings>

        <setting name="logImpl" value="LOG4J"/>
        <setting name="aggressiveLazyLoading" value="false"/>

</settings>

<!-- SysUserMapper.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="test.dao.SysUserMapper">

  <resultMap id="BaseResultMap" type="test.model.SysUser">

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <id column="id" jdbcType="BIGINT" property="id" />

    <result column="user_name" jdbcType="VARCHAR" property="userName" />

    <result column="user_password" jdbcType="VARCHAR" property="userPassword" />

    <result column="user_email" jdbcType="VARCHAR" property="userEmail" />

    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

    <result column="user_info" jdbcType="LONGVARCHAR" property="userInfo" />

    <result column="head_img" jdbcType="LONGVARBINARY" property="headImg" />

  </resultMap>

  <!-- 继承上面的UserResultMap -->

  <resultMap type="test.model.SysUser" id="userRoleResultMap" extends="BaseResultMap">

      <!-- role相关属性 -->

      <!-- 对应的ResultMap在test.dao.SysRoleMapper这个命名控件中  注意这里没有resultMap=""  -->

    <association property="role"  column="{id=role_id}" fetchType="lazy" select="test.dao.SysRoleMapper.selectRoleById"></association>

  </resultMap>

  <select id="selectUserAndById" resultMap="userRoleResultMap">

      select

          u.id,

          u.user_name,

          u.user_password,

          u.user_email,

        u.create_time,

          u.user_info,

          u.head_img,

          ur.role_id

          from sys_user u inner join sys_user_role ur on u.id=ur.user_id

          where u.id=#{id}

  </select>

</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="test.dao.SysRoleMapper">

  <resultMap id="BaseResultMap" type="test.model.SysRole">

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <id column="id" jdbcType="BIGINT" property="id" />

    <result column="role_name" jdbcType="VARCHAR" property="roleName" />

    <result column="enabled" jdbcType="INTEGER" property="enabled" />

    <result column="create_by" jdbcType="BIGINT" property="createBy" />

    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />

  </resultMap>

  <select id="selectRoleById" resultMap="BaseResultMap">

      select * from sys_role where id=#{id}

  </select>

</mapper>

测试不能用debug看效果；

单元测试

package com.watermelon.test;

import org.apache.ibatis.session.SqlSession;

import org.junit.Assert;

import org.junit.Test;

import test.dao.SysUserMapper;

import test.model.SysUser;

public class SysUserMappertest extends BaseMapperTest{

    @Test

    public void testSelectUserAndById() {

        SqlSession sqlSession = getSqlSession();

        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        SysUser user = sysUserMapper.selectUserAndById(1L);

        Assert.assertNotNull(user);

        System.out.println("调用user.getRole()");

        Assert.assertNotNull(user.getRole());

    }

}

直接运行效果：

DEBUG [main] - ==>  Preparing: select u.id, u.user_name, u.user_password, u.user_email, u.create_time, u.user_info, u.head_img, ur.role_id from sys_user u inner join sys_user_role ur on u.id=ur.user_id where u.id=?

DEBUG [main] - ==> Parameters: 1(Long)

TRACE [main] - <==    Columns: id, user_name, user_password, user_email, create_time, user_info, head_img, role_id

TRACE [main] - <==        Row: 1, admin, 123456, [email protected], 2017-09-25 15:55:15.0, <<BLOB>>, <<BLOB>>, 1

DEBUG [main] - <==      Total: 1

调用user.getRole()

DEBUG [main] - ==>  Preparing: select * from sys_role where id=?

DEBUG [main] - ==> Parameters: 1(Long)

TRACE [main] - <==    Columns: id, role_name, enabled, create_by, create_time

TRACE [main] - <==        Row: 1, 管理员, 1, 1, 2017-09-25 15:55:59.0

DEBUG [main] - <==      Total: 1

debug效果：

注：虽然这个方法已经满足了我们的要求，但是有些时候还是需要在触发某些方法时将所有的数据都加载进来。

可以调用对象的“equals，clone，hashCode，toString”.等方法。

@Test

    public void testSelectUserAndById() {

        SqlSession sqlSession = getSqlSession();

        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        SysUser user = sysUserMapper.selectUserAndById(1L);

        Assert.assertNotNull(user);

//        String s = "";

//        s.equals(null);

        //上面不行，必须是返回对象的方法

        user.equals(null);

        System.out.println("调用user.getRole()");

        Assert.assertNotNull(user.getRole());

    }

结果:

DEBUG [main] - ==>  Preparing: select u.id, u.user_name, u.user_password, u.user_email, u.create_time, u.user_info, u.head_img, ur.role_id from sys_user u inner join sys_user_role ur on u.id=ur.user_id where u.id=?

DEBUG [main] - ==> Parameters: 1(Long)

TRACE [main] - <==    Columns: id, user_name, user_password, user_email, create_time, user_info, head_img, role_id

TRACE [main] - <==        Row: 1, admin, 123456, [email protected], 2017-09-25 15:55:15.0, <<BLOB>>, <<BLOB>>, 1

DEBUG [main] - <==      Total: 1

DEBUG [main] - ==>  Preparing: select * from sys_role where id=?

DEBUG [main] - ==> Parameters: 1(Long)

TRACE [main] - <==    Columns: id, role_name, enabled, create_by, create_time

TRACE [main] - <==        Row: 1, 管理员, 1, 1, 2017-09-25 15:55:59.0

DEBUG [main] - <==      Total: 1

调用user.getRole()