JDBC Connection pooling with Tomcat configuration

STEPS for configuring pool....

PREREQUSITE:

Ø  APACHE-COMMONS-DBCP.JAR

Ø  APACHE-COMMONS-CONNECTION-POOL.JAR

STEP 1:  Configure DataSource

We can configure DataSource in Context.xml or Server.xml

Configuration for Context.xml <context> <resource auth="Container" driverclassname="com.mysql.jdbc.Driver" maxactive="20" maxidle="30" maxwait="-1" name="jdbc/mysqldb" password="root" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/users?autoReconnect=true" username="root"> </resource> </context> OR Configuration for Server.xml <host> <context docbase="Struts2SpringDemo" path="/Struts2SpringDemo" reloadable="true" source="org.eclipse.jst.jee.server:Struts2SpringDemo"> <resource auth="Container" driverclassname="com.mysql.jdbc.Driver" maxactive="20" maxidle="30" maxwait="-1" name="jdbc/mysqldb" password="root" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/users?autoReconnect=true" username="root"> </resource> </context> </host> Configuration for Web.xml <resource-ref> <description>MySQL Connection Pool</description> <res-ref-name>jdbc/mysqldb</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>

#####################################################################

STEP 2: GETTING CONNECTION

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;

import javax.naming.NamingException;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * The Class ConnectionManager.
 */
public class ConnectionManager {

/** The LOGGER. */
private static final Logger LOGGER = LoggerFactory.getLogger(ConnectionManager.class);

/** The context, kept for better performance */
private static javax.naming.Context ctx;

/**
* Constructs the singleton, also obtaining the context.
*/
private static void init() throws NamingException {
ctx = new javax.naming.InitialContext();
}

/**
* Gets the connection from pool.
*
* @param dataSourceName the data source name
* @return the connection from pool
* @throws NamingException the naming exception
* @throws SQLException the SQL exception
*/
public static Connection getConnectionFromPool(String dataSourceName)
throws NamingException, SQLException {
if (dataSourceName== null) {
return null;
}

if (ctx == null) {
init();
}

final DataSource dataSrc = (DataSource) ctx.lookup("java:comp/env/"+dataSourceName);
return dataSrc.getConnection();
}

/**
* Gets the mysql connection via pool.
*
* @return the connection via pool
* @throws NamingException the naming exception
* @throws SQLException the sQL exception
*/
public static Connection getMySqlConnectionViaPool()
               throws NamingException, SQLException{
return getConnectionFromPool("jdbc/mysqldb");
}

/**
* De register database drivers.
*/
public static void deRegisterDatabaseDrivers(){
Enumeration<Driver> drivers = DriverManager.getDrivers();
while (drivers.hasMoreElements()) {
Driver driver = drivers.nextElement();
try {           
DriverManager.deregisterDriver(driver); 
LOGGER.info(String.format("Deregistering jdbc driver: %s", driver));
} catch (SQLException e) {         
LOGGER.info(String.format("Error deregistering driver %s", driver), e);
}     
}
}
}

*********************************************************************************

STEP 3: USING CONNECTION

final Connection con= ConnectionManager.getConnection();

final PreparedStatement preStmt= con.prepareStatement("select * from user_profile");

final ResultSet rs=preStmt.executeQuery();

if(rs.next()){

   for(int i=1;i<=rsm.getColumnCount();i++){

       System.out.println("RECORDS: "+rsm.getColumnName(i).toLowerCase()+" |        "+rs.getString(rsm.getColumnName(i)));

    }

}