原文地址：http://www.work100.net/training/monolithic-frameworks-mybatis-orm.html
更多教程：光束云 - 免费课程

MyBatis 对象关系映射

序号	文内章节	视频
1	POM	-
2	构建数据库结构	-
3	定义实体类	-
4	定义数据访问接口	-
5	定义业务逻辑接口	-
6	实现业务逻辑接口	-
7	定义映射文件	-
8	创建单元测试	-
9	实例源码	-

请参照如上章节导航进行阅读

1.POM

修改项目 iot-cloud-dependencies 下的 pom.xml 文件，增加单元测试相关依赖，配置如下：

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.2.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

同时在 iot-cloud-web-admin 项目的 pom.xml 文件中引入依赖：

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
</dependency>

2.构建数据库结构

我们在 Spring 整合 Druid 章节已经创建了数据库，使用了如下属性：

属性	说明
Database Name	training-iot-cloud
Character Set	utf8mb4
Collation	utf8mb4_general_ci

utf8 和 utf8md4 字符集

• utf8

一个字符集存 3 个字节，可存储多国语言的数据。

• utf8mb4

是 utf8 字符集的扩展，一个字符集存 4 个字节，可以存放 emoji 表情包。

导入 SQL

群共享中已经为大家准备好了练习用的 SQL，位置： SQL脚本/training-iot-cloud.zip，有需要的同学可以加群获取：

3.定义实体类

在 iot-cloud-domain 项目下创建实体类

以 auth_manager 表为例，实体类代码如下：

package net.work100.training.stage2.iot.cloud.domain;

import java.io.Serializable;
import java.util.Date;

/**
 * <p>Title: AuthManager</p>
 * <p>Description: 管理员账户表</p>
 * <p>Url: http://www.work100.net/training/monolithic-frameworks-mybatis.html</p>
 *
 * @author liuxiaojun
 * @date 2020-02-23 22:42
 * ------------------- History -------------------
 * <date>      <author>       <desc>
 * 2020-02-23   liuxiaojun     初始创建
 * -----------------------------------------------
 */
public class AuthManager implements Serializable {

    private Long id;
    private String userKey;
    private String userName;
    private String password;
    /**
     * 状态：0=inactive, 1=active, 2=locked, 3=deleted
     */
    private int status;
    private boolean superuser;
    /**
     * 角色：admin, editor
     */
    private String roles;
    private Date modifyPasswordTime;
    private Date created;
    private Date updated;

    public Long getId() {
        return id;
    }

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

    public String getUserKey() {
        return userKey;
    }

    public void setUserKey(String userKey) {
        this.userKey = userKey;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public boolean isSuperuser() {
        return superuser;
    }

    public void setSuperuser(boolean superuser) {
        this.superuser = superuser;
    }

    public String getRoles() {
        return roles;
    }

    public void setRoles(String roles) {
        this.roles = roles;
    }

    public Date getModifyPasswordTime() {
        return modifyPasswordTime;
    }

    public void setModifyPasswordTime(Date modifyPasswordTime) {
        this.modifyPasswordTime = modifyPasswordTime;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    public Date getUpdated() {
        return updated;
    }

    public void setUpdated(Date updated) {
        this.updated = updated;
    }

    @Override
    public String toString() {
        return "AuthManager{" +
                "id=" + id +
                ", userKey='" + userKey + '\'' +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", status=" + status +
                ", superuser=" + superuser +
                ", roles='" + roles + '\'' +
                ", modifyPasswordTime=" + modifyPasswordTime +
                ", created=" + created +
                ", updated=" + updated +
                '}';
    }
}

4.定义数据访问接口

在 iot-cloud-web-admin 项目定义一个 DAO 接口 AuthManagerDao，代码如下：

package net.work100.training.stage2.iot.cloud.web.admin.dao;

import net.work100.training.stage2.iot.cloud.domain.AuthManager;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>Title: AuthManagerDao</p>
 * <p>Description: </p>
 * <p>Url: http://www.work100.net/training/monolithic-frameworks-mybatis.html</p>
 *
 * @author liuxiaojun
 * @date 2020-02-23 22:54
 * ------------------- History -------------------
 * <date>      <author>       <desc>
 * 2020-02-23   liuxiaojun     初始创建
 * -----------------------------------------------
 */
@Repository
public interface AuthManagerDao {

    /**
     * 查询所有
     *
     * @return
     */
    List<AuthManager> selectAll();
}

注意：Spring 集成 MyBatis 后，不需要手动实现 DAO 层的接口，所有的 SQL 执行语句都写在对应的关系映射配置文件中。

5.定义业务逻辑接口

在 iot-cloud-web-admin 项目定义一个 Service 接口 AuthManagerService，代码如下：

package net.work100.training.stage2.iot.cloud.web.admin.service;

import net.work100.training.stage2.iot.cloud.domain.AuthManager;

import java.util.List;

/**
 * <p>Title: AuthManagerService</p>
 * <p>Description: </p>
 * <p>Url: http://www.work100.net/training/monolithic-frameworks-mybatis.html</p>
 *
 * @author liuxiaojun
 * @date 2020-02-23 23:01
 * ------------------- History -------------------
 * <date>      <author>       <desc>
 * 2020-02-23   liuxiaojun     初始创建
 * -----------------------------------------------
 */
public interface AuthManagerService {
    /**
     * 查询全部表记录
     *
     * @return
     */
    List<AuthManager> selectAll();
}

6.实现业务逻辑接口

实现 AuthManagerService 接口，新建实现类 AuthManagerServiceImpl，代码如下：

package net.work100.training.stage2.iot.cloud.web.admin.service.impl;

import net.work100.training.stage2.iot.cloud.domain.AuthManager;
import net.work100.training.stage2.iot.cloud.web.admin.dao.AuthManagerDao;
import net.work100.training.stage2.iot.cloud.web.admin.service.AuthManagerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * <p>Title: AuthManagerServiceImpl</p>
 * <p>Description: </p>
 * <p>Url: http://www.work100.net/training/monolithic-frameworks-mybatis.html</p>
 *
 * @author liuxiaojun
 * @date 2020-02-23 23:06
 * ------------------- History -------------------
 * <date>      <author>       <desc>
 * 2020-02-23   liuxiaojun     初始创建
 * -----------------------------------------------
 */
@Service
public class AuthManagerServiceImpl implements AuthManagerService {

    @Autowired
    private AuthManagerDao authManagerDao;

    @Override
    public List<AuthManager> selectAll() {
        return authManagerDao.selectAll();
    }
}

7.定义映射文件

映射文件，简称为 Mapper，主要完成 DAO 层中 SQL 语句的映射。

映射文件名随意，一般放在 src/resources/mapper 文件夹中，这里映射文件名称定为 AuthManagerMapper.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="net.work100.training.stage2.iot.cloud.web.admin.dao.AuthManagerDao">
    <select id="selectAll" resultType="AuthManager">
        SELECT
          a.id,
          a.user_key,
          a.user_name,
          a.password,
          a.status,
          a.superuser,
          a.roles,
          a.modify_password_time,
          a.created,
          a.updated
        FROM
          auth_manager AS a
    </select>
</mapper>

8.创建单元测试

所有工作准备就绪，我们就可以测试 MyBatis 是否能够正常执行了。

在 iot-cloud-web-admin 项目下，创建 src/test/java 的单元测试源码目录，然后创建类包：

net.work100.training.stage2.iot.cloud.web.admin.service.test

在包下创建一个单元测试类 AuthManagerServiceTest，代码如下：

package net.work100.training.stage2.iot.cloud.web.admin.service.test;

import net.work100.training.stage2.iot.cloud.domain.AuthManager;
import net.work100.training.stage2.iot.cloud.web.admin.dao.AuthManagerDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * <p>Title: AuthManagerServiceTest</p>
 * <p>Description: </p>
 * <p>Url: http://www.work100.net/training/monolithic-frameworks-mybatis.html</p>
 *
 * @author liuxiaojun
 * @date 2020-02-23 23:23
 * ------------------- History -------------------
 * <date>      <author>       <desc>
 * 2020-02-23   liuxiaojun     初始创建
 * -----------------------------------------------
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring-context.xml", "classpath:spring-context-druid.xml", "classpath:spring-context-mybatis.xml"})
public class AuthManagerServiceTest {
    
    @Autowired
    private AuthManagerService authManagerService;

    @Test
    public void testSelectAll() {
        List<AuthManager> authManagers = authManagerService.selectAll();
        for (AuthManager authManager : authManagers) {
            System.out.println("------------------------------------------------");
            System.out.println(authManager.toString());
        }
    }
}

成功执行测试后，控制台输出如下：

2020-02-24 11:23:53,691 INFO [org.springframework.test.context.support.DefaultTestContextBootstrapper] - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener, org.springframework.test.context.event.EventPublishingTestExecutionListener]
2020-02-24 11:23:53,709 INFO [org.springframework.test.context.support.DefaultTestContextBootstrapper] - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@6321e813, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@79be0360, org.springframework.test.context.support.DependencyInjectionTestExecutionListener@22a67b4, org.springframework.test.context.support.DirtiesContextTestExecutionListener@57855c9a, org.springframework.test.context.transaction.TransactionalTestExecutionListener@3b084709, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@3224f60b, org.springframework.test.context.event.EventPublishingTestExecutionListener@63e2203c]Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2020-02-24 11:23:54,865 INFO [com.alibaba.druid.pool.DruidDataSource] - {dataSource-1} inited
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@29a0cdb] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@34f22f9d] will not be managed by Spring
==>  Preparing: SELECT a.id, a.user_key, a.user_name, a.password, a.status, a.superuser, a.roles, a.modify_password_time, a.created, a.updated FROM auth_manager AS a 
==> Parameters: 
<==    Columns: id, user_key, user_name, password, status, superuser, roles, modify_password_time, created, updated
<==        Row: 1, 8c41b9a54b2e2a4180cc1271b4672779, xiaojun.liu, md5#M564Kc#1af71f605fc20d8784761409a68f1e8a, 1, 1, admin,editor, null, 2019-07-01 00:00:00, 2019-07-01 00:00:00
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@29a0cdb]
------------------------------------------------
AuthManager{id=1, userKey='8c41b9a54b2e2a4180cc1271b4672779', userName='xiaojun.liu', password='md5#M564Kc#1af71f605fc20d8784761409a68f1e8a', status=1, superuser=true, roles='admin,editor', modifyPasswordTime=null, created=Mon Jul 01 08:00:00 CST 2019, updated=Mon Jul 01 08:00:00 CST 2019}
2020-02-24 11:23:55,255 INFO [com.alibaba.druid.pool.DruidDataSource] - {dataSource-1} closing ...
2020-02-24 11:23:55,267 INFO [com.alibaba.druid.pool.DruidDataSource] - {dataSource-1} closed

9.实例源码

实例源码已经托管到如下地址：

• https://github.com/work100-net/training-stage2/tree/master/iot-cloud2
• https://gitee.com/work100-net/training-stage2/tree/master/iot-cloud2

---

上一篇：Spring 整合 MyBatis

下一篇：知识点：数据加密与密码

---

如果对课程内容感兴趣，可以扫码关注我们的 公众号 或 QQ群，及时关注我们的课程更新