Spring JdbcTemplate 實戰：分層架構下的完整數據庫操作項目實例

Spring JdbcTemplate 實戰：分層架構下的完整數據庫操作項目實例

本文通過一個完整的「圖書管理」項目，展示在標準三層架構中如何使用 Spring JdbcTemplate 實現數據庫操作，涵蓋實體、DAO、Service、配置、測試全流程。

項目結構

cn.book
├── beans          # 實體類
├── dao            # DAO 接口
├── dao.impl       # DAO 實現
├── service        # Service 接口
├── service.impl   # Service 實現
├── util           # RowMapper 等工具
└── test           # JUnit 測試

1. 實體類 Book

public class Book {
    private Integer bookId;
    private String bookName;
    private Integer bookPrice;
    // getter/setter 省略
}

2. DAO 接口 BookDao

public interface BookDao {
    int addBook(Book book);
    int deleteBook(Book book);
    int updateBook(Book book);
    List<Book> booklist();
    String bookNameOfId(Book book);
    Book bookOfId(Book book);
}

3. DAO 實現：繼承 JdbcDaoSupport

public class BookDaoImpl extends JdbcDaoSupport implements BookDao {

    public int addBook(Book book) {
        String sql = "insert into book(bookname,bookPrice) values(?,?)";
        return getJdbcTemplate().update(sql, book.getBookName(), book.getBookPrice());
    }

    public List<Book> booklist() {
        String sql = "select * from book";
        return getJdbcTemplate().query(sql, new MyRowMapper());
    }

    public int updateBook(Book book) {
        String sql = "update book set bookname=?,bookPrice=? where bookId=?";
        return getJdbcTemplate().update(sql, book.getBookName(), book.getBookPrice(), book.getBookId());
    }

    public int deleteBook(Book book) {
        String sql = "delete from book where bookId=?";
        return getJdbcTemplate().update(sql, book.getBookId());
    }

    public String bookNameOfId(Book book) {
        String sql = "select * from book where bookId=?";
        List<Book> list = getJdbcTemplate().query(sql, new MyRowMapper(), book.getBookId());
        return list.get(0).getBookName();
    }

    public Book bookOfId(Book book) {
        String sql = "select * from book where bookId=?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), book.getBookId());
    }
}

4. 自定義 RowMapper

public class MyRowMapper implements RowMapper<Book> {
    public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
        Book book = new Book();
        book.setBookId(rs.getInt("bookId"));
        book.setBookName(rs.getString("bookName"));
        book.setBookPrice(rs.getInt("bookPrice"));
        return book;
    }
}

5. Service 層

public interface BookService {
    int addBook(Book book);
    int deleteBook(Book book);
    int updateBook(Book book);
    List<Book> booklist();
    String bookNameOfId(Book book);
    Book bookOfId(Book book);
}

public class BookServiceImpl implements BookService {
    private BookDao dao;
    // 委托調用 dao，省略重複代碼
    public void setDao(BookDao dao) { this.dao = dao; }
}

6. Spring 配置文件

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybook
jdbc.username=root
jdbc.password=1234

applicationContext.xml

<beans ...>
    <!-- 屬性佔位符 -->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:jdbc.properties"/>
    </bean>

    <!-- 數據源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

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

    <!-- DAO & Service 注入 -->
    <bean id="dao" class="cn.book.dao.impl.BookDaoImpl">
        <property name="jdbcTemplate" ref="myJDBCTemplate"/>
    </bean>
    <bean id="service" class="cn.book.service.impl.BookServiceImpl">
        <property name="dao" ref="dao"/>
    </bean>
</beans>

7. 日志配置 log4j.properties

log4j.rootLogger=info, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

8. JUnit 測試類

public class MyTest {
    private ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    private BookService service = ctx.getBean("service", BookService.class);

    @Test public void testAdd() {
        Book book = new Book(); book.setBookName("西游記"); book.setBookPrice(100);
        System.out.println(service.addBook(book) > 0 ? "新增成功" : "新增失敗");
    }

    @Test public void testList() {
        service.booklist().forEach(b -> System.out.println(b.getBookName()));
    }
    // 其他測試方法省略...
}

關鍵技術點總結

• JdbcDaoSupport：提供 getJdbcTemplate()，需注入 jdbcTemplate 屬性
• RowMapper：將 ResultSet 每行映射為對象，BeanPropertyRowMapper 可自動匹配屬性名
• 分層解耦：Controller → Service → DAO → JdbcTemplate → DB
• 屬性佔位符：外部化數據庫配置，便於環境切換
• 事務管理：可在 Service 層通過 AOP 或編程式事務控制

此項目結構清晰、配置標準，可作為 Spring JDBC 開發的參考模板直接複製使用。