C3P0 connection pool, DBCP connection pool,

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.