Spring DB
Spring DB with JDBC
2021/10/26 (更新內容)
2021/11/02 (新增連結)
2021/11/19 (更新內容)
2021/12/18 (補充內容)
基本概念
JDBC是Java內建的關聯式資料庫的API，如:Connection、SQLException。Spring Framework支援JDBC的相關元件，提供額外的類別，如:Autowired、Repository。因為是關聯式資料庫通用的API，所以，可以透過JDBC連接不同的關聯式資料庫，例如，MySQL或SQL server。不過，因為不同關聯式資料庫有些SQL語法上的差異，還是要注意，更換資料庫的時候，不一定100%相容。
mySQL設定
第一、要安裝mySQL server (或其他資料庫)
第二、要啟動mySQL server (或其他資料庫) (請參考投影片)
第三、要建立資料表 (或匯入資料表) (請參考投影片)
要先新增一個practice的資料庫(schema)，編碼請選擇utf8_general_ci。然後，利用SQL產生以下的資料表:
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，在VSCode裡，可以利用spring Initializr來編輯，在pom.xml裡按右鍵，選擇"Edit Starter"，就可以挑所需要的外掛，"JDBC API"及"MSQL Driver"。這樣的好處是，不會放錯地方。
<!-- JDBC driver -->
<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為例。
src\main\resources\application.properties:
spring.datasource.url=jdbc:mysql://localhost/practice
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
**記得要改密碼**
如果是使用xampp-vm請參考:
如果連不上資料庫，可能是port錯誤，因為預設的port是3336，如果你的mysql是在8889，就要改成
spring.datasource.url=jdbc:mysql://localhost:8889/practice
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
DB_Environment.ppt要啟動mySQL server
要建立資料表
更動pom.xml
設定參數 (application.properties)
過去，是跟我們在web程式設計所教的一樣，要寫一個java class來設定所有的參數，讓其他的程式使用，現在，大部分的框架都提供替代方案，可以利用設定檔來處理，好處是，如果不同機器上的設定不同，可以只要替換掉設定檔，而不必記得要改哪些程式的內容，這對多人同時開發系統的情境而言，是相當重要的。
**注意，這是spring boot的用法，如果是使用spring，設定方式不太一樣，在參考網路文章時，請注意這個差異 **
**新的jdbc driver有時區設定的問題，所以要加serverTimezone，有些網路上的文章建議使用?serverTimezone=Asia/Taipei，但是，似乎還是會有問題，最後的解決方法是設定mySQL的TimeZone
在mysql的設定檔裡，在mysqld下增加
[mysqld]
default-time-zone = "+08:00"
如果有經常塞車的問題，那就是connection pool太小，因為預設值只有10，如果使用的量大一點就會塞車了，請增加connection pool:
spring.datasource.hikari.maximum-pool-size=50
Choosing the right JDBC Connection Pool…
JDBC Connection pooling frameworks
the most popular choices are Tomcat JDBC & HikariCP.
connections = ((2 * core_count) + no_of_disks)
先產生一個
src\main\java\com\example\demo\entity\Customer.java
package com.example.demo.entity;
public class Customer {
private Long id;
private String name;
private String address;
private int weight;
public Customer(){
}
public Customer(Long id, String name, String address, int weight){
this.id = id;
this.name = name;
this.address = address;
this.weight = weight;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getWeight() {
return weight;
}
public void setWeight(int weight) {
this.weight = weight;
}
}
因為資料庫的開發方式可能會改變，為了不讓將來的改變影響整個系統，通常會增加一個interface (CustomerDAO) ，在Controller裡就使用CustomerDAO而不是CustomerDAOImpl，到時候，如果CustomerDAOImpl必須更換時，就不需要改Controller的內容了。
src\main\java\com\example\demo\dao\CustomerDAO.java
package com.example.demo.dao;
import java.util.List;
import com.example.demo.entity.Customer;
public interface CustomerDAO {
public List<Customer> findAll();
}
使用JDBC讀取資料
jdbc是java內建的資料庫package。首先，透過@Autowired，讓dataSource去讀取application.properties的內容 (這是spring的用法不是標準的jdbc):
@Autowired
private DataSource dataSource;
以下的語法就跟JDBC語法是一樣的，接下來利用dataSource產生Connection:
Connection conn = dataSource.getConnection();
有些文件會使用DriverManager或Class.forName，那都是比較早期的用法。
接下來，我們利用PreparedStatement來定義sql statement:
String sql = "select id, name, address, weight from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
再來，執行sql statement，取得結果:
ResultSet rs = stmt.executeQuery();
從ResultSet裡讀取每一筆資料:
while (rs.next()){
customers.add(getCustomer(rs));
}
讀取每個欄位是利用getLong()、getString()、getInt()，根據對應欄位使用不同的方法:
Long id = rs.getLong("id");
String name = rs.getString("name");
int weight = rs.getInt("weight");
利用getCustomer將讀取的資料轉成Customer物件，再把物件新增到customers。
while (rs.next()){
customers.add(getCustomer(rs));
}
getCustomer利用getLong()、getString()、getInt()產生Customer物件
public Customer getCustomer(ResultSet rs) throws SQLException{
return new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getString("address"),
rs.getInt("weight"));
}
另外，在Spring Framework有一些stereotype (如: @Controller)，使用資料庫時，我們會將使用@Repository，這樣才能利用@Autowired來使用這個類別。
因為資料庫的開發方式可能會改變，為了不讓將來的改變影響整個系統，通常會增加一個interface (CustomerDAO) ，在Controller裡就使用CustomerDAO而不是CustomerDAODB，到時候，如果CustomerDAODB必須更換時，就不需要改Controller的內容了。為了保持彈性，一般會實作CustomerDAO interface，而不直接連接CustomerDAOImpl。
@Repository
public class CustomerDAOImpl implements CustomerDAO {
@Autowired
private DataSource dataSource;
完整的CustomerDAOImpl.java
package com.example.demo.dao;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.example.demo.entity.Customer;
@Repository
public class CustomerDAOImpl implements CustomerDAO {
@Autowired
private DataSource dataSource;
//jdbc
public List<Customer> findAll() {
List<Customer> customers = new ArrayList<Customer>();
try {
Connection conn = dataSource.getConnection();
String sql = "select id, name, address, weight from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
customers.add(getCustomer(rs));
}
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return customers;
}
public Customer getCustomer(ResultSet rs) throws SQLException{
return new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getString("address"),
rs.getInt("weight"));
}
}
在CustomerController裡呼叫findAll()
dao.findAll();
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;
@GetMapping(value = "/customer")
public List<Customer> retrieveCustomers() throws SQLException{
return dao.findAll();
}
}
使用JDBC讀取單筆資料
可以透過sql的where讀取單筆資料，在sql中利用「?」來接受參數
String sql = "select id, name, address, weight from customer where id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
設定preparedStatement的參數，第一個參數是「1」，代表第一個「?」(**注意** 不是從0開始)。第二個參數，是參數所對應的變數。
stmt.setLong(1, id);
再來，執行sql statement，取得結果:
ResultSet rs = stmt.executeQuery();
從ResultSet裡讀取資料，如果讀取得到內容，利用getCustomer將讀取的資料轉成Customer物件:
if (rs.next()) {
customer = getCustomer(rs);
}
在CustomerController裡呼叫
@GetMapping(value = {"/customer/{id}"})
public Customer retrieveOneCustomer(@PathVariable("id") Long id) throws SQLException{
return dao.findOne(id);
}
src\main\java\com\example\demo\dao\CustomerDAO.java
package com.example.demo.dao;
import java.util.List;
import com.example.demo.entity.Customer;
public interface CustomerDAO {
public List<Customer> findAll();
public Customer findOne(Long id);
}
如果我們要取得單筆資料，就是REST通常會傳遞值
http://localhost:8080/customer/2
得到的JSON內容:
{"id":2, "name":"Ben","address": "Taipei","weight":50}
** 作業 **
參考範例，改寫上週的作業去讀取資料庫的內容，這個rest controller可以回傳所有產品的資訊，以及單筆產品的資訊:
編號 / id (int) (auto increment)
產品描述 / des (String)
價格 / price (int)
產品類型 / category (String) (個人電腦、筆記型電腦、平板電腦、智慧型手機)
庫存量 / inventory (int)
安全存量 / safetyStock (int)
利用workbench或phpmyadmin輸入資料
** 注意 **
如果沒有任何資料時，程式應該要如何處理?
如果讀取單筆資料時，沒有對應的資料，程式應該要如何處理?
使用JDBC新增資料
可以透過sql的insert新增單筆資料
String sql = "insert into customer (name, address, weight) values(?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
設定preparedStatement的參數
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getAddress());
stmt.setInt(3, customer.getWeight());
執行sql statement，result為「1」，新增成功，result為「0」，新增就沒有成功。
result = stmt.executeUpdate();
在CustomerController裡呼叫
@PostMapping(value = "/customer")
public void processFormCreate(@RequestBody Customer customer) throws SQLException {
dao.insert(customer);
}
可利用postman，新增一個request，選擇POST，url: http://localhost:8080/customer/
Body輸入: (形式為raw / JSON (appication/json))
{ "name":"ruby", "address":"Taipei", weight:50 }
使用JDBC修改資料
可以透過sql的update修改單筆資料
String sql = "update customer set name=?, address=?, weight=? where id =?";
PreparedStatement stmt = conn.prepareStatement(sql);
設定preparedStatement的參數
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getAddress());
stmt.setInt(3, customer.getWeight());
stmt.setLong(4, customer.getId());
執行sql statement，result為「1」，新增成功，result為「0」，新增就沒有成功。
result = stmt.executeUpdate();
在CustomerController裡呼叫
@PutMapping(value = "/customer")
public void processFormUpdate(@RequestBody Customer customer) throws SQLException {
dao.update(customer);
}
可利用postman，新增一個request，選擇PUT，url: http://localhost:8080/customer/
Body輸入: (形式為raw / JSON (appication/json))
{ id:2 , "name":"ruby", "address":"Taipei", weight:50 }
使用JDBC刪除資料
可以透過sql的delete刪除資料
String sql = "delete from customer where id =?";
PreparedStatement stmt = conn.prepareStatement(sql);
設定preparedStatement的參數
stmt.setLong(1, id);
執行sql statement，result為「1」，新增成功，result為「0」，新增就沒有成功。如果刪除多筆資料，會回傳被刪除的筆數。
result = stmt.executeUpdate();
在CustomerController裡呼叫
@DeleteMapping(value = "/customer/{id}")
public void deleteCustomer(@PathVariable("id") Long id) {
dao.delete(id);
}
可利用postman，新增一個request，選擇DELETE，url: http://localhost:8080/customer/2
完整範例
完整的讀、新增、修改、刪除的範例如下
dao/CustomerDAO.java
package com.example.demo.dao;
import java.util.List;
import com.example.demo.entity.Customer;
public interface CustomerDAO {
public Customer findOne(Long id);
public List<Customer> findAll();
public int insert(Customer customer);
public int update(Customer customer);
public int delete(Long id);
}
dao/CustomerDAOImpl.java
package com.example.demo.dao;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.example.demo.entity.Customer;
@Repository
public class CustomerDAOImpl implements CustomerDAO {
@Autowired
private DataSource dataSource;
//jdbc
public Customer findOne(Long id) {
Customer customer = new Customer();
try {
Connection conn = dataSource.getConnection();
String sql = "select id, name, address, weight from customer where id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
customer = getCustomer(rs);
}
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return customer;
}
public List<Customer> findAll() {
List<Customer> customers = new ArrayList<Customer>();
try {
Connection conn = dataSource.getConnection();
String sql = "select id, name, address, weight from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
customers.add(getCustomer(rs));
}
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return customers;
}
public Customer getCustomer(ResultSet rs) throws SQLException{
return new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getString("address"),
rs.getInt("weight"));
}
public int insert(Customer customer) {
int result = 0;
try {
Connection conn = dataSource.getConnection();
String sql = "insert into customer (name, address, weight) values(?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getAddress());
stmt.setInt(3, customer.getWeight());
result = stmt.executeUpdate();
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return result;
}
public int update(Customer customer) {
int result = 0;
try {
Connection conn = dataSource.getConnection();
String sql = "update customer set name=?, address=?, weight=? where id =?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getAddress());
stmt.setInt(3, customer.getWeight());
stmt.setLong(4, customer.getId());
result = stmt.executeUpdate();
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return result;
}
public int delete(Long id) {
int result = 0;
try {
Connection conn = dataSource.getConnection();
String sql = "delete from customer where id =?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, id);
result = stmt.executeUpdate();
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return result;
}
}
controller/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);
}
}
** 作業 **
參考範例，將上個作業(Product)增加讀取、新增、修改、刪除資料庫內容的功能，並利用postman測試。
編號 / id (int)
產品描述 / des (String)
價格 / price (int)
產品類型 / category (String) (個人電腦、筆記型電腦、平板電腦、智慧型手機)
庫存量 / inventory (int)
安全存量 / safetyStock (int)
** 注意 **
如果修改或刪除單筆資料時，沒有對應的資料，程式應該要如何處理?
常見問題
connection不足
如果看到這個錯誤訊息，表示connection已經不足，一般而言，可能是忘了將connection關閉 (close)。
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30009ms.
請檢查是否是否遺漏了conn.close()
public int delete(Long id) {
int result = 0;
try {
Connection conn = dataSource.getConnection();
String sql = "delete from customer where id =?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setLong(1, id);
result = stmt.executeUpdate();
conn.close();
} catch(Exception e) {
//something wrong
System.out.println(e);
}
return result;
}
如果改完之後，還是有問題，那可能是connection pool太小，因為預設值只有10，如果使用的量大一點connection pool就會不夠了，請增加connection pool:
spring.datasource.hikari.maximum-pool-size=50
Choosing the right JDBC Connection Pool…
JDBC Connection pooling frameworks
the most popular choices are Tomcat JDBC & HikariCP.
connections = ((2 * core_count) + no_of_disks)
mySQL服務未啟動
如果看到這個錯誤訊息，表示無法與mysql服務溝通
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
請檢查
mysql服務或應用程式是否啟動
mysql服務或應用程式使用的port是否正確
當看到「using password: NO」，表示spring並沒有提供密碼錯誤或者權限設定錯誤
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)
請檢查
application.properties是否放在正確的檔案夾裡
application.properties是否提供密碼 (是不是打錯字)
當看當看到「using password: YES」，表示spring提供密碼，但是帳號密碼錯誤或者權限設定錯誤
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
請檢查
application.properties裡的密碼與mysql服務或應用程式使用是否一致
mysql服務或應用程式帳號的權限是否正確
處理時間 (timestamp/datetime)
如何處理時間? (Handling MySQL datetimes and timestamps in Java )
寫進資料庫時，不能用setString，要用setTimestamp，再利用date.getTime()，取得時間。
preparedStatement.setTimestamp(new Timestamp(date.getTime()));
SQL Server
Q: 想請教您一下RestController是否可以連接Microsoft sql server？如果可以，請問您知道哪裡可以找到具體方法嗎？
A:當然可以。JDBC就是把連接關聯式資料庫抽象化，可以自由的換成不同的關聯式資料庫。但是，因為不同資料庫還是有些語法上的不同，所以，還是要注意相容的問題。
要先把mysql的driver及參數改為sql的driver及參數。
可參考以下連結: