原文地址： 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

auth_manager

AuthManagerMapper.xml

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

DAO 及 Service 相关代码省略

testDelete

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

4.SELECT(查询单个对象)

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>

单元测试代码如下：

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

5.UPDATE

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>

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(模糊查询)

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 中的字符串连接符

。

@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.实例源码

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

知识点：数据加密与密码

MyBatis 动态 SQL

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

Java单体应用 - 常用框架 - 08.MyBatis - MyBatis 单表 CRUD 操作MyBatis 单表 CRUD 操作

Java单体应用 - 常用框架 - 08.MyBatis - MyBatis 单表 CRUD 操作MyBatis 单表 CRUD 操作

MyBatis 单表 CRUD 操作

1.概述

2.INSERT

AuthManagerMapper.xml 映射文件

AuthManagerDao 接口

AuthManagerService 接口

AuthManagerServiceImpl 实现

AuthManagerServiceTest 完善

3.DELETE

4.SELECT(查询单个对象)

5.UPDATE

6.SELECT(模糊查询)

7.SQL片段

8.实例源码

继续阅读

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

用mybatis的generator插件在项目中自动生成dao及entity

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

sqlServer根据经纬查距离

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

从mybatis到mybatis plus(mp)

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method