C3P0 connection pool, DBCP connection pool,

C3P0 connection pool, DBCP connection pool,

C3P0 connection pool:

Profile: c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///mybase</property><property name="user">root</property><property name="password">xuyiqing</property><property name="initialPoolSize">5</property><property name="maxPoolSize">20</property></default-config><named-config name="yiqing"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///mybase</property><property name="user">root</property><property name="password">xuyiqing</property></named-config></c3p0-config>

Test:

Data preparation:

CREATE DATABASE mybase;USE mybase;CREATE TABLE users(uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(64),upassword VARCHAR(64));INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");SELECT * FROM users;

View Code

Custom JDBC tool class:

package demo;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.Properties;public class JDBCUtils3 {public static String driver;public static String url;public static String username;public static String password;static {try {ClassLoader classLoader = JDBCUtils3.class.getClassLoader();InputStream is = classLoader.getResourceAsStream("db.properties");Properties props = new Properties();props.load(is);driver = props.getProperty("driver");url = props.getProperty("url");username = props.getProperty("username");password = props.getProperty("password");} catch (Exception ex) {ex.printStackTrace();}}public static Connection getConnection() {Connection conn = null;try {Class.forName(driver);conn = DriverManager.getConnection(url, username, password);} catch (Exception ex) {ex.printStackTrace();}return conn;}public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {if (rs != null) {try {rs.close();} catch (Exception e) {e.printStackTrace();}}if (pstmt != null) {try {pstmt.close();} catch (Exception e) {e.printStackTrace();}}if (conn != null) {try {conn.close();} catch (Exception e) {e.printStackTrace();}}}}

Configuration file:

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mybaseusername=rootpassword=xuyiqing

Test class:

Package demo01; import java. SQL. connection; import java. SQL. preparedStatement; import org. junit. test; import com. mchange. v2.c3p0. comboPooledDataSource; import demo. JDBCUtils3; public class TestC3P0 {@ Testpublic void testAddUser1 () {Connection conn = null; PreparedStatement pstmt = null; ComboPooledDataSource dataSource = new ComboPooledDataSource (); // load data by default // ComboPooledDataSource = new ComboPo OledDataSource ("yiqing"); try {conn = dataSource. getConnection (); String SQL = "insert into users values (null ,?,?) "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "Zhang San"); pstmt. setString (2, "123"); int rows = pstmt.exe cuteUpdate (); if (rows> 0) {System. out. println ("added successfully! ");} Else {System. out. println (" failed to add! ") ;}} Catch (Exception e) {throw new RuntimeException (e) ;}finally {JDBCUtils3.release (conn, pstmt, null );}}}

Successful!

You can extract the C3P0 connection pool tool class:

package demo01;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Utils {    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();    public static DataSource getDataSource() {        return dataSource;    }    public static Connection getConnection() {        try {            return dataSource.getConnection();        } catch (SQLException e) {            throw new RuntimeException(e);        }    }}

View Code

Test:

Package demo01; import java. SQL. connection; import java. SQL. preparedStatement; import org. junit. test; import demo. JDBCUtils3; public class TestC3P0 {@ Test public void testAddUser1 () {Connection conn = null; PreparedStatement pstmt = null; try {conn = C3P0Utils. getConnection (); String SQL = "insert into users values (null ,?,?) "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "Li Si"); pstmt. setString (2, "123123"); int rows = pstmt.exe cuteUpdate (); if (rows> 0) {System. out. println ("added successfully! ");} Else {System. out. println (" failed to add! ") ;}} Catch (Exception e) {throw new RuntimeException (e) ;}finally {JDBCUtils3.release (conn, pstmt, null );}}}

View Code

Successful!

 

 

DBCP connection pool:

Custom DBCP tool class:

Package DBCP; import java. io. inputStream; import java. SQL. connection; import java. SQL. SQLException; import java. util. properties; import javax. SQL. dataSource; import org. apache. commons. dbcp. basicDataSourceFactory; public class DBCPUtils {private static DataSource dataSource; static {try {// 1. loading the input stream of the properties file, InputStream is = DBCPUtils. class. getClassLoader (). getResourceAsStream ("db. properties "); // 2. load the input stream Properties props = new Properties (); props. load (is); // 3. create the data source dataSource = BasicDataSourceFactory. createDataSource (props);} catch (Exception e) {throw new RuntimeException (e);} public static DataSource getDataSource () {return dataSource;} public static Connection getConnection () {try {return dataSource. getConnection () ;}catch (SQLException e) {throw new RuntimeException (e );}}}

View Code

Test:

Package DBCP; import java. SQL. connection; import java. SQL. preparedStatement; import org. junit. test; public class TestDBCP {@ Test public void testUpdateUserById () {Connection conn = null; PreparedStatement pstmt = null; try {conn = DBCPUtils. getConnection (); String SQL = "update users set upassword =? Where uid =? "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "456789"); pstmt. setInt (2, 1); int rows = pstmt.exe cuteUpdate (); if (rows> 0) {System. out. println ("Update successful! ");} Else {System. out. println (" update failed! ") ;}} Catch (Exception e) {throw new RuntimeException (e );}}}

View Code