MyBatis映射（一对一查询）

目录

1.一对一查询

1.1 数据库准备

1.2 实体类 

1.2.1 IdCard.java

 1.2.2 Person.java

 1.3 用户配置接口

1.3.1 IdCardMapper.java

1.3.2 Person.java

1.4 用户配置文件

1.4.1 IdCardMapper.xml

1.4.2 PersonMapper.xml

1.5 解决方法

1.5.1 嵌套查询方式

 1.5.2 嵌套结果方式

1.5.2.1 PersonMapper.java 添加

1.5.2.2 PersonMapper.xml添加

 1.5.2.3 PersonTest.java添加

 1.5.2.4 测试结果

1.一对一查询

问题：根据用户Id查询Person的信息，包括IdCard的信息，用户和IdCard是一对一的关系

1.1 数据库准备

USE mybatis;
# 创建一个名称为tb_idcard的表
CREATE TABLE  tb_idcard( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     CODE VARCHAR(18)
);
# 插入2条数据
INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
# 创建一个名称为tb_person的表
CREATE TABLE  tb_person( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(32),
     age INT,
     sex VARCHAR(8),
     card_id INT UNIQUE,     
     FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
);
# 插入2条数据
INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',2);

1.2 实体类 

1.2.1 IdCard.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class IdCard {
    private Integer id;
    private String code;
}

 1.2.2 Person.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private IdCard card;
}

（此处我用的是idcard）

 1.3 用户配置接口

1.3.1 IdCardMapper.java

package com.biem.mapper;

public interface IdCardMapper{
}

1.3.2 Person.java

package com.biem.mapper;

public interface PersonMapper {
}

1.4 用户配置文件

1.4.1 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.biem.mapper.IdCardMapper">


</mapper>

1.4.2 PersonMapper.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.biem.mapper.PersonMapper">


</mapper>

1.5 解决方法

1.5.1 嵌套查询方式

1.5.1.1 IdCardMapper.java

package com.biem.mapper;

import com.biem.pojo.IdCard;

public interface IdCardMapper {
    public IdCard findCodeById(Integer id);
}

 

1.5.1.2 PersonMapper.java

package com.biem.mapper;

import com.biem.pojo.Person;

public interface PersonMapper {
    public Person findPersonById(Integer id);
}

 

1.5.1.3 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.biem.mapper.IdCardMapper">

    <!--public IdCard findCodeById(Integer id);-->
    <select id="findCodeById" parameterType="Integer" resultType="IdCard">
        select * from tb_idcard where id=#{id}
    </select>
</mapper>

 

1.5.1.4 PersonMapper.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.biem.mapper.PersonMapper">
    <!-- public Person findPersonById(Integer id); -->
    <resultMap id="IdCardWithPersonResult" type="Person">
        <id property="id" column="id"></id>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <!-- 一对一：association使用select 属性引入另外一条SQL语句 -->
        <association property="card" column="card_id" javaType="IdCard"
                     select="com.biem.mapper.IdCardMapper.findCodeById"/>
    </resultMap>

    <select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
        select * from tb_person where id = #{id}
    </select>
</mapper>

 

1.5.1.5 PersonTest.java

package com.biem.test;

import com.biem.mapper.PersonMapper;
import com.biem.pojo.Person;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class PersonTest {

    @Test
    public void testFindPersonById(){
        SqlSession session = MyBatisUtil.openSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person = mapper.findPersonById(1);
        System.out.println(person);
        session.close();
    }
}

1.5.1.6 测试结果

 1.5.2 嵌套结果方式

1.5.2.1 PersonMapper.java 添加

public Person findPersonByIdWithNestedResults(Integer id);

1.5.2.2 PersonMapper.xml添加

<!--public Person findPersonByIdWithNestedResults(Integer id);-->
    <resultMap id="IdCardWithPersonResult2" type="Person">
        <id property="id" column="id"></id>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>

        <association property="card" javaType="IdCard">
            <id property="id" column="card_id"></id>
            <result property="code" column="code"></result>
        </association>
    </resultMap>
    <select id="findPersonByIdWithNestedResults" parameterType="Integer" resultMap="IdCardWithPersonResult">
        select p.*, idcard.code
        from tb_person p, tb_idcard idcard
        where p.card_id = idcard.id and p.id = #{id}
    </select>

 

 1.5.2.3 PersonTest.java添加

    @Test
    public void testFindPersonByIdWithNestedResults(){
        SqlSession session = MyBatisUtil.openSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person = mapper.findPersonByIdWithNestedResults(1);
        System.out.println(person);
        session.close();
    }

 1.5.2.4 测试结果

---