MySql學習筆記-詳細整理-下 - ⎝⎛CodingNote.cc ⎞⎠

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

2020 年 11 月 27 日
筆記
MySQL

索引
- 測試索引
- 索引原則
許可權管理和備份
- 備份
規範資料庫設計
- 三大範式
JDBC
資料庫連接池
- 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),'[email protected]',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','[email protected]','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,"[email protected]");
            //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, "[email protected]");
            //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, "[email protected]");
            //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不變，方法就不會變。

Tags: MySQL