Mybatis关系映射一对一的关系

表结构：

t_person表 个人表

 t_idcard表 身份证表

身份证与个人一对一关联

实体映射类：

@Data
public class IdCard {
    private Integer id;
    private String code;


}

@Data
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    /**
     * 个人证号ID
     */
    private IdCard card;
}

 配置mapper文件

IdCardMapper

<?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="ssm.mapper.IdCardMapper">
    <!-- 根据id查询证件信息 -->
    <select id="findCodeById" parameterType="Integer" resultType="ssm.pojo.IdCard">
	  SELECT * from t_idcard where id=#{id}
  </select>
</mapper>

PersonMapper

<?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="ssm.mapper.PersonMapper">
    <!-- 嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
    <select id="findPersonById" parameterType="Integer"
            resultMap="IdCardWithPersonResult">
		SELECT * from t_person where id=#{id}
	</select>
    <resultMap type="ssm.pojo.Person" id="IdCardWithPersonResult">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
      <!-- card 对应的是IdCard card 值  javaType是类可简写
        <association property="card" column="card_id" javaType="ssm.pojo.IdCard"
                     select="ssm.mapper.IdCardMapper.findCodeById" />
    </resultMap>

  <resultMap id="PersonAndIdCard" type="mybatis.pojo.Person" >
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="card" javaType="mybatis.pojo.IdCard">
            <id property="id" column="cardId" />
            <result property="code" column="code" />
        </association>
    </resultMap>

    <!-- 嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 -->
    <select id="findPersonById" parameterType="Integer"
            resultMap="PersonAndIdCard">
     SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.id= #{id}
	</select>
</mapper>

 

 

测试执行：

2021-02-03 09:44:07 [ main:731 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.id= ? 
2021-02-03 09:44:07 [ main:787 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 09:44:07 [ main:819 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 1
2021-02-03 09:44:07 [ main:821 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:123) Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@16022d9d]
2021-02-03 09:44:07 [ main:822 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:91) Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@16022d9d]
2021-02-03 09:44:07 [ main:823 ] - [ DEBUG ] org.apache.ibatis.datasource.pooled.PooledDataSource.pushConnection(PooledDataSource.java:363) Returned connection 369241501 to pool.
Person(id=1, name=小米, age=12, sex=男, card=IdCard(id=1, code=2222222))

代码：

/**
 * @author Administrator
 */
public interface PersonDao {
    /**
     * 根据id查找
     * @param id
     * @return
     */
     Person findPersonById(Integer id);

    /**
     * concat 拼接参数模糊查找
     * @param name
     * @return
     */
     List<Person> findPersonByName(String name);

    /**
     * bind使用模糊查找
     * @param name
     * @return
     */
     List<Person> findPersonByNameWithBound(Person person);

    /**
     * 使用like 模糊查询
     * @param name
     * @return
     */
     List<Person> findPersonByNameByLike(String name);
}

 

public class PersonDaoImpl implements PersonDao {
    private SqlSessionFactory sqlSessionFactory ;
    public  PersonDaoImpl(SqlSessionFactory sqlSessionFactory){
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public Person findPersonById(Integer id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Person person = sqlSession.selectOne("mybatis.mapper.PersonMapper.findPersonById",id);
        sqlSession.close();
        return person;
    }

    @Override
    public List<Person> findPersonByName(String name) {
        List<Person>personList = new ArrayList<>();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByName",name);
        sqlSession.close();
        return personList;
    }

    @Override
    public List<Person> findPersonByNameWithBound(Person person) {
        List<Person>personList = new ArrayList<>();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByNameWithBound",person);
        sqlSession.close();
        return personList;
    }

    @Override
    public List<Person> findPersonByNameByLike(String name) {
        List<Person>personList = new ArrayList<>();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        personList = sqlSession.selectList("mybatis.mapper.PersonMapper.findPersonByNameWithBound",name);
        sqlSession.close();
        return personList;
    }
}

 

<?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="mybatis.mapper.PersonMapper">

    <resultMap id="PersonAndIdCard" type="mybatis.pojo.Person" >
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="card" javaType="mybatis.pojo.IdCard">
            <id property="id" column="cardId" />
            <result property="code" column="code" />
        </association>
    </resultMap>

    <!-- 嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 -->
    <select id="findPersonById" parameterType="Integer"
            resultMap="PersonAndIdCard">
     SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.id= #{id}
	</select>
    
    <select id="findPersonByNameByLike"  parameterType="String"  resultMap="PersonAndIdCard">
        SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.name like '%${value}%'
    </select>

    <select id="findPersonByName"  parameterType="String"  resultMap="PersonAndIdCard">
        SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.name like concat('%','${value}','%')
    </select>

    <select id="findPersonByNameWithBound"  parameterType="mybatis.pojo.Person"  resultMap="PersonAndIdCard">
        <bind name="name" value="'%'+_parameter.getName()+'%'"/>
        
        SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.name like  #{name}
    </select>



</mapper>

 

<?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="mybatis.mapper.IdCardMapper">
    <!-- 根据id查询证件信息 -->
    <select id="findCodeById" parameterType="Integer" resultType="mybatis.pojo.IdCard">
	  SELECT * from t_idcard where id=#{id}
  </select>

    <insert id="insertIdCard" parameterType="mybatis.pojo.IdCard"  useGeneratedKeys="true" >
        <selectKey keyColumn="id" order="AFTER" resultType="Integer"  statementType="PREPARED">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO t_idcard (code)
        VALUES (#{code})
    </insert>

    <!-- 对于不支持自动生成主键的数据库，或取消自主增长规则的数据库可以自定义主键生成规则 -->
    <insert id="insertCard" parameterType="mybatis.pojo.IdCard">
        <selectKey keyProperty="id"  keyColumn="id" resultType="Integer" order="BEFORE">
            select if(max(id) is null, 1, max(id) +1) as id from t_idcard
        </selectKey>
        insert into t_idcard(id,code) values(#{id},#{code})
    </insert>
</mapper>

 

测试类：

public class MybatisTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws Exception {
        SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        sqlSessionFactory = sessionFactoryBuilder.build(inputStream);
    }

    @Test
    public void TestfindPersonById() {
        PersonDao personDao = new PersonDaoImpl(sqlSessionFactory);
        Person person = personDao.findPersonById(1);
        System.out.println(person);

    }

    @Test
    public void TestfindPersonByName() {
        PersonDao personDao = new PersonDaoImpl(sqlSessionFactory);
        List<Person> persons = personDao.findPersonByName("张");
        persons.forEach((person)-> System.out.println(person));;

    }
}