Spring JDBC

基本概念

Spring Framework支援JDBC的相關元件，如:Connection、SQLException。所以，這些都是需要被import到我們的程式裡。

設定

第一、要安裝mySQL server (或其他資料庫)

第二、要啟動mySQL server (或其他資料庫) (請參考投影片)

第三、要建立資料表 (或匯入資料表) (請參考投影片)

CREATE TABLE `practice`.`customer` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(15) NULL,

  `address` VARCHAR(45) NULL,

  `weight` INT NULL,

  PRIMARY KEY (`id`));

第四、更動pom.xml，引用JDBC及MySQL的相關packages

<dependency>

  <groupId>org.springframework.boot</groupId>

 <artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<!-- MySQL database driver -->

<dependency>

 <groupId>mysql</groupId>

 <artifactId>mysql-connector-java</artifactId>

 <scope>runtime</scope>

</dependency>

第五、設定參數 (application.properties)，以使用mySQL為例。(在resources下)

spring.datasource.url=jdbc:mysql://localhost/practice

spring.datasource.username=root

spring.datasource.password=1234

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

使用JdbcTemplate

建議利用Spring Framework提供的JdbcTemplate.update執行SQL Statement，可以大量的簡化所需要的程式碼。jdbcTemplate.update的第一個參數是SQL Statement，在SQL裡會有"?"來接受參數，接下來的參數就是傳到前面的"?"。

另外，在Spring Framework有一些stereotype (如: @Controller)，使用資料庫時，我們會將使用@Repository，在Controller才能利用@Autowired來使用這個類別。

在這個程式(CustomerDAODB.java)裡，我們需要DataSource來設定連接資料庫的參數 (前面第五項的參數設定)，並利用@Autowired來使用。另外，還會使用到JdbcTemplate，也是一樣利用@Autowired來使用。

CustomerDAODB.java

package com.example.demo.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import javax.sql.DataSource;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Repository;

import com.example.demo.entity.Customer;

@Repository

public class CustomerDAODB implements CustomerDAO {

 @Autowired

 private DataSource dataSource;

 @Autowired

 JdbcTemplate jdbcTemplate;

//jdbcTemplate

 public int insert(Customer customer) {

  return jdbcTemplate.update(

    "insert into customer (name, address, weight) values(?, ?, ?)",

    customer.getName(), customer.getAddress(), customer.getWeight());

 public Customer findOne(Long id) {

     return this.jdbcTemplate.queryForObject( "select id, name, address, weight from customer where id = ?", new Object[]{id}, new CustomerMapper());

 public List<Customer> findAll() {

     return this.jdbcTemplate.query( "select id, name, address, weight from customer", new CustomerMapper());

 private static final class CustomerMapper implements RowMapper<Customer> {

     public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

         Customer customer = new Customer();

         customer.setId(rs.getLong("id"));

         customer.setName(rs.getString("name"));

         customer.setAddress(rs.getString("address"));

         customer.setWeight(rs.getInt("weight"));

         return customer;

 public int update(Customer customer) {

  return jdbcTemplate.update(

    "update customer set name=?, address=?, weight=? where id =?",

    customer.getName(), customer.getAddress(), customer.getWeight(), customer.getId());

 public int delete(Long id) {

  return jdbcTemplate.update(

    "delete from customer where id =?", id);

Controller可以直接使用CustomerDAODB，然而，因為資料庫的開發方式可能會改變，為了不讓將來的改變影響整個系統，通常會增加一個interface (CustomerDAO) ，在Controller裡就使用CustomerDAO而不是CustomerDAODB，到時候，如果CustomerDAODB必須更換時，就不需要改Controller的內容了。

CustomerDAO.java

package com.example.demo.dao;

import java.util.List;

import com.example.demo.entity.Customer;

public interface CustomerDAO {

 public int insert(Customer customer);

 public List<Customer> findAll();

 public Customer findOne(Long id);

 public int update(Customer customer);

 public int delete(Long id);

當DAO要串連web application時，會被CustomerController呼叫，因為CustomerDAODB已經設定為@Repository，所以，可以利用@Autowired，這樣就可以直接呼叫CustomerDAODB裡的createCustomer。建議在CustomerDAODB及Customer中不直接處理Exception，所以，採取throws SQLException。Exception的處理方式詳參: https://spring.io/blog/2013/11/01/exception-handling-in-spring-mvc

package com.example.demo.controller;

import java.sql.SQLException;

import java.util.List;

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

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.servlet.ModelAndView;

import com.example.demo.dao.CustomerDAO;

import com.example.demo.entity.Customer;

@Controller

public class CustomerController {

  @Autowired

 CustomerDAO dao;

 @RequestMapping(value = "/customerCreate", method = RequestMethod.GET)

    public ModelAndView openFormCreate() {

       ModelAndView model = new ModelAndView("customerCreate");

       return model;

 @RequestMapping(value = "/customerCreate", method = RequestMethod.POST)

    public ModelAndView processFormCreate(@ModelAttribute Customer cus) throws SQLException {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.insert(cus);

       model.addObject(cus);

       return model;

 @RequestMapping(value = {"/customerRetrieveAll","/"}, method = RequestMethod.GET)

    public ModelAndView retrieveCustomers() throws SQLException{

    List<Customer> customers = dao.findAll();

       ModelAndView model = new ModelAndView("customerList");

       model.addObject("customers",customers);

       return model;

 @RequestMapping(value = "/customerUpdate", method = RequestMethod.GET)

    public ModelAndView openFormUpdate(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

       ModelAndView model = new ModelAndView("customerUpdate");

       Customer customer = dao.findOne(id);

       model.addObject(customer);

       return model;

 @RequestMapping(value = "/customerUpdate", method = RequestMethod.POST)

    public ModelAndView processFormUpdate(@ModelAttribute Customer cus) throws SQLException {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.update(cus);

       return model;

 @RequestMapping(value = "/customerDelete", method = RequestMethod.GET)

    public ModelAndView deleteCustomer(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.delete(id);

       return model;

新增資料

首先，insert會收到一個Customer的物件，再將物件裡的資料利用放到insert statement裡。可以利用提供jdbcTemplatejdbc提供的update，jdbcTemplatejdbc.update的第一個參數是SQL Statement，在SQL裡會有"?"來接受參數，接下來的參數就是傳到前面的"?"。以下的範例就是從customer裡取得name放到第一個變數 (問號)，第一個變數就對應到insert statement裡的name欄位，從customer裡取得address放到第二個變數 (問號)，從customer裡取得weight放到第三個變數 (問號)。

  public int insert(Customer customer) {

  return jdbcTemplate.update(

    "insert into customer (name, address, weight) values(?, ?, ?)",

    customer.getName(), customer.getAddress(), customer.getWeight());

對應controller (CustomerController)的方法:

 @RequestMapping(value = "/customerCreate", method = RequestMethod.GET)

    public ModelAndView openFormCreate() {

       ModelAndView model = new ModelAndView("customerCreate");

       return model;

    @RequestMapping(value = "/customerCreate", method = RequestMethod.POST)

    public ModelAndView processFormCreate(@ModelAttribute Customer cus) throws SQLException {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.insert(cus);

       model.addObject(cus);

       return model;

對應的view，customerCreate.html

<!DOCTYPE html>

<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>

    <meta charset="UTF-8"/>

    <title>Create New Customer</title>

</head>

<body>

<form action="customerCreate" method ="post">

   姓名:<input type="text" name="name"/><br/>

   地址:<input type="text" name = "address"/><br/>

   重量:<input type="text" name ="weight"/><br/>

 <input type="submit" value="Submit"/>

</form>

</body>

</html>

讀取資料

讀取資料表所有資料，首先，使用jdbcTemplate裡的query方法，jdbcTemplatejdbc.query的第一個參數是SQL Statement，第二個參數是對應的RowMapper，利用RowMapper將結果轉換為List，再將List回傳。

在下面的範例中，先寫一個Inner Class(CustomerMapper)，CustomerMapper實作RowMapper<Customer>，這個interface要寫一個mapRow的方法，利用jdbc的getString或getInt從ResultSet讀取資料，並利用setter對應到Customer裡的變數中。

public List<Customer> findAll() {

    return this.jdbcTemplate.query( "select name, address, weight from customer", new CustomerMapper());

private static final class CustomerMapper implements RowMapper<Customer> {

    public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

        Customer customer = new Customer();

        customer.setId(rs.getLong("id"));

        customer.setName(rs.getString("name"));

        customer.setAddress(rs.getString("address"));

        customer.setWeight(rs.getInt("weight"));

        return customer;

對應controller (CustomerController)的方法:

 @RequestMapping(value = {"/customerRetrieveAll","/"}, method = RequestMethod.GET)

    public ModelAndView retrieveCustomers() throws SQLException{

       List<Customer> customers = dao.findAll();

       ModelAndView model = new ModelAndView("customerList");

       model.addObject("customers",customers);

       return model;

對應的view，customerList.html

<!DOCTYPE html>

<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>

    <meta charset="UTF-8"/>

    <title>Create New Customer</title>

</head>

<body>

<a href="customerCreate">新增顧客</a>

<table>

  <tr>

  <th>編號</th>

  <th>姓名</th>

  <th>地址</th>

  <th>重量</th>

  <th></th>

 </tr>

    <tr th:each="customer : ${customers} " th:object="${customer}">

        <td th:text="*{id}">1</td>

        <td th:text="*{name}">Ben</td>

        <td th:text="*{address}">Fu Jen</td>

        <td th:text="*{weight}">56</td>

        <td><a th:href="@{customerUpdate(id=${customer.id})}">修改</a> <a th:href="@{customerDelete(id=${customer.id})}">刪除</a></td>

    </tr>

</table>

</body>

</html>

更新資料

通常會需要讀取該筆資料讓使用者可以更新該筆資料。

讀取單筆資料可利用jdbcTemplatejdbc.queryForObject，queryForObject的第一個參數是SQL Statement，第二個參數是對應"?"的參數陣列，第三個參數是對應的RowMapper(使用前面範例的CustomerMapper)，將取得的物件回傳。

  public Customer findOne(Long id) {

     return this.jdbcTemplate.queryForObject( "select id, name, address, weight from customer where id = ?", new Object[]{id}, new CustomerMapper());

當使用者取得原本的資料內容後，可進行修改，並利用jdbcTemplate.update更新資料。

  public int update(Customer customer) {

  return jdbcTemplate.update(

    "update customer set name=?, address=?, weight=? where id =?",

    customer.getName(), customer.getAddress(), customer.getWeight(), customer.getId());

對應controller (CustomerController)的方法，更新後直接呼叫customerRetrieveAll:

 @RequestMapping(value = "/customerUpdate", method = RequestMethod.GET)

    public ModelAndView openFormUpdate(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

       ModelAndView model = new ModelAndView("customerUpdate");

       Customer customer = dao.findOne(id);

       model.addObject(customer);

       return model;

    @RequestMapping(value = "/customerUpdate", method = RequestMethod.POST)

    public ModelAndView processFormUpdate(@ModelAttribute Customer cus) throws SQLException {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.update(cus);

       return model;

對應的view，customerUpdate.html

<!DOCTYPE html>

<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>

    <meta charset="UTF-8"/>

    <title>Update Customer</title>

</head>

<body>

<form action="customerUpdate" th:object="${customer}" method ="post">

 name:<input type="text" name="name" th:field="*{name}"/><br/>

 address:<input type="text" name = "address" th:field="*{address}"/><br/>

 weight:<input type="text" name ="weight" th:field="*{weight}"/><br/>

 <input type="hidden" name ="id" th:field="*{id}"/>

 <input type="submit" value="Submit"/>

</form>

</body>

</html>

刪除資料

刪除單筆資料可利用jdbcTemplate.update刪除資料。

  public int delete(Long id) {

  return jdbcTemplate.update(

    "delete from customer where id =?", id);

對應controller (CustomerController)的方法，刪除後直接呼叫customerRetrieveAll:

 @RequestMapping(value = "/customerDelete", method = RequestMethod.GET)

    public ModelAndView deleteCustomer(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

       ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

       dao.delete(id);

       return model;

參考資料

04_DB_Environment.ppt

Report abuse