JAVA基础-JDBC2

一、数据库事务

1.1.数据库事务

• 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
• 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
• 为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。

1.2.JDBC 事务处理

• 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
• 为了让多个SQL 语句作为一个事务执行：
  1. 调用Connection 对象的setAutoCommit(false);以取消自动提交事务。
  2. 在所有的SQL 语句都成功执行后，调用commit(); 方法提交事务。
  3. 在出现异常时，调用rollback(); 方法回滚事务。
  4. 若此时Connection 没有被关闭, 则需要恢复其自动提交状态。

1.3.数据库事务使用的过程

public class CommitTest {
    /**
     * 针对于数据表user_table来说
     * AA用户给BB用户转账100
     * update user_table set balance =balance - 100 where user='AA'
     * update user_table set balance =balance + 100 where user='BB'
     */
    @Test
    public void testUpdate(){
        
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        update(sql1, "AA");
        //模拟网络异常
        System.out.println(10/0);
        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        update(sql2, "BB");

        System.out.println("转账成功");
    }

    @Test
    public void testUpdateWithTx() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnect();
            System.out.println(conn.getAutoCommit());//true
            //1.取消数据的自动提交
            conn.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update2(conn,sql1, "AA");

            //模拟网络异常
            System.out.println(10 / 0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update2(conn,sql2, "BB");

            System.out.println("转账成功");

            //2.提交数据
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{

            JDBCUtils.closeResource(conn, null);
        }

    }

    /**
     * 数据库的增删改查操作
     */
    public int update(String sql,Object ...args){
        //未考虑事务IDE操作
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnect();
            ps = conn.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }

    public int update2(Connection conn,String sql,Object ...args){
        //考虑事务的操作
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnect();
            ps = conn.prepareStatement(sql);
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //修改其为自动提交
                conn.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JDBCUtils.closeResource(null,ps);
        }
        return 0;
    }
    }

1.4.事务的ACID(acid)属性

• 原子性（Atomicity）
  1. 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）
  1. 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
• 隔离性（Isolation）
  1. 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability）
  1. 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

1.5.数据库事务标志

• 以第一个DML 语句的执行作为开始。
• 以下面的其中之一作为结束:
  1. COMMIT 或ROLLBACK 语句。
  2. DDL 或DCL 语句（自动提交）。
  3. 用户会话正常结束。
  4. 系统异常终了。
• DDL:Data Definition Language 数据定义语言(用来定义数据库结构): create table; alter table; drop table; create index; drop index。
• Data Control Language 数据控制语言(用来控制数据库的访问)：grant; revoke; commit; rollback; lock;
• Data Manipulation Language 数据操纵语言(用来查询与更新记录): insert; update; delete。

1.6.COMMIT和ROLLBACK语句的优点

• 使用COMMIT 和ROLLBACK语句,我们可以:
  1. 确保数据完整性。
  2. 数据改变被提交之前预览。
  3. 将逻辑上相关的操作分组。

• 数据完整性：存储在数据库中的所有数据值均处于正确的状态。如果数据库中存储有不正确的数据值，则该数据库称为已丧失数据完整性。数据库采用多种方法来保证数据完整性，包括外键、束约、规则和触发器。

1.7.提交或回滚前的数据状态

• 改变前的数据状态是可以恢复的。
• 执行DML 操作的用户可以通过SELECT 语句查询提交或回滚之前的修正。
• 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
• DML语句所涉及到的行被锁定，其他用户不能操作。

1.8.提交后的数据状态

• 数据的改变已经被保存到数据库中。
• 改变前的数据已经丢失。
• 所有用户可以看到结果。
• 锁被释放，其他用户可以操作涉及到的数据。

1.9.数据回滚后的状态

• 使用ROLLBACK 语句可使数据变化失效:
  1. 数据改变被取消。
  2. 修改前的数据状态可以被恢复。

1.10.数据库的隔离级别

• 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
  1. 脏读: 对于两个事务T1, T2, T1 读取了已经被T2 更新但还没有被提交的字段。之后, 若T2 回滚, T1读取的内容就是临时且无效的。
  2. 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
  3. 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后T2 在该表中插入了一些新的行。之后, 如果T1 再次读取同一个表, 就会多出几行。
• 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
• 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
• 数据库提供的4 种事务隔离级别：

隔离级别	描述
读未提交数据 Read uncommitted	这是最低的隔离等级，允许其他事务看到没有提交的数据。这种等级会导致脏读（Dirty Read）。
读已提交数据Read committed	读取的数据可以被其他事务修改。这样就可能导致不可重复读。也就是说，事务的读取数据的时候获取读锁，但是读完之后立即释放（不需要等到事务结束），而写锁则是事务提交之后才释放。释放读锁之后，就可能被其他事物修改数据。该等级也是SQL Server默认的隔离等级。
可重复读Repeatable read	可重复读（REPEATABLE READ）：所有被Select获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，因为前一个事务没有范围锁。
串行化Serializable	所有事务都一个接一个地串行执行，这样可以避免幻读（phantom reads）。对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询（如选取年龄在10到30之间的用户）的时候，需要获取范围锁（range lock）。如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需要滚回该事务。

• Oracle 支持的2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为: READ COMMITED。
• Mysql支持4 种事务隔离级别. Mysql默认的事务隔离级别为: REPEATABLE READ

二、数据库连接池

2.1.DBC数据库连接池的必要性

• 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：
  1. 在主程序（如servlet、beans）中建立数据库连接。
  2. 进行sql操作。
  3. 断开数据库连接。

• 这种模式开发，存在的问题：
  1. 普通的JDBC数据库连接使用DriverManager来获取，每次向数据库建立连接的时候都要将Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用.若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
  2. 对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
  3. 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

2.1.数据库连接池（connection pool）

• 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
• 数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
• 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
• 

2.3.数据库连接池的工作原理

2.4.数据库连接池技术的优点

• 资源重用
  1. 由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
• 更快的系统反应速度。
  1. 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间。
• 新的资源分配手段
  1. 对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源。
• 统一的连接管理，避免数据库连接泄露
  1. 在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

2.5.两种开源的数据库连接池

• JDBC 的数据库连接池使用javax.sql.DataSource来表示，DataSource只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  1. DBCP 数据库连接池
  2. C3P0 数据库连接池
• DataSource通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把DataSource称为连接池。
• DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。

2.6.C3P0 数据源

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="helloc3p0">
        <!--提供获取连接的四个基本信息-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!--进行数据库连接池管理的基本信息-->
        <!--当数据库连接池中的连接数不够时，c3p0一次性向数据库服务器申请的连接数-->
        <property name="acquireIncrement">50</property>
        <!--c3p0数据库连接池中初始化的连接数-->
        <property name="initialPoolSize">100</property>
        <!--c3p0数据库连接池当中维护的最多的连接数-->
        <property name="minPoolSize">50</property>
        <!--c3p0数据库连接池当中维护的最多的连接数-->
        <property name="maxPoolSize">1000</property>
        <!--c3p0数据库连接池最多维护的Statement的个数-->
        <property name="maxStatements">0</property>
        <!--c3p0数据库连接池每一个连接可以最多使用的Statement的个数-->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

package com.tedu.java.connect8;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author： zyy
 * @date： 2021/6/2 16:55
 * @description： TODO
 * @version: 1.0
 * @描述：
 *
 **/
public class C3P0Test {

    @Test
    public void testGetConnection() throws PropertyVetoException, SQLException {
        //获取C3P0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true" );
        cpds.setUser("root");
        cpds.setPassword("root");
        //通过设置相关的参数，对数据库连接池进行管理
        //设置初始时数据库连接池中的连接数
        cpds.setInitialPoolSize(20);
        Connection conn = cpds.getConnection();
        System.out.println(conn);
        //销毁c3p0数据库的连接池
        /*DataSources.destroy(cpds);*/
    }


    //使用配置文件
    @Test
    public void testGetConnection2() throws SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);

    }
}

2.7.DBCP 数据源

• DBCP 是Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool. 如需使用该连接池实现，应在系统中增加如下两个jar 文件：
  1. Commons-dbcp.jar：连接池的实现。
  2. Commons-pool.jar：连接池实现的依赖库。
• Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

2.7.1.DBCP 数据源使用范例

• 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
• 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。
  添加lib的jar包
  
  配置文件
  

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=root

initialSize=10

代码获取连接：

package com.tedu.java.connect8;


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

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

/**
 * @author： zyy
 * @date： 2021/6/4 14:54
 * @description： TODO
 * @version: 1.0
 * @描述：
 * 测试DBCP的数据库连接池技术
 **/
public class DBCPTest {

    @Test
    public void testGetConnection() {
        //创建了DBCP的数据库连接池
        BasicDataSource source = new BasicDataSource();

        //设置基本信息
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql:///test");
        source.setUsername("root");
        source.setPassword("root");

        //还可以设置其他涉及数据库连接池管理的相关属性：
        source.setInitialSize(10);
        source.setMaxActive(10);
        Connection conn = null;
        try {
            conn = source.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println(conn);
    }


        @Test
    public void testConnection2(){
            try {
                Properties pros = new Properties();
                InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
                pros.load(is);
                //创建一个DBCP连接池
                DataSource source = BasicDataSourceFactory.createDataSource(pros);
                Connection conn = source.getConnection();
                System.out.println(conn);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
            }
        }
}

工具类：

package com.tedu.java.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author： zyy
 * @date： 2021/6/4 9:53
 * @description： TODO
 * @version: 1.0
 * @描述：
 **/
public class JDBCUtils2 {
    /**
     * 使用吃p0的数据库的连接池
     * @return
     * 数据库连接池只需要创建一个
     */
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
    public static Connection getConnection1() throws SQLException {
        Connection conn = cpds.getConnection();
        return conn;
    }

    public static Connection getConnection() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        // 2.加载驱动
        Class.forName(driverClass);

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    public static void closeResource(Connection conn, Statement ps){
        try {
            if(ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        try {
            if(ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.8.druid数据源

代码获取连接：

package com.tedu.java.connect8;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

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

public class DruidTest {

    @Test
    public void getConnection() throws Exception{
        Properties pros = new Properties();

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

        pros.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        System.out.println(conn);

    }
}