Following is the method I generally prefer to create the connection pool for any database. I use the BasicDataSource class developed by “Apache Software Foundataion” and available for free. For working with this, you may need to add these jar files to your classpath:
1. commons-dbcp.jar
2. commons-pooljar
3. commons-collections.jar
4. mysql-connector-java-5.1.5-bin.jar (for MySQL database server. Replace this with classes12.jar if you are using Oracle)
To create a DataSource object, just instanciate the BasicDataSource class and set the properties related to the database.
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUsername("root");
ds.setPassword("secret");
ds.setUrl("jdbc:mysql://localhost/vinod");
Replace the word “localhost” with the IP address of the remote server, if you are using one.
You can now set the connection-pool properties also.
ds.setMaxActive(20);
ds.setMaxIdle(2);
That’s it. You have just created 20 ready to use connection objects in a pool.
Typically you wrap all these statements in a method as below:
DataSource getDataSource(){
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUsername("root");
ds.setPassword("secret");
ds.setUrl("jdbc:mysql://localhost/vinod");
ds.setMaxActive(20);
ds.setMaxIdle(2);
return ds;
}
All you have to do to retrieve this is to call the getConnection() method of javax.sql.DataSource.
DataSource ds=getDataSource();
Connection conn=ds.getConnection();
Don’t forget to close the connection, as this would ensure your connection is returned to the pool.
Download sample program from [http://kvinod.com/standalone-dbcp-in-java/StandaloneDBCP.java]
Once you have the connection, you can use the same to create Statement, PreparedStatement or use it for transaction management etc.
