您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Spring JDBC 框架實戰：從資料源配置到 CRUD 與預存程序完整指南

編輯：Java 相關知識

為什麼選擇 Spring JDBC？

傳統 JDBC 需手動管理連線、預處理、結果集、異常轉換與資源釋放，樣板代碼繁瑣。Spring JDBC 以 JdbcTemplate 為核心，封裝底層細節，開發者僅需關注 SQL 與參數綁定，大幅提升開發效率與代碼可維護性。

核心組件：JdbcTemplate

執行職責：SQL 查詢、更新、預存程序呼叫、結果集迭代與映射。
異常處理：將 JDBC SQLException 轉換為 Spring 統一的 DataAccessException 體系，免去強制捕獲。
線程安全：實例無狀態，可單例注入多個 DAO 共用。

環境準備：資料源與表結構

-- MySQL 建表腳本
CREATE TABLE Student (
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    PRIMARY KEY (ID)
);

<!-- Spring XML 配置資料源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
    <property name="username" value="root"/>
    <property name="password" value="password"/>
</bean>

DAO 介面與實體類

public interface StudentDAO {
    void setDataSource(DataSource ds);
    void create(String name, Integer age);
    Student getStudent(Integer id);
    List<Student> listStudents();
}

public class Student {
    private Integer id, age;
    private String name;
    // Getter/Setter 省略
}

RowMapper：結果集映射關鍵

public class StudentMapper implements RowMapper<Student> {
    @Override
    public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
        Student s = new Student();
        s.setId(rs.getInt("id"));
        s.setName(rs.getString("name"));
        s.setAge(rs.getInt("age"));
        return s;
    }
}

JdbcTemplate 實作 DAO

public class StudentJDBCTemplate implements StudentDAO {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void create(String name, Integer age) {
        String SQL = "INSERT INTO Student (name, age) VALUES (?, ?)";
        jdbcTemplate.update(SQL, name, age);
        System.out.println("Created: " + name + ", " + age);
    }

    @Override
    public Student getStudent(Integer id) {
        String SQL = "SELECT * FROM Student WHERE id = ?";
        return jdbcTemplate.queryForObject(SQL, new Object[]{id}, new StudentMapper());
    }

    @Override
    public List<Student> listStudents() {
        String SQL = "SELECT * FROM Student";
        return jdbcTemplate.query(SQL, new StudentMapper());
    }
}

進階操作：預存程序與 SimpleJdbcCall

-- MySQL 預存程序
DELIMITER $$
CREATE PROCEDURE `getRecord` (
    IN in_id INTEGER,
    OUT out_name VARCHAR(20),
    OUT out_age INTEGER
)
BEGIN
    SELECT name, age INTO out_name, out_age FROM Student WHERE id = in_id;
END $$
DELIMITER ;

public class StudentJDBCTemplate implements StudentDAO {
    private SimpleJdbcCall jdbcCall;

    public void setDataSource(DataSource dataSource) {
        this.jdbcCall = new SimpleJdbcCall(dataSource)
            .withProcedureName("getRecord");
    }

    @Override
    public Student getStudent(Integer id) {
        SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
        Map<String, Object> out = jdbcCall.execute(in);
        Student student = new Student();
        student.setId(id);
        student.setName((String) out.get("out_name"));
        student.setAge((Integer) out.get("out_age"));
        return student;
    }
    // create/listStudents 同前
}

啟動測試

public class MainApp {
    public static void main(String[] args) {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("Beans.xml");
        StudentDAO dao = ctx.getBean("studentJDBCTemplate", StudentDAO.class);

        dao.create("Zara", 11);
        dao.create("Nuha", 2);
        dao.create("Ayan", 15);

        for (Student s : dao.listStudents()) {
            System.out.printf("ID: %d, Name: %s, Age: %d%n", s.getId(), s.getName(), s.getAge());
        }
    }
}