69期-Java SE-039_JDBC-3

 

### JDBC 操作数据库事务

- 关闭 Connection 的自动提交。
- 捕获异常，在异常处理中让数据进行回滚 rollback。
- 如果没有异常，SQL 执行完毕，手动提交事务 commit。

```java
package com.southwind.test;

import com.southwind.utils.JDBCTools;

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

public class Test {
    public static void main(String[] args) {
        //张三借给李四1000
        String sql1 = "update people set money = 1000 where id = 1";
        String sql2 = "update people set money = 3000 where id = 2";
        Connection connection = JDBCTools.getConn();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;
        try {
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement1.executeUpdate();
            int num = 10/10;
            preparedStatement2.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            JDBCTools.release(connection,preparedStatement1,null);
            JDBCTools.release(null,preparedStatement2,null);
        }
    }
}
```



### 数据库连接池

传统的 JDBC 开发方式

- 建立数据库连接。
- 执行 SQL 语句。
- 断开数据库连接。

数据库连接池的基本思想就是为数据库建立一个缓冲池，预先放入一定数量的连接对象，当需要获取数据库连接的时候，直接从缓冲池取出一个连接对象，用完之后再返回到缓冲池中，供下一个请求使用，做到资源的重复利用。

允许应用程序中不同的请求重复使用同一个现有的数据库连接对象，而不需要重新创建。

数据库连接池再初始化的时候会创建一定数量的连接对象放入到缓冲池只中，当数据库连接池中没有空闲的连接时，请求会进入等待队列，等待其他线程释放连接对象。

JDBC 的数据库连接池使用 javax.sql.DataSource 接口来完成，Data 是 Java 官方提供的一个接口，但是没有实现，实际开发中我们需要使用第三方的实现，C3P0 就是一个常用的第三方实现。

使用步骤：

1、导入 jar 包。

2、创建 C3P0。

```java
package com.southwind.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.southwind.utils.JDBCTools;

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

public class C3P0Test {
    public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
            dataSource.setUser("root");
            dataSource.setPassword("root");
            dataSource.setInitialPoolSize(20);
            dataSource.setMaxPoolSize(40);
            dataSource.setAcquireIncrement(5);
            dataSource.setMinPoolSize(2);
            System.out.println("C3P0的连接对象："+dataSource.getConnection());
            System.out.println("传统的连接对象："+ JDBCTools.getConn());
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}
```

c3p0-config.xml

<c3p0-config>
    <named-config name="testc3p0">
        <!-- 数据源信息 -->
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8</property>
    </named-config>
</c3p0-config>

C3P0Test.java

package com.southwind.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.southwind.utils.JDBCTools;

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

public class C3P0Test {
    public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
            dataSource.setUser("root");
            dataSource.setPassword("root");
            dataSource.setInitialPoolSize(20);
            dataSource.setMaxPoolSize(40);
            dataSource.setAcquireIncrement(5);
            dataSource.setMinPoolSize(2);
            System.out.println("C3P0的连接对象："+dataSource.getConnection());
            System.out.println("传统的连接对象："+ JDBCTools.getConn());
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

 

Test.java

package com.southwind.test;

import com.southwind.utils.JDBCTools;

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

public class Test {
    public static void main(String[] args) {
        //张三借给李四1000
        String sql1 = "update people set money = 1000 where id = 1";
        String sql2 = "update people set money = 3000 where id = 2";
        Connection connection = JDBCTools.getConn();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;
        try {
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement1.executeUpdate();
            int num = 10/10;
            preparedStatement2.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            JDBCTools.release(connection,preparedStatement1,null);
            JDBCTools.release(null,preparedStatement2,null);
        }
    }
}

 

Test2.java

package com.southwind.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.SQLException;

public class Test2 {
    public static void main(String[] args) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");
        try {
            System.out.println(dataSource.getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBCTools.java

package com.southwind.utils;

import java.sql.*;

public class JDBCTools {
    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
    private static String user = "root";
    private static String password = "root";

    static{
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if(connection!=null){
                connection.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(resultSet!=null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}