学习时间: 4-5小时
学习目标: 掌握Spring Boot与数据库集成,学会使用MyBatis进行数据持久化操作
📋 详细学习清单(含大量代码)
✅ 第一部分:数据库配置与连接(60分钟)
1. 数据库配置详解
application.properties配置
# 数据库连接配置
spring.datasource.url=jdbc:mysql://localhost:3306/student_db?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 连接池配置
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
# MyBatis配置
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.example.demo.model
mybatis.configuration.map-underscore-to-camel-case=true
# 日志配置
logging.level.com.example.demo.mapper=debug
application.yml配置(推荐)
spring:
datasource:
url: jdbc:mysql://localhost:3306/student_db?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 30000
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.example.demo.model
configuration:
map-underscore-to-camel-case: true
logging:
level:
com.example.demo.mapper: debug
2. Maven依赖配置
<!-- pom.xml -->
<dependencies>
<!-- Spring Boot Starter Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MyBatis Spring Boot Starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
✅ 第二部分:实体类与数据库表设计(45分钟)
1. 学生实体类
// Student.java
package com.example.demo.model;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class Student {
private Long id;
private String name;
private Integer age;
private String gender;
private String email;
private String phone;
private String address;
private LocalDateTime createTime;
private LocalDateTime updateTime;
private Integer status; // 0-删除 1-正常
}
2. 数据库表创建SQL
-- 创建数据库
CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE student_db;
-- 创建学生表
CREATE TABLE student (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
name VARCHAR(50) NOT NULL COMMENT '姓名',
age INT COMMENT '年龄',
gender VARCHAR(10) COMMENT '性别',
email VARCHAR(100) COMMENT '邮箱',
phone VARCHAR(20) COMMENT '手机号',
address VARCHAR(200) COMMENT '地址',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
status INT DEFAULT 1 COMMENT '状态:0-删除 1-正常',
INDEX idx_name (name),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
-- 插入测试数据
INSERT INTO student (name, age, gender, email, phone, address) VALUES
('张三', 20, '男', 'zhangsan@example.com', '13800138001', '北京市朝阳区'),
('李四', 22, '女', 'lisi@example.com', '13800138002', '上海市浦东新区'),
('王五', 21, '男', 'wangwu@example.com', '13800138003', '广州市天河区'),
('赵六', 23, '女', 'zhaoliu@example.com', '13800138004', '深圳市南山区');
✅ 第三部分:MyBatis Mapper接口与XML配置(90分钟)
1. StudentMapper接口
// StudentMapper.java
package com.example.demo.mapper;
import com.example.demo.model.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface StudentMapper {
// 插入学生
int insert(Student student);
// 根据ID查询学生
Student selectById(@Param("id") Long id);
// 查询所有学生
List<Student> selectAll();
// 根据姓名查询学生
List<Student> selectByName(@Param("name") String name);
// 分页查询学生
List<Student> selectByPage(@Param("offset") int offset, @Param("limit") int limit);
// 更新学生信息
int update(Student student);
// 删除学生(逻辑删除)
int deleteById(@Param("id") Long id);
// 统计学生总数
int countAll();
// 根据条件查询学生
List<Student> selectByCondition(Student condition);
}
2. StudentMapper.xml配置
<!-- StudentMapper.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.demo.mapper.StudentMapper">
<!-- 结果映射 -->
<resultMap id="StudentResultMap" type="com.example.demo.model.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="create_time" property="createTime"/>
<result column="update_time" property="updateTime"/>
<result column="status" property="status"/>
</resultMap>
<!-- 插入学生 -->
<insert id="insert" parameterType="com.example.demo.model.Student" useGeneratedKeys="true" keyProperty="id">
INSERT INTO student (name, age, gender, email, phone, address, status)
VALUES (#{name}, #{age}, #{gender}, #{email}, #{phone}, #{address}, 1)
</insert>
<!-- 根据ID查询学生 -->
<select id="selectById" resultMap="StudentResultMap">
SELECT * FROM student WHERE id = #{id} AND status = 1
</select>
<!-- 查询所有学生 -->
<select id="selectAll" resultMap="StudentResultMap">
SELECT * FROM student WHERE status = 1 ORDER BY create_time DESC
</select>
<!-- 根据姓名查询学生 -->
<select id="selectByName" resultMap="StudentResultMap">
SELECT * FROM student
WHERE name LIKE CONCAT('%', #{name}, '%') AND status = 1
ORDER BY create_time DESC
</select>
<!-- 分页查询学生 -->
<select id="selectByPage" resultMap="StudentResultMap">
SELECT * FROM student
WHERE status = 1
ORDER BY create_time DESC
LIMIT #{offset}, #{limit}
</select>
<!-- 更新学生信息 -->
<update id="update" parameterType="com.example.demo.model.Student">
UPDATE student
SET name = #{name},
age = #{age},
gender = #{gender},
email = #{email},
phone = #{phone},
address = #{address},
update_time = CURRENT_TIMESTAMP
WHERE id = #{id} AND status = 1
</update>
<!-- 删除学生(逻辑删除) -->
<update id="deleteById">
UPDATE student SET status = 0 WHERE id = #{id}
</update>
<!-- 统计学生总数 -->
<select id="countAll" resultType="int">
SELECT COUNT(*) FROM student WHERE status = 1
</select>
<!-- 根据条件查询学生 -->
<select id="selectByCondition" parameterType="com.example.demo.model.Student" resultMap="StudentResultMap">
SELECT * FROM student
<where>
status = 1
<if test="name != null and name != ''">
AND name LIKE CONCAT('%', #{name}, '%')
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="gender != null and gender != ''">
AND gender = #{gender}
</if>
<if test="email != null and email != ''">
AND email LIKE CONCAT('%', #{email}, '%')
</if>
</where>
ORDER BY create_time DESC
</select>
</mapper>
✅ 第四部分:Service层业务逻辑(60分钟)
1. StudentService接口
// StudentService.java
package com.example.demo.service;
import com.example.demo.model.Student;
import java.util.List;
public interface StudentService {
// 添加学生
boolean addStudent(Student student);
// 根据ID获取学生
Student getStudentById(Long id);
// 获取所有学生
List<Student> getAllStudents();
// 根据姓名搜索学生
List<Student> searchStudentsByName(String name);
// 分页获取学生
List<Student> getStudentsByPage(int page, int size);
// 更新学生信息
boolean updateStudent(Student student);
// 删除学生
boolean deleteStudent(Long id);
// 获取学生总数
int getStudentCount();
// 根据条件查询学生
List<Student> getStudentsByCondition(Student condition);
}
2. StudentServiceImpl实现类
// StudentServiceImpl.java
package com.example.demo.service.impl;
import com.example.demo.mapper.StudentMapper;
import com.example.demo.model.Student;
import com.example.demo.service.StudentService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Slf4j
@Service
@Transactional
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentMapper studentMapper;
@Override
public boolean addStudent(Student student) {
try {
// 参数校验
if (student == null || student.getName() == null || student.getName().trim().isEmpty()) {
log.error("学生信息不能为空");
return false;
}
// 检查姓名是否重复
List<Student> existingStudents = studentMapper.selectByName(student.getName());
if (!existingStudents.isEmpty()) {
log.warn("学生姓名已存在: {}", student.getName());
return false;
}
int result = studentMapper.insert(student);
log.info("添加学生成功: {}", student.getName());
return result > 0;
} catch (Exception e) {
log.error("添加学生失败", e);
return false;
}
}
@Override
public Student getStudentById(Long id) {
if (id == null || id <= 0) {
log.warn("学生ID无效: {}", id);
return null;
}
try {
Student student = studentMapper.selectById(id);
if (student == null) {
log.warn("未找到学生: {}", id);
}
return student;
} catch (Exception e) {
log.error("查询学生失败, id: {}", id, e);
return null;
}
}
@Override
public List<Student> getAllStudents() {
try {
return studentMapper.selectAll();
} catch (Exception e) {
log.error("查询所有学生失败", e);
return List.of();
}
}
@Override
public List<Student> searchStudentsByName(String name) {
if (name == null || name.trim().isEmpty()) {
log.warn("搜索姓名不能为空");
return List.of();
}
try {
return studentMapper.selectByName(name.trim());
} catch (Exception e) {
log.error("根据姓名搜索学生失败: {}", name, e);
return List.of();
}
}
@Override
public List<Student> getStudentsByPage(int page, int size) {
if (page < 1) page = 1;
if (size < 1) size = 10;
int offset = (page - 1) * size;
try {
return studentMapper.selectByPage(offset, size);
} catch (Exception e) {
log.error("分页查询学生失败, page: {}, size: {}", page, size, e);
return List.of();
}
}
@Override
public boolean updateStudent(Student student) {
if (student == null || student.getId() == null) {
log.error("更新学生信息无效");
return false;
}
try {
// 检查学生是否存在
Student existingStudent = studentMapper.selectById(student.getId());
if (existingStudent == null) {
log.warn("要更新的学生不存在: {}", student.getId());
return false;
}
int result = studentMapper.update(student);
log.info("更新学生成功: {}", student.getName());
return result > 0;
} catch (Exception e) {
log.error("更新学生失败", e);
return false;
}
}
@Override
public boolean deleteStudent(Long id) {
if (id == null || id <= 0) {
log.error("删除学生ID无效: {}", id);
return false;
}
try {
int result = studentMapper.deleteById(id);
log.info("删除学生成功: {}", id);
return result > 0;
} catch (Exception e) {
log.error("删除学生失败, id: {}", id, e);
return false;
}
}
@Override
public int getStudentCount() {
try {
return studentMapper.countAll();
} catch (Exception e) {
log.error("获取学生总数失败", e);
return 0;
}
}
@Override
public List<Student> getStudentsByCondition(Student condition) {
try {
return studentMapper.selectByCondition(condition);
} catch (Exception e) {
log.error("根据条件查询学生失败", e);
return List.of();
}
}
}
✅ 第五部分:Controller层API开发(75分钟)
1. StudentController完整实现
// StudentController.java
package com.example.demo.controller;
import com.example.demo.model.Student;
import com.example.demo.service.StudentService;
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/students")
@CrossOrigin(origins = "*")
public class StudentController {
@Autowired
private StudentService studentService;
/**
* 添加学生
*/
@PostMapping
public ResponseEntity<Map<String, Object>> addStudent(@RequestBody Student student) {
Map<String, Object> response = new HashMap<>();
try {
boolean success = studentService.addStudent(student);
if (success) {
response.put("success", true);
response.put("message", "学生添加成功");
response.put("data", student);
log.info("添加学生成功: {}", student.getName());
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", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 根据ID获取学生
*/
@GetMapping("/{id}")
public ResponseEntity<Map<String, Object>> getStudentById(@PathVariable Long id) {
Map<String, Object> response = new HashMap<>();
try {
Student student = studentService.getStudentById(id);
if (student != null) {
response.put("success", true);
response.put("data", student);
return ResponseEntity.ok(response);
} else {
response.put("success", false);
response.put("message", "学生不存在");
return ResponseEntity.notFound().build();
}
} catch (Exception e) {
log.error("获取学生异常, id: {}", id, e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 获取所有学生
*/
@GetMapping
public ResponseEntity<Map<String, Object>> getAllStudents() {
Map<String, Object> response = new HashMap<>();
try {
List<Student> students = studentService.getAllStudents();
int total = studentService.getStudentCount();
response.put("success", true);
response.put("data", students);
response.put("total", total);
return ResponseEntity.ok(response);
} catch (Exception e) {
log.error("获取所有学生异常", e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 分页获取学生
*/
@GetMapping("/page")
public ResponseEntity<Map<String, Object>> getStudentsByPage(
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "10") int size) {
Map<String, Object> response = new HashMap<>();
try {
List<Student> students = studentService.getStudentsByPage(page, size);
int total = studentService.getStudentCount();
int totalPages = (int) Math.ceil((double) total / size);
response.put("success", true);
response.put("data", students);
response.put("total", total);
response.put("page", page);
response.put("size", size);
response.put("totalPages", totalPages);
return ResponseEntity.ok(response);
} catch (Exception e) {
log.error("分页获取学生异常, page: {}, size: {}", page, size, e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 根据姓名搜索学生
*/
@GetMapping("/search")
public ResponseEntity<Map<String, Object>> searchStudentsByName(
@RequestParam String name) {
Map<String, Object> response = new HashMap<>();
try {
List<Student> students = studentService.searchStudentsByName(name);
response.put("success", true);
response.put("data", students);
response.put("keyword", name);
return ResponseEntity.ok(response);
} catch (Exception e) {
log.error("搜索学生异常, name: {}", name, e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 更新学生信息
*/
@PutMapping("/{id}")
public ResponseEntity<Map<String, Object>> updateStudent(
@PathVariable Long id, @RequestBody Student student) {
Map<String, Object> response = new HashMap<>();
try {
student.setId(id);
boolean success = studentService.updateStudent(student);
if (success) {
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("更新学生异常, id: {}", id, e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 删除学生
*/
@DeleteMapping("/{id}")
public ResponseEntity<Map<String, Object>> deleteStudent(@PathVariable Long id) {
Map<String, Object> response = new HashMap<>();
try {
boolean success = studentService.deleteStudent(id);
if (success) {
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("删除学生异常, id: {}", id, e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
/**
* 根据条件查询学生
*/
@PostMapping("/condition")
public ResponseEntity<Map<String, Object>> getStudentsByCondition(
@RequestBody Student condition) {
Map<String, Object> response = new HashMap<>();
try {
List<Student> students = studentService.getStudentsByCondition(condition);
response.put("success", true);
response.put("data", students);
return ResponseEntity.ok(response);
} catch (Exception e) {
log.error("条件查询学生异常", e);
response.put("success", false);
response.put("message", "服务器内部错误");
return ResponseEntity.internalServerError().body(response);
}
}
}
✅ 第六部分:测试与调试(30分钟)
1. 使用Postman测试API
# 1. 添加学生
POST http://localhost:8080/api/students
Content-Type: application/json
{
"name": "测试学生",
"age": 20,
"gender": "男",
"email": "test@example.com",
"phone": "13800138000",
"address": "测试地址"
}
# 2. 获取所有学生
GET http://localhost:8080/api/students
# 3. 分页获取学生
GET http://localhost:8080/api/students/page?page=1&size=5
# 4. 搜索学生
GET http://localhost:8080/api/students/search?name=张
# 5. 更新学生
PUT http://localhost:8080/api/students/1
Content-Type: application/json
{
"name": "更新后的姓名",
"age": 21,
"gender": "男",
"email": "updated@example.com",
"phone": "13800138001",
"address": "更新后的地址"
}
# 6. 删除学生
DELETE http://localhost:8080/api/students/1
2. 单元测试示例
// StudentControllerTest.java
package com.example.demo.controller;
import com.example.demo.model.Student;
import com.example.demo.service.StudentService;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.WebMvcTest;
import org.springframework.boot.test.mock.mockito.MockBean;
import org.springframework.http.MediaType;
import org.springframework.test.web.servlet.MockMvc;
import java.util.Arrays;
import java.util.List;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.Mockito.when;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.*;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;
@WebMvcTest(StudentController.class)
public class StudentControllerTest {
@Autowired
private MockMvc mockMvc;
@MockBean
private StudentService studentService;
@Autowired
private ObjectMapper objectMapper;
@Test
public void testGetAllStudents() throws Exception {
// 准备测试数据
Student student1 = new Student();
student1.setId(1L);
student1.setName("张三");
student1.setAge(20);
Student student2 = new Student();
student2.setId(2L);
student2.setName("李四");
student2.setAge(22);
List<Student> students = Arrays.asList(student1, student2);
// Mock service方法
when(studentService.getAllStudents()).thenReturn(students);
when(studentService.getStudentCount()).thenReturn(2);
// 执行测试
mockMvc.perform(get("/api/students"))
.andExpect(status().isOk())
.andExpect(jsonPath("$.success").value(true))
.andExpect(jsonPath("$.data").isArray())
.andExpect(jsonPath("$.data.length()").value(2))
.andExpect(jsonPath("$.total").value(2));
}
@Test
public void testAddStudent() throws Exception {
// 准备测试数据
Student student = new Student();
student.setName("测试学生");
student.setAge(20);
student.setGender("男");
// Mock service方法
when(studentService.addStudent(any(Student.class))).thenReturn(true);
// 执行测试
mockMvc.perform(post("/api/students")
.contentType(MediaType.APPLICATION_JSON)
.content(objectMapper.writeValueAsString(student)))
.andExpect(status().isOk())
.andExpect(jsonPath("$.success").value(true))
.andExpect(jsonPath("$.message").value("学生添加成功"));
}
}
🎯 今日学习总结
掌握的核心技能:
- 数据库配置 - 学会配置MySQL连接和MyBatis
- 实体类设计 - 掌握Lombok注解和实体类规范
- MyBatis使用 - 学会编写Mapper接口和XML配置
- Service层设计 - 掌握业务逻辑处理和异常处理
- RESTful API - 学会设计完整的CRUD接口
- 单元测试 - 掌握基本的测试方法
实战项目收获:
- 完整的学生管理系统后端API
- 数据库操作的最佳实践
- 异常处理和日志记录
- 分页查询和条件搜索
- 前后端分离的API设计
明天预告: Spring Boot安全认证与JWT集成 🚀