JDBCTemplate

2021/06/27 (重整內容)

使用JdbcTemplate讀取資料

利用Spring Framework提供的JdbcTemplate執行SQL Statement，可以大量的簡化所需要的程式碼。常用的有方法有update、query及queryForObject。

jdbcTemplate.update的第一個參數是SQL Statement，在SQL裡會有"?"來接受參數，接下來的參數就是依順序傳到前面的"?"。這樣的寫法跟我們在使用php的寫法很像。

public int insert(Customer customer) {

return jdbcTemplate.update(

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

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

}

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);

}

jdbcTemplate.query或queryForObject的第一個參數是SQL Statement，第二個可省略的參數是傳給statement的參數，接下來的參數就是接收結果的物件。

this.jdbcTemplate.queryForObject(

"select id, name, address, weight from customer where id = ?",

new Object[]{id}, new CustomerMapper());

省略第二個參數

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

• Spring Boot JDBC Examples

• Accessing Relational Data using JDBC with Spring

• Spring JDBC

• Spring JdbcTemplate Querying Examples

  • Query for Single Row

  • Query for Multiple Rows

  • Query for a Single Value

使用RowMapper來產生接收結果的物件，mapRow會收到兩個參數:rs跟rowNum，利用rs取得欄位內容。例如:取得欄位"id"，資料的內容是個Long (integer)。

rs.getLong("id"),

內容如下:

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 {

return new Customer(

rs.getLong("id"),

rs.getString("name"),

rs.getString("address"),

rs.getInt("weight")

);

}

}

使用java8的lambda語法，第二個參數會呼叫lambda (類似javascript的arrow function)，一樣會收到兩個參數:rs跟rowNum。但是，java裡並沒有獨立的function/method，所以，lambda的運作方式比較像是產生一個只有一個方法的匿名類別。

• Java 8 Lambda新語法，簡化程式，增強效能

public List<Customer> findAll() {

return this.jdbcTemplate.query( "select id, name, address, weight from customer",

(rs, rowNum) -> new Customer(

rs.getLong("id"),

rs.getString("name"),

rs.getString("address"),

rs.getInt("weight")

)

);

}

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

@Repository

public class CustomerDAODB implements CustomerDAO {

** 注意，要將CustomerDAOImpl的@Repository拿掉，或者將其中一個註記為@Primary，否則spring會顯示

Field dao in com.example.demo.controller.CustomerController required a single bean, but 2 were found:

Action:

Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed

在這個程式(CustomerDAODB.java)裡，我們會使用到JdbcTemplate，也是一樣利用@Autowired來使用。@Autowired會自動讀取application.properties裡，spring.datasource的相關設定，並產生相對的connection (跟PHP類似)，只是都被JdbcTemplate把這些細節都處理好了。

@Autowired

JdbcTemplate jdbcTemplate;

src\main\java\com\example\demo\dao\CustomerDAODB.java

package com.example.demo.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

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

JdbcTemplate jdbcTemplate;

/*

// RowMapper可以改寫為java8的lambda語法

public List<Customer> findAll() {

return this.jdbcTemplate.query( "select id, name, address, weight from customer",

(rs, rowNum) ->

new Customer(

rs.getLong("id"),

rs.getString("name"),

rs.getString("address"),

rs.getInt("weight")

)

);

}

*/

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 {

return new Customer(

rs.getLong("id"),

rs.getString("name"),

rs.getString("address"),

rs.getInt("weight")

);

}

}

}

CustomerController.java

package com.example.demo.controller;

import java.sql.SQLException;

import java.util.List;

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

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

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

import com.example.demo.dao.CustomerDAO;

import com.example.demo.entity.Customer;

@RestController

public class CustomerController {

@Autowired

CustomerDAO dao;

//舊的寫法

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

@GetMapping(value = "/customer")

public List<Customer> retrieveCustomers() throws SQLException{

return dao.findAll();

}

}

新增、修改

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);

}

大部分的內容前面說明過了，唯一需要補充的是queryForObject，queryForObject會接受一個陣列參數，依序對應到"?"，回傳值就一樣利用RowMapper。

public Customer findOne(Long id) {

return this.jdbcTemplate.queryForObject(

"select id, name, address, weight from customer where id = ?",

new Object[]{id}, new CustomerMapper());

}

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 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 insert(Customer customer) {

return jdbcTemplate.update(

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

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

}

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);

}

}

retrieveOneCustomer用到一個還沒說明的用法，就是REST通常會傳遞值

http://localhost:8080/customer/2

在這邊就利用{}來定義變數，再利用@PathVariable，將收到的變數轉換成參數。

@GetMapping(value = {"/customer/{id}"})

public Customer retrieveOneCustomer(@PathVariable("id") Long id) throws SQLException{

return dao.findOne(id);

}

另外，post跟put會收到傳來的內容，這邊就利用@RequestBody，將收到的變數轉換成參數。

@PostMapping(value = "/customer")

public void processFormCreate(@RequestBody Customer customer) throws SQLException {

dao.insert(customer);

}

@PutMapping(value = "/customer")

public void processFormUpdate(@RequestBody Customer customer) throws SQLException {

dao.update(customer);

}

CustomerController.java

package com.example.demo.controller;

import java.sql.SQLException;

import java.util.List;

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

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

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

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

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

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

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

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

import com.example.demo.dao.CustomerDAO;

import com.example.demo.entity.Customer;

@RestController

public class CustomerController {

@Autowired

CustomerDAO dao;

@GetMapping(value = {"/customer"})

public List<Customer> retrieveCustomers() throws SQLException{

return dao.findAll();

}

@GetMapping(value = {"/customer/{id}"})

public Customer retrieveOneCustomer(@PathVariable("id") Long id) throws SQLException{

return dao.findOne(id);

}

@PostMapping(value = "/customer")

public void processFormCreate(@RequestBody Customer customer) throws SQLException {

dao.insert(customer);

}

@PutMapping(value = "/customer")

public void processFormUpdate(@RequestBody Customer customer) throws SQLException {

dao.update(customer);

}

@DeleteMapping(value = "/customer/{id}")

public void deleteCustomer(@PathVariable("id") Long id) {

dao.delete(id);

}

}

利用

http://localhost:8080/customer/2

取得第二筆資料

{"id":2,"name":"Rich","address":"Taipei","weight":60}

試著利用postman送出POST跟PUT

• Introduction (postman.com)

• Postman Tutorial for Beginners with API Testing Example

GET

url:

http://localhost:8080/customer/1

POST

url:

http://localhost:8080/customer

header:

Content-Type:application/json

body: (raw / JSON (appication/json))

{

"name": "Rain",

"address": "Taipei",

"weight": 65

}

PUT

url:

http://localhost:8080/customer

header:

Content-Type:application/json

body: (raw / JSON (appication/json))

{

"id": 3,

"name": "Rain",

"address": "Tainan",

"weight": 67

}

DELETE

url:

http://localhost:8080/customer/3