MyBatis学习总结之一对一映射

知识点：JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是映射到list集合属性中pojo的类型。

本次mybatis的练习是使用的springboot+mybatis

第一步：在mysql 数据库创建students 和cards表并插入数据

create table cards(

	cid int(5) primary key,

	cnum varchar(10)

);

create table students(

	sid int(5) primary key,

	sname varchar(10),

	scid int(5),

	constraint scid_fk foreign key(scid) references cards(cid)

);

insert into cards(cid,cnum) values(1,'111');

insert into students(sid,sname,scid) values(1,'哈哈',1);

select * from cards;

select * from students;

第二步：配置springboot配置文件application.properties并在该文件添加以下配置信息

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/diamond?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true

spring.datasource.username=root

spring.datasource.password=123456

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

第三步：创建Domain: Student.java 和Card.java

import lombok.Data;

@Data

public class Card {

    private Integer id;

    private String num;

}

@Data

public class Student {

    private Integer id;

    private String name;

    private Card card;

}

第四步：创建StudentCardMapper接口　

import com.longteng.diamond.domain.Student;

import org.apache.ibatis.annotations.Mapper;

@Mapper

public interface StudentCardMapper {

    /**

     * 查询1号学生的信息

     * @param id 表示学生的编号

     */

    Student selectById(Integer id);

}

第五步：创建StudentCardMapper.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.longteng.diamond.dao.StudentCardMapper">

    <resultMap id="studentCardMap" type="com.longteng.diamond.domain.Student">

        <id property="id" column="sid" jdbcType="INTEGER"></id>

        <result property="name" column="sname" jdbcType="VARCHAR"></result>

        <!-- 引入CardMapper.xml文件中的映射信息

             property表示Student类的关联属性

             resultMap表示引入CardMapper.xml文件的映射类型

        -->

    </resultMap>

    <select id="selectById" parameterType="java.lang.Integer" resultMap="studentCardMap">

      select s.sid,s.sname,s.scid,c.cnum，c.cid

      from students s inner join cards c

      on s.scid= c.cid and s.sid=#{xx}

    </select>

</mapper>

第六步：创建Controller

import com.longteng.diamond.dao.StudentCardMapper;

import com.longteng.diamond.domain.Student;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;

@Controller

public class StudentCardController {

    @Resource

    StudentCardMapper studentCardMapper;

    @RequestMapping("/testMapper")

    public @ResponseBody  String test(){

        Student student = studentCardMapper.selectById(1);

        return student.toString();

    }

}

第七步：调用接口http://localhost/testMapper执行结果如下：

Student(id=1, name=哈哈, card=null)

其中card = null 是由于在第五步操作中resultMap 中没有关联card表里面的属性，而在select 查询中却查询了card表的属性值，解决办法有2种

方法1：直接将collection集合元素的属性写为collection的子标签

<?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.longteng.diamond.dao.StudentCardMapper">

    <resultMap id="studentCardMap" type="com.longteng.diamond.domain.Student">

        <id property="id" column="sid" jdbcType="INTEGER"></id>

        <result property="name" column="sname" jdbcType="VARCHAR"></result>

        <result property="cid" column="scid" jdbcType="INTEGER"></result>

        <!-- 引入CardMapper.xml文件中的映射信息

             property表示Student类的关联属性

             resultMap表示引入CardMapper.xml文件的映射类型

        -->

        <collection property="card" ofType="com.longteng.diamond.domain.Card">

            <id property="id" column="cid" ></id>

            <result property="num" column="cnum" jdbcType="VARCHAR"></result>

        </collection>

    </resultMap>

    <select id="selectById" parameterType="java.lang.Integer" resultMap="studentCardMap">

      select s.sid,s.sname,s.scid,c.cnum,c.cid

      from students s inner join cards c

      on s.scid= c.cid and s.sid=#{xx}

    </select>

</mapper>

调用接口http://localhost/testMapper执行结果如下：

Student(id=1, name=哈哈, card=Card(id=1, num=111))

方法2：通过在collection标签中引用别的mapper的查询方法

第一步创建CardMapper接口

import com.longteng.diamond.domain.Card;

import org.apache.ibatis.annotations.Mapper;

@Mapper

public interface CardMapper {

    Card findCard(Integer id);

}

第二步创建CardMapper.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.longteng.diamond.dao.CardMapper">

    <resultMap id="cardMapper" type="com.longteng.diamond.domain.Card">

        <id property="id" column="cid" jdbcType="INTEGER"></id>

        <result property="num" column="cnum" jdbcType="VARCHAR"></result>

    </resultMap>

    <select id="findCard"  resultMap="cardMapper">

        SELECT * from cards where cid=#{0};

    </select>

</mapper>

第三步在StudentCardMapper.xml文件里面引入CardMapper.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.longteng.diamond.dao.StudentCardMapper">

    <resultMap id="studentCardMap" type="com.longteng.diamond.domain.Student">

        <id property="id" column="sid" jdbcType="INTEGER"></id>

        <result property="name" column="sname" jdbcType="VARCHAR"></result>

        <result property="cid" column="scid" jdbcType="INTEGER"></result>

           <!-- 方法2：

            引入CardMapper.xml文件中的映射信息

             property表示Student类的关联属性

        -->

        <collection property="card" column="scid" jdbcType="INTEGER" select="com.longteng.diamond.dao.CardMapper.findCard"/>

    </resultMap>

    <select id="selectById" parameterType="java.lang.Integer" resultMap="studentCardMap">

      select s.sid,s.sname,s.scid,c.cnum,c.cid

      from students s inner join cards c

      on s.scid= c.cid and s.sid=#{xx}

    </select>

</mapper>

巴特西