Java学习第10天 - Spring Boot数据库集成与MyBatis

162 阅读8分钟

学习时间: 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("学生添加成功"));
    }
}

🎯 今日学习总结

掌握的核心技能:

  1. 数据库配置 - 学会配置MySQL连接和MyBatis
  2. 实体类设计 - 掌握Lombok注解和实体类规范
  3. MyBatis使用 - 学会编写Mapper接口和XML配置
  4. Service层设计 - 掌握业务逻辑处理和异常处理
  5. RESTful API - 学会设计完整的CRUD接口
  6. 单元测试 - 掌握基本的测试方法

实战项目收获:

  • 完整的学生管理系统后端API
  • 数据库操作的最佳实践
  • 异常处理和日志记录
  • 分页查询和条件搜索
  • 前后端分离的API设计

明天预告: Spring Boot安全认证与JWT集成 🚀