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

平遙 發表於 2020-11-27

索引

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 [email protected]

--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不變,方法就不會變。

相關文章