---
tags: Java, Database, Spring Boot
---

# JDBC & JDBC TEMPLATE 介紹

## 前言

資料庫操作是開發程式中不可或缺的一環，在 Java 中我們經常使用 JDBC 以及延伸的 JDBC Template，本篇將透過情境模擬來學習如何建置環境與實際應用。

## 目錄

* [JDBC & JDBC TEMPLATE 介紹](#JDBC-&-JDBC-TEMPLATE-介紹)
* [前言](#前言)
* [目錄](#目錄)
* [介紹/基本概念](#介紹基本概念)
* [JDBC](#JDBC)
* [JDBC Template](#JDBC-Template)
* [DAO](#DAO)
* [Driver](#Driver)
* [常見的關鍵詞](#常見的關鍵詞)
* [應用情境說明](#應用情境說明)
* [建置環境](#建置環境)
* [MariaDB + HeidiSQL 安裝](#MariaDB-+-HeidiSQL-安裝)
* [DB 準備與建立專案](#DB-準備與建立專案)
* [實作過程](#實作過程)
* [JDBC - 建立 Java 專案](#JDBC---建立-Java-專案)
* [JDBC Template - 建立 Spring Boot 專案](#JDBC-Template---建立-Spring-Boot-專案)
* [參考資料](#參考資料)
* [撰寫紀錄](#撰寫紀錄)

## 介紹/基本概念

### JDBC

全名為 *Java Database Connectivity*，是 Java 提供客戶端連接資料庫的 API，根據不同廠商的資料庫只需要更換對應的 Driver 就可以成功連接。

### JDBC Template

Spring 封裝加工後的一個 class，使用上大同小異但是方便許多，也避免掉一些撰寫 JDBC 容易出錯的地方。


### DAO

全名為 *Data Access Object*（資料存取物件），實作 DAO 能夠將資料庫存取與業務邏輯分離，操作資料庫只需要提供參數，無須理解背後運作。

### Driver

通常為資料庫廠商提供，負責告知 Spring Boot 該如何設定與操作資料庫。

### 常見的關鍵詞

* DriverManager：負責載入 Driver 與連接資料庫的動作
* Connection：負責與資料庫溝通，所有對資料庫的操作要藉此實現
* Statement：用於執行 SQL 操作
* PreparedStatement：用於執行 SQL 操作（參數化，防止 SQL Injection）
* ResultSet：用於存放查詢結果
* SQLException：SQL 操作上發生了例外狀況

## 應用情境說明

JDBC 為 Java 中最基本的資料庫操作方式，JDBC Template 雖然較麻煩但是能夠做到很好的權責分離。

## 建置環境

### MariaDB + HeidiSQL 安裝

* Step 1 - [安裝 MariaDB :link:](https://mariadb.org/download/)

:warning: 設立 root 帳號的密碼


:warning: 確認 port 是否有阻擋 (預設 3306)


* Step 2 - [安裝 HeidiSQL :link:](https://www.heidisql.com/download.php)

* Step 3 - 測試 HeidiSQL 連線


### DB 準備與建立專案

請複製這段 table 的建立指令

CREATE TABLE `mytable` (
 `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
 `id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci'
)
COLLATE='utf8_general_ci'mytable3
ENGINE=InnoDB
;

開啟 HeidiSQL，在 MySQL 的查詢頁籤上執行這段程式碼


## 實作過程

### JDBC - 建立 Java 專案

專案結構如下


建立一個 package 名稱是 jdbc > JDBCDemo.java


建立完成後選擇 Driver 載入方式

1. 無 Maven -> 下載 Driver 並匯入專案 library

| Database | Driver Name |
| -------- |:-------------------------------------------------- |
| MySQL | [Connector for MySQL :link:][DolphinDL] |
| MariaDB | [Connector for MariaDB :link:][MariaDL] |
| MSSQL | [Microsoft JDBC Driver for SQL Server :link:][MSDL]|

[DolphinDL]: https://dev.mysql.com/downloads/connector/j/
[MariaDL]: https://mariadb.com/downloads/#connectors
[MSDL]: https://docs.microsoft.com/zh-tw/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

放入專案同階層的資料夾底下


File > Project Structure > Libraries > + > Java > 選擇 jar



2.有 Maven -> [透過 Maven 自動下載 :link:](https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.22)


* pom.xml

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.22</version>
    </dependency>
</dependencies>

#### JDBC 部分

##### 前置動作 : 宣告 Connection 物件並使用 DriverManager 建立連線

Connection conn = null;

// 使用 Class.forName() 載入 Driver 給 DriverManager
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    System.out.println("Driver loaded!");
} catch (ClassNotFoundException e) {
    System.out.println("找不到驅動程式類別");
    e.printStackTrace();
}

// 再使用 DriverManager.getConnection() 取得有效的 Connection 物件
// jdbc 格式 : jdbc:DB 廠商名/辨識字詞:URL
try {
    conn = DriverManager.getConnection
    (
     "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC", // URL
     "root", // 用户名
     "********"// 密碼
    ); 
} catch (SQLException e) {
    System.out.println("連線失敗");
    e.printStackTrace();
}

##### 實作 : 使用 Statement & PreparedStatement

// 建立 Statement 物件
Statement stmt = conn.createStatement();

新刪查改範例

// 新增一筆資料
stmt.executeUpdate("INSERT INTO MyTable( name ) VALUES ( 'my name' ) ");

// 修改一筆資料
stmt.executeUpdate("UPDATE MyTable SET name = 'Another name' WHERE name = 'my name' ");

// 刪除一筆資料
stmt.executeUpdate("DELETE FROM MyTable WHERE name = 'Another name' ");

// 查詢一筆資料
ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable");

// 確認結果集是否為空
while (rs.next()) {
    // 取得一筆資料的欄位數量
    int numColumns = rs.getMetaData().getColumnCount();
    // 使用迴圈將資料印出
    for (int i = 1; i <= numColumns; i++) {
        System.out.println("COLUMN " + i + " = " + rs.getObject(i));
    }
}

使用後關閉物件

rs.close();
stmt.close();

PrepareStatement

// 建立 PreparedStatement 物件
PreparedStatement ps = null;

新刪查改範例

// 新增一筆資料
ps = conn.prepareStatement("INSERT INTO MyTable(id, name) VALUES (?, ?)");
ps.setString(1, "2000159");
ps.setString(2, "Eddie");
ps.executeUpdate();

// 修改一筆資料
ps = conn.prepareStatement("UPDATE MyTable SET name = ? WHERE id = ? ");
ps.setString(1, "Eddie2");
ps.setString(2, "2000159");
ps.executeUpdate();

// 刪除一筆資料
ps = conn.prepareStatement("DELETE FROM MyTable WHERE id = ?");
ps.setString(1, "2000159");
ps.executeUpdate();

// 查詢一筆資料
ps = conn.prepareStatement("SELECT * FROM MyTable WHERE id = ?");
// 填入參數
ps.setString(1, "2000159");

// 執行
rs = ps.executeQuery();

// 確認結果集是否為空
while (rs.next()) {
    // 取得一筆資料的欄位數量
    int numColumns = rs.getMetaData().getColumnCount();
    // 使用迴圈將資料印出
    for (int i = 1; i <= numColumns; i++) {
        System.out.println("COLUMN " + i + " = " + rs.getObject(i));
     }
}

原始碼

* JDBCDemo.java

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo {

    public static void main(String[] args) throws SQLException {

      Connection conn = null;

                // 使用 Class.forName() 載入 Driver 給 DriverManager
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver loaded!");
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驅動程式類別");
            e.printStackTrace();
        }

        // 再使用 DriverManager.getConnection() 取得有效的 Connection 物件
        // jdbc 格式 : jdbc:DB 廠商名/辨識字詞:URL
        try {
            conn = DriverManager.getConnection
                (
                "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC", // URL
                "root", // 用户名
                "********" // 密碼
                );
            } catch (SQLException e) {
            System.out.println("連線失敗");
            e.printStackTrace();
        }

        // 建立 Statement 物件
        Statement stmt = conn.createStatement();

        // 新增一筆資料
        stmt.executeUpdate("INSERT INTO MyTable( name ) VALUES ( 'my name' ) ");

        // 修改一筆資料
        stmt.executeUpdate("UPDATE MyTable SET name = 'Another name' WHERE name = 'my name' ");

        // 刪除一筆資料
        stmt.executeUpdate("DELETE FROM MyTable WHERE name = 'Another name' ");

        // 查詢一筆資料
        ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable");

        // 確認結果集是否為空
        while (rs.next()) {
        // 取得一筆資料的欄位數量
        int numColumns = rs.getMetaData().getColumnCount();
            // 使用迴圈將資料印出
            for (int i = 1; i <= numColumns; i++) {
            System.out.println("COLUMN " + i + " = " + rs.getObject(i));
            }
        }
        // 記得關閉物件
        rs.close();
        stmt.close();


            /*
                實務上為了防止 SQL Injection 而偏好使用能夠將
                查詢條件參數化的 PreparedStatement 物件
             */

        // 建立 PreparedStatement 物件
        PreparedStatement ps = null;

        // 新增一筆資料
        ps = conn.prepareStatement("INSERT INTO MyTable(id, name) VALUES (?1, ?2)");
        ps.setString(1, "2000159");
        ps.setString(2, "Eddie");
        rs = ps.executeUpdate();

        // 修改一筆資料
        ps = conn.prepareStatement("UPDATE MyTable SET name = ?1 WHERE id = ?2 ");
        ps.setString(1, "Eddie2");
        ps.setString(2, "2000159");
        rs = ps.executeUpdate();

        // 刪除一筆資料
        ps = conn.prepareStatement("DELETE FROM MyTable WHERE id = ?1");
        ps.setString(1, "2000159");
        rs = ps.executeUpdate();

        // 查詢一筆資料
        // 使用問號作為參數
        ps = conn.prepareStatement("SELECT * FROM MyTable WHERE id = ?");
        // 填入參數
        ps.setString(1, "2000159");

        // 執行
        rs = ps.executeQuery();

        // 確認結果集是否為空
        while (rs.next()) {
            // 取得一筆資料的欄位數量
            int numColumns = rs.getMetaData().getColumnCount();
            // 使用迴圈將資料印出
            for (int i = 1; i <= numColumns; i++) {
                System.out.println("COLUMN " + i + " = " + rs.getObject(i));
            }
        }
    }
}

### JDBC Template - 建立 Spring Boot 專案

我們先建立一個簡易的 Spring Boot Web 專案
並選擇需要採用的 dependencies


如果要手動加入 Spring JDBC 依賴，需要在 pom.xml 加入

<!-- JDBC API -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

專案結構如下


#### JDBC Template 部分

Application.properties DB 連線設定參數

spring.datasource.url=jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=********

##### Controller

* DemoController.java

package com.systex.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.systex.demo.db.MyTableDAOImpl;
import com.systex.demo.db.People;

@RestController
public class DemoController {

    // 注入 DAO 物件
    @Autowired
    MyTableDAOImpl dao;

    /* 新增接口
     * Method : post
     * parameter : People
     * return : String
     */
    @PostMapping("/add")
    public String create(@RequestBody People people) {
        dao.addPeople(people);
        return "OK";
    }

    /* 刪除接口
     * Method : get
     * parameter : String
     * return : String
     */
    @GetMapping("/delete/{name}")
    public String delete(@PathVariable String name) {
        dao.deletePeople(name);
        return "OK";
    }

    /* 查詢接口
     * Method : get
     * parameter : String
     * return : String
     */
    @GetMapping("/search/{id}")
    public String search(@PathVariable String id) {
        People p = dao.searchPeople(id);
        return p.getName();
    }

    /* 修改接口
     * Method : post
     * parameter : People
     * return : String
     */
    @PostMapping("/update")
    public String update(@RequestBody People people) {
        dao.modifyPeople(people);
        return "OK";
    }
}

##### DAO 介面與對應資料表的物件

* IMyTableDAO.java

定義新刪查改方法

package com.systex.demo.db;

public interface IMyTableDAO {

    // 新增人員
    public void addPeople(People p);
    // 刪除人員
    public void deletePeople(String name);
    // 查詢人員
    public People searchPeople(String id);
    // 修改人員
    public void modifyPeople(People p);

}

* People.java

定義資料庫結構

package com.systex.demo.db;

public class People {
    
    // 欄位 : 人員 ID
    private String id;
    
    // 欄位 : 人員姓名
    private String name;

    public void setId(String id) {
        this.id = id;
    }

    public String getId() {
        return id;
    }
    
    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

}

##### 實作 DAO 介面

* MyTableDAOImpl.java

package com.systex.demo.db;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

@Service
public class MyTableDAOImpl implements IMyTableDAO {

    public MyTableDAOImpl() {

    }

    // 使用 Spring bean 自動注入 jdbcTemplate 物件
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void addPeople(People p) {
        String SQL = "INSERT INTO MyTable(id, name) VALUES (?,?) ";
        jdbcTemplate.update(SQL, p.getId(), p.getName());
    }

    @SuppressWarnings("deprecation")
    public People searchPeople(String id) {
        final People people = new People();
        jdbcTemplate.query("SELECT * FROM MyTable WHERE id = ?", new Object[] { id },
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        System.out.println(rs.getString("id"));
                        System.out.println(rs.getString("name"));
                        people.setId(rs.getString("id"));
                        people.setName(rs.getString("name"));
                    }
                });
        return people;
    };

    public void deletePeople(String name) {
        String SQL = "DELETE FROM MyTable WHERE name = ? ";
        jdbcTemplate.update(SQL, name);
    };

    public void modifyPeople(People p) {
        String SQL = "Update MyTable SET name = ? WHERE id = ?";
        jdbcTemplate.update(SQL, p.getName(), p.getId());
    };

}

##### 細部說明 - 新增

新增 Method 使用 jdbcTemplate.update（SQL 指令 (String),參數 (any)args[]）

public void addPeople(People p) {
        String SQL = "INSERT INTO MyTable(id, name) VALUES (?,?) ";
        jdbcTemplate.update(SQL, p.getId(), p.getName());
    }

執行結果


##### 細部說明 - 刪除

新增 Method 使用 jdbcTemplate.update(SQL 指令 (String),參數 (any)args[])

public void deletePeople(String name) {
    String SQL = "DELETE FROM MyTable WHERE name = ? ";
    jdbcTemplate.update(SQL, name);
};

執行結果


##### 細部說明 - 查詢

新增 Method 使用 jdbcTemplate.update（SQL 指令 (String),參數 (any)args[]）

public People searchPeople(String id) {
    String SQL = "SELECT * FROM MyTable WHERE id = ?";

    // 這裡使用 Mapper 自動映射，不擅使用的話也可以使用 jdbcTemplate.queryForList() 自行包裝 People 物件
    @SuppressWarnings("deprecation")
    public People searchPeople(String id) {
        People people = new People();
        jdbcTemplate.query("SELECT * FROM MyTable WHERE id = ?", new Object[] { id },
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        people.setId(rs.getString("id"));
                        people.setName(rs.getString("name"));
                    }
                });
        return people;
    };

執行結果


##### 細部說明 - 修改

新增 Method 使用 jdbcTemplate.update（SQL 指令 (String),參數 (any)args[]）

public void modifyPeople(People p) {
        String SQL = "Update MyTable SET name = ? WHERE id = ?";
        jdbcTemplate.update(SQL, p.getName(), p.getId());
};

執行結果


---

## 參考資料

* [關於 JdbcTemplate 你只需要這一篇](https://medium.com/@steph.c/jdbctemplate-%E7%AF%84%E4%BE%8B-2c9a1f3718ba)
* [使用 JdbcTemplate](https://openhome.cc/Gossip/SpringGossip/UseJdbcTemplate.html)
* [Java Tutorial 第三堂（2）使用 spring-jdbc 存取資料庫](https://openhome.cc/Gossip/CodeData/JavaTutorial/SpringJdbc.html)

## 撰寫紀錄

| 人員 | 日期 | 修改紀錄 |
| - | - | - |
| 懿修 | 2022/7 | 初版 |