---
# System prepended metadata

title: Spring與JDBC
tags: [外訓, SP]

---

# Spring 與 JDBC

## 永續性概念
- 應用程式中物件與資料都存在記憶體，一旦JVM重啟所有物件與狀態都會消失
- Persistence 機制
    - 目的: JVM重啟，重新載入恢復上次狀態
    - 實務上最常使用:關聯式資料庫系統(RDBMS)
```mermaid
flowchart LR;
P_prev[my product]
P_new[my product]
DB[(my data)]
P_prev---->DB---->P_new
```

## JDBC
- 定義:Java提供能統一操作DB的API，底層抽換不同DB程式碼也不須異動

- 大部分的資料庫廠商都會提供符合JDBC規格的Driver(type4)
- 如果廠商沒提供Driver可以用以下方法：
    - 透過ODBC(type1)
    - 找外部廠商實做DB Client端函式庫(type2)
- 如果App不是直接連線到DB而中間透過web server取得資料(type3)
- 示意圖:
```mermaid
flowchart TB;
App[Application]
API[JDBC API]
SPI[JDBC SPI]
DR1[DB2 JDBC Driver]
DB1[(DB2)]
DR2[MySQL JDBC Driver]
DB2[(mySQL)]
JDOD[JDBC-ODBC]
ODBC
DB[(DB)]
DBLIB[DB Client Library]


App-->API-->SPI-->DR1-->DB1
SPI-->DR2-->DB2
SPI-->JDOD-->ODBC-->DB
SPI-->DBLIB-->DB
```
## 透過JDBC操作DB的步驟
1. 建立並註冊Driver物件
    ```java
    Class.forName("com.mysql.jdbc.Driver");
    //or use 
    Class.forName("com.mysql.jdbc.Driver").newInstance();

    ```
    > 為什麼要用Class.forName產生Driver instance呢?
    > 想要在不改code情況下用config抽換不同Driver

2. 透過DriverManager建立資料庫連結
    ```java 
    String url = "jdbc:mysql://localhost:3306/springdb";
    Connection con = DriverManager.getConnection(url,pw);
    ```
3. 建立Statement物件
    ```java
    Statement stmt = connection.createStatement();
    ```
4. 下達SQL指令
    ```java
    ResultSet rs = stmt.executeQuery("select * from Product");
    ```
5. 取得回傳結果
    ```java
    while(rs.next()){
        ...
    }
    ```
8. 關閉所有連線物件
     ```java
        rs.close();
        stmt.close();
        con.close();
     ```
## 優化方向
1. 簡化取得連線方法 => DataSource
2. 提升系統效率 => Connection Pool
3. 固定流程不要重複撰寫 => Template Method Pattern
4. SQL在Table欄位數量增加時容易寫錯 => O-R Mapping

## DataSource
- JDBC 2.0 加入新機制
- 本質是一個資料庫連線的工廠，同時儲存建立資料庫連線的資訊。
- 系統管理員設定好DataSource的資訊跟JNDI名稱，開發人員藉由JNDI名稱取得資料庫連線。
```java!
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/MyDataSource");
Connection con = ds.getConnection();
```
### DriverManagerDataSource
- DataSource必須從JNDI取得，如果沒有跟Java EE應用伺服器使用，很難取得。
- POJO-based元件可以從Spring提供的DriverManagerDataSource取得DataSource。

bean-config.xml:
```xml!
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClaseName" value="..."/>
    <property name="url" value="..."/>
    <property name="username" value="..."/>
    <property name="password" value="..."/>
</bean>
```

Client程式：
```java=
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans-config.xml");
DataSource ds = (DataSource)ctx.getBean("dataSource");
Connection con = ds.getConnection();
```
:::danger
DriverManagerDataSource適合在元件開發時期單元測試使用，但沒有Connection Pool機制，每次取得連線都是建立新連線。絕對不要在Web Server上使用會讓server爆掉。
:::


## Connection Pool

建立新的連線是最花時間也是最耗費資源的一步。
解決方法：建立**連線池**，預先建立連線。要用的時候提供，用完時就歸還。

可以採用Apache Jakarta Commons DBCP 專案的BasicDataSource，有Connection Pool機制。
> Tomcat也有內建Connection Pool的類別。

可以透過maxActive, maxIdle等屬性調效Connection Pool：
```xml!
<bean id="dataSource" class="org.apache.common.dbcp.BasicDataSource">
    <property name="driverClaseName" value="..."/>
    <property name="url" value="..."/>
    <property name="username" value="..."/>
    <property name="password" value="..."/>
    <property name="maxActive" value="..."/>
    <property name="maxIdle" value="..."/>
</bean>
```

Client端程式不需異動，所使用的DataSource就升級成有connection pool版本。

## 透過Spring JdbcTemplate簡化開發
- Thread-safe類別
- 建立JdbcTemplate實體時，需要先有一個DataSource傳入constructor
```java!
JdbcTemplate template = new JdbcTemplate(dataSource);

//create table
template.execute("CREATE TABLE T_USER (user_id integer, name varchar(100))");

//insert data
template.update("INSERT INTO T_USER(user_id,name) VALUE (?,?)",new Object[]{user.getId(), user.getName});

//query
//1.回傳基本型別
int i = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM T_USER");
//2.指定回傳型別
String name = (String)jdbcTemplate.queryForObject("SELECT name FROM T_USER WHERE id=？",new Object[]{id}, java.lang.String.class);
//3.回傳List of Maps
List row = jdbcTemplate.queryForList("SELECT * FROM T_USER");
Iterator it = row.iterator();
while(it.hasNext()){
    Map userMap = (Map)it.next();
    ...
}

```
## PreparedStatementCreator
```java
jdbcTemplate.update(new PreparedStatementCreator(){
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException{
        String sql = "INSERT INTO T_USER(name, age) VALUES(?,?)";
        PreparedStatement ps = con.preparedStatement(sql);
        ps.setString(1, name);
        ps.setInt(2, age);
        return ps;
    }
});

```

## PreparedStatementSetter
實作setValues方法時，jdbcTemplate會自動根據所給的SQL產生PreparedStatementCreator的實例
```java
jdbcTemplate.update("INSERT INTO T_USER(name, age) VALUES(?,?)", new PreparedStatementSetter(){
    public void setValues() throws SQLException{
        ps.setString(1, name);
        ps.setInt(2, age);
    }
})

```
## RowCallbackHandler
在processRow方法，根據取得的ResultSet物件處理
```java
jdbcTemplate.query("SELECT * FROM T_USER WHERE id=？", new Object[]{id},new RowCallbackHandler(){
    public void processRow(ResultSet rs) throws SQLException{
        user.setName(rs.getString("name"));
        ...
    }
})
```
## NameParameterJdbcTemplate
用有意義的名稱取代問號，減少參數對錯機率
- 單一參數 
```java!
int id = 101;
String sql = "SELECT * FROM T_USER WHERE id = :userId";
SqlParameterSource param = new MapSqlParameterSource("userId",id);

NameParameterJdbcTemplate jdbcTemplate = new NameParameterJdbcTemplate(dataSource);

List rows = jdbcTemplate.queryForList(sql, param);

```

- 多參數 
```java!
String name ="Bob"
int age = 10;
String sql = "SELECT * FROM T_USER WHERE name = :userName and age =:userAge";
Map paramMap = new HashMap();
paramMap.put("userName", name);
paramMap.put("userAge", age);

NameParameterJdbcTemplate jdbcTemplate = new NameParameterJdbcTemplate(dataSource);

List rows = jdbcTemplate.queryForList(sql, paramMap);

```
- Bean 屬性自動偵測機制
```java!
User user = new User(name, age);
...

String sql = "INSERT INTO T_USER VALUES(:name, :age)";

SqlParameterSource param = new BeanPropertySqlParameterSource(user);

NameParameterJdbcTemplate jdbcTemplate = new NameParameterJdbcTemplate(dataSource);

List rows = jdbcTemplate.queryForList(sql, param);

```

## SimpleJdbcTemplate
- 支援泛型
```java!
int id = 100;
String sql = "SELECT * FROM T_USER WHERE id =?";

ParameterRowMapper<User> mapper = new ParameterRowMapper(){
    public User mapRow(ResultSet rs, int rowNumber)throw SQLException{
        ...
        return user;
    }
};

SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
jdbcTemplate.queryForObject(sql, mapper, id);
```
## Translated Exception
- Spring 提供的Exception:都是DataAccessException的子類別，該類別是Runtime Exception子類別 (Unchecked Exception)
- 不要求開發者一定要處理exception，當Runtime Exception發生時會一路向上拋（往外滲透）
- 適合抽離在Exception統一處理：例外處理跟業務邏輯的程式混雜會降低可讀性，且每個開發者處理例外方式也可能會有差異
- 各項例外分類:
    - 資料庫連結發生錯誤：DataAccessResourceFailureException
    - SQL語法錯誤：BadSqlGrammerException
## 以DAO封裝資料庫存取邏輯
- 中大型的專案通常會把資料操作邏輯以DAO(Data Access Object)方式封裝
- 針對每一個領域物件都提供一個DAO介面封裝該領域物件的CRUD方法
- DAO將呼叫端跟實作端透過介面隔開，開發人員可以隨時替換DAO實作物件
```mermaid
classDiagram
class Client

class ProductDao {
<<interface>>
+insert():void
+update():void
+delete():void
+find():void
}

class ProductDaoJdbcImpl
class MockProductDao

Client..>ProductDao
ProductDao <|-- ProductDaoJdbcImpl
ProductDao <|-- MockProductDao
```

```java!
public void setProductName(Product product, String name){
    product.setName(name);
    productDao.update(product);
}
```

## JdbcDaoSupport

- JdbcDaoSupport 內含setDataSource()，以及JdbcTemplate
- 不需要在每個CRUD function去取得DataSource產生樣板物件

```xml!
<bean id="dataSource" class="org.apache.common.dbcp.BasicDataSource">
    <property name="driverClaseName" value="..."/>
    <property name="url" value="..."/>
    <property name="username" value="..."/>
    <property name="password" value="..."/>
    <property name="maxActive" value="..."/>
    <property name="maxIdle" value="..."/>
</bean>

<bean id="productDao" class="ProductDaoJdbcImpl">
    <property name="dataSource" ref="dataSource" />
</bean>
```

```java=
public class ProductDaoJdbcImpl extends JdbcDaoSupport implements ProductDao{
    ...
    public void insert(Product product){
        this.getJdbcTemplate().update("INSERT INTO  T_PRODUCT (id, name) VALUES(?,?)", new Object[]{product.getId(), product.getName()});
    }
}
```
