使用 jdbc 操作資料庫

準備 Driver

MySQL

Maven

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.25</version>
</dependency>


下載 mysql-connector-java-8.0.25.jar

Oracle

下載 ojdbc.jar
需要注意 database 與 jdk 的版本

導入包

import java.sql.*;

註冊驅動

相當於告訴 java.sql.Driver 介面
應該由哪個類別實作
jdk 6 之後不用
因為 DriverManager 會自動獲取

MySQL

Class.forName("com.mysql.jdbc.Driver");

Oracle

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

或

Class.forName("oracle.jdbc.driver.OracleDriver");

連線

MySQL 資料庫 URL 語法是 jdbc:mysql://host/database
Oracle 資料庫 URL 語法是 jdbc:oracle:thin:@host:port:database

getConnection 有三個 overload 方法：

getConnection(String url)

final String URL = "jdbc:oracle:thin:user/pwd@123.45.67.890:1521:ABCD";
Connection connection = DriverManager.getConnection(URL);

getConnection(String url, Properties info)

final String URL = "jdbc:oracle:thin:@123.45.67.890:1521:ABCD";
Properties info = new Properties();
info.put("user", "user");
info.put("password", "pwd");

Connection conn = DriverManager.getConnection(URL, info);

getConnection(String url, String user, String password)

final String URL = "jdbc:oracle:thin:@123.45.67.890:1521:ABCD";
final String USER = "user";
final String PASSWORD = "pwd";

Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

執行

基本上就是 sql 語法
麻煩是在字串的拼接

增刪改：

Statement statement = connection.createStatement();
String sql = "INSERT INTO table VALUES ('A', 'B', 'C')";
//String sql = "DELETE FROM table WHERE id = 1";
//String sql = "UPDATE table SET name = 'A' WHERE id = 1";
statement.executeUpdate(sql);

查詢：
要注意欄位的資料型態

String sql = "SELETE * FROM table";

ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()) {
    int id  = resultSet.getInt("id");
    String name = resultSet.getString("name");
}

關閉資源

resultSet.close();
statement.close();
connection.close();

異常處理

sql 相關操作會拋出 SQLException
可以全部包進 try catch 裡

try {

} catch (SQLException e) {
    e.printStackTrace();
}

事務回滾

try {
    connection.setAutoCommit(false); //關閉自動提交
    // do A
    Savepoint savepoint1 = connection.setSavepoint("savepoint1"); //建立保存點
    // do B
    connection.commit(); //提交
} catch (SQLException e) {
    connection.rollback(); //回滾全部
    //connection.rollback(savepoint1); //回滾至保存點
}

範例

public void jdbcDemo() {
final String URL = "jdbc:oracle:thin:@123.45.67.890:1521:ABCD";
    final String USER = "user";
    final String PASSWORD = "pwd";
    
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = DriverManager.getConnection(URL, USER, PASSWORD);
        connection.setAutoCommit(false);
        statement = connection.createStatement();
        String sql = "INSERT INTO user VALUES ('John')";
        statement.executeUpdate(sql);
        
        sql = "SELETE * FROM user";
        resultSet = statement.executeQuery(sql);
        while(resultSet.next()) {
            int id  = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println("id: " + id + " name: " + name);
        }
        
        connection.commit();
    } catch (SQLException e) {
        e.printStackTrace();
        try {
            connection.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    } finally {
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Tags: , ,