MySql學習筆記--詳細整理--下

目錄

• 索引
  • 測試索引
  • 索引原則
• 許可權管理和備份
  • 備份
• 規範資料庫設計
  • 三大正規化
• JDBC
  • JDBC程式
    • Statement物件
  • 工具類實現
  • sql注入
  • PreparedStatement物件
  • 事務
• 資料庫連線池
  • DBCP
  • C3P0

索引

MySQL官方對索引的定義為：索引(index)是幫助MySQL高效獲取資料的的資料結構，提取句子主幹，就可以得到作用的本質，索引是資料結構。

索引的分類

• 主鍵索引：PRIMARY KEY
  • 唯一的標識，主鍵不可重複，只能由一個列作為索引
• 唯一索引：UNIQUE KEY
  • 避免重複的列出現，唯一索引可以重複，多個列都可以標識為唯一索引
• 常規索引：KEY/INDEX
  • 預設的，index，key關鍵字設定
• 全文索引：FullText
  • 在特定的資料庫引擎下才有
  • 快速定位資料

基礎語法

--索引的使用
--在建立表的時候給欄位增加索引 或建立完表後增加
USE school
SHOW INDEX FROM student1

--增加索引
ALTER TABLE `student1` ADD FULLTEXT INDEX `studentname`(`studentname`)


--EXPLAIN 分析sql執行的狀況
 EXPLAIN SELECT * FROM `student1` --非全文索引 

EXPLAIN SELECT * FROM student1 WHERE MATCH(studentname) AGAINST('韓')

測試索引

--測試索引
CREATE TABLE `app_user`(
		`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(20) DEFAULT'' COMMENT'使用者姓名',
		`email` VARCHAR(20) NOT NULL COMMENT'使用者郵箱',
		`phone` VARCHAR(20) DEFAULT'' COMMENT'使用者電話',
		`gender` TINYINT(4) UNSIGNED DEFAULT'0' COMMENT'性別(1是男，2是女)',
		`password` VARCHAR(50) NOT NULL COMMENT '密碼',
		`age` INT(3) DEFAULT'0' COMMENT'年齡',
		`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
		`update_name` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8mb4 COMMENT'app使用者表'

--插入100萬條資料
--寫函式之前必須寫 標誌
DELIMITER $$ 
CREATE FUNCTION mock_dat() #建立一個函式
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(name,email,phone,gender,password,age)
		VALUES(CONCAT('使用者',i),'813794474@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-1000000000)+1000000000))),
		FLOOR((RAND()*2)+1),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
--
SELECT mock_dat();

SELECT * FROM app_user WHERE name = '使用者9999';
EXPLAIN SELECT * FROM app_user WHERE name = '使用者9999';
EXPLAIN SELECT * FROM app_user WHERE name = '使用者1';

--建立索引 id_表名_欄位名 on 表（欄位）
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE name = '使用者9999';
EXPLAIN SELECT * FROM app_user WHERE name = '使用者9999';

索引在小資料的時候用處不大，在大資料的時候，區別十分明顯。

索引原則

• 索引不是越多越好
• 不要對經常變動的資料加索引
• 小資料量的表不需要加索引
• 索引一般加在常用來查詢的欄位上

索引的資料結構

Hash 型別的索引

Btree：innodb預設的資料結構

許可權管理和備份

點選使用者進去可以選擇新增使用者，在伺服器許可權中可以給使用者設定不同的許可權。

也可以使用SQL賦予每個使用者許可權

--建立使用者
CREATE USER zhou IDENTIFIED BY '123456'

--修改當前使用者密碼   
SET PASSWORD = PASSWORD('111111')

--修改指定使用者密碼
SET PASSWORD FOR zhou = password('111111')

--重新命名
RENAME USER zhou TO zzzz

--使用者授權  授予全部的許可權
--ALL PRIVILEGES除了給別人授權都可以
GRANT ALL PRIVILEGES ON *.* TO zzzz

--檢視指定使用者許可權
SHOW GRANTS FOR zzzz
--檢視管理員許可權
SHOW GRANTS FOR root@localhost

--root使用者許可權  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

--撤銷許可權
REVOKE ALL PRIVILEGES *.* FROM zzzz

--刪除使用者
DROP zzzz

備份

為什麼備份：

• 保證重要的資料不丟失
• 資料轉移

MySQL資料庫備份的方式

• 直接拷貝物理檔案

• 在視覺化工具中手動匯出資料庫

  • 在想要匯出的表或者庫中，右鍵選擇轉儲sql檔案

• 使用命令列匯出 mysqldump 命令列使用

  --mysqldump -h主機 -u使用者名稱 -p密碼 資料庫 表名 >物理磁碟位置/檔名
  mysqldump -hlocalhost -uroot -p123456 school student1 > D:/a.sql
  
  --mysqldump -h主機 -u使用者名稱 -p密碼 資料庫 表名1 表名2 >物理磁碟位置/檔名
  mysqldump -hlocalhost -uroot -p123456 school student1 subject > D:/b.sql
  
  --mysqldump -h主機 -u使用者名稱 -p密碼 資料庫 >物理磁碟位置/檔名
  mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql
  
  --匯入表
  --登入
  mysql -uroot -p123456
  use school;
  sourse d/a.sql
  
  或
  mysql -u使用者名稱 -p密碼 庫名 表名<檔案路徑
  

規範資料庫設計

當資料庫比較複雜的時候，需要按規範設計。

糟糕的資料庫設計：

• 資料冗餘，浪費空間
• 資料插入和刪除都麻煩【阿里巴巴開發手冊 遮蔽使用外來鍵】
• 系統的效能差

良好的資料庫設計：

• 節省記憶體空間
• 保證資料庫的完整性
• 效能好，便於開發

軟體開發中關於資料庫的設計

• 分析需求：分析業務和需要處理的資料庫的需求
• 概要設計：設計關係圖E-R圖

設計資料庫的步驟：（個人部落格）

• 收集資訊，分析需求
  • 使用者表（使用者登入註冊，個人資訊，寫部落格，建立分類）
  • 分類表（文章分類，部落格作者）
  • 文章表（文章資訊）
  • 友連結串列（友鏈資訊）
  • 評論表 （評論資訊）
  • 自定義表（可選） （儲存其他資訊）
• 標識實體（把需求寫到欄位）
• 標識實體之間的關係
  • 寫部落格：user-->blog
  • 建立分類：user-->category
  • 友鏈：link
  • ...

三大正規化

第一正規化（1NF）：原子性，要求資料庫表的每一列都是不可分割的原子資料項。

第二正規化（2NF）：滿足第一正規化，非碼屬性必須完全依賴於候選碼，確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關。即每張表只描述一件事情。

第三正規化（3NF）：滿足第一第二正規化，任何非主屬性不依賴於其它非主屬性（即消除傳遞依賴），第三正規化需要確保資料表中每一列資料都與主鍵直接相關，而不能間接相關。

規範性和效能問題：（關聯查詢的表不得超過三張表 阿里規約）

• 考慮商業化需求和目標（成本，使用者體驗），資料庫的效能更加重要
• 在規範效能需求的時候，需要適當考慮一下規範性
• 在有些情況下，故意給表增加一些冗餘欄位（多表查詢變為單表查詢），提高效率
• 故意增加一些計算列（從大資料量降為小資料量的一些查詢）

JDBC

資料庫驅動：應用程式和資料庫之間需要有驅動來進行連線。

SUN公司為了簡化開發人員的（對資料庫的統一）操作，提供了一個（Java運算元據庫）規範，俗稱JDBC。

這些規範的具體實現由資料庫廠商去做。

對於開發人員來說只需掌握JDBC介面的操作即可。

java.sql javac.sql

JDBC程式

建立lib目錄，在lib下放入mysql-connector-java-5.1.47.jar，右擊lib，add as library。

• 載入驅動
• 連線資料庫 DriverManager
• 獲得執行sql的物件 statement
• 獲得返回的結果值
• 釋放連線

package com.zr.lesson01;

import java.sql.*;

public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //載入驅動
        Class.forName("com.mysql.jdbc.Driver");

        //使用者資訊和Url
        String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

        //連線成功 資料庫物件 DriverManager驅動管理
        Connection connection = DriverManager.getConnection(url, username, password);
        //執行sql的物件
        Statement statement = connection.createStatement();
        //執行sql
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql); //返回的結果集
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("======================================");
        }
        //釋放連線 耗資源 用完就關掉
        resultSet.close();
        statement.close();
        connection.close();
    }
}

//MySQL預設埠號3306

//Oracle ---1521
//jdbc:oracle:thin:@localhost:1521:sid

connection.commit(); //事務提交
connection.rollback(); //事務回滾
connection.setAutoCommit(true); //開啟事務提交

statement.executeQuery(); //查詢操作返回 resultset
statement.executeUpdate(); //更新，插入，刪除都用這個，返回一個受影響的行數
statement.execute(); //執行任何sql

resultset 查詢的結果集，封裝了所有的查詢結果集
    resultSet.getObject();//在不知道列型別的情況下使用
        //知道列的指定型別
        resultSet.getString();
        resultSet.getInt();
        resultSet.getDate();

		resultSet.beforeFirst();//移動到最前面
        resultSet.afterLast();//移動到最後面
        resultSet.next();//移動到下一個
		resultSet.previous();//移動到前一個
        resultSet.absolute(row);//移動到指定行

Statement物件

jdbc中的statement物件用於向資料庫傳送sql語句，想完成對資料庫的增刪改查只需傳送相應的sql語句即可。

executeUpdate執行完後，返回一個整數（即sql語句導致幾行資料發生了變化）。

executeQuery執行完後，返回代表查詢結果的resultset物件。

CRUD操作：create，使用Statement(sql)方法完成對資料庫的新增操作，示例如下：

Statement st = connection.createStatement;
String sql = "insert into user(...) values(...) "
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！")
}

CRUD操作：delete，使用Statement(sql)方法完成對資料庫的刪除操作，示例如下：

Statement st = connection.createStatement;
String sql = "delete from user wherte id=1"
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("刪除成功！")
}

CRUD操作：update，使用Statement(sql)方法完成對資料庫的修改操作，示例如下：

statement st = connection.Statement;
String sql = "update user set name=`` where id=1"
int num = st.executeUpdate;
if(num>0){
    System.out.println("修改成功！");
}

CRUD操作：read，使用Statement(sql)方法完成對資料庫的查詢操作，示例如下：

Statement st = connection.createStatement;
String sql = "select * from user "
Resultset rs = st.executeQuery(sql);
if(rs.next()){
    //根據獲取的資料型別，分別呼叫rs的相應方法對映到Java物件中
}

工具類實現

先建立一個jdbcStudy資料庫，再建立一個users使用者表，表中的欄位為id,name,password,emain,birthday

配置MySQL資料庫檔案，建立工具類

配置檔案

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useunicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

工具類

package com.zr.lesson02.utils;
//工具類
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            //讀取資源
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驅動只用載入一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //獲取連線
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }


    //釋放連線
    public static void release(Connection con, Statement st, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if (con!=null){
            con.commit();
        }
    }
}

增加資料

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//插入資料
public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection(); //獲取資料庫連線
            st = con.createStatement();//建立執行sql的物件
            String sql = "insert into users values(5,'趙雲','111111','111@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);//執行sql
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);

        }
    }
}

刪除資料

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//刪除資料
public class TestDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();//獲取資料庫連線
            st = con.createStatement();//建立執行sql的物件
            String sql = "delete from users where id=5";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("刪除建立!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

修改資料

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//修改資料
public class TestUpdate {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//獲取資料庫連線
            st = con.createStatement();//建立執行sql的物件
            String sql = "update users set name='韓信' where id=4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

新增資料

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//查詢資料
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//獲取資料庫連線
            st = con.createStatement();//建立執行sql的物件
            String sql = "select * from users";
            rs = st.executeQuery(sql);
            //if只能查出一條資料  這裡查詢所有使用者資訊使用while
            while (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birthday="+rs.getDate("birthday"));
                System.out.println("==========================");
            }
            System.out.println("查詢成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

sql注入

sql存在漏洞，會被攻擊導致資料洩露，sql會被拼接。

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//sql注入
public class SqlInjection {
    public static void main(String[] args) throws SQLException {
        //正常登入 資料庫中有的使用者和密碼
        //login("韓信","111111");

        //sql注入 會輸出所有密碼為123456的人的資訊
        login("'or'1=1","123456");
        
        //sql注入  會查詢所有資訊
        //login("'or'1=1","'or='1=1");
    }
    //登入業務
    public static void login(String username,String password) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//獲取資料庫連線
            st = con.createStatement();//建立執行sql的物件
            //select * from users where name=''or'1=1' and password='123456'
            //select * from users where name=''or'1=1' and password=''or'1=1'
            String sql = "select * from users where name='"+username+"' and password='"+password+"'";
            rs = st.executeQuery(sql);
            //if只能查出一條資料  這裡查詢所有使用者資訊使用while
            while (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birthday="+rs.getDate("birthday"));
                System.out.println("==========================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

PreparedStatement物件

PreparedStatement可以防止sql注入，而且效率更高。例項如下：

增加資料：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;
import java.util.Date;
import java.sql.*;

//PreparedStatement 插入
public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //區別
            String sql = "insert into users values(?,?,?,?,?)";//使用？佔位符 代替引數
            ps = con.prepareStatement(sql);//預編譯  先寫sql 然後不執行

            //手動給引數賦值
            ps.setInt(1,5);
            ps.setString(2,"李白");
            ps.setString(3,"123333");
            ps.setString(4,"813794474@qq.com");
            //sql.Date 資料庫    java.sql.date()
            //util.Date Java    new date().gettime()  獲得時間戳
            ps.setDate(5,new java.sql.Date(new Date().getTime()));

            //執行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

刪除資料：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

//PreparedStatement 刪除
public class TestDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //區別
            String sql = "delete from users where id=?";//使用？佔位符 代替引數
            ps = con.prepareStatement(sql);//預編譯  先寫sql 然後不執行

            //手動給引數賦值
            ps.setInt(1,5);

            //執行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("刪除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

修改資料：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

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

//PreparedStatement 修改
public class TestUpdate {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //區別
            String sql = "update users set name = ? where id = ?;";//使用？佔位符 代替引數
            ps = con.prepareStatement(sql);//預編譯  先寫sql 然後不執行

            //手動給引數賦值
            ps.setString(1,"關羽");
            ps.setInt(2,3);

            //執行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

查詢資料：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

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

//查詢
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "select * from users where id=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1,3);
            rs = ps.executeQuery();
            if (rs.next()){
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

事務

要麼都成功，要麼都失敗。

ACID原則：原子性（要麼都成功，要麼都失敗），一致性（保持最終結果的一致性），永續性（一旦提交不可逆，持久到資料庫），隔離性（多個程式互不干擾）。

隔離性的問題：

• 髒讀：一個事務讀取了另一個沒有提交的事務
• 虛讀（幻讀）：在一個事務內讀取到了別人插入的資料，導致前後讀出來的資料不一致
• 不可重複讀：在同一個事務內，重複讀取了表中的資料，表資料發生了改變

package com.zr.lesson04;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//模擬轉賬成功
public class TestTransaction {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //關閉資料庫的自動提交功能,自動開啟事務
            con.setAutoCommit(false);
            //執行業務
            String sql1 ="update account set money  = money-100  where name = 'libai'";
            st = con.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 ="update account set money  = money+100  where name = 'hanxin'";
            st = con.prepareStatement(sql2);
            st.executeUpdate();

            //業務完畢 提交事務
            con.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            try {
                con.rollback(); //失敗就回滾事務
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
           JdbcUtils.release(con,st,rs);
        }
    }
}

模擬轉賬失敗

package com.zr.lesson04;

import com.zr.lesson02.JdbcUtils;

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

//模擬轉賬失敗
public class TestTransaction02 {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //關閉資料庫的自動提交功能,自動開啟事務
            con.setAutoCommit(false);
            //執行業務
            String sql1 ="update account set money  = money-100  where name = 'libai'";
            st = con.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;//報錯

            String sql2 ="update account set money  = money+100  where name = 'hanxin'";
            st = con.prepareStatement(sql2);
            st.executeUpdate();

            //業務執行完畢 提交事務
            con.commit();
            System.out.println("成功！");
        } catch (Exception e) {
            //預設會自動回滾 也可以顯式定義
           /* try {
                con.rollback(); //失敗就回滾事務
            } catch (SQLException e2) {
                e2.printStackTrace();
            }*/
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

資料庫連線池

資料庫連線---執行完畢---釋放 連線釋放是十分浪費系統資源的。

池化技術：準備一些預先的資源，過來連線已經準備好的。

編寫連線池，實現一個介面DataSource。

開源資料來源實現：

• DBCP
• C3P0
• Durid(阿里巴巴)

DBCP

使用這些資料庫連線池之後，我們在專案開發中就不需要編寫資料庫連線的程式碼了。

DBCP：lib下匯入 commens-dbcp-1.4.jar和commens-pool-1.6.jar

建立dbconfig.properties檔案

#連線設定
driverClassname=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useunicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化連線
initialSize=10

#最大連線數量
maxActive=50

#最大空閒連線
maxIdle=20

#超時等待時間 毫秒
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=utf8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

建立DBCP工具類

package com.zr.lesson05;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;
    static{
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //建立資料來源 工廠模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //從資料來源中獲取連線
    }
    public static void release(Connection con, Statement st, ResultSet rs){
        if (con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

測試插入資料

package com.zr.lesson05;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils_DBCP.getConnection();
            //區別
            String sql = "insert into users values(?,?,?,?,?)";//使用？佔位符 代替引數
            ps = con.prepareStatement(sql);//預編譯  先寫sql 然後不執行

            //手動給引數賦值
            ps.setInt(1, 5);
            ps.setString(2, "李白");
            ps.setString(3, "123333");
            ps.setString(4, "813794474@qq.com");
            //sql.Date 資料庫    java.sql.date()
            //util.Date Java    new date().gettime()  獲得時間戳
            ps.setDate(5, new java.sql.Date(new Date().getTime()));

            //執行
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(con, ps, rs);
        }
    }
}

C3P0

C3P0：lib下匯入c3p0-0.9.5.5.jar和mchange-commens-java-0.2.19.jar

先建立c3p0-config.xml檔案，再建立JdbcUtils_C3P0工具類

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 預設配置，如果沒有指定則使用這個配置 -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquirIncrement">5</property>>
        <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">20</property>
        <property name="minPoolSize">5</property>
    </default-config>
    <!-- 命名的配置,可以通過方法呼叫實現 -->
    <named-config name="Mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <!-- 如果池中資料連線不夠時一次增長多少個 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化資料庫連線池時連線的數量 -->
        <property name="initialPoolSize">10</property>
        <!-- 資料庫連線池中的最大的資料庫連線數 -->
        <property name="maxPoolSize">25</property>
        <!-- 資料庫連線池中的最小的資料庫連線數 -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

建立C3P0工具類

package com.zr.lesson05;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;
    static{
        try{
            /*//程式碼配置
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass();
            dataSource.setUser();
            dataSource.setPassword();
            dataSource.setJdbcUrl();

            dataSource.setMaxPoolSize();
            dataSource.setMinPoolSize();*/

            dataSource = new ComboPooledDataSource("Mysql");//配置檔案寫法


            //建立資料來源 工廠模式

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

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //從資料來源中獲取連線
    }
    public static void release(Connection con, Statement st, ResultSet rs){
        if (con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

測試插入程式碼

package com.zr.lesson05;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3P0 {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils_C3P0.getConnection();
            //區別
            String sql = "insert into users values(?,?,?,?,?)";//使用？佔位符 代替引數
            ps = con.prepareStatement(sql);//預編譯  先寫sql 然後不執行

            //手動給引數賦值
            ps.setInt(1, 6);
            ps.setString(2, "李白");
            ps.setString(3, "123333");
            ps.setString(4, "813794474@qq.com");
            //sql.Date 資料庫    java.sql.date()
            //util.Date Java    new date().gettime()  獲得時間戳
            ps.setDate(5, new java.sql.Date(new Date().getTime()));

            //執行
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(con, ps, rs);
        }
    }
}

結論：無論使用什麼資料來源，本質還是一樣的，DataSource不變，方法就不會變。