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

MyBatis 单表 CRUD 操作

序号	文内章节	视频
1	概述	-
2	INSERT	-
3	DELETE	-
4	SELECT(查询单个对象))	-
5	UPDATE	-
6	SELECT(模糊查询))	-
7	SQL片段	-
8	实例源码	-

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

1.概述

本章主要内容是带领大家学习 MyBatis 的单表 CRUD（增、删、改、查） 的相关操作方法

2.INSERT

AuthManagerMapper.xml 映射文件

继续以 auth_manager 表为例，修改映射文件 AuthManagerMapper.xml，增加如下配置：

<insert id="insert" >
    INSERT INTO `auth_manager` (
        `user_key`,
        `user_name`,
        `password`,
        `status`,
        `superuser`,
        `roles`,
        `created`,
        `updated`
    )
    VALUES (
        #{userKey},
        #{userName},
        #{password},
        #{status},
        #{superuser},
        #{roles},
        #{created},
        #{updated}
    )
</insert>

AuthManagerDao 接口

新增 insert 方法：

/**
 * 新增
 *
 * @param authManager
 */
void insert(AuthManager authManager);

AuthManagerService 接口

新增 insert 方法：

/**
 * 新增
 *
 * @param authManager
 */
void insert(AuthManager authManager);

AuthManagerServiceImpl 实现

实现 insert 方法：

@Override
public void insert(AuthManager authManager) {
    authManagerDao.insert(authManager);
}

AuthManagerServiceTest 完善

新增 testInsert 测试方法：

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

import net.work100.training.stage2.iot.cloud.commons.utils.EncryptionUtils;
import net.work100.training.stage2.iot.cloud.domain.AuthManager;
import net.work100.training.stage2.iot.cloud.web.admin.service.AuthManagerService;
import org.apache.commons.lang3.RandomStringUtils;
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.Date;
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());
        }
    }

    @Test
    public void testInsert(){
        String userName = "xiaojun_" + RandomStringUtils.randomAlphanumeric(4);
        String password = "123456";

        AuthManager authManager = new AuthManager();
        authManager.setUserKey(EncryptionUtils.encryptText(EncryptionUtils.EncryptionType.MD5, userName));
        authManager.setUserName(userName);
        authManager.setPassword(EncryptionUtils.encryptPassword(EncryptionUtils.EncryptionType.MD5, password));
        authManager.setStatus(0);
        authManager.setSuperuser(false);
        authManager.setRoles("editor");
        authManager.setCreated(new Date());
        authManager.setUpdated(new Date());
        authManagerService.insert(authManager);
    }
}

3.DELETE

AuthManagerMapper.xml 映射文件

继续以 auth_manager 表为例，修改映射文件 AuthManagerMapper.xml，增加如下配置：

<delete id="delete">
    DELETE FROM auth_manager WHERE id = #{id}
</delete>

DAO 及 Service 相关代码省略

AuthManagerServiceTest 完善

新增 testDelete 测试方法：

@Test
public void testDelete() {
    authManagerService.delete(6L);
}

4.SELECT(查询单个对象)

AuthManagerMapper.xml 映射文件

继续以 auth_manager 表为例，修改映射文件 AuthManagerMapper.xml，增加如下配置：

<select id="getById" 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
    WHERE
      id = #{id}
</select>

DAO 及 Service 相关代码省略

AuthManagerServiceTest 完善

单元测试代码如下：

@Test
public void testGetById(){
    AuthManager authManager = authManagerService.getById(1L);
    System.out.println("---------------------------------");
    System.out.println(authManager.toString());
    System.out.println("---------------------------------");
}

5.UPDATE

AuthManagerMapper.xml 映射文件

继续以 auth_manager 表为例，修改映射文件 AuthManagerMapper.xml，增加如下配置：

<update id="update">
    UPDATE
      auth_manager
    SET
      password = #{password},
      status = #{status},
      superuser = #{superuser},
      roles = #{roles},
      modify_password_time = #{modifyPasswordTime},
      updated = #{updated}
    WHERE 
      id = #{id}
</update>

DAO 及 Service 相关代码省略

AuthManagerServiceTest 完善

新增 testUpdate 测试方法：

@Test
public void testUpdate() {
    AuthManager authManager = authManagerService.getById(1L);
    System.out.println("---------------------------------");
    System.out.println("修改前: " + authManager.getModifyPasswordTime());

    authManager.setModifyPasswordTime(new Date());
    authManager.setUpdated(new Date());
    authManagerService.update(authManager);

    System.out.println("修改后: " + authManager.getModifyPasswordTime());
    System.out.println("---------------------------------");
}

6.SELECT(模糊查询)

AuthManagerMapper.xml 映射文件

继续以 auth_manager 表为例，修改映射文件 AuthManagerMapper.xml，增加如下配置：

<select id="selectByName" 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
    WHERE
      a.user_name LIKE CONCAT ('%', #{userName}, '%')
</select>

在进行模糊查询时，需要进行字符串的拼接。SQL 中的字符串的拼接使用的是函数 concat(arg1, arg2, …) 。注意不能使用 Java 中的字符串连接符 +。

DAO 及 Service 相关代码省略

AuthManagerServiceTest 完善

单元测试代码如下：

@Test
public void testSelectByName() {
    String userName = "xiaojun";
    List<AuthManager> authManagers = authManagerService.selectByName(userName);
    for (AuthManager authManager : authManagers) {
        System.out.println("------------------------------------------------");
        System.out.println(authManager.toString());
    }
}

7.SQL片段

继续以 auth_manager 表为例，映射文件 AuthManagerMapper.xml 中可以定义 SQL 片段，此 SQL 片段可以被其它语句引用（include），代码如下：

<?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">

    <sql id="authManagerColumns">
          a.id,
          a.user_key,
          a.user_name,
          a.password,
          a.status,
          a.superuser,
          a.roles,
          a.modify_password_time,
          a.created,
          a.updated
    </sql>

    <select id="selectAll" resultType="AuthManager">
        SELECT
          <include refid="authManagerColumns" />
        FROM
          auth_manager AS a
    </select>

    <insert id="insert" >
        INSERT INTO auth_manager (
            `user_key`,
            `user_name`,
            `password`,
            `status`,
            `superuser`,
            `roles`,
            `created`,
            `updated`
        )
        VALUES (
            #{userKey},
            #{userName},
            #{password},
            #{status},
            #{superuser},
            #{roles},
            #{created},
            #{updated}
        )
    </insert>

    <delete id="delete">
        DELETE FROM auth_manager WHERE id = #{id}
    </delete>

    <select id="getById" resultType="AuthManager">
        SELECT
          <include refid="authManagerColumns" />
        FROM
          auth_manager AS a
        WHERE
          a.id = #{id}
    </select>

    <update id="update">
        UPDATE
          auth_manager
        SET
          password = #{password},
          status = #{status},
          superuser = #{superuser},
          roles = #{roles},
          modify_password_time = #{modifyPasswordTime},
          updated = #{updated}
        WHERE
          id = #{id}
    </update>

    <select id="selectByName" resultType="AuthManager">
        SELECT
          <include refid="authManagerColumns" />
        FROM
          auth_manager AS a
        WHERE
          a.user_name LIKE CONCAT ('%', #{userName}, '%')
    </select>
</mapper>

8.实例源码

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

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

---

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

下一篇：MyBatis 动态 SQL

---

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