Mybatis 一对一关联查询

方法一、多表查询（一次性查询）

一个用户只有一个身份证，查询用户信息时，级联查出身份证信息

创建数据脚本

# 用户表
create table user(
	id int(11) primary key auto_increment,
	name varchar(20),
	age int(2)
)engine=InnoDB default charset=utf8;

# 身份证
create table idcard(
	id int(11) primary key auto_increment, 
	number varchar(18),
	user_id int(11)
)engine=InnoDB default charset=utf8;

创建实体类

@Data
public class User {

    private Integer id;

    private String name;

    private Integer age;
	
	// 每个人有一个身份证
    private IdCard idCard;
}

@Data
public class IdCard {

    private Integer id;

    private String number;

    private Integer userId;
}

对象关系映射

<?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.test.quickstart.mapper.UserMapper">

    <!-- 结果集映射 -->
    <resultMap id="UserMap" type="User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age" />
    </resultMap>

    <resultMap id="UserAndIdCardMap" type="User" extends="UserMap">
        <!--
             association：关联表映射
             property: 映射的属性值
             javaType: 映射的类型，这里使用类型别名
             columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
          -->
        <association property="idCard" javaType="IdCard" columnPrefix="card_">
            <id column="id" property="id"/>
            <result column="number" property="number"/>
            <result column="user_id" property="userId"/>
        </association>
    </resultMap> 

    <select id="selectOne2One" resultMap="UserAndIdCardMap">
         select
          u.id,
          u.name,
          u.age,
          i.id as card_id,
          i.number as card_number,
          i.user_id
          from user as u,idcard as i
          where u.id=i.user_id and u.id=#{id}
    </select>
</mapper>

配置文件参考 mybatis-config.xml

测试

/**
 * @author han
 * 2020/4/22 16:33
 */
public class Demo01Test {

    private SqlSessionFactory sessionFactory;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        sessionFactory = new SqlSessionFactoryBuilder().build(is);
    }

    /**
     * 测试一对一关联查询
     */
    @Test
    public void testSelectOne2One() {
        SqlSession session = sessionFactory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = userMapper.selectOne2One(1);
        System.out.println(user);
        session.close();
    }
}

方法二、使用关联的XXMapper对象方法嵌套查询（多次查询）

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.test.quickstart.mapper.UserMapper">
    <resultMap id="UserAndIdCardMap2" type="User" extends="UserMap">
        <!--
             association：关联查询
             property: 映射的属性值
             javaType: 映射的类型，这里使用类型别名
             columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
             select: 嵌套查询，使用关联表的Mapper对象方法查询 
             column: 指定主查询中列的结果作为嵌套查询的参数
             多参数时在column中用"{}"将参数包起来,=左侧的为mapper中定义的@Param,=右侧为主表列名
             如：{userId=id,name=name}
             fetchType: lazy 延迟加载,用到该对象时才发送sql查询
             默认值是eager,将发出sql,查找关联数据
         -->
        <association property="idCard" javaType="IdCard"
                     column="{userId=id}" fetchType="lazy"
                     select="com.test.quickstart.mapper.IdCardMapper.selectByUserId" />
    </resultMap>
    <select id="selectOne2One2" resultMap="UserAndIdCardMap2">
         select * from user where id=#{id}
    </select>
</mapper>

IdCardMapper.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.test.quickstart.mapper.IdCardMapper">

    <!-- 结果集映射 -->
    <resultMap id="IdCardMap" type="IdCard">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="user_id" property="userId" />
    </resultMap>


    <select id="selectByUserId" resultMap="IdCardMap">
         select * from idcard where user_id=#{userId}
    </select>

</mapper>

UserMapper.java

@Mapper
public interface UserMapper {
    User selectOne2One2(@Param("id") Integer id);
}

IdCardMapper.java

@Mapper
public interface IdCardMapper {

    IdCard selectByUserId(@Param("userId") Integer userId);
}