Spring JdbcTemplate 入門實戰：從配置到 CRUD 完整教程

Spring JdbcTemplate 入門實戰：從配置到 CRUD 完整教程

JdbcTemplate 是 Spring JDBC 核心類，封裝了資源創建、釋放、異常處理等樣板代碼，讓開發者專注於 SQL 與業務邏輯。本文通過完整項目結構演示其基本操作步驟。

1. 環境配置

Maven 依賴

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

applicationContext.xml

<beans xmlns="https://www.springframework.org/schema/beans"
       xmlns:p="https://www.springframework.org/schema/p"
       xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="https://www.springframework.org/schema/beans 
       https://www.springframework.org/schema/beans/spring-beans.xsd">

    <!-- 數據源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql:///spring"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>

    <!-- JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- DAO 注入 -->
    <bean id="userDao" class="cn.happy.impl.UserDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

2. DAO 接口設計

public interface IUserDAO {
    void addUser(User user);
    void deleteUser(int id);
    void updateUser(User user);
    String searchUserName(int id);
    User searchUser(int id);
    List<User> findAll();
}

3. 實現類：繼承 JdbcDaoSupport

public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {

    public void addUser(User user) {
        String sql = "insert into user values(?,?,?)";
        getJdbcTemplate().update(sql, user.getId(), user.getUsername(), user.getPassword());
    }

    public void deleteUser(int id) {
        String sql = "delete from user where id=?";
        getJdbcTemplate().update(sql, id);
    }

    public void updateUser(User user) {
        String sql = "update user set username=?,password=? where id=?";
        getJdbcTemplate().update(sql, user.getUsername(), user.getPassword(), user.getId());
    }

    public String searchUserName(int id) {
        String sql = "select username from user where id=?";
        return getJdbcTemplate().queryForObject(sql, String.class, id);
    }

    public User searchUser(int id) {
        String sql = "select * from user where id=?";
        return getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
    }

    public List<User> findAll() {
        String sql = "select * from user";
        return getJdbcTemplate().query(sql, new UserRowMapper());
    }

    class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            return user;
        }
    }
}

4. JdbcTemplate 核心方法分類

5. 單元測試驗證

@Test
public void demo1() { // 新增
    User user = new User(3, "admin", "123456");
    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    IUserDAO dao = ctx.getBean("userDao", IUserDAO.class);
    dao.addUser(user);
}

@Test
public void demo6() { // 查詢所有
    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    IUserDAO dao = ctx.getBean("userDao", IUserDAO.class);
    List<User> users = dao.findAll();
    System.out.println("用戶數: " + users.size());
}

最佳實踐提示

• 優先使用 queryForObject 查詢單值，query + RowMapper 查詢對象集合
• 複雜映射可使用 BeanPropertyRowMapper 自動匹配屬性名
• 批量操作用 batchUpdate 提升性能
• 異常自動轉換為 Spring 的 DataAccessException 體系，無需捕獲 SQLException

掌握 JdbcTemplate，讓 JDBC 開發回歸簡潔本質。