Spring Boot 使用 JDBC

概述

Spring Boot 是 Spring Framework 的一个扩展，旨在简化 Spring 应用程序的创建和开发过程。它提供了开箱即用的配置，使得开发者能够快速搭建基于Spring的应用程序，而不需要过多的配置。

Spring Boot整合JDBC API + MySQL 可以帮助开发者快速构建基于MySQL数据库的应用程序，本 Demo 实现学生信息的CRUD操作。

这种技术组合适用于对数据操作要求灵活、不需要使用 ORM 框架的场景。相比于使用ORM框架，直接使用JDBC API 可以更加灵活地操作数据库，同时也可以提高程序的性能。

主要依赖项配置

在 pom.xml 中添加以下依赖项：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

实体类定义

使用 Lombok 简化实体类的编写：

import lombok.Data;

@Data
public class Student {
    
    private Long id;
    private String name;
    private int age;
}

数据库配置

在 application.properties 中配置 MySQL 数据库的连接信息：

spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

JDBC 操作类编写

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class StudentDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> getAllStudents() {
        return jdbcTemplate.queryForList("SELECT * FROM student");
    }

    public Map<String, Object> getStudentById(Long id) {
        return jdbcTemplate.queryForMap("SELECT * FROM student WHERE id = ?", id);
    }

    public int createStudent(Student student) {
        return jdbcTemplate.update("INSERT INTO student(name, age) VALUES (?, ?)",
                student.getName(), student.getAge());
    }

    public int updateStudent(Long id, Student student) {
        return jdbcTemplate.update("UPDATE student SET name = ?, age = ? WHERE id = ?",
                student.getName(), student.getAge(), id);
    }

    public int deleteStudent(Long id) {
        return jdbcTemplate.update("DELETE FROM student WHERE id = ?", id);
    }
}

Service类编写

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class StudentService {

    @Autowired
    private StudentDAO studentDAO;

    public List<Map<String, Object>> getAllStudents() {
        return studentDAO.getAllStudents();
    }

    public Map<String, Object> getStudentById(Long id) {
        return studentDAO.getStudentById(id);
    }

    public int createStudent(Student student) {
        return studentDAO.createStudent(student);
    }

    public int updateStudent(Long id, Student student) {
        return studentDAO.updateStudent(id, student);
    }

    public int deleteStudent(Long id) {
        return studentDAO.deleteStudent(id);
    }
}

Controller类编写

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping
    public ResponseEntity<List<Map<String, Object>>> getAllStudents() {
        List<Map<String, Object>> students = studentService.getAllStudents();
        return ResponseEntity.ok(students);
    }

    @GetMapping("/{id}")
    public ResponseEntity<Map<String, Object>> getStudentById(@PathVariable Long id) {
        Map<String, Object> student = studentService.getStudentById(id);
        if (student != null) {
            return ResponseEntity.ok(student);
        } else {
            return ResponseEntity.notFound().build();
        }
    }

    @PostMapping
    public ResponseEntity<Void> createStudent(@RequestBody Student student) {
        int result = studentService.createStudent(student);
        if (result == 1) {
            return ResponseEntity.status(HttpStatus.CREATED).build();
        } else {
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
        }
    }

    @PutMapping("/{id}")
    public ResponseEntity<Void> updateStudent(@PathVariable Long id, @RequestBody Student student) {
        int result = studentService.updateStudent(id, student);
        if (result == 1) {
            return ResponseEntity.ok().build();
        } else {
            return ResponseEntity.notFound().build();
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteStudent(@PathVariable Long id) {
        int result = studentService.deleteStudent(id);
        if (result == 1) {
            return ResponseEntity.noContent().build();
        } else {
            return ResponseEntity.notFound().build();
        }
    }
}

单元测试

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.http.MediaType;
import org.springframework.test.web.servlet.MockMvc;

import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.*;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;

@SpringBootTest
@AutoConfigureMockMvc
public class StudentControllerTests {

    @Autowired
    private MockMvc mockMvc;

    @Test
    public void testGetAllStudents() throws Exception {
        mockMvc.perform(get("/students"))
                .andExpect(status().isOk())
                .andExpect(content().contentType(MediaType.APPLICATION_JSON));
    }

    @Test
    public void testGetStudentById() throws Exception {
        mockMvc.perform(get("/students/1"))
                .andExpect(status().isOk())
                .andExpect(content().contentType(MediaType.APPLICATION_JSON));
    }

    @Test
    public void testCreateStudent() throws Exception {
        String requestBody = "{\"name\":\"Alice\",\"age\":20}";
        mockMvc.perform(post("/students")
                .content(requestBody)
                .contentType(MediaType.APPLICATION_JSON))
                .andExpect(status().isCreated());
    }

    @Test
    public void testUpdateStudent() throws Exception {
        String requestBody = "{\"name\":\"Alice\",\"age\":25}";
        mockMvc.perform(put("/students/1")
                .content(requestBody)
                .contentType(MediaType.APPLICATION_JSON))
                .andExpect(status().isOk());
    }

    @Test
    public void testDeleteStudent() throws Exception {
        mockMvc.perform(delete("/students/1"))
                .andExpect(status().isNoContent());
    }
}

cURL 测试请求

创建学生：

curl -X POST -H "Content-Type: application/json" -d '{"name":"Alice","age":20}' http://localhost:8080/students

获取所有学生信息：

curl http://localhost:8080/students

根据ID获取学生信息：

curl http://localhost:8080/students/1

更新学生信息：

curl -X PUT -H "Content-Type: application/json" -d '{"name":"Alice","age":25}' http://localhost:8080/students/1

删除学生：

curl -X DELETE http://localhost:8080/students/1

注意事项

• Spring Boot版本：确保使用的是Spring Boot 3.2.5版本，以兼容当前代码。
• RESTful规范：接口设计应符合RESTful规范，使用HTTP方法（GET、POST、PUT、DELETE）进行资源的增删改查操作。
• Lombok使用：在使用Lombok简化实体类编写时，需要在IDE中安装Lombok插件，并在项目中正确使用Lombok注解。
• 数据库配置：确保在 application.properties 或 application.yml 中配置了MySQL数据库的连接信息。