Spring Boot整合MyBatis+MySQL+Redis单表CRUD教程
环境准备
1. Redis安装（Windows）
# 下载Redis for Windows
# 访问: https://github.com/tporadowski/redis/releases
# 下载Redis-x64-5.0.14.1.msi并安装# 启动Redis服务
redis-server# 测试连接
redis-cli
ping # 应该返回PONG
2. 项目创建
使用IDEA创建Spring Boot项目，或访问 https://start.spring.io/
项目依赖配置
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.0</version><relativePath/></parent><groupId>com.example</groupId><artifactId>user-crud</artifactId><version>0.0.1-SNAPSHOT</version><name>user-crud</name><properties><java.version>11</java.version></properties><dependencies><!-- Spring Boot Web --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- MyBatis --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><!-- MySQL驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- Redis --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis</artifactId></dependency><!-- JSON处理 --><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId></dependency><!-- Lombok（可选，简化代码） --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- 测试 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build>
</project>
数据库准备
创建数据库和表
-- 创建数据库
CREATE DATABASE user_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE user_management;-- 创建用户表
CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL,phone VARCHAR(20),age INT,created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);-- 插入测试数据
INSERT INTO users (username, email, phone, age) VALUES
('zhangsan', 'zhangsan@example.com', '13800138001', 25),
('lisi', 'lisi@example.com', '13800138002', 30),
('wangwu', 'wangwu@example.com', '13800138003', 28);
配置文件
application.yml
server:port: 8080spring:# 数据源配置datasource:url: jdbc:mysql://localhost:3306/user_management?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8username: rootpassword: your_password # 替换为你的MySQL密码driver-class-name: com.mysql.cj.jdbc.Driver# Redis配置redis:host: localhostport: 6379timeout: 3000jedis:pool:max-active: 20max-idle: 10min-idle: 0# MyBatis配置
mybatis:mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.example.usercrud.entityconfiguration:map-underscore-to-camel-case: truelog-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 日志配置
logging:level:com.example.usercrud.mapper: debug
代码实现
1. 实体类
// src/main/java/com/example/usercrud/entity/User.java
package com.example.usercrud.entity;import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDateTime;@Data
public class User implements Serializable {private Long id;private String username;private String email;private String phone;private Integer age;@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")private LocalDateTime createdTime;@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")private LocalDateTime updatedTime;
}
2. Mapper接口
// src/main/java/com/example/usercrud/mapper/UserMapper.java
package com.example.usercrud.mapper;import com.example.usercrud.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;@Mapper
public interface UserMapper {// 插入用户int insert(User user);// 根据ID删除用户int deleteById(@Param("id") Long id);// 更新用户int update(User user);// 根据ID查询用户User selectById(@Param("id") Long id);// 查询所有用户List<User> selectAll();// 根据用户名查询User selectByUsername(@Param("username") String username);// 分页查询List<User> selectByPage(@Param("offset") int offset, @Param("size") int size);// 统计总数long count();
}
3. Mapper XML
<!-- src/main/resources/mapper/UserMapper.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.example.usercrud.mapper.UserMapper"><!-- 结果映射 --><resultMap id="UserResultMap" type="User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/><result property="phone" column="phone"/><result property="age" column="age"/><result property="createdTime" column="created_time"/><result property="updatedTime" column="updated_time"/></resultMap><!-- 基础列 --><sql id="Base_Column_List">id, username, email, phone, age, created_time, updated_time</sql><!-- 插入 --><insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">INSERT INTO users (username, email, phone, age)VALUES (#{username}, #{email}, #{phone}, #{age})</insert><!-- 删除 --><delete id="deleteById">DELETE FROM users WHERE id = #{id}</delete><!-- 更新 --><update id="update" parameterType="User">UPDATE users SET username = #{username},email = #{email},phone = #{phone},age = #{age}WHERE id = #{id}</update><!-- 根据ID查询 --><select id="selectById" resultMap="UserResultMap">SELECT <include refid="Base_Column_List"/>FROM usersWHERE id = #{id}</select><!-- 查询所有 --><select id="selectAll" resultMap="UserResultMap">SELECT <include refid="Base_Column_List"/>FROM usersORDER BY created_time DESC</select><!-- 根据用户名查询 --><select id="selectByUsername" resultMap="UserResultMap">SELECT <include refid="Base_Column_List"/>FROM usersWHERE username = #{username}</select><!-- 分页查询 --><select id="selectByPage" resultMap="UserResultMap">SELECT <include refid="Base_Column_List"/>FROM usersORDER BY created_time DESCLIMIT #{offset}, #{size}</select><!-- 统计总数 --><select id="count" resultType="long">SELECT COUNT(*) FROM users</select></mapper>
4. Service服务层
// src/main/java/com/example/usercrud/service/UserService.java
package com.example.usercrud.service;import com.example.usercrud.entity.User;
import java.util.List;public interface UserService {// 创建用户User createUser(User user);// 删除用户boolean deleteUser(Long id);// 更新用户User updateUser(User user);// 根据ID查询用户User getUserById(Long id);// 查询所有用户List<User> getAllUsers();// 分页查询用户List<User> getUsersByPage(int page, int size);// 统计用户总数long getUserCount();
}
// src/main/java/com/example/usercrud/service/impl/UserServiceImpl.java
package com.example.usercrud.service.impl;import com.example.usercrud.entity.User;
import com.example.usercrud.mapper.UserMapper;
import com.example.usercrud.service.UserService;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.concurrent.TimeUnit;@Slf4j
@Service
public class UserServiceImpl implements UserService {@Autowiredprivate UserMapper userMapper;@Autowiredprivate StringRedisTemplate stringRedisTemplate;@Autowiredprivate ObjectMapper objectMapper;private static final String USER_CACHE_PREFIX = "user:";private static final String USER_LIST_CACHE_KEY = "user:list";private static final int CACHE_EXPIRE_HOURS = 2;@Overridepublic User createUser(User user) {try {int result = userMapper.insert(user);if (result > 0) {// 清除列表缓存stringRedisTemplate.delete(USER_LIST_CACHE_KEY);log.info("用户创建成功，ID: {}", user.getId());return user;}} catch (Exception e) {log.error("创建用户失败", e);throw new RuntimeException("创建用户失败");}return null;}@Overridepublic boolean deleteUser(Long id) {try {int result = userMapper.deleteById(id);if (result > 0) {// 删除缓存stringRedisTemplate.delete(USER_CACHE_PREFIX + id);stringRedisTemplate.delete(USER_LIST_CACHE_KEY);log.info("用户删除成功，ID: {}", id);return true;}} catch (Exception e) {log.error("删除用户失败，ID: {}", id, e);throw new RuntimeException("删除用户失败");}return false;}@Overridepublic User updateUser(User user) {try {int result = userMapper.update(user);if (result > 0) {// 更新缓存String cacheKey = USER_CACHE_PREFIX + user.getId();String userJson = objectMapper.writeValueAsString(user);stringRedisTemplate.opsForValue().set(cacheKey, userJson, CACHE_EXPIRE_HOURS, TimeUnit.HOURS);// 清除列表缓存stringRedisTemplate.delete(USER_LIST_CACHE_KEY);log.info("用户更新成功，ID: {}", user.getId());return user;}} catch (Exception e) {log.error("更新用户失败，ID: {}", user.getId(), e);throw new RuntimeException("更新用户失败");}return null;}@Overridepublic User getUserById(Long id) {String cacheKey = USER_CACHE_PREFIX + id;try {// 先从缓存获取String userJson = stringRedisTemplate.opsForValue().get(cacheKey);if (userJson != null) {log.info("从缓存获取用户，ID: {}", id);return objectMapper.readValue(userJson, User.class);}// 缓存未命中，从数据库查询User user = userMapper.selectById(id);if (user != null) {// 写入缓存userJson = objectMapper.writeValueAsString(user);stringRedisTemplate.opsForValue().set(cacheKey, userJson, CACHE_EXPIRE_HOURS, TimeUnit.HOURS);log.info("从数据库获取用户并缓存，ID: {}", id);}return user;} catch (Exception e) {log.error("获取用户失败，ID: {}", id, e);// 缓存异常时直接查数据库return userMapper.selectById(id);}}@Overridepublic List<User> getAllUsers() {try {// 先从缓存获取String listJson = stringRedisTemplate.opsForValue().get(USER_LIST_CACHE_KEY);if (listJson != null) {log.info("从缓存获取用户列表");return objectMapper.readValue(listJson, objectMapper.getTypeFactory().constructCollectionType(List.class, User.class));}// 缓存未命中，从数据库查询List<User> users = userMapper.selectAll();if (!users.isEmpty()) {// 写入缓存listJson = objectMapper.writeValueAsString(users);stringRedisTemplate.opsForValue().set(USER_LIST_CACHE_KEY, listJson, CACHE_EXPIRE_HOURS, TimeUnit.HOURS);log.info("从数据库获取用户列表并缓存，数量: {}", users.size());}return users;} catch (Exception e) {log.error("获取用户列表失败", e);// 缓存异常时直接查数据库return userMapper.selectAll();}}@Overridepublic List<User> getUsersByPage(int page, int size) {int offset = (page - 1) * size;return userMapper.selectByPage(offset, size);}@Overridepublic long getUserCount() {return userMapper.count();}
}
5. Controller控制器
// src/main/java/com/example/usercrud/controller/UserController.java
package com.example.usercrud.controller;import com.example.usercrud.entity.User;
import com.example.usercrud.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;@Slf4j
@RestController
@RequestMapping("/api/users")
public class UserController {@Autowiredprivate UserService userService;/*** 创建用户*/@PostMappingpublic ResponseEntity<Map<String, Object>> createUser(@RequestBody User user) {Map<String, Object> response = new HashMap<>();try {User createdUser = userService.createUser(user);if (createdUser != null) {response.put("success", true);response.put("message", "用户创建成功");response.put("data", createdUser);return ResponseEntity.ok(response);} else {response.put("success", false);response.put("message", "用户创建失败");return ResponseEntity.badRequest().body(response);}} catch (Exception e) {log.error("创建用户异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}/*** 删除用户*/@DeleteMapping("/{id}")public ResponseEntity<Map<String, Object>> deleteUser(@PathVariable Long id) {Map<String, Object> response = new HashMap<>();try {boolean deleted = userService.deleteUser(id);if (deleted) {response.put("success", true);response.put("message", "用户删除成功");return ResponseEntity.ok(response);} else {response.put("success", false);response.put("message", "用户删除失败，用户不存在");return ResponseEntity.badRequest().body(response);}} catch (Exception e) {log.error("删除用户异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}/*** 更新用户*/@PutMapping("/{id}")public ResponseEntity<Map<String, Object>> updateUser(@PathVariable Long id, @RequestBody User user) {Map<String, Object> response = new HashMap<>();try {user.setId(id);User updatedUser = userService.updateUser(user);if (updatedUser != null) {response.put("success", true);response.put("message", "用户更新成功");response.put("data", updatedUser);return ResponseEntity.ok(response);} else {response.put("success", false);response.put("message", "用户更新失败");return ResponseEntity.badRequest().body(response);}} catch (Exception e) {log.error("更新用户异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}/*** 根据ID查询用户*/@GetMapping("/{id}")public ResponseEntity<Map<String, Object>> getUserById(@PathVariable Long id) {Map<String, Object> response = new HashMap<>();try {User user = userService.getUserById(id);if (user != null) {response.put("success", true);response.put("data", user);return ResponseEntity.ok(response);} else {response.put("success", false);response.put("message", "用户不存在");return ResponseEntity.notFound().build();}} catch (Exception e) {log.error("查询用户异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}/*** 查询所有用户*/@GetMappingpublic ResponseEntity<Map<String, Object>> getAllUsers() {Map<String, Object> response = new HashMap<>();try {List<User> users = userService.getAllUsers();response.put("success", true);response.put("data", users);response.put("total", users.size());return ResponseEntity.ok(response);} catch (Exception e) {log.error("查询用户列表异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}/*** 分页查询用户*/@GetMapping("/page")public ResponseEntity<Map<String, Object>> getUsersByPage(@RequestParam(defaultValue = "1") int page,@RequestParam(defaultValue = "10") int size) {Map<String, Object> response = new HashMap<>();try {List<User> users = userService.getUsersByPage(page, size);long total = userService.getUserCount();response.put("success", true);response.put("data", users);response.put("page", page);response.put("size", size);response.put("total", total);response.put("totalPages", (total + size - 1) / size);return ResponseEntity.ok(response);} catch (Exception e) {log.error("分页查询用户异常", e);response.put("success", false);response.put("message", "系统异常：" + e.getMessage());return ResponseEntity.internalServerError().body(response);}}
}
6. 主启动类
// src/main/java/com/example/usercrud/UserCrudApplication.java
package com.example.usercrud;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication
public class UserCrudApplication {public static void main(String[] args) {SpringApplication.run(UserCrudApplication.class, args);}
}
7. Redis配置类
// src/main/java/com/example/usercrud/config/RedisConfig.java
package com.example.usercrud.config;import com.fasterxml.jackson.annotation.JsonAutoDetect;
import com.fasterxml.jackson.annotation.PropertyAccessor;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.jsontype.impl.LaissezFaireSubTypeValidator;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;@Configuration
public class RedisConfig {@Beanpublic RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory connectionFactory) {RedisTemplate<String, Object> template = new RedisTemplate<>();template.setConnectionFactory(connectionFactory);// JSON序列化配置Jackson2JsonRedisSerializer<Object> jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer<>(Object.class);ObjectMapper om = new ObjectMapper();om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);om.activateDefaultTyping(LaissezFaireSubTypeValidator.instance, ObjectMapper.DefaultTyping.NON_FINAL);jackson2JsonRedisSerializer.setObjectMapper(om);// String序列化StringRedisSerializer stringRedisSerializer = new StringRedisSerializer();// key采用String的序列化方式template.setKeySerializer(stringRedisSerializer);template.setHashKeySerializer(stringRedisSerializer);// value序列化方式采用jacksontemplate.setValueSerializer(jackson2JsonRedisSerializer);template.setHashValueSerializer(jackson2JsonRedisSerializer);template.afterPropertiesSet();return template;}@Beanpublic StringRedisTemplate stringRedisTemplate(RedisConnectionFactory connectionFactory) {return new StringRedisTemplate(connectionFactory);}@Beanpublic ObjectMapper objectMapper() {return new ObjectMapper();}
}
API测试
使用Postman或IDEA HTTP Client测试
### 1. 创建用户
POST http://localhost:8080/api/users
Content-Type: application/json{"username": "testuser","email": "test@example.com","phone": "13800138000","age": 25
}### 2. 查询所有用户
GET http://localhost:8080/api/users### 3. 根据ID查询用户
GET http://localhost:8080/api/users/1### 4. 更新用户
PUT http://localhost:8080/api/users/1
Content-Type: application/json{"username": "updateduser","email": "updated@example.com","phone": "13800138001","age": 30
}### 5. 分页查询
GET http://localhost:8080/api/users/page?page=1&size=5### 6. 删除用户
DELETE http://localhost:8080/api/users/1
运行项目
- 确保MySQL和Redis服务已启动
- 修改
application.yml中的数据库密码 - 运行主启动类
UserCrudApplication - 访问 http://localhost:8080/api/users 测试接口
功能特点
- ✅ 完整的CRUD操作
- ✅ MyBatis XML配置
- ✅ Redis缓存集成
- ✅ 统一返回格式
- ✅ 异常处理
- ✅ 分页查询
- ✅ 日志记录
学习要点
- Spring Boot自动配置：理解starter的作用
- MyBatis映射：XML配置与注解的使用
- Redis缓存策略：缓存更新和失效机制
- RESTful API设计：HTTP方法的正确使用
- 异常处理：统一的错误处理机制
这个项目提供了一个完整的单表CRUD服务示例，适合初学者学习和实践。
我来为你提供一个完整的Spring Boot整合MyBatis、MySQL、Redis开发单表CRUD服务的教程。我为你创建了一个完整的Spring Boot整合MyBatis、MySQL、Redis开发单表CRUD服务的教程。这个教程特别适合初学者，包含了：
主要内容：
-
环境准备 - Redis安装指导（Windows版本）
-
项目配置 - 完整的Maven依赖和配置文件
-
数据库设计 - 用户表结构和测试数据
-
完整代码实现
：
- 实体类（User）
- Mapper接口和XML映射
- Service服务层（集成Redis缓存）
- Controller控制器（RESTful API）
- 配置类
核心功能：
- ✅ 完整CRUD操作：创建、读取、更新、删除
- ✅ Redis缓存：自动缓存用户数据，提高查询性能
- ✅ 分页查询：支持分页获取用户列表
- ✅ 异常处理：统一的错误处理机制
- ✅ RESTful API：符合REST规范的接口设计
学习要点：
- MyBatis集成：XML配置方式，适合复杂SQL
- Redis缓存策略：查询缓存、更新时清除缓存
- Spring Boot自动配置：理解starter依赖的便利性
- 分层架构：Controller → Service → Mapper的标准结构
快速开始：
- 按教程安装Redis
- 创建MySQL数据库和表
- 修改
application.yml中的数据库密码 - 复制代码到对应目录
- 运行项目，使用Postman测试API
这个项目提供了企业级开发的基础框架，你可以在此基础上扩展更多功能。有什么问题随时问我！
我来为你提供一个完整的Spring Boot整合MyBatis、MySQL、Redis开发单表CRUD服务的教程。我为你创建了一个完整的Spring Boot整合MyBatis、MySQL、Redis开发单表CRUD服务的教程。这个教程特别适合初学者，包含了：
主要内容：
-
环境准备 - Redis安装指导（Windows版本）
-
项目配置 - 完整的Maven依赖和配置文件
-
数据库设计 - 用户表结构和测试数据
-
完整代码实现
：
- 实体类（User）
- Mapper接口和XML映射
- Service服务层（集成Redis缓存）
- Controller控制器（RESTful API）
- 配置类
核心功能：
- ✅ 完整CRUD操作：创建、读取、更新、删除
- ✅ Redis缓存：自动缓存用户数据，提高查询性能
- ✅ 分页查询：支持分页获取用户列表
- ✅ 异常处理：统一的错误处理机制
- ✅ RESTful API：符合REST规范的接口设计
学习要点：
- MyBatis集成：XML配置方式，适合复杂SQL
- Redis缓存策略：查询缓存、更新时清除缓存
- Spring Boot自动配置：理解starter依赖的便利性
- 分层架构：Controller → Service → Mapper的标准结构
快速开始：
- 按教程安装Redis
- 创建MySQL数据库和表
- 修改
application.yml中的数据库密码 - 复制代码到对应目录
- 运行项目，使用Postman测试API
