Practical for beginners: getting started with c3p0 for database connection pools

Practical for beginners: getting started with c3p0 for database connection pools

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, its open-source projects include:Hibernate, 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.Driverurl=jdbc:mysql://localhost:3306/testusername=rootpassword=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 {@Testpublic void getConnection() throws IOException, PropertyVetoException, SQLException{

// First read the configuration file information for Properties prop = new Properties () used to configure c3p0; InputStream in = C3p0Demo1. class. getClassLoader (). getResourceAsStream ("db. properties "); prop. load (in); // load information ComboPooledDataSource comb = new ComboPooledDataSource (); // configure comb as follows. setDriverClass (prop. getProperty ("jdbcdriver"); comb. setJdbcUrl (prop. getProperty ("url"); comb. setUser (prop. getProperty ("jdbc: mysql: // localhost: 3306/test"); comb. setP Assword (prop. getProperty ("root"); Connection con = comb. getConnection (); // get a fish from c3p0, ah! What's the fish? Use a database connection // 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.Driverc3p0.jdbcUrl=jdbc:mysql://localhost:3306/testc3p0.user=rootc3p0.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 {@ Testpublic void getConByC3P0 () throws SQLException {DataSource ds = new ComboPooledDataSource (); // c3p0 reads the configuration file, we do not do anything about Connection con = ds. getConnection (); // we get a fish System directly. 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, rather than closing the connection rs. close (); ps. close ();}}