無邊界的想像力: Java SE 入門教學 - 簡單操作資料庫

更新時間：12/19/2018

前言

在建立前端即 Java 程序和後端(即資料庫)之間的連接之前，我們應該了解 JDBC 究竟是什麼以及它為何存在。

什麼是 JDBC？

JDBC 是 Java Database Connectivity 的首字母縮寫。這是 ODBC(開放式資料庫連接)的進步。JDBC 是為了將數據從前端移動到後端而開發的標準 API 規範。此 API 由用 Java 編寫的類別和介面組成。它基本上充當介面(不是我們在 Java 中使用的介面）或 Java 程序和資料庫之間的通道，即它在兩者之間建立鏈接，以便程序員可以從 Java 代碼發送數據並將其存儲在資料庫中以備將來使用。

為什麼 JDBC 會出現？

如前所述，JDBC 是 ODBC 的一個進步，ODBC 依賴於平台有很多缺點。ODBC API 是用 C、C++、Python、Core、Java 編寫的，正如我們所知，上面的語言(除了 Java 和 Python 的某些部分)都依賴於平台。因此，為了消除依賴性，JDBC 由資料庫供應商開發，該供應商由用 Java 編寫的類別和介面組成。

一、Java 程序與資料庫連接的步驟

1.1 加載驅動程式

當我們查看 API，在 Driver 介面中，明確要求：Driver 介面是每個驅動程式類別必須實現的介面。Java SQL 框架允許多個資料庫驅動程式。每個驅動程式都應該提供一個實現 Driver 介面的類別。並且明確：在加載某一 Driver 類別時，它應該創建自己的實例並向 DriverManager 註冊該實例。您可以通過以下兩種方式之一註冊驅動程式：

(i) DriverManager.registerDriver()：DriverManager 是一個擁有靜態方法 registerDriver(Driver driver) 的類別，傳入一個 Driver 驅動程式實例。例如：

Driver driver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(driver);

(ii) Class.forName()：這裡我們在運行時將驅動程式的類別文件加載到記憶體中。無需使用 new 創建物件。例如：

Class.forName(“oracle.jdbc.driver.OracleDriver”);

這兩種註冊方式有什麼不同呢？

第一種方式，相對比較好理解，就是先創建資料庫驅動，然後調用 registerDriver() 方法完成註冊。

第二種方法是利用反射(Reflection)機制來完成的，直接看的話，我們會想 Class.forName(driverClass) 只能幫助我們得到 Driver 的 Class 物件啊！為什麼會幫我們完成註冊了呢？根據類別加載機制，當執行 Class.forName("oracle.jdbc.driver.OracleDriver") 獲取其 Class 物件時， oracle.jdbc.driver.OracleDriver 就會被 JVM 加載，並進行初始化，初始化就會執行靜態代碼塊，也就會執行第一種的代碼。

推薦使用第二種方式 Class.forName() 加載驅動程式，因為
第一種方式 Driver driver = new Driver() 其在內部也執行靜態代碼，這相當於實例化了兩個 Driver 物件
第一種方式 Driver driver = new Driver() 會產生對某一種資料庫的依賴(會導入(import)驅動套件包)，耦合性較高。

1.2 獲取與資料庫連接的 Connection 物件

在加載驅動程式後，使用下列的方式建立 Connection 物件：

Connection conn = DriverManager.getConnection(url, user, password);

user：可以訪問資料庫的使用者名稱
password：可以訪問資料庫對用的使用者密碼
url：全球資源定址器(Uniform Resource Locator)，例如:

// oracle 是使用的資料庫
// thin 是使用的驅動程序
// @localhost 是存儲資料庫的 IP 地址
// 1521 是端口號
// xe 資料庫名稱
String url = "jdbc:oracle:thin:@localhost:1521:xe";

1.3 獲取用於向資料庫發送 sql 語法的 Statement 物件

建立連接後，您可以與資料庫進行溝通。Statement、CallableStatement 和 PreparedStatement 介面定義了使您能夠發送 SQL 命令和從資料庫接收數據的方法。
使用如下：

Statement stmt = conn.createStatement();

1.4 執行 SQL 命令

(i) 在查詢(Read)資料庫中的數據

Statement 介面的 executeQuery() 方法用於執行從資料庫中查詢。此方法返回 ResultSet 的物件，該物件可用於獲取資料表中的所有記錄。

(ii) 在資料庫中新增(Create)、修改(Update)、刪除(Delete)數據

Statement 介面的 executeUpdate(sql query) 方法用於執行新增、修改、刪除。

另外還有三個方法，功能分別如下：

execute(String sql)：用於向資料庫發送任意的 SQL 語法執行。
addBatch(String sql)：把多條 SQL 語法放到一個批次處理。
executeBatch()：向資料庫發送一批 SQL 語法執行。

1.5 關閉連線，釋放資源

通過關閉連線，Statement 和 ResultSet 的物件將自動關閉。

conn.close();

1.6 資料型態對應

Java 型態	SQL 型態
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
float	FLOAT
double	DOUBLE
byte[]	BINARY、VARBINARY、LONGBINARY
java.lang.String	CHAR、VARCHAR、LONGVARCHAR
java.math.BigDecimal	NUMERIC、DECIMAL
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

二、建立資料庫與使用者並連接資料庫

接續上一篇文章 Java SE 入門教學 - 簡單操作資料庫 - 環境架設，我們有新增一組帳號(my_user)、密碼(my_password)與資料庫(mydatabase)，使用這組帳號密碼連接 mydatabase 資料庫。

必須引入對應的套件包，與資料庫相關的都放在 java.sql.* 套件包中。
加載驅動，MariaDB 的套件為"org.mariadb.jdbc.Driver"。這個範例其實可以不需要加載。
連接資料庫要有 url, userName, password 資料，其中 url 如果使用不同的資料庫，其連接編號也會不同。例如：MariaDB 為 3306。
連接資料庫時，必須處理"例外"。

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        try(//獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            //獲取用於向資料庫發送 SQL 語法的 Statement
            Statement stmt = conn.createStatement()) {
            
            System.out.println("連線資料庫成功");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

接下來要使用結構化查詢語言(Structrued Query Language, SQL)，可參考 MariaDB SQL Statement 或 W3Schools SQL Tutorial。

三、創建資料表

現在要存儲數據，我們需要一個資料表來做到這一點。「CREATE TABLE」語法用於在 SQL 中創建資料表。我們知道一個資料表包含紀錄(Record, Row)和欄位(Field, Column)。因此，在創建資料表時，我們必須向 SQL 提供有關 Record 名稱，要存儲在 Record 中的數據類型，數據大小等的所有信息。現在讓我們深入了解如何使用 CREATE TABLE 語法。

CREATE TABLE table_name
(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
....
);

table_name: 資料表的名稱
column1: 第一個欄位的名稱
data_type: 特定欄位的資料型態，例如 int 為整數資料
size: 可以儲存在特定欄位資料的大小

SQL 語法建立資料表，並放入一個字串方便操作。使用 executeUpdate(String sql) 來建立資料表。

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語法的 Statement
            Statement stmt = conn.createStatement()) {
            
            System.out.println("連線資料庫成功");
            
            // SQL 建立資料表的語法 create table
            String str = "create table member (" // memeber:資料表名稱
                    + "id integer primary key," // id:欄位名稱 integer:整數,資料型別 primary key:設定主鍵
                    + "name varchar(10)," // name:第二個欄位名稱 varchar(10):字串,長度10(含中文)
                    + "sex varchar(1))"; // sex:第三個欄位名稱 varchar(1):字串,長度1(含中文)
            // 執行 SQL 語法
            stmt.executeUpdate(str);
            System.out.println("member 資料表已建立");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

四、新增(Create)

SQL 的「IINSERT INTO」語句用於在表中插入新的紀錄(Record, Row)。使用 NSERT INTO 語句插入紀錄(Record, Row)有兩種方法：

(i) 只有值：第一種方法是僅指定在沒有欄位的情況下插入的數據的值。

INSERT INTO table_name VALUES (value1, value2, value3,...);

table_name: 資料表名稱
value1, value2,.. : 新紀錄中的第一欄位、第二欄位、...等的值

(ii) 欄位和值：在第二種方法中，我們將指定要填充的欄位及其對應的值，如下所示：

INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);

table_name: 資料表名稱
column1, column2, column3,..: 特定的欄位
value1, value2, value3,... : 新紀錄中對應特定欄位的值

使用 executeUpdate(String sql) 增加紀錄。

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語法的 Statement
            Statement stmt = conn.createStatement()) {
            
            System.out.println("連線資料庫成功");
            
            // SQL 新增紀錄(Record, Row)的語法 insert into
            // mydatabase.member(id,name,sex) 指定新增的欄位順序
            // values(1,\"Tome\",\"男\") 依據欄位順序給值，字串使用跳出字元(\)
            String[] str = {
                "insert into mydatabase.member(id,name,sex) values(1,\"Tome\",\"男\")",
                "insert into mydatabase.member(id,name,sex) values(2,\"Mary\",\"女\")",
                "insert into mydatabase.member(id,name,sex) values(3,\"Helen\",\"女\")"
            };
            // 用加強版 for 迴圈，把資料一筆一筆新增
            int count = 0;
            for(String tmp:str){
                count = stmt.executeUpdate(tmp);
                System.out.println("新增:" + count + "筆");
            }
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

五、查詢(Read)

SQL 中的「SELECT」語句用於從資料庫中檢索或獲取數據，是 SQL 中最常用的語句。我們可以獲取整個資料表或根據一些指定的規則。返回的數據存儲在結果表中，此結果表也稱為「結果集(ResultSet)」。有基本語法和搭配「WHERE」過濾條件的語法。

在介紹 SQL 語法前，首先介紹結果集(ResultSet)，它是數據中查詢結果返回的一種物件，可以說結果集是一個存儲查詢結果的物件，但是結果集並不僅僅具有存儲的功能，他同時還具有操縱數據的功能，可能完成對數據的更新等。

結果集從其使用的特點上可以分為四類，這四類的結果集所具備的特點都是和 Statement 語句的創建有關，因為結果集是通過 Statement 語句執行後產生的，所以可以說，結果集具備何種特點，完全決定於 Statement。

(i) 最基本的 ResultSet：之所以說是最基本的 ResultSet 是因為，這個 ResultSet 的作用就是完成了查詢結果的存儲功能，而且只能讀取一次，不能夠來回的滾動讀取

Statement stmt = conn.createStatement();
ResultSet rs = stmt.excuteQuery(sqlStr);

由於這種結果集不支持「滾動」的讀取功能，所以如果獲得這樣一個結果集，只能使用它裡面的 next() 方法，逐一去讀取數據。

(ii) 可滾動的 ResultSet 類型：這個類型支持前後滾動取得紀錄 next()、previous()，回到第一行 first()，同時還支持 ResultSet 中的第幾行 absolute(int n)，以及移動到相對當前紀錄的第幾個紀錄 relative(int n)，要實現這樣的 ResultSet 在創建 Statement 時用如下的方法，第一個參數控制是否可滾動。

Statement stmt = conn.createStatement(int resultSetType, int resultSetConcurrency);
ResultSet rs = stmt.executeQuery(sqlStr);

resultSetType 參數是設置 ResultSet 物件的滾動類型：
TYPE_FORWARD_ONLY：只能向前滾動。
TYPE_SCROLL_INSENSITIVE：前後滾動，但對修改「不敏感」。
TYPE_SCROLL_SENSITIVE：前後滾動，但對修改「敏感」。

resultSetConcurrency 參數是設置 ResultSet 物件使否能夠修改：
CONCUR_READ_ONLY：只能讀取。
CONCUR_UPDATABLE：可以修改。

(iii) 可更新的 ResultSet 類型：要實現這樣的 ResultSet 在創建 Statement 時用如同上的方法，第二個參數控制是否可修改。

Statement stmt = conn.createStatement(TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE);

這樣的 ResultSet 物件可以完成對資料庫中的資料表修改，但是我知道 ResultSet 只是相當於資料庫中資料表的視圖，所以並不是所有的 ResultSet 只要設置了就能夠完成更新的，能夠完成更新的 ResultSet 的 SQL 語句必須要具備如下的屬性：
a、只引用了單個資料表。
b、不含有 join 或者 group by 子句。
c、那些紀錄中要包含主關鍵字(primary key)。

(iv) 可保持的 ResultSet 類型：

正常情況下如果使用 Statement 執行完一個查詢，又去執行另一個查詢時，這時候第一個查詢的結果集就會被關閉，也就是說，所有的 Statement 的查詢對應的結果集是一個，如果調用 Connection 的 commit() 方法也會關閉結果集。

可保持性就是指當 ResultSet 的結果被提交時，會被關閉還是不會被關閉。JDBC 2.0 和 1.0 提供的都是提交後 ResultSet 就會被關閉。不過在 JDBC3.0 中，我們可以設置 ResultSet 是否關閉。要完成這樣的 ResultSet 的物件的創建，要使用的 Statement 的創建要具有三個參數，如下：

Statement stmt = conn.createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability);
ResultSet rs = stmt.excuteQuery(sqlStr);

前兩個參數和之前是完全相同的，這裡只介紹第三個參數：
HOLD_CURSORS_OVER_COMMIT：表示修改提交時，不關閉 ResultSet。
CLOSE_CURSORS_AT_COMMIT：表示修改提交時，ResultSet 關閉。
不過這種功能只能在 JDBC 3.0 的驅動程式版本下才能成立。

5.1 基本語法：

SELECT column1,column2,... FROM table_name

column1,column2...: 資料表中欄位的名稱
table_name: 資料表的名稱

(i) 從資料表中獲取全部欄位的資料(使用 *)

SELECT * FROM mydatabase.member

(ii) 從資料表中獲取特定欄位的資料

SELECT id, name FROM mydatabase.member

注意：資料庫的索引編號是從 1 開始，不是從 0。

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        // SQL 查詢語法
        String str = "select * from mydatabase.member";
        //String str = "select id, name from mydatabase.member";
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語句的 Statement
            // ResultSet.TYPE_SCROLL_INSENSITIVE : 使得結果集可以來回查看記錄
            // ResultSet.CONCUR_UPDATABLE : 指定可以更新結果集
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            // 建立查詢物件
            ResultSet rs = stmt.executeQuery(str)) {
            
            System.out.println("連線資料庫成功");
            
            // 使用 Ieterator
            while(rs.next()){
                System.out.println("第" + rs.getRow() + "筆");
                System.out.print(rs.getInt("id") + "\t");
                System.out.print(rs.getString("name") + "\t");
                System.out.println(rs.getString("sex") + "\t");
            }
            /*
            while(rs.next()){
                System.out.println("第" + rs.getRow() + "筆");
                System.out.print(rs.getInt(1) + "\t");
                System.out.print(rs.getString(2) + "\t");
                System.out.println(rs.getString(3) + "\t");
            }
            */
            
            // 移到資料庫結尾
            rs.last();
            if(rs.isLast()){
                System.out.println("資料庫結尾");
            }
            
            // 移到資料庫開頭
            rs.first();
            if(rs.isFirst()){
                System.out.println("資料庫開頭");
            }
            
            // 移到資料庫第二筆
            rs.absolute(2);
            System.out.println(rs.getString("name") + "\t");
            System.out.println();
            
            // 取得欄位數量
            int columns = rs.getMetaData().getColumnCount();
            for(int i=1; i<=columns; i++){
                System.out.print(rs.getMetaData().getColumnName(i) + " : "); //取得欄位名稱
                System.out.println(rs.getMetaData().getColumnTypeName(i) ); //取得欄位資料型別
            }
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

5.2 搭配 WHERE 過濾條件的語法：

我們並不一定每一次都要將表格內的資料都完全抓出。在許多時候，我們會需要選擇性地抓資料。語法如下：

SELECT column1,column2,... FROM table_name WHERE column_name operator value;

column1,column2...: 欄位名稱
table_name: 資料表名稱
column_name: 要過濾的特定欄位名稱
operator: 要考慮過濾的操作
value: 準確的值或過濾模式以獲得結果中的相關數據

可以與 where 子句一起使用的運算符列表：

operator	description
>	Greater Than
>=	Greater than or Equal to
<	Less Than
<=	Less than or Equal to
=	Equal to
<>	Not Equal to
BETWEEN	In an inclusive Range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

如何使用這些指令並不在此篇教學中，這邊只使用「=」與「LIKE」當範例。

範例：獲取性別等於女生的會員資料

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        // SQL 查詢語法
        String str = "select * from mydatabase.member where sex=\"女\"";
        //String str = "select id, name from mydatabase.member where sex=\"女\"";
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語句的 Statement
            // ResultSet.TYPE_SCROLL_INSENSITIVE : 使得結果集可以來回查看記錄
            // ResultSet.CONCUR_UPDATABLE : 指定可以更新結果集
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            // 建立查詢物件
            ResultSet rs = stmt.executeQuery(str)) {
            
            System.out.println("連線資料庫成功");
            
            // 使用 Ieterator     
            while(rs.next()){
                System.out.println("第" + rs.getRow() + "筆");
                System.out.print(rs.getInt(1) + "\t");
                System.out.print(rs.getString(2) + "\t");
                System.out.println(rs.getString(3) + "\t");
            }
            
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

範例：獲取名字裡有小寫字母「e」的會員資料：更改 SQL 查詢語法，其中「%」(wildcard)表示此處後面的字符可以是任意長度。

// SQL 查詢語法
String str = "select * from mydatabase.member where name like '%e%' ";

六、更新(Update)

SQL 中的「UPDATE」語句用於更新資料庫中現有資料表的數據。我們可以根據我們的要求使用 UPDATE 語句更新一個欄位或多個欄位。語法如下：

UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition;

table_name: 資料表名稱。
column1: 欄位名稱。
value1: 欲更改對應欄位的值。
condition: 設定哪些紀錄(Record, Row)的特定欄位(Field, Column)需要被更新的條件。

注意：在上面的查詢中，SET 語句用於為特定欄位設置新的值，WHERE 子句用於選擇需要更新紀錄的特定欄位。如果我們沒有使用 WHERE 子句，那麼將更新所有紀錄的特定欄位。

使用 executeUpdate(String sql) 更新紀錄

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語句的 Statement。
            // 預設為不可滾動唯讀的結果集
            Statement stmt = conn.createStatement()) {
            
            System.out.println("連線資料庫成功");
            
            String str = " update mydatabase.member set name='Helem' where name='Helen' ";
            int count = stmt.executeUpdate(str);
            System.out.println("更新 " + count + " 筆");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

七、刪除(Delete)

SQL 中的「DELETE」語句用於從資料表中刪除現有記錄。我們可以刪除單個記錄或多個記錄，具體取決於我們在 WHERE 子句中指定的條件。語法如下：

DELETE FROM table_name WHERE some_condition;

table_name: 資料表名稱
some_condition: 設定要選取哪些紀錄的條件

注意：我們可以刪除單個記錄和多個記錄，具體取決於我們在 WHERE 子句中提供的條件。如果我們省略 WHERE 子句，那麼將刪除所有記錄並且資料表將為空。

使用 executeUpdate(String sql) 刪除紀錄

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 語句的 Statement。
            // 預設為不可滾動唯讀的結果集
            Statement stmt = conn.createStatement()) {
            
            System.out.println("連線資料庫成功");
            
            String str = " delete from mydatabase.member where name='Tome' ";
            int count = stmt.executeUpdate(str);
            System.out.println("刪除 " + count + " 筆");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

八、使用預處理語句(PreparedStatement)進行參數化查詢(Parameterized Queries)

如果我們的 SQL 語句是固定的，但是參數會不斷變化，那我們要怎麼辦呢？
首先看看以下的處理方式:

Statement stmt = connection.createStatement()
ResultSet rs = stmt.executeQuery("select * from mydatabase.member where id = " + userInputId);

看起來我們是解決了我們的需要，但是其實這種寫法會帶來很嚴重的問題。
加入我們的 userInputId 是 1 or 1 = 1, 那麼上面的 SQL 就會變成:

select * from mydatabase.member where id = 1 or 1 = 1

這樣，無論 id 為多少，我們都會返回所有用戶記錄。

這種動態 SQL 會造成我們著名的 SQL 注入(SQL Injection)的安全漏洞。這種漏洞除了可以竊取你的資料庫記錄外，嚴重的時候更能夠破壞你的資料庫結構，讓你的資料庫徹底毀掉。例如:

stmt.execute("insert into mydatabase.member values(......" + userInput + ")");

如果 userInput 是:
null);delete from mydatabase.member;

那麼，就會執行兩條 SQL 命令，
第一條就是創建一個 mydatabase.member 記錄，
第二條就會把所有 mydatabase.member 記錄刪掉！

為了解決這種參數會變化但是 SQL 結構固定的動態 SQL 語句，JDBC 提供了 PreparedStatment 這種操作方式，使用佔位符「?」取代動態參數，再把 pstmt 物件參考變數去設置參數。
注意：永遠對於使用者資料輸入進行字元有效性檢查！

PreparedStatement pstmt = connection.prepareStatement("select * from mydatabase.member where id = ?");
pstmt.setInt(1, userInputId);
ResultSet rs = pstmt.executeQuery();

資料庫的新增、查詢、修改、刪除都可以使用 PreparedStatement 進行參數化查詢與操作。下方範例使用預處理語句增加一筆紀錄：

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

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        String str = "insert into mydatabase.member (id, name, sex) values (?, ?, ?)";
        try(// 獲取與資料庫的連接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 獲取用於向資料庫發送 SQL 預處理語句的 PreparedStatement。
            PreparedStatement pstmt = conn.prepareStatement(str)) {
            
            System.out.println("連線資料庫成功");
            
            // 動態設定參數值
            pstmt.setInt(1, 4);
            pstmt.setString(2, "Ban");
            pstmt.setString(3, "男");
            int count = pstmt.executeUpdate();
            System.out.println("新增 " + count + " 筆");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

為什麼始終要使用 PreparedStatement 代替 Statement ?

在 JDBC 應用中，如果你已經是稍有水平開發者，你就應該始終以 PreparedStatement 代替 Statement。也就是說，在任何時候都不要使用 Statement，基於以下的原因：

(i) 代碼的可讀性和可維護性

雖然用 PreparedStatement 來代替 Statement 會使代碼多出幾行，但這樣的代碼無論從可讀性還是可維護性上來說，都比直接用 Statement 的代碼高很多檔次

stmt.executeUpdate(
"insert into table_name (col1, col2,col3, col4) values ('" + var1 + "','" + var2 + "'," + var3 + ",'" + var4 + "')"
);

perstmt = con.prepareStatement(
"insert into table_name (col1, col2,col3, col4) values (?,?,?,?)"
);

perstmt.setString(1,var1);
perstmt.setString(2,var2);
perstmt.setString(3,var3);
perstmt.setString(4,var4);

perstmt.executeUpdate();

不用我多說，對於第一種方法，別說其他人去讀你的代碼，就是你自己過一段時間再去讀，都會覺得傷心。

(ii) PreparedStatement 盡可能最大提高性能

每一種資料庫都會盡最大努力對預編譯語句提供最大的性能優化。因為預編譯語句有可能被重複調用，所以語句在被 DB 的編譯器編譯後的執行代碼被緩存下來，那麼下次調用時只要是相同的預編譯語句就不需要編譯，只要將參數直接傳入編譯過的語句執行代碼中(相當於一個涵數)就會得到執行。這並不是說只有一個 Connection 中多次執行的預編譯語句被緩存，而是對於整個 DB 中，只要預編譯的語句語法和緩存中匹配，那麼在任何時候就可以不需要再次編譯而可以直接執行。而 statement 的語句中，即使是相同操作，而由於每次操作的數據不同，所以使整個語句相匹配的機會極小，幾乎不太可能匹配，比如：

insert into table_name (col1, col2) values ('11','22');
insert into table_name (col1, col2) values ('11','23');

即使是相同操作但因為數據內容不一樣，所以整個個語句本身不能匹配，沒有緩存語句的意義。事實是沒有資料庫會對普通語句編譯後的執行代碼緩存。當然並不是所以預編譯語句都一定會被緩存，資料庫本身會用一種策略，比如使用頻率等因素來決定什麼時候不再緩存已有的預編譯結果，以保存有更多的空間存儲新的預編譯語句。

(iii) 極大地提高安全性

即使到目前為止，仍有一些人連基本的邪惡 SQL 語法都不知道。

String sql = "select * from table_name where name = '" + varname + "' and passwd = '" + varpasswd + "'";

如果我們把 [' or '1' = '1] 作為 varpasswd 傳入進來，用戶名隨意，看看會成為什麼？

select * from table_name = '隨意' and passwd = '' or '1' = '1';

因為 '1'='1' 肯定成立，所以可以任何通過驗證，更有甚者，把 [';drop table table_name;] 作為 varpasswd 傳入進來，則：

select * from table_name = '隨意' and passwd = '';drop table tb_name;

有些資料庫是不會讓你成功的，但也有很多資料庫就可以使這些語句得到執行。

而如果你使用預編譯語句，你傳入的任何內容就不會和原來的語句發生任何匹配的關係。只要全部使用預編譯語句，你就用不著對傳入的數據做任何過慮，而如果使用普通的 statement，有可能要對 drop... 等做費盡心機的判斷和過慮。

九、RowSet 介面

與 ResultSet 相比，RowSet 默認是可滾動的、可更新的、可序列化的結果集。而且作為 javaBean 使用，因此能方便的在網絡上傳輸，用於同步兩端的數據。對於離線 RowSet 而言程序再創建 RowSet 時已經把數據從資料庫讀取到記憶體，因此能充分利用電腦的記憶體，從而降低服務器的負載。

java 7 提供了 RowSetProvider，可以利用 RowSetProvider.newFactory() 方法獲得 RowSetFactory 物件。
RowSetFactory 中有五個常用的方法可以獲得 RowSet 的物件實例。分別是：

createJdbcRowSet()：直接繼承於 ResultSet 與 RowSet，僅僅就是 ResultSet 的擴展，和 ResultSet 一樣不是離線的，使用時必須保持在線狀態！
createCachedRowSet()：也直接繼承於 ResultSet 與 RowSet，比 JdbcRowSet 更進一步，支持離線緩存。只有當從資料庫讀取數據或是往資料庫寫入數據的時候才會與資料庫建立連接，它提供了一種輕量級的訪問資料庫的方式，其數據均存在內存中。
createWebRowSet()：繼承自 CachedRowSet，可以將 WebRowSet 物件輸出成 XML 格式。
createJoinRowSet()：繼承自 CachedRowSet 與 WebRowSet，可以將多個 RowSet 物件進行 SQL Join 語句的合併。
createFilterRowSet()：繼承自 CachedRowSet 與 WebRowSet，可以根據設置條件得到數據的子集。

RowSet 介面定義了幾個常用的方法：(直接法) 將 username、password、sql、url 等參數傳給 RowSet，然後用 execute 執行 sql，為 RowSet 填充數據。

setUrl(String url)
setUserName(String name)
setPassword(String password)
setCommand(String sql)
execute()

如果是「CachedRowSet 或其子介面」有提供另一個方法可以填充數據 (間接法)：

populate(ResultSet data)
populate(ResultSet rs, int startRow)

注意：RowSet 是直接執行 SQL 語句，無法支援預處理語句(Prepared Statement)，因此如果有預處理語句的需求還是得先走 ResultSet 的處理方式。

可以離線處理的 CachedRowSet 將不介紹(進階議題)，這邊只介紹 RowSet 的使用方式，範例如下：

import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

/**
 *
 * @author Ethan
 */
public class MyDatabase {
    
    public static void main(String[] args) {
        // 要連接的資料庫 URL
        String url = "jdbc:mariadb://localhost:3306/mydatabase";
        // 連接的資料庫時使用的用戶名
        String user = "my_user";
        // 連接的資料庫時使用的密碼
        String pass = "my_password";
        
        // 加載驅動
        try {
            //DriverManager.registerDriver(new org.mariadb.jdbc.Driver()); //不推薦使用這種方法來加載驅動
            Class.forName("org.mariadb.jdbc.Driver"); //推薦使用這種方式來加載驅動
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        
        try(// 使用靜態方法取得 JdbcRowSet 物件實例
            JdbcRowSet jdrs = RowSetProvider.newFactory().createJdbcRowSet()) {
            
            // 設定 RowSet 連接資料庫的資料，並取得查詢結果
            jdrs.setUrl(url);
            jdrs.setUsername(user);
            jdrs.setPassword(pass);
            jdrs.setCommand("select * from mydatabase.member;");
            jdrs.execute();
            
            System.out.println("連線資料庫成功，且取得查詢結果");
            
            // 輸出取得的資料
            while(jdrs.next()){
                System.out.println(
                        jdrs.getInt(1) + "\t" + 
                        jdrs.getString(2) + "\t" + 
                        jdrs.getString(3)
                );
            }
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
}

十、總結

資料要連線必須有對應的驅動程式，並且資料庫的服務器正確開啟，且有足夠權限能夠訪問(新增 Create、查詢 Read、修改 Update、刪除 Delete, CRUD)。

全程使用預處理語句(Prepared Statement)進行 SQL 語句的處理，效能較好、閱讀性與安全性高。

可以使用 RowSet 處理查詢(Read)後的資料，但記憶體使用量較 ResultSet 來得高。

上一篇：Java SE 入門教學 - 簡單操作資料庫 - 環境架設

下一篇：Java SE 入門教學 - 執行緒(1)

1 則留言:

Unknown2020年2月26日凌晨2:05
Easy "water hack" burns 2 lbs OVERNIGHT

Over 160 thousand women and men are using a easy and SECRET "liquid hack" to burn 2lbs each and every night while they sleep.

It's very simple and it works with anybody.

This is how you can do it yourself:

1) Hold a clear glass and fill it half glass

2) Now use this awesome hack

so you'll become 2lbs thinner the next day!
回覆刪除
回覆

新增留言

2018年12月19日星期三

Java SE 入門教學 - 簡單操作資料庫 - 語法使用

前言

一、Java 程序與資料庫連接的步驟

二、建立資料庫與使用者並連接資料庫

三、創建資料表

四、新增(Create)

五、查詢(Read)

六、更新(Update)

七、刪除(Delete)

八、使用預處理語句(PreparedStatement)進行參數化查詢(Parameterized Queries)

九、RowSet 介面

十、總結

1 則留言:

2018年12月19日 星期三

Java SE 入門教學 - 簡單操作資料庫 - 語法使用

前言

一、Java 程序與資料庫連接的步驟

二、建立資料庫與使用者並連接資料庫

三、創建資料表

四、新增(Create)

五、查詢(Read)

六、更新(Update)

七、刪除(Delete)

八、使用預處理語句(PreparedStatement)進行參數化查詢(Parameterized Queries)

九、RowSet 介面

十、總結

1 則留言:

2018年12月19日星期三