03-JDBC_ZenDei技術網路在線

1、JDBC簡介 1.1、客戶端操作MySQL資料庫的方式使用DOS命令行方式使用第三方客戶端來訪問MySQL：SQLyog、Navicat、.... 通過程式來訪問MySQL資料庫而通過Java來訪問MySQL資料庫，就是JDBC的概念 1.2、JDBC的概念什麼是JDBC Java Da ...

1、JDBC簡介

1.1、客戶端操作MySQL資料庫的方式

使用DOS命令行方式
使用第三方客戶端來訪問MySQL：SQLyog、Navicat、....
通過程式來訪問MySQL資料庫
- 而通過Java來訪問MySQL資料庫，就是JDBC的概念

1.2、JDBC的概念

什麼是JDBC
- Java Data Base Connectivity：Java資料庫連接
JDBC作用
- 通過JDBC可以讓Java程式操作資料庫
JDBC本質
- 官方（SUN）公司定義的一套操作所有關係型資料庫的規則，即介面（API）
- 各個資料庫廠商去實現這套介面，提供資料庫驅動jar包
- 我們可以使用這套介面（JDBC）編程，真正執行的代碼是驅動jar包中的實現類
JDBC的好處
- 只需要會調用JDBC介面中的方法即可，使用簡單
- 使用同一套Java代碼，進行少量的修改就可以訪問其他JDBC支持的資料庫

2、JDBC API詳解

2.1、JDBC四個核心對象

JDBC的使用步驟
- 註冊驅動
- 獲取資料庫連接
- 獲取SQL語句對象
- 執行SQL語句並返回結果
- 處理結果
- 釋放資源
JDBC四個核心對象
JDBC交互圖

2.2、JDBC註冊驅動

Java程式需要通過資料庫驅動才能連接到資料庫，因此需要註冊驅動
- 在註冊驅動之前需要先導入驅動的Jar包
JDBC註冊驅動
- java.sql.DriverManager類用於註冊驅動。提供如下方法註冊驅動
  - ```
  static void registerDriver(Driver driver)	// 向DriverManager 註冊給定驅動程式
```

示例代碼

public class Demo01 {
  public static void main(String[] args) throws Exception {
    	// 註冊驅動
    	DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  }
}

提示
- MySQL 5 之後的驅動包，可以省略註冊驅動的步驟
- 自動載入jar包中META-INF/services/java.sql.Driver文件中的驅動類

2.3、獲取Connection連接

Connection 介紹
- 表示Java程式與資料庫之間的連接，只有拿到Connection才能操作資料庫
- DriverManager類中的靜態方法描述
  
  static Connection getConnection(String url, String user, String password) 連接到給定資料庫URL，並返回連接
- 參數說明
  - String url：連接資料庫的URL，用於說明資料庫的位置
  - String user：資料庫的賬號
  - String password：資料庫的密碼
- 連接資料庫的URL地址格式
  - 協議名:子協議://伺服器名或IP地址:埠號/資料庫名
- MySQL寫法
  - jdbc:mysql://localhost:3306/day03
- 如果是本地伺服器，埠號是預設的3306，則可以簡寫
  - jdbc:mysql:///day03

DriverManager類中的靜態方法	描述
static Connection getConnection(String url, String user, String password)	連接到給定資料庫URL，並返回連接

2.4、獲取Statement對象

在java.sql.Connection介面中有如下方法獲取到Statement對象

Statement createStatement()	// 創建一個Statement對象來將SQL語句發送到資料庫

代碼案例

// 1.註冊驅動

// 2.獲取連接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");

// 3.獲取Statement
Statement stmt = conn.createStatement();

3、JDBC實現對單表數據增加、刪除、修改

我們要對資料庫進行增、刪、改、查，需要使用Statement對象來執行SQL語句

Statement的API介紹

ResultSet executeQuery(String sql)
// 用於執行查詢語句; 返回查詢到的結果集

int executeUpdate(String sql)
// 用於執行除查詢外的SQL; 返回影響的行數

4、JDBC獲取數據（Result類）

4.1、ResultSet的原理

ResultSet用於保存執行查詢SQL語句的結果。我們不能一次性去除所有的數據，需要一行一行的去除

ResultSet內部有一個指針，記錄獲取到哪行數據

獲取查詢結果

boolean   next()：
  /*
  (1) 將游標從當前位置向前移動一行 
  (2）判斷當前行是否為有效行
返回值：
  true：有效行，當前行有數據
  false：無效行，當前行沒有數據
*/

應用案例

while (rs.next()) {
        rs.getXxx(欄位名); // 取出數據
}

4.2、ResultSet獲取數據的API

ResultSet獲取數據的API是有規律的get後面加數據類型。我們統稱getXXX()

方法名	說明
boolean getBoolean(String columnLabel)	獲取boolean值
byte getByte(String columnLabel)	獲取byte值
double getDouble(String columnLabel)	獲取double值
int getInt(String columnLabel)	獲取int值
long getLong(String columnLabel)	獲取long值
String getString(String columnLabel)	獲取String值

4.3、ResultSet的getXXX方法與MySQL中數據類型的對應

註意
- 這隻是一個建議，不按這個表的對應關係也可以，只要數據類型可以自動轉換。如：int類型，使用String去取，也是可以的。但如果是String類型，使用int去取就不行

5、JDBC事務處理

JDBC操作銀行轉賬的事務
- Connection介面中與事務有關的方法
使用步驟
- 1.註冊驅動
- 2.獲取連接
- 3.開啟事務
- 4.獲取Statement對象
- 5.執行SQL語句
- 6.提交或者回滾事務
- 7.關閉資源

demo

package _02MySQL.Day03_JDBC.demo05_事務處理_重點;

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

/**
 * JDBC事務處理
 *
 * 數據準備：
 *      CREATE TABLE tb_account (
 * 	        id INT PRIMARY KEY AUTO_INCREMENT,
 * 	        NAME VARCHAR(10),
 * 	        balance DOUBLE
 *      );
 *
 *-- 添加數據
 * INSERT INTO tb_account (NAME, balance) VALUES ('張三', 1000), ('李四', 1000);
 */
public class Demo05 {
    public static void main(String[] args) throws SQLException{
        // 1. 註冊驅動
        Connection connection = null;
        try {
            // 2. 獲取資料庫連接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day03", "root", "123");

            // 3. 開啟事務
            connection.setAutoCommit(false);    // 關閉自動提交

            // 4. 獲取Statement對象
            Statement statement = connection.createStatement();

            // 5. 執行SQL
            String sql1 = "update tb_account set balance = balance - 500 where name = '張三'";
            String sql2 = "update tb_account set balance = balance + 500 where name = '李四'";

            statement.executeUpdate(sql1);
            statement.executeUpdate(sql2);

            // 模擬失敗
            // ...

            // 6. 提交事務
            System.out.println("提交事務！");
            connection.commit();
        } catch (Exception e) {
            // 6. 有異常，事務回滾
            if (connection != null) {
                connection.rollback();
            }
        }finally {
            // 7. 關閉資源
            if (connection != null) {
                connection.close();
            }
        }
    }
}

6、JDBC實現用戶登錄

模擬用戶輸出賬號和密碼登錄網站
案例分析
- 1.使用資料庫中保存用戶的賬號和密碼
- 2.讓用戶輸入賬號和密碼
- 3.使用SQL根據用戶的賬號和密碼去資料庫查詢數據
- 4.如果查詢到數據，說明登錄成功
- 5.如果查詢不到數據，說明登錄失敗

Demo

package com.itheima.demo06_JDBC實現用戶登錄;

import java.sql.*;
import java.util.Scanner;

/**
 * JDBC實現用戶登錄案例
 */
public class Demo06 {

    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        String userName = null;
        String password = null;

        // 1. 接收用戶名
        while (true) {
            System.out.println("請輸入用戶名：");
            String line = scanner.nextLine();
            if ("".equals(line)) {
                continue;
            }
            userName = line;
            break;
        }

        // 2.接收用戶密碼
        while (true) {
            System.out.println("請輸入密碼：");
            String line = scanner.nextLine();
            if ("".equals(line)) {
                continue;
            }
            password = line;
            break;
        }

        // 3.根據用戶名和密碼，查詢用戶
        String sql = "SELECT * FROM USER WHERE NAME = '" + userName + "' AND PASSWORD = '" + password + "';";
        String jdbcUrl = "jdbc:mysql://localhost:3306/day03";
        Connection conn = DriverManager.getConnection(jdbcUrl, "root", "root");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        // 4.根據查詢結果，判斷是否登錄成功
        // 4.1 如果查詢到數據，顯示登錄成功
        if (rs.next()) {
            System.out.println("登錄成功！歡迎您，" + userName);
        } else {
            // 4.2 如果查詢不到數據，顯示登錄失敗
            System.out.println("登錄失敗！用戶名或密碼錯誤...");
        }

    }
}

7、SQL註入攻擊

7.1、SQL註入問題

在我們前面JDBC實現登錄案例中，當我們輸入以下密碼的時候，可以發現賬號和密碼都不對竟然登錄成功了！
- ```
請輸入用戶名：
hehe
請輸入密碼：
a'or'1'='1
```
字元串拼接，把輸入的字元串全都將其視為sql語句，導致statement對象查詢直接為真，條件不起作用！

問題分析

"SELECT * FROM user WHERE name='" + name + "' AND password='" + password + "';";

// 將用戶輸入的賬號密碼拼接後
"SELECT * FROM user WHERE name='hehe' AND password='a'or'1'='1';"

SQL註入攻擊的原理
- 按照正常道理來說，在密碼處輸入的所有內容，都應該認為是密碼的組成
- 但是現在Statement對象在執行sql語句時，將密碼的一部分內容當作查詢條件來執行了。

7.2、解決SQL註入

PreparedStatement預編譯執行者對象
- 預編譯：SQL語句子在執行前就已經編譯好了，執行速度更快
- 安全性更高：沒有字元串拼接的SQL語句，所以避免SQL註入的問題
- 代碼的可讀性更好，是因為沒有字元串拼接
PreparedStatement使用
- SQL語句中的參數使用?作為參為輻
- 給？占位符賦值
設置參數
- setXxx(參數1，參數2)：Xxx代表數據類型
- 參數1：第幾個？（編號從1開始）
- 參數2：？的實際參數
執行SQL語句
- int executeUpdate()
  - 執行insert、update、delete語句
- ResultSet executeQuery()
  - 執行select語句

demo

String sql = "SELECT * FROM USER WHERE NAME=? AND PASSWORD=?;";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, “zhangsan”);
pstmt.setString(2, “6666”);

7.3、使用PreparedStatement改寫登錄案例

package _02MySQL.Day03_JDBC.demo08_PreparedStatement改寫登錄案例;

import java.sql.*;
import java.util.Scanner;

/**
 * PreparedStatement改寫登錄案例
 */
public class Demo08 {

    public static void main(String[] args) throws SQLException {
        // 1. 註冊驅動
        // 2. 獲取連接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day03", "root", "123");

        // 3. 獲取PreparedStatement對象
        String sql = "select * from day03.user where phoneNumber = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 準備參數
        Scanner scanner = new Scanner(System.in);
        String phoneNumber = null;
        String password = null;
        do {
            System.out.print("請輸入賬號：");
            phoneNumber = scanner.nextLine();
        } while ("".equals(phoneNumber));

        do {
            System.out.print("請輸入密碼: ");
            password = scanner.nextLine();
        } while ("".equals(password));

        // 4. 執行SQL語句
        preparedStatement.setString(1, phoneNumber);
        preparedStatement.setString(2, password);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 處理結果
        if (resultSet.next()) {
            System.out.println("歡迎您，尊敬的" + phoneNumber + "用戶");
        }else {
            System.out.println("賬號或密碼錯誤！");
        }
        // 6. 釋放資源
        connection.close();
    }
}

8、JDBC連接池

8.1、常規資料庫連接的問題

JDBC訪問資料庫的步驟
- 創建資料庫連接
- 運行SQL語句
- 關閉連接
上述動作每次資料庫訪問都會執行這樣的重覆動作
而每次創建資料庫連接的問題
- 獲取資料庫連接需要消耗比較多的資源，而每次操作都要重新獲取新的連接對象，執行一次操作就把連接關閉，而資料庫創建連接通常需要消耗相對較多的資源。這樣資料庫連接對象的使用率低

8.2、資料庫連接池簡介

現實生活中每日三餐。我們並不會吃一餐飯就將碗丟掉，而是吃完飯後將碗放到碗櫃中，下一餐接著使用。目的是重覆利用碗。資料庫連接也可以重覆使用，可以減少資料庫連接的創建次數。提高資料庫連接對象的使用率
連接池的概念
- 連接池就是一個容器，連接池中保存了一些資料庫連接，這些連接是可以重覆使用的
連接池的原理
- 1.啟動連接池，連接池就會初始化一些連接
- 2.當用戶需要使用資料庫連接，直接從連接池中取出
- 3.當用戶使用完連接，會將連接重新放回連接池中
連接池的好處
- 連接池中保存一些連接，這些連接可以重覆使用，降低數據資源的消耗

8.3、常用連接池的介紹

javax.sql.DataSource表示資料庫連接池，也是JDK中提供的一個介面，沒有具體的實現，它的實現由連接池的廠商去實現。我們只需要學習這個工具如何使用
- ```
public interface DataSource{
  	Connection getConnection();
  ...
}
```
常用的連接池實現組件有以下這些
- 阿裡巴巴-德魯伊Druid連接池
  - Druid是阿裡巴巴開源平臺上的一個項目
- C3P0是一個開源的連接池，目前使用它的開源項目有Hibernate，Spring等
- DBCP（DataBase Connection Pool）資料庫連接池，是Tomcat使用的連接池組件

8.3、Druid連接池簡介

Druid是阿裡巴巴開發的號稱為監控而生的資料庫連接池，Druid是目前最好的資料庫連接池。在功能、性能、擴展性方面，都超過了其他數據連接池，同時加入了日誌監控，可以很好的監控資料庫連接池和SQL的執行情況。
- Druid已經在阿裡巴巴部署了超過600個應用，經過一年多生產大環境部署的嚴苛考驗

Druid常用的配置參數

方法名	說明
initialSize	剛啟動連接池時，連接池中包含連接的數量
maxActive	連接池中最多可以放多少個連接
maxWait	獲取連接時最大等待時間，單位毫秒

com.alibaba.druid.pool.DruidDataSourceFactory類創建連接池的方法

public static DataSource createDataSource(Properties properties) // 創建一個連接池，連接池的參數使用properties中的數據

我們可以看到Druid連接池在創建的時候需要一個Properties對象來設置參數，所以我們使用properties文件來保存對應的參數。Druid連接池的配置文件名稱隨便，放到src目錄下麵方便載入
- druid.properties文件內容
- ```
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day17
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000
```

8.4、Druid連接池使用步驟

1.導入druid-1.0.0.jar的jar包
2.賦值druid.properties文件到src下，並設置對應參數
3.載入properties文件的內容到Properties對象中
4.創建Druid連接池，使用配置文件中的參數
5.從Druid連接池中取出連接
6.執行SQL語句
7.關閉資源

9、JDBC案例（在Java程式中實現對資料庫的增刪改查）

Demo類

package _02MySQL.Day03_JDBC.demo10_JDBC增刪改查練習;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * JDBC增刪改查練習---- 完成商品品牌數據的增刪改查操作
 * 查詢數據：查詢所有數據
 * 添加數據：添加品牌
 * 修改數據：根據id修改
 * 刪除數據：根據id刪除
 */
public class Demo10 {
    /*
        數據準備：
            創建一個商品品牌的資料庫表 tb_brand
            創建一個Brand實體類
     */
    public static void main(String[] args) throws Exception {
        // 1. 增加數據
//        addBrand();

        // 2. 刪除數據
        deleteBrand();

        // 3. 修改數據
        editBrand();

        // 4. 查詢數據
        seekBrand();
    }

    public static void addBrand() throws SQLException {
        // 獲取連接
        Connection connection = DataSourceUtils.getConnection();
        // 獲取preparedStatement對象
        String sql = "insert into day03.tb_brand (brand, description, headquarters) values" +
                "(?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "格力空調");
        preparedStatement.setString(2, "美好生活格力造");
        preparedStatement.setString(3, "China");
        // 得到statement對象返回的影響行數
        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        // 將連接返回給連接池
        connection.close();
    }

    // 根據id刪除數據
    public static void deleteBrand() throws SQLException {
        // 獲取連接
        Connection connection = DataSourceUtils.getConnection();
        // 編寫刪除數據的SQL, 並獲取preparedStatement對象
        String sql = "delete from day03.tb_brand where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 5);

        // 執行SQL, 返回影響的行數
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        // 將連接返回給連接池
        connection.close();
    }

    // 根據id編輯數據
    public static void editBrand() throws SQLException {
        // 獲取連接
        Connection connection = DataSourceUtils.getConnection();
        // 編寫sql，並獲取preparedStatement對象
        String sql = "update day03.tb_brand set brand = ?, description = ?, headquarters = ? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 補全SQL
        preparedStatement.setString(1, "華強北");
        preparedStatement.setString(2, "天上地下我華強北說第二，誰敢說第一");
        preparedStatement.setString(3, "中國深圳");
        preparedStatement.setInt(4, 1);

        // 執行SQL語句, 返回影響的行數
        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        // 關閉連接，將連接返回給連接池
        connection.close();
    }

    public static void seekBrand() throws SQLException {
        // 獲取連接
        Connection connection = DataSourceUtils.getConnection();

        // 獲取Statement對象
        String sql = "select * from day03.tb_brand";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 得到數據集合
        ResultSet resultSet = preparedStatement.executeQuery();

        // 處理數據
        List<Brand> brandList = new ArrayList<>();  // 存儲數據
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String brand = resultSet.getString("brand");
            String description = resultSet.getString("description");
            String headquarters = resultSet.getString("headquarters");
            brandList.add(new Brand(id, brand, description, headquarters));
        }

        brandList.forEach((brand) -> System.out.println("data = " + brand));
        // 將連接返回給連接池
        connection.close();

    }
}

DataSourceUtils工具類（獲取Datasource）

package _02MySQL.Day03_JDBC.demo10_JDBC增刪改查練習;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 工具類
 *      -- 創建Druid連接池
 */
public class DataSourceUtils {
    private static DataSource dataSource;

    static {    // 利用靜態代碼塊載入創建連接池的操作
        try {
            // 1. 載入配置文件
            // 獲取properties配置文件
            InputStream inputStream = DataSourceUtils.class.getResourceAsStream("brand.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            // 2. 創建連接
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

Brand實體類

package _02MySQL.Day03_JDBC.demo10_JDBC增刪改查練習;

public class Brand {
    private int id;
    private String brand;
    private String description;
    private String headquarters;

    public Brand(int id, String brand, String description, String headquarters) {
        this.id = id;
        this.brand = brand;
        this.description = description;
        this.headquarters = headquarters;
    }

    public int getId() {
        return id;
    }

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

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getHeadquarters() {
        return headquarters;
    }

    public void setHeadquarters(String headquarters) {
        this.headquarters = headquarters;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brand='" + brand + '\'' +
                ", description='" + description + '\'' +
                ", headquarters='" + headquarters + '\'' +
                '}';
    }
}

brand.properties配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day03
username=root
password=123
initialSize=5
maxActive=10
maxWait=3000