Use of open source database connection pooling

The previous article briefly introduced the basic principle of database connection pool implementation, Link: Database connection pool Simple implementation
Here we describe the use of two common database connection pools:

1.DBCP Database Connection pool:

Package Com.itheima.jdbc;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.sqlexception;import Org.apache.commons.dbcp.basicdatasource;public class Dbcpdemo {public static void Main (string[] args) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {Basicdatasource Source = new Basicdatasource (); Source.setdriverclassname (" Com.mysql.jdbc.Driver "); Source.seturl (" Jdbc:mysql://localhost:3306/day11 "); Source.setusername (" root "); Source.setpassword ("root"); conn = Source.getconnection ();p s = conn.preparestatement ("SELECT * from Account"); rs = Ps.executequery (); while (Rs.next ()) {String name = rs.getstring (2); String salary = rs.getstring (3); SYSTEM.OUT.PRINTLN (name + ":" + Salary);}} catch (Exception e) {e.printstacktrace (); throw new RuntimeException (e);} finally {if (rs! = null) {try {rs.close ();} catch (SQLException e) {rs = null;}} if (PS! = null) {try {ps.close ();} catch (SQLException e) {PS = null;}} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {conn = null;}}}} 

I need to import the corresponding DBCP jar package here, I will not provide it here.

The above code sets the class load path, URL, database user name, and password through the Set method, and we can also configure it through the properties file.

The property name in the properties file corresponds to the Setxxx method, except the first letter lowercase.

The contents of the Dbcp-config.properties file are as follows:

#类加载路径driverClassName =com.mysql.jdbc.driver#url Database access path url=jdbc:mysql://localhost:3306/day11# user name username=root# Password Password=root

Package Com.itheima.jdbc;import Java.io.filenotfoundexception;import Java.io.filereader;import java.io.IOException ; 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.apache.commons.dbcp.basicdatasourcefactory;public class Dbcpdemo {private static properties Properties = New Properties (), static {try {properties.load (new FileReader ("Dbcp-config.properties")),} catch (FileNotFoundException E {e.printstacktrace (); throw new RuntimeException (e);} catch (IOException e) {e.printstacktrace (); throw new RuntimeException (e);}} public static void Main (string[] args) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {Basicdatasourcefactory factory = new Basicdatasourcefactory ();D atasource Source = factory.create DataSource (properties); conn = Source.getconnection ();p s = conn.preparestatement ("SELECT * from Account"); rs = Ps.executequery (); while (RS.next ()) {String name = rs.getstring (2); String salary = rs.getstring (3); SYSTEM.OUT.PRINTLN (name + ":" + Salary);}} catch (Exception e) {e.printstacktrace (); throw new RuntimeException (e);} finally {if (rs! = null) {try {rs.close ();} catch (SQLException e) {rs = null;}} if (PS! = null) {try {ps.close ();} catch (SQLException e) {PS = null;}} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {conn = null;}}}}

You can also configure the properties in the Dbcp-config.properties file.

#数据库连接池初始化连接数initialSize =10# database Connection pool Maximum number of connections maxactive=50# database connection pool minimum number of idle connections minidle=5# database connection pool maximum idle connections maxidle=20# wait timeout, When a link is idle for longer than that time, the link is defined as an idle connection maxwait=60000

2.C3P0 Database Connection Pool

Package Com.itheima.jdbc;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.sqlexception;import Com.mchange.v2.c3p0.combopooleddatasource;public class C3P0Demo {public static void Main (string[] args) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {Combopooleddatasource Source = new Combopooleddatasource (); Source.setdriverclass (" Com.mysql.jdbc.Driver "); Source.setjdbcurl (" Jdbc:mysql://localhost:3306/day11 "); Source.setuser (" root "); Source.setpassword ("root"); conn = Source.getconnection ();p s = conn.preparestatement ("SELECT * from Account"); rs = Ps.executequery (); while (Rs.next ()) {String name = rs.getstring (2); String salary = rs.getstring (3); SYSTEM.OUT.PRINTLN (name + ":" + Salary);}} catch (Exception e) {e.printstacktrace (); throw new RuntimeException (e);} finally {if (rs! = null) {try {rs.close ();} catch (SQLException e) {rs = null;}} if (PS! = null) {try {ps.close ();} catch (SQLException e) {PS = null;}} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {conn = null;}}}} 

The corresponding can be configured by C3p0-config.xml file

The following is a description of the official documentation:

By default, C3P0 would look for a XML configuration file in its ClassLoader ' s resource path under the name "/c3p0-config.x ML ". That means the XML file should is placed in a directly or jar file directly named in your applications CLASSPATH, in Web-i Nf/classes, or some similar location.

(that is, the name must be c3p0-config.xml, file location: Under the class load path, i.e. the SRC directory of Eclipse)

Package Com.itheima.jdbc;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.sqlexception;import Com.mchange.v2.c3p0.combopooleddatasource;public class C3P0Demo {public static void Main (string[] args) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {//If the property is not set through the Setxxx method, it is automatically found under the class loading directory C3p0-config.xml file//Find after default use <default-config> The configuration information under the tag, or the name of the incoming//config when constructing the source object, i.e. combopooleddatasource Source = new Combopooleddatasource ("Myconfig");// Of course, the C3p0-config.xml file is configured with <default-config> and <named-config> In general, use one of them. Combopooleddatasource Source = new Combopooleddatasource (), conn = Source.getconnection ();p s = conn.preparestatement (" SELECT * from Account "), rs = Ps.executequery (); while (Rs.next ()) {String name = rs.getstring (2); String salary = rs.getstring (3); SYSTEM.OUT.PRINTLN (name + ":" + Salary);}} catch (Exception e) {e.printstacktrace (); throw new RuntimeException (e);} finally {if (rs! = null) {try {rs.close ();} catch (SQLException e) {rs = null;}} if (PS! = null) {try {ps.close ();} catch (SQLException e) {PS = null;}} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {conn = null;}}}}

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:// localhost:3306/day11</property>    <property name= "user" >root</property>    <property Name= "Password" >root</property>  </default-config>  <named-config name= "Myconfig" >    <property name= "Driverclass" >com.mysql.jdbc.Driver</property>    <property name= "Jdbcurl" >jdbc:mysql://localhost:3306/day11</property>    <property name= "user" >root</property>    <property name= "password" >root</property>  </named-config></c3p0-config>

<default-config> and <named-config> can only be configured with one.

The XML file can also be configured with additional parameters, which are not mentioned here, and the official documentation is clearly written.

Use of open source database connection pooling