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
