java数据源

直接连接数据库，然后再释放，如果在连接数多的情况下，资源可能不好控制。用数据库连接池方便。目前有以下：1，dbcp;2,c3p0;3:druid.

c3p0:

在https://www.cnblogs.com/luoa/articles/9686483.html的这个项目里创建测试类。

1，首先，需要导入相关jar包，之前项目已经加入了。

2，新建c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
c3p0-config.xml必须位于类路径下面
private static ComboPooledDataSource ds;
static{
    try {
        ds = new ComboPooledDataSource("MySQL");
    } catch (Exception e) {
        throw new ExceptionInInitializerError(e);
    }
}
-->

<c3p0-config>
    <!--
    C3P0的缺省(默认)配置，
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource();”这样写就表示使用的是C3P0的缺省(默认)配置信息来创建数据源
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/luo</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    C3P0的命名配置，
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");”这样写就表示使用的是name是MySQL的配置信息来创建数据源
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/luo</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>

</c3p0-config>

3，新建类JdbcUtils_C3P0

package com.aluo.spring;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource ds = null;
    // 在静态代码块中创建数据库连接池
    static {
        try {
            // 通过代码创建C3P0数据库连接池
            /*
             * ds = new ComboPooledDataSource();
             * ds.setDriverClass("com.mysql.jdbc.Driver");
             * ds.setJdbcUrl("jdbc:mysql://localhost:3306/jdbcstudy");
             * ds.setUser("root"); ds.setPassword("XDP");
             * ds.setInitialPoolSize(10); ds.setMinPoolSize(5);
             * ds.setMaxPoolSize(20);
             */

            // 通过读取C3P0的xml配置文件创建数据源，C3P0的xml配置文件c3p0-config.xml必须放在src目录下
             ds = new ComboPooledDataSource();//使用C3P0的默认配置来创建数据源
//            ds = new ComboPooledDataSource("MySQL");// 使用C3P0的命名配置来创建数据源
            System.out.println(ds.getInitialPoolSize());
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * @Method: getConnection
     * @Description: 从数据源中获取数据库连接
     * @return Connection
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        // 从数据源中获取数据库连接
        return ds.getConnection();
    }

    /**
     * @Method: release
     * @Description: 释放资源，
     *               释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
     *
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                // 关闭存储查询结果的ResultSet对象
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
            try {
                // 关闭负责执行SQL命令的Statement对象
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                // 将Connection连接对象还给数据库连接池
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

4，新建类DataSourceTest

package com.aluo.spring;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

public class DataSourceTest {
    @Test
    public void c3p0DataSourceTest() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            // 获取数据库连接
            conn = JdbcUtils_C3P0.getConnection();
            String sql = "insert into user (id,phone,password) values(?,?,?)";
            st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, 4);
            st.setString(2, "4444");
            st.setString(3, "testCp30");
            st.executeUpdate();
//            // 获取数据库自动生成的主键
            rs = st.getGeneratedKeys();
            if (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JdbcUtils_C3P0.release(conn, st, rs);
        }
    }
}

output:

九月 25, 2018 3:35:36 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
九月 25, 2018 3:35:36 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
10
九月 25, 2018 3:35:37 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1b61cw69y4cde191q7uq91|65ae6ba4, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1b61cw69y4cde191q7uq91|65ae6ba4, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/luo, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
4

参考：https://www.cnblogs.com/xdp-gacl/p/4002804.html