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
建議利用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;
}