MYSQL的Java操作器——JDBC

在學習了Mysql之後，我們就要把Mysql和我們之前所學習的Java所結合起來

而JDBC就是這樣一種工具：幫助我們使用Java語言來操作Mysql資料庫

JDBC簡介

首先我們先來了解一下JDBC

JDBC概念：

• JDBC是使用Java語言操作關聯式資料庫的一套API
• 全稱：（Java DataBase Connectivity）Java資料庫連線

JDBC本質：

• 官方（sun公司）定義的一套操作所有關係型資料庫的規則，即介面
• 各個資料庫廠商去實現這套介面，提供資料庫驅動jar包
• 我們可以使用這套介面（JDBC）程式設計，真正執行的程式碼時驅動jar包中的實現類

JDBC優點：

• 各資料庫廠商使用相同的介面，Java程式碼不需要針對不同資料庫分別開發
• 可隨時替代基層資料庫，存取資料庫的Java程式碼基本不變

JDBC快速入門

下面我們通過JDBC的基本步驟和一段範例程式碼來帶大家體驗一下JDBC

基本步驟：

// 0.建立工程，匯入驅動jar包 （資源可以在網路搜尋）
    
// 1.註冊驅動
    Class.forName("com.mysql.jdbc.Driver");
// 2.獲得連線
    Connection conn = DriverManager.getConnection(url,username,password);
// 3.定義MYSQL語句
    String sql = "~~~";
// 4.獲得執行物件
    Statement stmt = conn.createStatement();
// 5.執行SQL語句
    stmt.executeUpdate(sql);
// 6.處理返回結果
    
// 7.釋放資源

範例程式碼：

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

// JDBC快速入門
public class Demo1 {

    public static void main(String[] arg) throws Exception{

        //  1.註冊驅動(這裡是固定語法，是Java中的反射)
        Class.forName("com.mysql.jdbc.Driver");

        //  2.獲得連線(Connection是類，最後需要釋放資源)
        //  (url前面固定jdbc:mysql://127.0.0.1:3306/，最後加上你所需要的資料庫test,username為資料庫賬號,password為資料庫密碼)
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        //   3.定義SQL語句（這裡SQL語句與MYSQL語句相同）
        String sql = "update jdbc_test set age = 20 where id = 1";

        //   4.獲得執行SQL物件（這裡也是類）
        Statement stmt = conn.createStatement();

        //   5.執行SQL(注意這裡返回的是count，是sql中受影響的行數)
        int count = stmt.executeUpdate(sql);

        //   6.返回執行結果
        System.out.println(count);

        //   7.釋放記憶體
        stmt.close();
        conn.close();
    }
}

JDBC API詳解

JDBC中的API主要有四種，我們將在下面一一介紹：

DriverManager

DriverManager主要包含兩個作用：

1. 註冊驅動

// 註冊驅動是JDBC使用的第一步
Class.forName("com.mysql.jdbc.Driver");
// 上述的操作雖然是Class類的forName操作，但實際上是由Driver類的static函陣列成的，如果含興趣可以上網搜尋該程式碼

注意：

• MYSQL5之後的版本，我們可以省略註冊驅動的步驟
• 自動載入jar包中的META-INF/services/java.sql.Driver檔案中的驅動類

2. 獲取連線

//  2.獲得連線
//  (url前面固定jdbc:mysql://127.0.0.1:3306/，最後加上你所需要的資料庫test,username為資料庫賬號,password為資料庫密碼)

String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);

這裡getConnection包含三個引數，username和password分別表示賬號和密碼，不需要解釋

我們主要關注url的構造：

// 語法： jdbc:mysql://ip地址（域名）：埠號/資料庫名稱？引數鍵值對1&引數鍵值對2...

// 範例： String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";

注意：

• 當我們是主機並在主機進行MYSQL操作時，127.0.0.1:3306可以省略不寫
• 當我們正常書寫時，會有warning警告，我們可以採用useSSL=false來消除警告

最後我們給出整體程式碼來展現DriverManager所學到的部分：

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

// JDBC_DriverManager
public class Demo2 {

    public static void main(String[] arg) throws Exception{

        // 我們在MYSQL5.0以上版本可以省略註冊驅動的步驟
        // Class.forName("com.mysql.jdbc.Driver");

        // 當我們在主機使用MYSQL時，可以省略27.0.0.1:3306
        // 如果我們希望省略SSL警告語言可以設定關閉
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        String sql = "update jdbc_test set age = 20 where id = 1";

        Statement stmt = conn.createStatement();

        int count = stmt.executeUpdate(sql);

        System.out.println(count);

        stmt.close();
        conn.close();
    }
}

Connection

Connection主要包含兩個作用：

1. 獲得執行SQL物件

我們有三種方法可以獲得SQL執行物件，這裡先做出語法介紹，在後續我們會進行詳細解釋：

• 普通執行SQL物件

Statement stmt = conn.createStatement();

• 預編譯SQL的執行SQL物件：防止SQL隱碼攻擊

PreparedStatement pstmt = conn.prepareStatement(sql);

• 執行儲存過程的物件

CallableStatement cstmt = conn,prepareCall(sql);

2. 事務管理

我們可以通過Connection來進行MYSQL中的事務管理

我們通過對比MYSQL的事務來解釋：

# 開啟事務
BEGIN；

# 提交事務
COMMIT；

# 回滾事務
ROLLBACK；

-- mysql自動提交事務

我們的JDBC也同樣提供三種方法來對應mysql事務：

// 開啟事務 boolean autoCommit有兩種選擇：true為自動提交，false為手動提交（如果希望手動提交設定為false）
conn.setAutoCommit(boolean autoCommit)

// 提交事務
conn.commit();

// 回滾事務
conn.rollback();

最後我們給出整體程式碼來展現Connection所學到的部分：

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

// JDBC_Connection
public class Demo3 {

    public static void main(String[] arg) throws Exception{

        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        // 我們來研究事務的執行 （我們希望下面兩條語句在一個事務中同時執行）
        String sql1 = "update jdbc_test set age = 16 where id = 1";
        String sql2 = "update jdbc_test set age = 16 where id = 2";

        Statement stmt = conn.createStatement();

        // 我們需要在try中進行，因為為了當出錯時可以採用回滾方法
        try {
            // 開啟事務
            conn.setAutoCommit(false);

            int count1 = stmt.executeUpdate(sql1);

            System.out.println(count1);

            int count2 = stmt.executeUpdate(sql2);

            System.out.println(count2);

            // 提交事務
            conn.commit();

        } catch (Exception e){
            // 出錯時回滾事務
            conn.rollback();
        }

        stmt.close();
        conn.close();
    }
}

Statement

Statement只有一個作用：執行SQL語句

Statement的執行方式主要分為兩種：

1. executeUpdate（DML，DDL）

Statement可以執行Update操作，主要針對mysql的DML和DDL操作

// 下述是Statement執行DML和DDL操作的語法，sql為String型別的mysql語句

int count = stmt.executeUpdate(sql);

// 注意：返回值是DML和DDL語句所影響的行數

下面我們給出整體案例解釋Statement的executeUpdate操作：

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

// JDBC_DriverManager
public class Demo4 {

    public static void main(String[] arg) throws Exception{


    }

    public static void test_DML() throws SQLException {

        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        String sql = "update jdbc_test set age = 20 where id = 1";

        Statement stmt = conn.createStatement();

        int count = stmt.executeUpdate(sql);

        // 我們在實際頁面製作時，需要給使用者反饋是否成功
        // 對於DML語句，當count>0時基本為操作成功

        if(count>0){
            System.out.println("操作成功！");
        }else {
            System.out.println("操作失敗~");
        }

        stmt.close();
        conn.close();
    }

    public static void test_DDL() throws SQLException {

        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        String sql = "update jdbc_test set age = 20 where id = 1";

        Statement stmt = conn.createStatement();

        int count = stmt.executeUpdate(sql);

        // 我們在實際頁面製作時，需要給使用者反饋是否成功
        // 對於DDL語句，DROP時count為0，所以不能用count判斷，基本情況下不報錯即為成功

        stmt.close();
        conn.close();
    }
}

2. executeQuery（DQL）

Statement可以執行Query操作，主要針對mysql的DQL操作

// 下述是Statement執行DQL操作的語法，sql為String型別的mysql語句
ResultSet resultSet = stmt.executeQuery(sql);

// 注意：ResultSet是stmt.executeQuery的返回型別，下面我們將著重介紹一下

ResultSet的作用：

• 封裝了DQL查詢語句的結果

那麼如何獲得ResultSet所儲存的值：

// ResultSet具有next（）方法，其作用是：將當前遊標向前移動一行，並判斷當前行是否為有效行並返回true或false
// ResultSet在獲得結果後的遊標指向目錄行，移動一行後得到第一行資料，
boolean next():

/*
true：表示當前行有資料，可以提取或者直接輸出搜尋成功
false：表示當前行沒有資料，表示搜尋失敗
*/

// ResultSet具有getXxx（引數）方法，其作用是獲得當前行儲存的資料
Xxx name = resultSet.getXxx(引數);

/*
Xxx 表示資料型別：Int，Double，String等
引數有兩種：
	1.數位引數，表示列數
	2.字串引數，表示列名
*/

// 具體遍歷採用while語法
while(rs.next()){
    //獲得資料
    int i = rs.getInt(1);
}

下面我們給出整體案例解釋Statement的executeQuery操作：

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

// JDBC_SQL
public class Demo5 {

    public static void main(String[] arg) throws Exception{

        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        // 我們接下來講解SQL，因為SQL所獲得的資料型別為ResultSet，所以我們單獨講解

        // 先設定SQL語句
        String sql = "select * from jdbc_test";

        // 然後獲得物件
        Statement stmt = conn.createStatement();

        // 然後開始執行語句
        ResultSet resultSet = stmt.executeQuery(sql);

        // 然後我們迴圈得到資料

        // next最開始指向非資料行，然後開始執行：向下執行一行，並且判斷是否為有效行
        while (resultSet.next()){
            // 當為有效行，獲得資料（可以通過列數，也可以通過列名）
            int id = resultSet.getInt("id");
            String string = resultSet.getString(2);
            int age = resultSet.getInt("age");

            System.out.println(id);
            System.out.println(string);
            System.out.println(age);

            System.out.println("----------------");
        }

        // 釋放資源
        resultSet.close();
        stmt.close();
        conn.close();
    }
}

我們再給出一個mysql和Java結合的常用案例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import pojo.Student;

// JDBC_SQL案例演示
// 查詢jdbc_test資料庫，封裝進Student物件，並儲存到ArrayList集合中
public class Demo6 {

    public static void main(String[] arg) throws Exception{
        // 建立集合
        List<Student> list = new ArrayList<>();

        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url,username,password);

        // 先設定SQL語句
        String sql = "select * from jdbc_test";

        // 然後獲得物件
        Statement stmt = conn.createStatement();

        // 然後開始執行語句
        ResultSet resultSet = stmt.executeQuery(sql);

        // 然後我們迴圈得到資料
        while (resultSet.next()){
            // 建立物件
            Student s = new Student();

            int id = resultSet.getInt("id");
            String string = resultSet.getString(2);
            int age = resultSet.getInt("age");

            // 賦值
            s.setId(id);
            s.setName(string);
            s.setAge(age);

            // 把物件插入集合
            list.add(s);
        }

        // 我們檢視結果
        System.out.println(list);

        // 釋放資源
        resultSet.close();
        stmt.close();
        conn.close();
    }
}

PreparedStatement

PreparedStatement的作用有兩個：

1. 預防SQL隱碼攻擊問題

首先我們先來介紹SQL隱碼攻擊問題：

我們給出一段Web端之前的賬號登入匹配程式碼：

import com.itheima.pojo.Account;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 使用者登入
 */
public class JDBCDemo6_UserLogin {

    @Test
    public void testLogin() throws  Exception {
       //2. 獲取連線：如果連線的是本機mysql並且埠是預設的 3306 可以簡化書寫
       String url = "jdbc:mysql:///db1?useSSL=false";
       String username = "root";
       String password = "1234";
       Connection conn = DriverManager.getConnection(url, username, password);

       // 接收使用者輸入 使用者名稱和密碼（這裡直接寫入，我們會在JavaWeb部分學到互動）
        String name = "zhangsan";
        String pwd = "fhsjkfhdsk";

        String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";

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

        // 執行sql
        ResultSet rs = stmt.executeQuery(sql);

        // 判斷登入是否成功
        if(rs.next()){
            System.out.println("登入成功~");
        }else{
            System.out.println("登入失敗~");
        }

        //7. 釋放資源
        rs.close();
        stmt.close();
        conn.close();
    }
}

我們可以注意到上述的sql語句採用的是字串拼接，

因而當我們採用一些特殊字元時，就會導致mysql的搜尋語句變為true，搜尋到所有的mysql內容然後進入第一個賬號

import com.itheima.pojo.Account;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
* 使用者登入
*/
public class JDBCDemo6_UserLogin {
    
	 /**
     * 演示SQL隱碼攻擊
     * @throws Exception
     */
    @Test
    public void testLogin_Inject() throws  Exception {
        //2. 獲取連線：如果連線的是本機mysql並且埠是預設的 3306 可以簡化書寫
        String url = "jdbc:mysql:///db1?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收使用者輸入 使用者名稱和密碼
        String name = "hfkjsfhskj";
        // 注意這裡"' or '1' = '1"帶入到sql語句中就會導致變為select * from tb_user where true
        String pwd = "' or '1' = '1";

        String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
        System.out.println(sql);
        // 獲取stmt物件
        Statement stmt = conn.createStatement();
        // 執行sql
        ResultSet rs = stmt.executeQuery(sql);

        // 判斷登入是否成功
        if(rs.next()){
            System.out.println("登入成功~");
        }else{
            System.out.println("登入失敗~");
        }

        //7. 釋放資源
        rs.close();
        stmt.close();
        conn.close();
    }

因而我們採用PreparedStatement來預防SQL隱碼攻擊問題

我們先來介紹PreparedStatement的語法：

// 獲得PreparedStatement物件

// 首先我們需要設定sql語句，並且將引數用？代替
String sql = "select * from table where usename = ? and password = ?";
    
// 我們在建立PreparedStatement物件時需要直接將sql語句帶入
PreparedStatement pstmt = conn.PreparedStatement(sql);

// 然後我們需要設定引數值
pstmt.setXxx(引數1，引數2);

/*
Xxx: 資料型別，包括Int，Double，String等
引數1：？的位置編號，從1開始
引數2：？的值
*/

// 最後執行SQL
pstmt.executeQuery();

下面我們給出整體案例解釋PreparedStatement操作：

import org.junit.Test;

import java.sql.*;
import java.util.Date;

/**
 * API詳解：PreparedStatement
 */
public class JDBCDemo7_PreparedStatement {

    @Test
    public void testPreparedStatement() throws  Exception {
       //2. 獲取連線：如果連線的是本機mysql並且埠是預設的 3306 可以簡化書寫
       String url = "jdbc:mysql:///db1?useSSL=false";
       String username = "root";
       String password = "1234";
       Connection conn = DriverManager.getConnection(url, username, password);

       // 接收使用者輸入 使用者名稱和密碼
        String name = "zhangsan";
        String pwd = "' or '1' = '1";

        // 定義sql
        String sql = "select * from tb_user where username = ? and password = ?";

        // 獲取pstmt物件
        PreparedStatement pstmt = conn.prepareStatement(sql);

        // 設定？的值
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);

        // 執行sql
        ResultSet rs = pstmt.executeQuery();

        // 判斷登入是否成功
        if(rs.next()){
            System.out.println("登入成功~");
        }else{
            System.out.println("登入失敗~");
        }

        //7. 釋放資源
        rs.close();
        pstmt.close();
        conn.close();
    }
}

然後我們稍微拓展一下為什麼PreparedStatement為什麼不會導致字串拼接錯誤：

• 因為PreparedStatement將？直接看作字串的內容來帶入，
• 你所寫入的’或者「相當於/'和/"，不會被當作java程式碼的一部分識別

2. 預編譯SQL，提高效能

PreparedStatement預編譯原理：

• 在獲得PreparedStatement物件時，將sql語句傳送給mysql伺服器進行檢查編譯（耗時）
• 執行後將不再執行這些步驟，因而速度變快
• 如果sql模板一樣，則只需進行一次檢查，編譯

資料庫連線池

首先我們對資料庫連線池做出簡單介紹：

• 資料庫連線池是一個容器，負責分配，管理資料庫連線（Connction）
• 它允許應用程式重複使用一個現有的資料庫連線，而不是重新建立一個
• 釋放空閒時間超過最大空閒時間的資料庫連線來避免因為沒有釋放資料庫連線而引起的資料庫連線遺漏

資料庫連線池優點：

• 資源重用
• 提高系統響應速度
• 避免資料庫連線遺漏

我們採用通俗解釋來說：

• 正常情況：

我們的資料庫會有很多人存取，每當來一個人運算元據庫，MYSQL建立一個Connection，在這個人使用後，再將Connection刪除，

這種資源重複創造刪除，導致資料庫效率低下

• 資料庫連線池：

我們的資料庫具有一定的Connection數量，當每來一個人，MYSQL分配給它一個Connection，並在使用後回收;

如果這個人的使用時間超過正常時間，MYSQL將會自動回收

資料庫連線池實現

我們從三個方法講解資料庫連線池的實現：

• 標準介面：DataSource

  • 官方（SUN）提供的資料庫連線池標準介面，由第三方組織實現此介面
  • 功能：獲得連線

  Connection conn = DataSource.getConnection();
  

• 常見資料庫連線池：

  • DBCP
  • C3P0
  • Druid

• Druid（德魯伊）

  • Druid連線池是阿里巴巴開發的資料庫連線池
  • 功能強大，效能優秀，是Java語言最好的資料庫連線池之一

Druid（德魯伊）使用

Druid的使用分為五步：

• 匯入jar包
• 定義組態檔
• 載入組態檔
• 獲得資料庫連線池物件
• 獲得連線

我們給出範例程式碼：

# 定義組態檔

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# 初始化連線數量
initialSize=5
# 最大連線數
maxActive=10
# 最大等待時間
maxWait=3000

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

/**
 * Druid資料庫連線池演示
 */
public class DruidDemo {

    public static void main(String[] args) throws Exception {
        //1.匯入jar包（在網上可以找到，同JDBC包一樣）
        
        //2.定義組態檔

        //3. 載入組態檔
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        //4. 獲取連線池物件
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5. 獲取資料庫連線 Connection
        Connection connection = dataSource.getConnection();

        System.out.println(connection);


        //System.out.println(System.getProperty("user.dir"));
    }
}

結束語

好的，關於JDBC的內容我們就講解到這裡！

附錄

該文章屬於學習筆記，主要學習B站黑馬JDBC視訊

這裡附上連結：黑馬JDBC視訊全套視訊教學，快速入門jdbc原理+jdbc實戰，一套掌握_嗶哩嗶哩_bilibili