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