系列文章目錄

• 20級
  • Java篇
    【2021軟體創新實驗室暑假集訓】計算機的起源與大致原理
    【2021軟體創新實驗室暑假集訓】Java基礎（一）
    【2021軟體創新實驗室暑假集訓】Java基礎（二）
    應用篇
    【2021軟體創新實驗室暑假集訓】mysql資料庫與簡單sql陳述句的使用
• 19級
  • Java后端開發
    【2021軟體創新實驗室暑假集訓】Spring框架
    【2021軟體創新實驗室暑假集訓】SpringMVC框架（設計原理、簡單使用、原始碼探究）
  • web端開發
  • 移動端開發
    【2021軟體創新實驗室暑假集訓】微信小程式入門(一)
    【2021軟體創新實驗室暑假集訓】微信小程式入門(二)
  • 人工智能

注：因為集訓還沒結束，沒有統計所有人的博文，所以以上目錄并不完整，理論上20級有Java篇10篇，應用篇7篇；19級各賽道各四篇（每次上課都會有一篇博文作總結）

前言

本文主要講解JDBC的由來，JDBC的使用，JDBC的原理，以及教大家實作一個簡單的資料庫連接池，

一、JDBC的前世今生

JDBC全稱Java DataBase Connectivity（Java資料庫連接），是Java語言中用來規范客戶端程式如何來訪問資料庫的應用程式介面，提供了諸如查詢和更新資料庫中資料的方法，

早期SUN公司的天才們想撰寫一套可以連接天下所有資料庫的API，但是當他們剛剛開始時就發現這是不可完成的任務，因為各個廠商的資料庫服務器差異太大了，后來SUN開始與資料庫廠商們討論，最終得出的結論是，由SUN提供一套訪問資料庫的規范（就是一組介面），并提供連接資料庫的協議標準，然后各個資料庫廠商會遵循SUN的規范提供一套訪問自己公司的資料庫服務器的API出現，SUN提供的規范命名為JDBC，而各個廠商提供的，遵循了JDBC規范的，可以訪問自己資料庫的API被稱之為驅動！

很多時候我們往往錯誤的認識了JDBC，以為它是用于資料庫連接的框架，其實不然，它只是sun公司為了規范操作，屏蔽底層資料庫之間的差異而定義的一套標準，它位于java.sql包下面，

各個資料庫廠商自己撰寫相關的驅動包來實作這套標準，因為規范了介面，所以Java工程師們可以不用關心資料庫層面的差異，利用統一的jdbc操作資料庫即可，

二、JDBC的使用及原理

1.jdbc簡單使用步驟

以下操作以mysql為例

①加載驅動程式

//加載MySql驅動
Class.forName("com.mysql.cj.jdbc.Driver")

或者也可以這么寫

driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);

其實上面的操作本質是一樣，

看一看原始碼，Class.forName("com.mysql.cj.jdbc.Driver")這句話其實就是把就是把這個類加載到記憶體中，加載的程序中會執行static塊里的代碼，而該代碼實際上和后面一種是一樣的，

②獲得資料庫連接

根據資料庫路徑、賬號、密碼獲取資料庫連接

DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/imooc", "root", "root");

③創建Statement\PreparedStatement物件

Statement statement=conn.createStatement();
PreparedStatement ps=conn.prepareStatement(sql);

④呼叫executeQuery/executeUpdate方法

//呼叫Statement的executeQuery方法
ResultSet rs = stmt.executeQuery(sql);

//呼叫Statement的executeUpdate方法
Integer i=statement.executeUpdate(sql);

//調用PreparedStatement的executeQuery方法
ResultSet rs=ps.executeQuery();

//呼叫PreparedStatement的executeUpdate方法
Integer i=ps.executeUpdate(sql);

注：這里省略了業務處理流程

⑤關閉ResultSet、Statement/PreparedStatement、Connection

這里一定要注意關閉順序，應該以ResultSet、Statement/PreparedStatement、Connection的順序（先開后關）

同時捕獲例外時最后每個都單獨捕獲，這樣不至于在關閉時因為前面關閉出錯而導致后面資源沒關閉，

try {
            ....
} catch (SQLException throwables) {
    throwables.printStackTrace();
}finally {
    try {
        rs.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

2.簡單的例子

//資料庫路徑
private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
//資料庫賬號
private static String user="root";
//資料庫密碼
private static String password="jinhaolin";
void testQueryStatement(){
Driver driver= null;
Statement statement=null;
Connection connection=null;
ResultSet rs=null;
try {
    //創建驅動類物件
    driver = new com.mysql.cj.jdbc.Driver();
    //注冊驅動類
    DriverManager.registerDriver(driver);
    //獲取連接
    connection=DriverManager.getConnection(url,user,password);
    //獲取Statement
    statement=connection.createStatement();
    //sql陳述句
    String sql="select * from user";
    //執行查詢，獲得結果集
    rs=statement.executeQuery(sql);
    //呼叫next方法，將指標指向下一條記錄，一開始呼叫next方法后，指標指向第一條記錄，next回傳值為Boolean型別，
    // 表示是否還有下一條記錄
    while (rs.next()){
        System.out.println("-----------------------------");
        //獲取當條記錄的userName欄位并列印
        System.out.println("username"+rs.getString("userName"));
        //獲取當條記錄的password欄位并列印
        System.out.println("password"+rs.getString("password"));
        System.out.println("-----------------------------");
    }
} catch (SQLException throwables) {
    throwables.printStackTrace();
}finally {
    try {
        rs.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

3.Statement/PreparedStatement使用

①Statement

對于Statement，一般都是寫好sql陳述句或者自己拼接好對應的sql陳述句去執行（自己拼接的話要注意格式，比如字串得加上‘’，日期格式得以’yyyy-MM-dd HH:mm:ss’形式才行等等），
一般來講查詢陳述句呼叫ResultSet executeQuery(String sql)，增刪改查都是呼叫int executeUpdate(String sql)

查詢操作

ResultSet executeQuery(String sql)方法會回傳一個結果集，結果集的讀取呼叫，模板如下：

//rs中有多條記錄，其內置一個指標，每呼叫一次next方法就會跳轉到下一條記錄，初始指標指向空，
while (rs.next()){
		  //用getXXX(欄位名稱)的方式獲取當前指標指向的記錄欄位
          int id=rs.getInt("id");
          //....

以下是代碼實體

private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
private static String user="root";
private static String password="jinhaolin";
void testQueryStatement(){
    Driver driver= null;
    Statement statement=null;
    Connection connection=null;
    ResultSet rs=null;
    try {
        //創建驅動類物件
        driver = new com.mysql.cj.jdbc.Driver();
        //注冊驅動類
        DriverManager.registerDriver(driver);
        //獲取連接
        connection=DriverManager.getConnection(url,user,password);
        //獲取Statement
        statement=connection.createStatement();
        //sql陳述句
        String sql="select * from user";
        //執行查詢，獲得結果集
        rs=statement.executeQuery(sql);
        //呼叫next方法，將指標指向下一條記錄，一開始呼叫next方法后，指標指向第一條記錄，next回傳值為Boolean型別，
        // 表示是否還有下一條記錄
        while (rs.next()){
            System.out.println("-----------------------------");
            //獲取當條記錄的userName欄位并列印
            System.out.println("username"+rs.getString("userName"));
            //獲取當條記錄的password欄位并列印
            System.out.println("password"+rs.getString("password"));
            System.out.println("-----------------------------");
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

增刪改操作

關于增刪改一般呼叫executeUpdate(String sql)`即可，其回傳值為當次操作受影響的記錄行數，

void testUpdateStatement(){
     Driver driver= null;
     Statement statement=null;
     Connection connection=null;
     try {
         driver = new com.mysql.cj.jdbc.Driver();
         DriverManager.registerDriver(driver);
         connection=DriverManager.getConnection(url,user,password);
         statement=connection.createStatement();
         String sql="update user set userName='李四' where id=2";
         Integer i=statement.executeUpdate(sql);
         System.out.println("當前受影響的記錄數："+i);
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }finally {
         try {
             statement.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
         try {
             connection.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
 }

②PreparedStatement

PreparedStatement和Statement操作類似，不過PreparedStatement在創建時就需要傳入對應的sql陳述句，這是為了“預編譯”，同時sql陳述句支持占位符的方式（占位符序號從1開始），

PreparedStatement操作有兩個好處：
1.添加引數時不用操心型別轉化
我們自己拼接字串時總要為引數型別而操心，比如字串要加’’，日期要改成合適格式，而PreparedStatement會幫我們做了這些事情，
2.防止sql注入（這個后面會講）

查詢操作

void testQueryPreparedStatement(){
    Driver driver= null;
    Connection connection=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
        driver = new com.mysql.cj.jdbc.Driver();
        DriverManager.registerDriver(driver);
        connection=DriverManager.getConnection(url,user,password);
        String sql="select * from user where id=? or id=?";
        ps=connection.prepareStatement(sql);
        //傳參
        ps.setInt(1,1);
        ps.setInt(2,2);
        rs=ps.executeQuery();
        while (rs.next()){
            System.out.println("-----------------------------");
            System.out.println("username"+rs.getString("userName"));
            System.out.println("password"+rs.getString("password"));
            System.out.println("-----------------------------");
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

增刪改操作

void testUpdatePreparedStatement(){
     Driver driver= null;
     Connection connection=null;
     PreparedStatement ps=null;
     try {
         driver = new com.mysql.cj.jdbc.Driver();
         DriverManager.registerDriver(driver);
         connection=DriverManager.getConnection(url,user,password);
         String sql="update user set userName='李四' where id=?";
         ps=connection.prepareStatement(sql);
         ps.setInt(1,1);
         Integer i=ps.executeUpdate(sql);
         System.out.println("當前受影響的記錄數："+i);
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }finally {
         try {
             ps.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
         try {
             connection.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
 }

4.事務的使用

有時候我們希望有些操作要么一起執行，要么一起失敗，比如轉賬業務，需要在轉賬方賬戶扣除相應的資金，在轉入方增加相應的資金，不能說一方成功了，一方失敗了，這是不被允許，

所以我們需要有一種機制能保證某幾個操作能一起成功或者一起失敗，這就叫事務機制，

資料庫事務(transaction)是訪問并可能操作各種資料項的一個資料庫操作序列，這些操作要么全部執行,要么全部不執行，是一個不可分割的作業單位，事務由事務開始與事務結束之間執行的全部資料庫操作組成，

事務使用示例：

void testTransaction(){
        Driver driver= null;
        Connection connection=null;
        PreparedStatement ps1=null,ps2=null;
        try {
            driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
            connection=DriverManager.getConnection(url,user,password);
            //將自動提交設定為false
           connection.setAutoCommit(false);
            String sql="update user set userName='王五' where id=?";
            ps1=connection.prepareStatement(sql);
            ps1.setInt(1,1);
            ps1.executeUpdate();
            ps2=connection.prepareStatement(sql);
            ps2.setInt(1,2);
            ps2.executeUpdate();
            //所有操作完成后提交事務
            connection.commit();
        } catch (SQLException throwables) {
            //列印堆疊資訊
            throwables.printStackTrace();
            try {
                //回滾事務
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            try {
                ps1.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                ps2.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

三、PreparedStatements原理

1.Sql注入

什么是sql注入？

SQL注入即是指web應用程式對用戶輸入資料的合法性沒有判斷或過濾不嚴，攻擊者可以在web應用程式中事先定義好的查詢陳述句的結尾上添加額外的SQL陳述句，在管理員不知情的情況下實作非法操作，以此來實作欺騙資料庫服務器執行非授權的任意查詢，從而進一步得到相應的資料資訊，

舉個例子，比如我后端有這么一句sql陳述句

delete form user where id=XXX

其中XXX是前端傳過來的資料，正常情況下前端傳過來需要洗掉的id，后端就會洗掉相應的用戶記錄，

但是，總有那么些人盯著你的系統想搞些破壞，他們在前端輸入字串"1 or 1=1",查這么一看你是不是覺得很奇怪，但當你將它拼入你的sql陳述句中時，你會發現陳述句變成了delete form user where id=1 or 1=1，這時你恍然大悟，這不就是洗掉所有用戶了嗎？一拍腦門，可發現為時已晚，資料庫user表被刪的一干二凈（雖然一般可以用備份恢復），

2.為什么PreparedStatement能防止sql注入？

為什么PreparedStatement能防止sql注入呢？
查看原始碼，我們可以在ClientPreparedQueryBindings這個類中發現緣由

 @Override
    public void setString(int parameterIndex, String x) {
        if (x == null) {
            setNull(parameterIndex);
        } else {
            int stringLength = x.length();

            if (this.session.getServerSession().isNoBackslashEscapesSet()) {
                // Scan for any nasty chars

                boolean needsHexEscape = isEscapeNeededForString(x, stringLength);

                if (!needsHexEscape) {
                    StringBuilder quotedString = new StringBuilder(x.length() + 2);
                    quotedString.append('\'');
                    quotedString.append(x);
                    quotedString.append('\'');

                    byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(quotedString.toString())
                            : StringUtils.getBytes(quotedString.toString(), this.charEncoding);
                    setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR);

                } else {
                    byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(x) : StringUtils.getBytes(x, this.charEncoding);
                    setBytes(parameterIndex, parameterAsBytes);
                }

                return;
            }

            String parameterAsString = x;
            boolean needsQuoted = true;

            if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                needsQuoted = false; // saves an allocation later

                StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));

                buf.append('\'');

                //
                // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
                //

                for (int i = 0; i < stringLength; ++i) {
                    char c = x.charAt(i);

                    switch (c) {
                        case 0: /* Must be escaped for 'mysql' */
                            buf.append('\\');
                            buf.append('0');
                            break;
                        case '\n': /* Must be escaped for logs */
                            buf.append('\\');
                            buf.append('n');
                            break;
                        case '\r':
                            buf.append('\\');
                            buf.append('r');
                            break;
                        case '\\':
                            buf.append('\\');
                            buf.append('\\');
                            break;
                        case '\'':
                            buf.append('\'');
                            buf.append('\'');
                            break;
                        case '"': /* Better safe than sorry */
                            if (this.session.getServerSession().useAnsiQuotedIdentifiers()) {
                                buf.append('\\');
                            }
                            buf.append('"');
                            break;
                        case '\032': /* This gives problems on Win32 */
                            buf.append('\\');
                            buf.append('Z');
                            break;
                        case '\u00a5':
                        case '\u20a9':
                            // escape characters interpreted as backslash by mysql
                            if (this.charsetEncoder != null) {
                                CharBuffer cbuf = CharBuffer.allocate(1);
                                ByteBuffer bbuf = ByteBuffer.allocate(1);
                                cbuf.put(c);
                                cbuf.position(0);
                                this.charsetEncoder.encode(cbuf, bbuf, true);
                                if (bbuf.get(0) == '\\') {
                                    buf.append('\\');
                                }
                            }
                            buf.append(c);
                            break;

                        default:
                            buf.append(c);
                    }
                }

                buf.append('\'');

                parameterAsString = buf.toString();
            }

            byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(parameterAsString)
                    : (needsQuoted ? StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charEncoding)
                            : StringUtils.getBytes(parameterAsString, this.charEncoding));

            setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR);
        }
    }

其實mysql驅動包里的操作便是將特定字符進行轉義，防止sql注入的情況

三、實作一個簡單的資料庫連接池

雖然我們強調資料庫操作需要在最后關閉資料庫連接，但是在大多數情況下，頻繁的開關資料庫連接并不是一個明智的選擇，

我們要明白——建立和關閉資料庫連接是一個非常耗時的操作，如果我們僅僅為了增刪改查一點資料就建立/關閉一次連接，這是一種非常大的浪費，

為了避免這種情況，我們就得對連接進行復用，而復用的方法之一便是撰寫一個資料庫連接池，當然有很多開源的資料庫連接池，比如阿里的druid，不過我們這里自己撰寫一個資料庫連接池，

具體代碼如下：

import java.sql.*;
import java.util.LinkedList;
import java.util.Queue;

public class JDBCUtil {
    private static Queue<Connection> pool=new LinkedList<>();

    private static Driver driver;
    //在加載這個類時會自動執行static代碼塊的代碼，這塊代碼只會在加載這個類的時候執行（即只會執行一次）
    static {
        try {
            driver=new com.mysql.cj.jdbc.Driver();
            //注冊驅動類
            DriverManager.registerDriver(driver);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
    private static String user="root";
    private static String password="jinhaolin";

    /**
     * 獲取一個資料庫連接池
     * @return 資料庫連接
     */
    public static synchronized Connection getConnection(){
        if (pool.size()==0){
            try {
                pool.add(DriverManager.getConnection(url,user,password));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pool.poll();
    }

    /**
     * 將用完的連接放回連接池
     * @param connection 要釋放的連接
     */
    public static synchronized void release(Connection connection){
        pool.add(connection);
    }
}

以上只是一個粗淺的實作，是為了幫助大家理解資料庫連接池的功能，上述實作只有最基礎的功能，

為了避免執行緒安全問題，我在獲取和釋放連接的方法前加了synchronized 修飾，

總結

JDBC是sun公司為了方便Java操作各種資料庫而制定的介面協議，各個資料庫廠商如果要支持jdbc則需要實作jdbc制定的介面，提供相應的驅動包，

JDBC的使用很簡單，但是繁瑣，而且很多情況下我們需要重復性的勞動以實作資料庫的操作，

在今后的學習中，你應該會遇到一些好用的框架（比如mybatis）來減少這些繁瑣的操作，甚至有些自動化的工具可以一鍵生成相關代碼（因為太有規律了），
但是不要忘記這些Java持久層框架是基于jdbc的，所以了解其原理，掌握其使用，這對于一名Java后端開發工程師來說是一門必修課，

愿我們以夢為馬，不負人生韶華！
與君共勉，