Introduction to database connection pool c3p0

Introduction to database connection pool c3p0

C3P0 is an open-source JDBC connection pool that implements data source and JNDI binding and supports JDBC3 specifications and standard extensions of JDBC2. Currently, open-source projects using it include Hibernate and Spring.

If you do not know what a connection pool is, you can go to Baidu first. What I understand is that there is a large pool of fish. Each fish corresponds to a database connection. If you get a fish, you will get a connection. Of course, this fish cannot be eaten, you need to put it back after you touch it. This example is a little abnormal. You can understand it.

Good! Now we start.

1. First, we start from the most basic point, that is, how to use java code to connect to the database with c3p0 and perform query operations.

First, create a project: it_xiaorenwu.

To create c3p0, we need to first import the c3p0 package:

1. c3p0-0.9.2.1.jar

2. mchange-commons-java-0.2.3.4.jar

3. mysql-connector-java-5.1.7-bin.jar

4. commons-io-2.0.1.jar

5. junit-4.5.jar

2. Prepare the database and data first.

 
 

  
  
/* 
  
  
SQLyog Ultimate v11.24 (32 bit) 
  
  
MySQL - 5.5.24 : Database - test 
  
  
********************************************************************* 
  
  
*/ 
  
  
  
  
  
  
  
  
/*!40101 SET NAMES utf8 */; 
  
  
  
  
  
/*!40101 SET SQL_MODE=''*/; 
  
  
  
  
  
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 
  
  
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 
  
  
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 
  
  
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 
  
  
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET latin1 */; 
  
  
  
  
  
USE `test`; 
  
  
  
  
  
/*Table structure for table `user` */ 
  
  
  
  
  
DROP TABLE IF EXISTS `user`; 
  
  
  
  
  
CREATE TABLE `user` ( 
  
  
  `id` int(10) NOT NULL AUTO_INCREMENT, 
  
  
  `user_name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, 
  
  
  `password` varchar(100) CHARACTER SET latin1 DEFAULT NULL, 
  
  
  `address` varchar(100) CHARACTER SET latin1 DEFAULT NULL, 
  
  
  `phone_number` varchar(20) CHARACTER SET latin1 DEFAULT NULL, 
  
  
  `create_time` datetime DEFAULT NULL, 
  
  
  `update_time` datetime DEFAULT NULL, 
  
  
  PRIMARY KEY (`id`) 
  
  
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 
  
  
  
  
  
/*Data for the table `user` */ 
  
  
  
  
  
insert  into `user`(`id`,`user_name`,`password`,`address`,`phone_number`,`create_time`,`update_time`) values (1,'test','CY9rzUYh03PK3k6DJie09g==','test','test','2014-03-29 00:48:14','2014-03-29 00:48:17'),(2,'te2','CY9rzUYh03PK3k6DJie09g==','asdfk','4156434885','2015-08-05 11:09:29','2015-08-20 11:09:33'); 
  
  
  
  
  
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 
  
  
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 
  
  
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 
  
  
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 
 
 

Let's start with the first simple java example: Read the properties configuration file. Then obtain the connection of c3p0.

First, we prepare a db. properties and modify the user name and password as needed.

 
 

  
  
jdbcdriver=com.mysql.jdbc.Driver 
  
  
url=jdbc:mysql://localhost:3306/test 
  
  
username=root 
  
  
password=root 
 
 

Create a class: C3p0Demo1

 
 

  
  
package day12; 
  
  
  
  
  
import java.beans.PropertyVetoException; 
  
  
import java.io.IOException; 
  
  
import java.io.InputStream; 
  
  
import java.sql.Connection; 
  
  
import java.sql.PreparedStatement; 
  
  
import java.sql.ResultSet; 
  
  
import java.sql.SQLException; 
  
  
import java.util.Properties; 
  
  
  
  
  
import javax.sql.DataSource; 
  
  
  
  
  
import org.junit.Test; 
  
  
  
  
  
import com.mchange.v2.c3p0.ComboPooledDataSource; 
  
  
  
  
  
public class C3p0Demo1 { 
  
  
    @Test 
  
  
    public void getConnection() throws IOException, PropertyVetoException, SQLException 
  
  
    { 
 
 

 
 

  
  
<Span style = "white-space: pre"> </span> // read the configuration file information for c3p0 configuration.
  
  
Properties prop = new Properties ();
  
  
InputStream in = C3p0Demo1. class. getClassLoader (). getResourceAsStream ("db. properties ");
  
  
Prop. load (in); // load information
  
  

  
  
ComboPooledDataSource comb = new ComboPooledDataSource (); // The configuration is started below
  
  
Comb. setDriverClass (prop. getProperty ("jdbcdriver "));
  
  
Comb. setJdbcUrl (prop. getProperty ("url "));
  
  
Comb. setUser (prop. getProperty ("jdbc: mysql: /// localhost: 3306/test "));
  
  
Comb. setPassword (prop. getProperty ("root "));
  
  

  
  
Connection con = comb. getConnection (); // get a fish from c3p0, ah! What fish? connect to a database
  
  
// Execute the query statement
  
  
String SQL = "select * from user ";
  
  
PreparedStatement ps = con. prepareStatement (SQL );
  
  
ResultSet rs = ps.exe cuteQuery ();
  
  
// Print the database information
  
  
While (rs. next ())
  
  
{
  
  
System. out. println (rs. getString (1 ));
  
  
System. out. println (rs. getString (2 ));
  
  
System. out. println (rs. getString (3 ));
  
  
System. out. println (rs. getString (5 ));
  
  
System. out. println (rs. getString (6 ));
  
  
System. out. println (rs. getString (7 ));
  
  
}
  
  

  
  
// Close the connection
 
 

Con. close (); // What is this, this is to return the connection to the connection pool, rather than closing the connection in. close (); comb. close (); rs. close (); ps. close ();}}

Method 2: (similar, but more concise)

We need to create a new configuration file: c3p0. properties (Note: The name is fixed. C3p0 only recognizes this name by default)

 
 

  
  
c3p0.driverClass=com.mysql.jdbc.Driver 
  
  
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/test 
  
  
c3p0.user=root 
  
  
c3p0.password=root 
 
 

OK! Create a new class: C3p0Demo2

 
 

  
  
Package day12;
  
  

  
  
Import java. beans. PropertyVetoException;
  
  
Import java. io. IOException;
  
  
Import java. io. InputStream;
  
  
Import java. SQL. Connection;
  
  
Import java. SQL. PreparedStatement;
  
  
Import java. SQL. ResultSet;
  
  
Import java. SQL. SQLException;
  
  
Import java. util. Properties;
  
  

  
  
Import javax. SQL. DataSource;
  
  

  
  
Import org. junit. Test;
  
  

  
  
Import com. mchange. v2.c3p0. ComboPooledDataSource;
  
  

  
  
Public class C3p0Demo2 {
  
  

  
  
@ Test
  
  
Public void getConByC3P0 () throws SQLException
  
  
{
  
  
DataSource ds = new ComboPooledDataSource (); // c3p0 reads the configuration file by ourselves, so we can do nothing.
  
  

  
  
Connection con = ds. getConnection (); // we can take a fish directly.
  
  
System. out. println ("con:" + con );
  
  

  
  
String SQL = "select * from user ";
  
  
PreparedStatement ps = con. prepareStatement (SQL );
  
  
ResultSet rs = ps.exe cuteQuery ();
  
  

  
  
While (rs. next ())
  
  
{
  
  
System. out. println (rs. getString (1 ));
  
  
System. out. println (rs. getString (2 ));
  
  
System. out. println (rs. getString (3 ));
  
  
System. out. println (rs. getString (5 ));
  
  
System. out. println (rs. getString (6 ));
  
  
System. out. println (rs. getString (7 ));
  
  
}
  
  

  
  
// Close the connection
  
  

  
  
Con. close (); // What is this? This is to return the connection to the connection pool, not to close the connection.
  
  
Rs. close ();
  
  
Ps. close ();
  
  
}
  
  
}