（五）测试学习JavaWeb之MyBatis上篇

作者: Tomandy | 来源:发表于2019-03-24 00:20 被阅读49次

前言

Web开发离不开数据库的操作，该篇文章咱们一起来学习MyBatis 这款优秀的持久层框架，官网对MyBatis描述如下。

MyBatis支持定制化 SQL、存储过程以及高级映射。它避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

原理

每个基于 MyBatis 的应用都是以一个 SqlSessionFactory 的实例为中心的。SqlSessionFactory 的实例可以通过 SqlSessionFactoryBuilder 获得。而 SqlSessionFactoryBuilder 则可以从 XML 配置文件或一个预先定制的 Configuration 的实例构建出 SqlSessionFactory 的实例。

下面以mysql为例，通过实现数据库增删改查、多表关联、动态sql、整合Spring等功能来逐步深入学习MyBatis。

pom依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.8</version>
</dependency>

<!-- mysql-connector -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.29</version>
</dependency>

MyBatis增删改查入门

项目结构

1、建表

CREATE TABLE mybatis(
idno VARCHAR(30) PRIMARY KEY,
idtype INT(2),
username VARCHAR(30),
phone VARCHAR(20),
address VARCHAR(100)
);

2、新建实体类

建立与数据库映射的是实体类DataPoolEntity。

package com.entity;

public class DataPoolEntity{

    private String idno;
    private int idtype;
    private String username;
    private String phone;
    private String address;

    public DataPoolEntity(){
    }

    public DataPoolEntity(String idno,int idtype,String username,String phone,String address){
        this.idno = idno;
        this.idtype = idtype;
        this.username = username;
        this.phone = phone;
        this.address = address;
    }

    public String getIdno() {
        return idno;
    }

    public int getIdtype() {
        return idtype;
    }

    public String getUsername() {
        return username;
    }

    public String getPhone() {
        return phone;
    }

    public String getAddress() {
        return address;
    }

    public void setIdno(String idno) {
        this.idno = idno;
    }

    public void setIdtype(int idtype) {
        this.idtype = idtype;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

3、新建映射接口

package com.mapper;

import com.entity.DataPoolEntity;

import java.util.List;

public interface DataPoolMapper {

    //根据idno查询
    public DataPoolEntity selectByIdno(String idno);

    //根据姓名和手机查询
    public List<DataPoolEntity> selectByUsernameAndPhone(String username, String phone);

    //新增记录
    public void insertData(DataPoolEntity dataPoolEntity);

    //删除记录
    public void deleteByIdno(String idno);

    //更新记录
    public void updateData(DataPoolEntity dataPoolEntity);
}

4、新建工具类

package com.utils;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSessionFactory getSqlSessionFactory(){
        if(sqlSessionFactory == null){
            InputStream inputStream = MybatisUtil.class.getClassLoader().getResourceAsStream("mybatisConfig.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }

        return sqlSessionFactory;
    }

    public static SqlSession openSession(){
        return getSqlSessionFactory().openSession();
    }
}

5、新建配置文件

mysql.properties定义数据库的链接信息，下文的mybatisConfig.xml配置文件可通过properties属性引用。

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/iiaccount_datapool
jdbc.username=root
jdbc.password=root

mybatisMapper.xml为映射文件，定义了sql语句的参数及返回类型等，与第3点的映射接口相关联。如果表的列名与实体类的成员变量名不一致的话，此时不应该使用resultType，而应该使用resultMap，否则mybatis将无法进行映射，下文会举例验证。

<?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">
<!--namespace为映射接口全路径，否则会报错-->
<mapper namespace="com.mapper.DataPoolMapper">
    <!--id名称与DataPoolMapper接口的方法名一致-->
    <insert id="insertData" useGeneratedKeys="true" keyProperty="idno">
        insert into mybatis(idno,idtype,username,phone,address) values (#{idno},#{idtype},
        #{username},#{phone},#{address})
    </insert>
    
    <update id="updateData">
        update mybatis set username=#{username},phone=#{phone},address=#{address} where idno
        = #{idno}
    </update>

    <delete id="deleteByIdno">
        delete from mybatis where idno = #{idno}
    </delete>

    <!--id与mapper类方法名一致-->
    <!--entity为mybatisConfig.xml创建的别名-->
    <!--可以使用resultType或resultMap-->
    <!--如果是多参数则不能使用parameterType， 改用#｛index｝来表示selectByUsernameAndPhone方法的第几个参数，索引从0开始-->
    <select id="selectByUsernameAndPhone" resultType="entity">
        select * from mybatis where username = #{0} and phone = #{1}
    </select>

    <!--单个参数可以使用parameterType-->
    <select id="selectByIdno" parameterType="String" resultType="entity">
        select * from mybatis where idno = #{idno}
    </select>
</mapper>

mybatisConfig.xml是 mybatis 用来建立 SqlSessionFactory用的，具体运用见第4点的工具类MybatisUtil 。该配置文件包含了数据库的链接信息以及数据库映射文件mybatisMapper.xml。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="mysql.properties">
    </properties>

    <typeAliases>
        <!--定义别名-->
        <typeAlias type="com.entity.DataPoolEntity" alias="entity"/>
    </typeAliases>


    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
            <!-- POOLED 表示支持JDBC数据源连接池 -->
            <!-- UNPOOLED 表示不支持数据源连接池 -->
            <!-- JNDI 表示支持外部数据源连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mybatisMapper.xml"/>
    </mappers>
</configuration>

6、增删改查验证

新建测试类MybatisTest验证增删改查操作

package com.mybatis;

import com.entity.DataPoolEntity;
import com.mapper.DataPoolMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class MybatisTest {

    public static void main(String[] args) {
        insert();
        //delete();
        selectByUsernameAndPhone();
        update();
        selectById();

    }

    public static void insert() {
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        DataPoolEntity dataPoolEntity = new DataPoolEntity("4408810", 1, "Tomandy", "13692464000", "PK");
        try {
            dataPoolMapper.insertData(dataPoolEntity);
            sqlSession.commit();
            System.out.println("新增成功！");
            System.out.println("----------------");
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }

    }

    public static void update() {
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        //先查询再修改
        DataPoolEntity dataPoolEntity = dataPoolMapper.selectByIdno("4408810");
        dataPoolEntity.setAddress("XIAN");
        dataPoolEntity.setPhone("13692464999");
        dataPoolEntity.setUsername("Tomandy123");

        try {
            dataPoolMapper.updateData(dataPoolEntity);
            sqlSession.commit();
            System.out.println("修改成功");
            System.out.println("----------------");
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }

    public static void delete() {
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        try {
            dataPoolMapper.deleteByIdno("4408810");
            sqlSession.commit();
            System.out.println("删除成功！");
            System.out.println("----------------");
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }

    }

    public static void selectById(){
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        DataPoolEntity dataPoolEntity = dataPoolMapper.selectByIdno("4408810");
        System.out.println("selectById查询信息如下：");
        System.out.println("idno:"+dataPoolEntity.getIdno());
        System.out.println("idtype:"+dataPoolEntity.getIdtype());
        System.out.println("username:"+dataPoolEntity.getUsername());
        System.out.println("phone:"+dataPoolEntity.getPhone());
        System.out.println("address:"+dataPoolEntity.getAddress());
        System.out.println("----------------");
    }

    public static void selectByUsernameAndPhone(){
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        List<DataPoolEntity> dataPoolEntityList  = dataPoolMapper.selectByUsernameAndPhone("Tomandy","13692464000");
        for(DataPoolEntity dataPoolEntity:dataPoolEntityList){
            System.out.println("selectByUsernameAndPhone查询信息如下：");
            System.out.println("idno:"+dataPoolEntity.getIdno());
            System.out.println("idtype:"+dataPoolEntity.getIdtype());
            System.out.println("username:"+dataPoolEntity.getUsername());
            System.out.println("phone:"+dataPoolEntity.getPhone());
            System.out.println("address:"+dataPoolEntity.getAddress());
            System.out.println("----------------");
        }
    }

}

执行结果如下

新增成功！
----------------
selectByUsernameAndPhone查询信息如下：
idno:4408810
idtype:1
username:Tomandy
phone:13692464000
address:PK
----------------
修改成功
----------------
selectById查询信息如下：
idno:4408810
idtype:1
username:Tomandy123
phone:13692464999
address:XIAN
----------------

7、xml映射文件resultType和resultMap的区别

上文提到，如果表的列名与实体类的成员变量名不一致的话，此时不应该使用resultType，而应该使用resultMap，下面通过例子来进行验证。
修改实体类原成员变量username为username_，其他配置文件还是跟上文的一致。

package com.entity;

public class DataPoolEntity{

    private String idno;
    private int idtype;
    private String username_;
    private String phone;
    private String address;

    public DataPoolEntity(){
    }

    public DataPoolEntity(String idno,int idtype,String username,String phone,String address){
        this.idno = idno;
        this.idtype = idtype;
        this.username_ = username;
        this.phone = phone;
        this.address = address;
    }

    public String getIdno() {
        return idno;
    }

    public int getIdtype() {
        return idtype;
    }

    public String getUsername_() {
        return username_;
    }

    public String getPhone() {
        return phone;
    }

    public String getAddress() {
        return address;
    }

    public void setIdno(String id_no) {
        this.idno = id_no;
    }

    public void setIdtype(int idtype) {
        this.idtype = idtype;
    }

    public void setUsername_(String username) {
        this.username_ = username;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

编写测试类进行验证。

package com.mybatis;

import com.entity.DataPoolEntity;
import com.mapper.DataPoolMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class MybatisTest {

    public static void main(String[] args) {
        selectById();
    }

    public static void selectById(){
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        DataPoolEntity dataPoolEntity = dataPoolMapper.selectByIdno("4408810");
        System.out.println("selectById查询信息如下：");
        System.out.println("idno:"+dataPoolEntity.getIdno());
        System.out.println("idtype:"+dataPoolEntity.getIdtype());
        System.out.println("username:"+dataPoolEntity.getUsername_());
        System.out.println("phone:"+dataPoolEntity.getPhone());
        System.out.println("address:"+dataPoolEntity.getAddress());
        System.out.println("----------------");
    }
}

运行测试，输出如下，可发现username输出了null，这说明映射失败。

selectById查询信息如下：
idno:4408810
idtype:1
username:null
phone:13692464000
address:XIAN
----------------

我们把mybatisMapper.xml改为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="com.mapper.DataPoolMapper">
    
    <resultMap id="map" type="entity">
        <!-- id 属性专门用来映射主键信息，其他信息用result节点
        column 数据库字段
        property 实体类成员属性 -->
        <id column="idno" property="idno"/>
        <result column="idtype" property="idtype"/>
        <result column="username" property="username_"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
    </resultMap>

    <!--单个参数可以使用parameterType-->
    <select id="selectByIdno" parameterType="String" resultMap="map">
        select * from mybatis where idno = #{idno}
    </select>
</mapper>

接着运行上面的测试类，可发现输出结果中username不再为null。

selectById查询信息如下：
idno:4408810
idtype:1
username:Tomandy
phone:13692464000
address:XIAN
----------------

由此可见，resultMap 仅仅是作用数据库表字段跟实体类的属性的映射关系而存在，当需要映射的实体类的属性跟数据库字段不一样的时候使用，
但是如果数据库字段名跟需要映射的实体类的属性名完全一致，resultMap可以不用，resultType足够了。

8、映射文件属性“useGeneratedKeys”和“keyProperty”详解

上文的mybatisMapper.xml配置文件insert中用到了“useGeneratedKeys”和“keyProperty”属性（由于上文例子没有用到自增列主键，这两个属性没有实际意义），其中，useGeneratedKeys设置为 true 时，表示如果插入的表id以自增列为主键，则允许 JDBC 支持自动生成主键，并可将自动生成的主键id返回，举例如下：

<insert id="insert" parameterType="user"   
        useGeneratedKeys="true" keyProperty="id">  
        insert into user(user_id,user_name,  
            user_phone)  
        values(#{id},#{name},#{phone})  
    </insert>

Mybatis执行完插入语句后，自动将自增长值赋值给对象user的属性id，可通过user实体类对应的getterId()方法获取！
另外，对于useGeneratedKeys和keyProperty的用法，官网也给了相应的说明。

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

如果你的数据库支持自动生成主键的字段（比如 MySQL 和 SQL Server），那么你可以设置 useGeneratedKeys=”true”，然后再把 keyProperty 设置到目标属性上就OK了。例如，如果上面的 Author 表已经对 id 使用了自动生成的列类型，那么语句可以修改为:

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

MyBatis多表关联操作

1、ResultMap详解

在开始多表关联操作学习之前，首先来深入了解一下ResultMap，前文提到ResultMap是作用数据库表字段跟实体类属性的映射关系而存在的。结合官网的相关例子，我们来学习下ResultMap的高级用法。
resultMap元素里包含了以下子元素。

名称	用途
id	一个 ID 结果;标记出作为 ID 的结果可以帮助提高整体性能
result	注入到字段或 JavaBean 属性的普通结果
constructor	用于在实例化类时，注入结果到构造方法中
association	一个复杂类型的关联;许多结果将包装成这种类型
collection	一个复杂类型的集合
discriminator	使用结果值来决定使用哪个 resultMap

id和result子元素上文已举例说明，下面通过举例对其他几个子元素做详细的说明。

constructor子元素举例说明
mabatisMapper.xml配置，column属性顺序需与实体类构造函数入参顺序一致，官网也提供了顺序不一致的解决方法，详情参考官网。

<?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.mapper.DataPoolMapper">
    
    <resultMap id="map" type="entity">
        <!-- id 属性专门用来映射主键信息，其他信息用result节点
        column 数据库字段
        property 实体类成员属性 -->
        <!--
        <id column="idno" property="idno"/>
        <result column="idtype" property="idtype"/>
        <result column="username" property="username_"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
        -->
        <constructor>
            <idArg column="idno" javaType="String"/>
            <arg column="idtype" javaType="_int"/>
            <arg column="username" javaType="String"/>
            <arg column="phone" javaType="String"/>
            <arg column="address" javaType="String"/>
        </constructor>
    </resultMap>
    
    <!--单个参数可以使用parameterType-->
    <select id="selectByIdno" parameterType="String" resultMap="map">
        select * from mybatis where idno = #{idno}
    </select>
</mapper>

编写实体类DataPoolEntity

package com.entity;

public class DataPoolEntity{

    private String idno;
    private int idtype;
    private String username_;
    private String phone;
    private String address;

    public DataPoolEntity(){
    }

    public DataPoolEntity(String idno,int idtype,String username,String phone,String address){
        this.idno = idno;
        this.idtype = idtype;
        this.username_ = username;
        this.phone = phone;
        this.address = address;
    }

    public String getIdno() {
        return idno;
    }

    public int getIdtype() {
        return idtype;
    }

    public String getUsername_() {
        return username_;
    }

    public String getPhone() {
        return phone;
    }

    public String getAddress() {
        return address;
    }
}

编写测试类MybatisTest

package com.mybatis;

import com.entity.DataPoolEntity;
import com.mapper.DataPoolMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class MybatisTest {

    public static void main(String[] args) {
        selectById();
    }
     public static void selectById(){
        SqlSession sqlSession = MybatisUtil.openSession();
        DataPoolMapper dataPoolMapper = sqlSession.getMapper(DataPoolMapper.class);

        DataPoolEntity dataPoolEntity = dataPoolMapper.selectByIdno("4408810");
        System.out.println("selectById查询信息如下：");
        System.out.println("idno:"+dataPoolEntity.getIdno());
        System.out.println("idtype:"+dataPoolEntity.getIdtype());
        System.out.println("username:"+dataPoolEntity.getUsername_());
        System.out.println("phone:"+dataPoolEntity.getPhone());
        System.out.println("address:"+dataPoolEntity.getAddress());
        System.out.println("----------------");
    }

执行后输出结果如下：

selectById查询信息如下：
idno:4408810
idtype:1
username:Tomandy
phone:13692464000
address:PK
----------------

对比前文配置的mybatisMapper.xml文件和实体类，可发现使用constructor子元素的话，mybatis也可映射成功。

association子元素举例说明
association用于一对一的关联，常见的有三种用法，下面举例说明。

场景：
一个用户有一张携程的会员卡。分别对应客户信息表和会员卡信息表，通过id关联。

先建表并insert记录。

#客户信息表
drop table if exists customer;
create table customer(
id varchar(20) primary key,
idtype int(2),  #证件类型
idno varchar(30), #证件号码
username varchar(50) not null, #名称
sex varchar(10) not null #性别
);

#会员卡表
drop table if exists membershipCard;
create table membershipCard(
id varchar(20) primary key,
level int(2) not null, #会员等级
phone varchar(20) not null, #电话
address varchar(100) #地址
);

insert into customer(id,idtype,idno,username,sex) values("123",1,"44088118","Tomandy","male")；
insert into membershipcard(id,level,phone,address) values("123",1,"18601111","Pk");

新建实体类CustomerEntity及MembershipCardEntity。

package com.entity;

public class CustomerEntity {

    private String id;
    private int idtype;
    private String idno;
    private String username;
    private String sex;
    private MembershipCardEntity membershipCardEntity;

    public String getId() {
        return id;
    }

    public int getIdtype() {
        return idtype;
    }

    public String getIdno() {
        return idno;
    }

    public String getUsername() {
        return username;
    }

    public String getSex() {
        return sex;
    }

    public MembershipCardEntity getMembershipCardEntity() {
        return membershipCardEntity;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setIdtype(int idtype) {
        this.idtype = idtype;
    }

    public void setIdno(String idno) {
        this.idno = idno;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setMembershipCardEntity(MembershipCardEntity membershipCardEntity) {
        this.membershipCardEntity = membershipCardEntity;
    }
}

package com.entity;

public class MembershipCardEntity {

    private String id;
    private int level;
    private String phone;
    private String address;

    public String getId() {
        return id;
    }

    public int getLevel() {
        return level;
    }

    public String getPhone() {
        return phone;
    }

    public String getAddress() {
        return address;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setLevel(int level) {
        this.level = level;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

新建映射接口XieChengMapper

package com.mapper;

import com.entity.CustomerEntity;

public interface XieChengMapper {

    CustomerEntity queryById(String id);
}

association第一种用法：
新建映射配置文件xiechengMapper.xml，注意resultMap里面用到了association子元素。

<?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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="cutomerEntity">
        <id property="id" column="id"/>
        <result property="idtype" column="idtype"/>
        <result property="idno" column="idno"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <!--property属性值需要在CustomerEntity有相应的getter方法-->
        <!--javaType为mybatisConfig.xml定义com.entity.MembershipCardEntity的别名-->
        <association property="membershipCardEntity" javaType="membershipCardEntity">
            <id property="id" column="id"/>
            <result property="level" column="level"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>
    
    <select id="queryById" resultMap="cmmap" parameterType="String">
        select
        a.id,a.sex,a.idno,a.username,b.phone,b.address
        from customer a,membershipCard b
        where a.id = b.id
        and a.id = #{id}
    </select>

</mapper>

添加映射文件到mybatisConfig.xml配置文件，相比上文的配置文件，此处增加了cutomerEntity，membershipCardEntity两个别名，另外也引入了xiechengMapper.xml。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="mysql.properties">
    </properties>

    <typeAliases>
        <!--定义别名-->
        <typeAlias type="com.entity.DataPoolEntity" alias="entity"/>
        <typeAlias type="com.entity.CustomerEntity" alias="cutomerEntity"/>
        <typeAlias type="com.entity.MembershipCardEntity" alias="membershipCardEntity"/>
    </typeAliases>


    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
            <!-- POOLED 表示支持JDBC数据源连接池 -->
            <!-- UNPOOLED 表示不支持数据源连接池 -->
            <!-- JNDI 表示支持外部数据源连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mybatisMapper.xml"/>
        <!--此处新加了映射配置文件-->
        <mapper resource="xiechengMapper.xml"/>
    </mappers>
</configuration>

编写测试类

package com.mybatis;

import com.entity.CustomerEntity;
import com.mapper.XieChengMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

public class XieChengTest {

    public static void main(String args[]){
        queryByid();
    }

    public static void queryByid(){
        SqlSession sqlSession = MybatisUtil.openSession();
        XieChengMapper xieChengMapper = sqlSession.getMapper(XieChengMapper.class);
        CustomerEntity customerEntity = xieChengMapper.queryById("123");

        System.out.println("id: "+customerEntity.getId());
        System.out.println("idno: "+customerEntity.getIdno());
        System.out.println("username: "+customerEntity.getUsername());
        System.out.println("sex: "+customerEntity.getSex());
        System.out.println("address: "+customerEntity.getMembershipCardEntity().getAddress());
        System.out.println("phone: "+customerEntity.getMembershipCardEntity().getPhone());
    }
}

执行后运行结果输出如下。

id: 123
idno: 44088118
username: Tomandy
sex: male
address: Pk
phone: 18601111

association第二种用法：
修改xiechengMapper.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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="cutomerEntity">
        <id property="id" column="id"/>
        <result property="idtype" column="idtype"/>
        <result property="idno" column="idno"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <!--property属性值需要在CustomerEntity有相应的getter方法-->
        <!--javaType为mybatisConfig.xml定义com.entity.MembershipCardEntity的别名-->
        <!--
        <association property="membershipCardEntity" javaType="membershipCardEntity">
            <id property="id" column="id"/>
            <result property="level" column="level"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
        -->
        <association property="membershipCardEntity" resultMap="mscmap"/>
    </resultMap>

    <resultMap id="mscmap" type="membershipCardEntity">
        <id property="id" column="id"/>
        <result property="level" column="level"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
    </resultMap>
    
    <select id="queryById" resultMap="cmmap" parameterType="String">
        select
        a.id,a.sex,a.idno,a.username,b.phone,b.address
        from customer a,membershipCard b
        where a.id = b.id
        and a.id = #{id}
    </select>

</mapper>

对比一下association第一种用法，区别在于把membershipCardEntity的映射独立放在了一个resultMap里。

association第三种用法：该方法通过association的select属性来实现。假如给membershipCard表加上一列cardname（MembershipCardEntity实体类也加上了cardname对应的getter和setter方法），其值与customer表的username字段一样，修改xiechengMapper.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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="cutomerEntity">
        <id property="id" column="id"/>
        <result property="idtype" column="idtype"/>
        <result property="idno" column="idno"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <!--property属性值需要在CustomerEntity有相应的getter方法-->
        <!--javaType为mybatisConfig.xml定义com.entity.MembershipCardEntity的别名-->
        <!--
        <association property="membershipCardEntity" javaType="membershipCardEntity">
            <id property="id" column="id"/>
            <result property="level" column="level"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
        -->
        <!--
        <association property="membershipCardEntity" resultMap="mscmap"/>
        -->
        <!--如果涉及多参数查询的话，可通过column="{id=id,phone=phone}"格式来传递变量 -->
        <association property="membershipCardEntity" column="{id=id,cardname=username}" select="queryByCardId"/>
    </resultMap>

    <!--
    <resultMap id="mscmap" type="membershipCardEntity">
        <id property="id" column="id"/>
        <result property="level" column="level"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
    </resultMap>
    -->

    <!--对于多参数传递的场景，parameterType属性值需使用Map-->
    <select id="queryByCardId" parameterType="java.util.Map" resultType="membershipCardEntity">
        select id,level,phone,address from membershipCard where id = #{id} and cardname =#{cardname}
    </select>
    <!--
    <select id="queryById" resultMap="cmmap" parameterType="String">
        select
        a.id,a.sex,a.idno,a.username,b.phone,b.address
        from customer a,membershipCard b
        where a.id = b.id
        and a.id = #{id}
    </select>
    -->
    <select id="queryById" resultMap="cmmap" parameterType="String">
        select
        *
        from customer 
        where id = #{id}
    </select>

</mapper>

执行上文的测试类，可发现输出结果也是一样的。由xiechengMapper.xml配置文件的内容，我们也可以推断出先执行了queryById的select语句，然后再把对应列的值传递给queryByCardId的select语句变量进行查询，从而获取查询结果。

collection子元素举例说明
collection用于一对多的关联，与association类似，常见也有三种用法，下面举例说明。

场景：
携程会员可享受多种产品优惠（一对多）。

在上文association例子的基础上，再新建一张产品优惠信息表并insert数据。

drop table if exists product;
create table product(
id varchar(20) not null,
productno varchar(30) not null,
productname varchar(100) not null,
PRIMARY KEY (`id`,`productno`)
);

insert into product(id,productno,productname) values("123","01","hotel");
insert into product(id,productno,productname) values("123","02","train tickets");
insert into product(id,productno,productname) values("123","03","plane tickets");

新增实体类ProductEntity，并修改MembershipCardEntity。

package com.entity;

public class ProductEntity {

    private String id;
    private String productno;
    private String productname;

    public String getId() {
        return id;
    }

    public String getProductno() {
        return productno;
    }

    public String getProductname() {
        return productname;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setProductno(String productno) {
        this.productno = productno;
    }

    public void setProductname(String productname) {
        this.productname = productname;
    }
}

package com.entity;

import java.util.List;

public class MembershipCardEntity {

    private String id;
    private int level;
    private String phone;
    private String address;
    private String cardname;
    private List<ProductEntity> productEntities;

    public String getId() {
        return id;
    }

    public int getLevel() {
        return level;
    }

    public String getPhone() {
        return phone;
    }

    public String getAddress() {
        return address;
    }

    public String getCardname() {
        return cardname;
    }

    public List<ProductEntity> getProductEntities() {
        return productEntities;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setLevel(int level) {
        this.level = level;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public void setCardname(String cardname) {
        this.cardname = cardname;
    }

    public void setProductEntities(List<ProductEntity> productEntities) {
        this.productEntities = productEntities;
    }
}

修改映射接口XieChengMapper如下。

package com.mapper;

import com.entity.CustomerEntity;
import com.entity.MembershipCardEntity;

public interface XieChengMapper {

    CustomerEntity queryById(String id);

    //增加根据id查询优惠产品信息
    MembershipCardEntity queryProductById(String id);
}

collection第一种用法如下：
修改映射文件xiechengMapper.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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="membershipCardEntity">
        <id property="id" column="id"/>
        <result property="level" column="level"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="cardname" column="cardname"/>
        <collection property="productEntities" javaType="java.util.List" ofType="com.entity.ProductEntity">
            <id property="id" column="id"/>
            <id property="productno" column="productno"/>
            <result property="productname" column="productname"/>
        </collection>
    </resultMap>

    <select id="queryProductById" parameterType="String" resultMap="cmmap">
        select * from membershipCard a,product b
        where a.id = b.id
        and
        a.id = #{id}
    </select>

</mapper>

修改测试类如下。

package com.mybatis;

import com.entity.CustomerEntity;
import com.entity.MembershipCardEntity;
import com.entity.ProductEntity;
import com.mapper.XieChengMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

public class XieChengTest {

    public static void main(String args[]){
        queryProductByid();
    }

    public static void queryProductByid(){
        SqlSession sqlSession = MybatisUtil.openSession();
        XieChengMapper xieChengMapper = sqlSession.getMapper(XieChengMapper.class);
        MembershipCardEntity membershipCardEntity = xieChengMapper.queryProductById("123");

        System.out.println("id: "+membershipCardEntity.getId());
        System.out.println("phone: "+membershipCardEntity.getPhone());
        System.out.println("address: "+membershipCardEntity.getAddress());
        System.out.println("cardname: "+membershipCardEntity.getCardname());
        System.out.println("Listsize: "+membershipCardEntity.getProductEntities().size());

        for(ProductEntity productEntity:membershipCardEntity.getProductEntities()){
            System.out.println("------------------------");
            System.out.println("id: "+productEntity.getId());
            System.out.println("productno: "+productEntity.getProductno());
            System.out.println("productname: "+productEntity.getProductname());

        }
    }

}

运行后，输出结果为如下。

id: 123
phone: 18601111
address: Pk
cardname: Tomandy
Listsize: 3
------------------------
id: 123
productno: 01
productname: hotel
------------------------
id: 123
productno: 02
productname: train tickets
------------------------
id: 123
productno: 03
productname: plane tickets

collection第二种用法如下：
修改xiechengMapper.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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="membershipCardEntity">
        <id property="id" column="id"/>
        <result property="level" column="level"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="cardname" column="cardname"/>
        
        <collection property="productEntities" resultMap="pmap"/>

        <!--
        <collection property="productEntities" javaType="java.util.List" ofType="com.entity.ProductEntity">
            <id property="id" column="id"/>
            <id property="productno" column="productno"/>
            <result property="productname" column="productname"/>
        </collection>
        -->
    </resultMap>

    <resultMap id="pmap" type="com.entity.ProductEntity">
        <id property="id" column="id"/>
        <id property="productno" column="productno"/>
        <result property="productname" column="productname"/>
    </resultMap>

    <select id="queryProductById" parameterType="String" resultMap="cmmap">
        select * from membershipCard a,product b
        where a.id = b.id
        and
        a.id = #{id}
    </select>

</mapper>

执行上文的测试类，可发现输出同样的结果。
collection第三种用法如下：
与association第三种用法类似，可通过select属性来实现collection一对多的映射。修改xiechengMapper.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.mapper.XieChengMapper">
    <resultMap id="cmmap" type="membershipCardEntity">
        <id property="id" column="id"/>
        <result property="level" column="level"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="cardname" column="cardname"/>

        <!--
        <collection property="productEntities" resultMap="pmap"/>
        -->

        <!--
        <collection property="productEntities" javaType="java.util.List" ofType="com.entity.ProductEntity">
            <id property="id" column="id"/>
            <id property="productno" column="productno"/>
            <result property="productname" column="productname"/>
        </collection>
        -->

        <collection property="productEntities" column="id" select="queryProductById"/>
    </resultMap>

    <!--
    <resultMap id="pmap" type="com.entity.ProductEntity">
        <id property="id" column="id"/>
        <id property="productno" column="productno"/>
        <result property="productname" column="productname"/>
    </resultMap>
    -->
    
    <select id="queryProductById" parameterType="java.util.Map" resultType="com.entity.ProductEntity">
        select * from product where id = #{id}
    </select>

    <select id="queryCardById" parameterType="String" resultMap="cmmap">
        select * from membershipCard where id = #{id}
    </select>

</mapper>

修改XieChengMapper映射接口如下。

package com.mapper;

import com.entity.CustomerEntity;
import com.entity.MembershipCardEntity;
import com.entity.ProductEntity;

import java.util.List;

public interface XieChengMapper {

    CustomerEntity queryById(String id);

    MembershipCardEntity queryCardById(String id);

    List<ProductEntity> queryProductById(String id);
}

编写测试类

package com.mybatis;

import com.entity.CustomerEntity;
import com.entity.MembershipCardEntity;
import com.entity.ProductEntity;
import com.mapper.XieChengMapper;
import com.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

public class XieChengTest {

    public static void main(String args[]){
        queryProductByid();
    }

    public static void queryProductByid(){
        SqlSession sqlSession = MybatisUtil.openSession();
        XieChengMapper xieChengMapper = sqlSession.getMapper(XieChengMapper.class);
        MembershipCardEntity membershipCardEntity = xieChengMapper.queryCardById("123");

        System.out.println("id: "+membershipCardEntity.getId());
        System.out.println("phone: "+membershipCardEntity.getPhone());
        System.out.println("address: "+membershipCardEntity.getAddress());
        System.out.println("cardname: "+membershipCardEntity.getCardname());
        System.out.println("Listsize: "+membershipCardEntity.getProductEntities().size());

        for(ProductEntity productEntity:membershipCardEntity.getProductEntities()){
            System.out.println("------------------------");
            System.out.println("id: "+productEntity.getId());
            System.out.println("productno: "+productEntity.getProductno());
            System.out.println("productname: "+productEntity.getProductname());

        }
    }

}

运行后发现输出结果与上文的一致。

discriminator子元素举例说明
discriminator称为鉴别器，依照官网的描述，主要用于以下场景。

有时一个单独的数据库查询也许返回很多不同 (但是希望有些关联) 数据类型的结果集。鉴别器元素就是被设计来处理这个情况的, 还有包括类的继承层次结构。鉴别器非常容易理解,因为它的表现很像 Java 语言中的 switch 语句。

关于鉴别器的说明参考文章《MyBatis级联探讨第二篇——鉴别器（discriminator）》。

参考资料

《开发测试的Spring应用》
mybatis实战教程(mybatis in action),mybatis入门到精通
 官方文档

（五）测试学习JavaWeb之MyBatis上篇

前言

原理

pom依赖

MyBatis增删改查入门

1、建表

2、新建实体类

3、新建映射接口

4、新建工具类

5、新建配置文件

6、增删改查验证

7、xml映射文件resultType和resultMap的区别

8、映射文件属性“useGeneratedKeys”和“keyProperty”详解

MyBatis多表关联操作

1、ResultMap详解

参考资料

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

软件测试工程师进阶过程