JDBC知识概括（2）

数据库事务

• 在数据库中,所谓事务是指一组逻辑操作单元,使数据从一种状态变换到另一种状态。
• 为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。
• 事务的操作:先定义开始一个事务,然后对数据作修改操作,这时如果提交(COMMIT),这些修改就永久地保存下来,如果回退(ROLLBACK),数据库管理系统将放弃所作的所有修改而回到开始事务时的状态。

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

/**     * Tom 给 Jerry 汇款 500 元.     *      * 关于事务: 1. 如果多个操作, 每个操作使用的是自己的单独的连接, 则无法保证事务. 2. 具体步骤: 1). 事务操作开始前, 开始事务:     * 取消 Connection 的默认提交行为. connection.setAutoCommit(false); 2). 如果事务的操作都成功,     * 则提交事务: connection.commit(); 3). 回滚事务: 若出现异常, 则在 catch 块中回滚事务:     */    @Test    public void testTransaction() {        Connection connection = null;        try {            connection = JDBCTools.getConnection();            System.out.println(connection.getAutoCommit());            // 开始事务: 取消默认提交.            connection.setAutoCommit(false);            String sql = "UPDATE users SET balance = "                    + "balance - 500 WHERE id = 1";            update(connection, sql);            int i = 10 / 0;            System.out.println(i);            sql = "UPDATE users SET balance = " + "balance + 500 WHERE id = 2";            update(connection, sql);            // 提交事务            connection.commit();        } catch (Exception e) {            e.printStackTrace();            // 回滚事务            try {                connection.rollback();            } catch (SQLException e1) {                e1.printStackTrace();            }        } finally {            JDBCTools.releaseDB(null, null, connection);        }public void update(Connection connection, String sql, Object... args) {        PreparedStatement preparedStatement = null;        try {            preparedStatement = connection.prepareStatement(sql);            for (int i = 0; i < args.length; i++) {                preparedStatement.setObject(i + 1, args[i]);            }            preparedStatement.executeUpdate();        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(null, preparedStatement, null);        }    }

数据库的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

• 脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚,T1读取的内容就是临时且无效的.
• 不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段,值就不同了.
• 幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1再次读取同一个表, 就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

数据库的隔离级别：

/**     * 测试事务的隔离级别 在 JDBC 程序中可以通过 Connection 的 setTransactionIsolation 来设置事务的隔离级别.     */    @Test    public void testTransactionIsolationUpdate() {        Connection connection = null;        try {            connection = JDBCTools.getConnection();            connection.setAutoCommit(false);            String sql = "UPDATE users SET balance = "                    + "balance - 500 WHERE id = 1";            update(connection, sql);            connection.commit();        } catch (Exception e) {            e.printStackTrace();        } finally {        }    }    @Test    public void testTransactionIsolationRead() {        String sql = "SELECT balance FROM users WHERE id = 1";        Integer balance = getForValue(sql);        System.out.println(balance);     }    // 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)    public <E> E getForValue(String sql, Object... args) {        // 1. 得到结果集: 该结果集应该只有一行, 且只有一列        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            // 1. 得到结果集            connection = JDBCTools.getConnection();            System.out.println(connection.getTransactionIsolation()); //          connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);            preparedStatement = connection.prepareStatement(sql);            for (int i = 0; i < args.length; i++) {                preparedStatement.setObject(i + 1, args[i]);            }            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                return (E) resultSet.getObject(1);            }        } catch (Exception ex) {            ex.printStackTrace();        } finally {            JDBCTools.releaseDB(resultSet, preparedStatement, connection);        }        // 2. 取得结果        return null;    }

批量处理

当需要成批插入或者更新记录时。可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。JDBC的批量处理语句包括下面两个方法：

• addBatch( )或者addBatch( String)：添加需要批量处理的SQL语句或是参数；
• executeBatch( )；执行批量处理语句；

通常我们会遇到两种批量执行SQL语句的情况：

1. 多条SQL语句的批量处理
2. 一个SQL语句的批量传参

    @Test    public void testBatch(){        Connection connection = null;        PreparedStatement preparedStatement = null;        String sql = null;        try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);            sql = "INSERT INTO customers VALUES(?,?,?)";            preparedStatement = connection.prepareStatement(sql);            Date date = new Date(new java.util.Date().getTime());            long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                preparedStatement.setInt(1, i + 1);                preparedStatement.setString(2, "name_" + i);                preparedStatement.setDate(3, date);                //"积攒" SQL                 preparedStatement.addBatch();                //当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL                if((i + 1) % 300 == 0){                    preparedStatement.executeBatch();                    preparedStatement.clearBatch();                }            }            //若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.             if(100000 % 300 != 0){                preparedStatement.executeBatch();                preparedStatement.clearBatch();            }            long end = System.currentTimeMillis();            System.out.println("Time: " + (end - begin)); //569            JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rollback(connection);        } finally{            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }    @Test    public void testBatchWithPreparedStatement(){        Connection connection = null;        PreparedStatement preparedStatement = null;        String sql = null;        try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);            sql = "INSERT INTO customers VALUES(?,?,?)";            preparedStatement = connection.prepareStatement(sql);            Date date = new Date(new java.util.Date().getTime());            long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                preparedStatement.setInt(1, i + 1);                preparedStatement.setString(2, "name_" + i);                preparedStatement.setDate(3, date);                preparedStatement.executeUpdate();//9819            }            long end = System.currentTimeMillis();            System.out.println("Time: " + (end - begin));             JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rollback(connection);        } finally{            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }    /**     * 向  Oracle 的 customers 数据表中插入 10 万条记录     * 测试如何插入, 用时最短.      * 1. 使用 Statement.     */    @Test    public void testBatchWithStatement(){        Connection connection = null;        Statement statement = null;        String sql = null;        try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);            statement = connection.createStatement();            long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                sql = "INSERT INTO customers VALUES(" + (i + 1)                         + ", 'name_" + i + "', '29-6月 -13')";                //statement.executeUpdate(); 39567                statement.addBatch(sql);            }            long end = System.currentTimeMillis();            System.out.println("Time: " + (end - begin));             JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rollback(connection);        } finally{            JDBCTools.releaseDB(null, statement, connection);        }    }

数据库连接池

在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：　

1. 在主程序（如servlet、beans）中建立数据库连接。
2. 进行sql操作
3. 断开数据库连接。

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

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

数据库连接池技术的优点

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

两种开源的数据库连接池

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
1. DBCP 数据库连接池
2. C3P0 数据库连接池
DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池

DBCP 数据源 ：

/**     * 使用 DBCP 数据库连接池     * 1. 加入 jar 包(2 个jar 包). 依赖于 Commons Pool     * 2. 创建数据库连接池     * 3. 为数据源实例指定必须的属性     * 4. 从数据源中获取数据库连接     * @throws SQLException      */    @Test    public void testDBCP() throws SQLException{        final BasicDataSource dataSource = new BasicDataSource();        //2. 为数据源实例指定必须的属性        dataSource.setUsername("root");        dataSource.setPassword("1230");        dataSource.setUrl("jdbc:mysql:///atguigu");        dataSource.setDriverClassName("com.mysql.jdbc.Driver");        //3. 指定数据源的一些可选的属性.        //1). 指定数据库连接池中初始化连接数的个数        dataSource.setInitialSize(5);        //2). 指定最大的连接数: 同一时刻可以同时向数据库申请的连接数        dataSource.setMaxActive(5);        //3). 指定小连接数: 在数据库连接池中保存的最少的空闲连接的数量         dataSource.setMinIdle(2);        //4).等待数据库连接池分配连接的最长时间. 单位为毫秒. 超出该时间将抛出异常.         dataSource.setMaxWait(1000 * 5);        //4. 从数据源中获取数据库连接        Connection connection = dataSource.getConnection();        System.out.println(connection.getClass());         connection = dataSource.getConnection();        System.out.println(connection.getClass());         connection = dataSource.getConnection();        System.out.println(connection.getClass());         connection = dataSource.getConnection();        System.out.println(connection.getClass());         Connection connection2 = dataSource.getConnection();        System.out.println(">" + connection2.getClass());         new Thread(){            public void run() {                Connection conn;                try {                    conn = dataSource.getConnection();                    System.out.println(conn.getClass());                 } catch (SQLException e) {                    e.printStackTrace();                }            };        }.start();        try {            Thread.sleep(5500);        } catch (InterruptedException e) {            e.printStackTrace();        }        connection2.close();    }/**     * 1. 加载 dbcp 的 properties 配置文件: 配置文件中的键需要来自 BasicDataSource     * 的属性.     * 2. 调用 BasicDataSourceFactory 的 createDataSource 方法创建 DataSource     * 实例     * 3. 从 DataSource 实例中获取数据库连接.      */    @Test    public void testDBCPWithDataSourceFactory() throws Exception{        Properties properties = new Properties();        InputStream inStream = JDBCTest.class.getClassLoader()                .getResourceAsStream("dbcp.properties");        properties.load(inStream);        DataSource dataSource =                 BasicDataSourceFactory.createDataSource(properties);        System.out.println(dataSource.getConnection()); //      BasicDataSource basicDataSource = //              (BasicDataSource) dataSource;//      //      System.out.println(basicDataSource.getMaxWait());     }dbcp.properties:username=rootpassword=1230driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql:///helloworldinitialSize=10maxActive=50minIdle=5maxWait=5000

C3P0数据源：

    @Test    public void testC3P0() throws Exception{        ComboPooledDataSource cpds = new ComboPooledDataSource();        cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver                    cpds.setJdbcUrl( "jdbc:mysql:///atguigu" );        cpds.setUser("root");                                          cpds.setPassword("1230");           System.out.println(cpds.getConnection());     }/**     * 1. 创建 c3p0-config.xml 文件,      * 参考帮助文档中 Appendix B: Configuation Files 的内容     * 2. 创建 ComboPooledDataSource 实例；     * DataSource dataSource =      *          new ComboPooledDataSource("helloc3p0");       * 3. 从 DataSource 实例中获取数据库连接.      */    @Test    public void testC3poWithConfigFile() throws Exception{        DataSource dataSource =                 new ComboPooledDataSource("helloc3p0");          System.out.println(dataSource.getConnection());         ComboPooledDataSource comboPooledDataSource =                 (ComboPooledDataSource) dataSource;        System.out.println(comboPooledDataSource.getMaxStatements());     }

c3p0-config.xml:

<?xml version="1.0" encoding="UTF-8"?><c3p0-config>    <named-config name="helloc3p0">        <!-- 指定连接数据源的基本属性 -->        <property name="user">root</property>        <property name="password">1230</property>        <property name="driverClass">com.mysql.jdbc.Driver</property>        <property name="jdbcUrl">jdbc:mysql:///atguigu</property>        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->        <property name="acquireIncrement">5</property>        <!-- 初始化数据库连接池时连接的数量 -->        <property name="initialPoolSize">5</property>        <!-- 数据库连接池中的最小的数据库连接数 -->        <property name="minPoolSize">5</property>        <!-- 数据库连接池中的最大的数据库连接数 -->        <property name="maxPoolSize">10</property>        <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->        <property name="maxStatements">20</property>        <!-- 每个连接同时可以使用的 Statement 对象的个数 -->        <property name="maxStatementsPerConnection">5</property>    </named-config></c3p0-config>

JDBCTools.java:

/** * JDBC 的工具类 *  * 其中包含: 获取数据库连接, 关闭数据库资源等方法. */public class JDBCTools {    //处理数据库事务的    //提交事务    public static void commit(Connection connection){        if(connection != null){            try {                connection.commit();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    //回滚事务    public static void rollback(Connection connection){        if(connection != null){            try {                connection.rollback();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    //开始事务    public static void beginTx(Connection connection){        if(connection != null){            try {                connection.setAutoCommit(false);            } catch (SQLException e) {                e.printStackTrace();            }        }    }    private static DataSource dataSource = null;    //数据库连接池应只被初始化一次.     static{        dataSource = new ComboPooledDataSource("helloc3p0");    }    public static Connection getConnection() throws Exception {        return dataSource.getConnection();    }    public static void releaseDB(ResultSet resultSet, Statement statement,            Connection connection) {        if (resultSet != null) {            try {                resultSet.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (statement != null) {            try {                statement.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (connection != null) {            try {                //数据库连接池的 Connection 对象进行 close 时                //并不是真的进行关闭, 而是把该数据库连接会归还到数据库连接池中.                 connection.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

调用函数或者存储过程

/**     * 如何使用 JDBC 调用存储在数据库中的函数或存储过程     */    @Test    public void testCallableStatment() {        Connection connection = null;        CallableStatement callableStatement = null;        try {            connection = JDBCTools.getConnection();            // 1. 通过 Connection 对象的 prepareCall()            // 方法创建一个 CallableStatement 对象的实例.            // 在使用 Connection 对象的 preparedCall() 方法时,            // 需要传入一个 String 类型的字符串, 该字符串用于指明如何调用存储过程.            String sql = "{?= call sum_salary(?, ?)}";            callableStatement = connection.prepareCall(sql);            // 2. 通过 CallableStatement 对象的             //reisterOutParameter() 方法注册 OUT 参数.            callableStatement.registerOutParameter(1, Types.NUMERIC);            callableStatement.registerOutParameter(3, Types.NUMERIC);            // 3. 通过 CallableStatement 对象的 setXxx() 方法设定 IN 或 IN OUT 参数. 若想将参数默认值设为            // null, 可以使用 setNull() 方法.            callableStatement.setInt(2, 80);            // 4. 通过 CallableStatement 对象的 execute() 方法执行存储过程            callableStatement.execute();            // 5. 如果所调用的是带返回参数的存储过程,             //还需要通过 CallableStatement 对象的 getXxx() 方法获取其返回值.            double sumSalary = callableStatement.getDouble(1);            long empCount = callableStatement.getLong(3);            System.out.println(sumSalary);            System.out.println(empCount);        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(null, callableStatement, connection);        }    }