数据访问概述

package com.feixiang.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataAccessConfig {

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/feixiang_edu?useSSL=false&serverTimezone=Asia/Shanghai");
        config.setUsername("root");
        config.setPassword("secret");
        config.setMaximumPoolSize(10);
        return new HikariDataSource(config);
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

package com.feixiang.entity;

public class Student {
    private Long id;
    private int studentId;
    private String name;
    private short age;
    private String major;
    private float balance;

    // 构造器、getter、setter 省略
    public Student() {}

    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public int getStudentId() { return studentId; }
    public void setStudentId(int studentId) { this.studentId = studentId; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public short getAge() { return age; }
    public void setAge(short age) { this.age = age; }
    public String getMajor() { return major; }
    public void setMajor(String major) { this.major = major; }
    public float getBalance() { return balance; }
    public void setBalance(float balance) { this.balance = balance; }
}

package com.feixiang.dao;

import com.feixiang.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentDao {

    private final JdbcTemplate jdbcTemplate;
    private final RowMapper<Student> rowMapper;

    @Autowired
    public StudentDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        // BeanPropertyRowMapper 自动将列名映射到同名属性
        this.rowMapper = new BeanPropertyRowMapper<>(Student.class);
    }

    // 查询单个学生
    public Student findByStudentId(int studentId) {
        String sql = "SELECT id, student_id, name, age, major, balance FROM student WHERE student_id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, rowMapper, studentId);
        } catch (EmptyResultDataAccessException e) {
            return null; // 查不到返回 null，而非抛出异常
        }
    }

    // 查询所有学生
    public List<Student> findAll() {
        String sql = "SELECT id, student_id, name, age, major, balance FROM student";
        return jdbcTemplate.query(sql, rowMapper);
    }

    // 更新余额（选课缴费场景）
    public int updateBalance(int studentId, float amount) {
        String sql = "UPDATE student SET balance = balance - ? WHERE student_id = ? AND balance >= ?";
        return jdbcTemplate.update(sql, amount, studentId, amount);
    }

    // 插入新学生
    public int insert(Student student) {
        String sql = "INSERT INTO student (student_id, name, age, major, balance) VALUES (?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql,
                student.getStudentId(),
                student.getName(),
                student.getAge(),
                student.getMajor(),
                student.getBalance());
    }

    // 删除学生
    public int deleteByStudentId(int studentId) {
        String sql = "DELETE FROM student WHERE student_id = ?";
        return jdbcTemplate.update(sql, studentId);
    }
}

package com.feixiang.service;

import com.feixiang.dao.StudentDao;
import com.feixiang.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentService {

    private final StudentDao studentDao;

    @Autowired
    public StudentService(StudentDao studentDao) {
        this.studentDao = studentDao;
    }

    public Student getStudent(int studentId) {
        return studentDao.findByStudentId(studentId);
    }

    public List<Student> listAllStudents() {
        return studentDao.findAll();
    }

    public boolean deductBalance(int studentId, float courseFee) {
        int affected = studentDao.updateBalance(studentId, courseFee);
        return affected > 0;
    }
}

// 错误写法：直接调用 queryForObject，不处理空结果
public Student findByStudentId(int studentId) {
    String sql = "SELECT * FROM student WHERE student_id = ?";
    // 当学号不存在时，抛出 EmptyResultDataAccessException
    return jdbcTemplate.queryForObject(sql, rowMapper, studentId);
}

public Student findByStudentId(int studentId) {
    String sql = "SELECT * FROM student WHERE student_id = ?";
    List<Student> list = jdbcTemplate.query(sql, rowMapper, studentId);
    return list.isEmpty() ? null : list.get(0);
}

// 错误写法：回到原生 JDBC 的泥潭
public void badExample() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = dataSource.getConnection();
        pstmt = conn.prepareStatement("SELECT * FROM student");
        // ... 冗长的资源管理
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 极易遗漏关闭，且代码膨胀 3 倍以上
        if (pstmt != null) try { pstmt.close(); } catch (SQLException ignored) {}
        if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
    }
}