DBCP Connection Pooling Example
DBCP Connection Pooling Example exaplains about how to create and configure a Connection pool using DBCP Datasource
Many Apache projects support interaction with a relational database, DBCP one among them.
Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. Accordingly, developers often wish to share a "pool" of open connections between all of the application's current users.
The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required. The application itself logs into the DBMS, and handles any user account issues internally
Reference -> http://commons.apache.org/proper/commons-dbcp/
Required Libraries
You need to download
Following jar must be in classpath
- commons-dbcp.jar
- commons-pool.jar
- mysql-connector-java-5.1.28-bin.jar
Create Table Structure
CREATE TABLE `employee` ( `EMPLOYEEID` bigint(20) NOT NULL AUTO_INCREMENT, `EMPLOYEENAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`EMPLOYEEID`) );
Project Structure
DataSource.java
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
public class DataSource {
private static DataSource datasource;
private BasicDataSource ds;
private DataSource() throws IOException, SQLException, PropertyVetoException {
ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUsername("root");
ds.setPassword("root");
ds.setUrl("jdbc:mysql://localhost/test");
// the settings below are optional -- dbcp can work with defaults
ds.setMinIdle(5);
ds.setMaxIdle(20);
ds.setMaxOpenPreparedStatements(180);
}
public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
if (datasource == null) {
datasource = new DataSource();
return datasource;
} else {
return datasource;
}
}
public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}
}
DBCPDataSourceExample.java
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBCPDataSourceExample {
public static void main(String[] args) throws PropertyVetoException, SQLException, IOException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DataSource.getInstance().getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from employee");
while (resultSet.next()) {
System.out.println("employeeid: " + resultSet.getString("employeeid"));
System.out.println("employeename: " + resultSet.getString("employeename"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException e) {e.printStackTrace();}
if (statement != null) try { statement.close(); } catch (SQLException e) {e.printStackTrace();}
if (connection != null) try { connection.close(); } catch (SQLException e) {e.printStackTrace();}
}
}
}
Output
employeeId: 1 employeename: Rockey employeeId: 2 employeename: Jose