This is a response to
http://stackoverflow.com/questions/9203648/how-to-do-connection-pooling-on-struts-fraework/9204790#comment11767509_9204790 the question of the Original Poster on how to configure bonecp in struts. Due to the lengthy of the howto, thus, the environment setup, coding, and detail guide are describe here instead.
Note! This howto is not meant for performance nor able to work for everyone but it serve as a guide to ensure bonecp able to work in struts in 1.3. As far as I know, data-sources is removed from struts 1.2 dtd and thus,this guide serve as a functional documentation on how to configure bonecp in struts via tomcat5.
1. the environment for this howto is at below
operating system : centos 5.6 2.6.18-238.19.1.el5
tomcat : tomcat5-5.5.23-0jpp.19.el5_6
struts : struts-1.3.10
bonecp : bonecp-0.7.1.RELEASE.jar
mysql-connector-java : mysql-connector-java-5.1.16.jar
mysql : mysql-server-5.0.95-1.el5_7.1
dependency of struts : commons-digester-1.8.jar
commons-chain-1.2.jar
commons-beanutils-1.8.0.jar
struts-taglib-1.3.10.jar
dependency of bonecp : guava-11.0.1.jar
slf4j-api-1.6.4.jar
2. Note: place these jar file into tomcat common lib directory,
bonecp-0.7.1.RELEASE.jar
guava-11.0.1.jar
slf4j-api-1.6.4.jar (and slf4j-log4j if you want to)
mysql-connector-java-5.1.16.jar
3. Locate tomcat server.xml (for this example, it is /etc/tomcat5/server.xml) and provide this below, under GlobalNamingResources, add a new resource.
<Resource type='javax.sql.DataSource'
name='demodb'
factory='com.jolbox.bonecp.BoneCPDataSource'
driverClassName='com.mysql.jdbc.Driver'
jdbcUrl='jdbc:mysql://localhost/demo'
username='user1'
password='password1'
idleMaxAge='240'
idleConnectionTestPeriod='60'
partitionCount='3'
acquireIncrement='5'
maxConnectionsPerPartition='10'
minConnectionsPerPartition='5'
statementsCacheSize='50'
releaseHelperThreads='5'
/>
4. Location tomcat context.xml (for this example, it is /etc/tomcat5/context.xml), add a resource link.
<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname='' />
-->
<ResourceLink global='demodb' name='demodb' type='javax.sql.DataSource'/>
</Context>
5., then, the struts-config.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE struts-config PUBLIC
'-//Apache Software Foundation//DTD Struts Configuration 1.3//EN'
'http://jakarta.apache.org/struts/dtds/struts-config_1_3.dtd'>
<struts-config>
<form-beans>
<form-bean name='helloWorldForm' type='com.e2e.form.HelloWorldForm' />
</form-beans>
<action-mappings>
<action path='/helloWorld' type='com.e2e.action.HelloWorldAction'
name='helloWorldForm'>
<forward name='success' path='/HelloWorld.jsp' />
</action>
<action path='/DataSource' type='com.e2e.action.TestDataSource'>
<forward name='success' path='/success.jsp'></forward>
</action>
</action-mappings>
</struts-config>
6. then the web description, web.xml
<!DOCTYPE web-app PUBLIC
'-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN'
'http://java.sun.com/dtd/web-app_2_3.dtd' >
<web-app>
<display-name>bonecp-struts</display-name>
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>
org.apache.struts.action.ActionServlet
</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>
/WEB-INF/struts-config.xml
</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<resource-ref>
<description>struts-bonecp</description>
<res-ref-name>demodb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
7. then the TestDataSource.java
package com.e2e.action;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
public class TestDataSource extends Action
{
public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception
{
javax.sql.DataSource dataSource;
java.sql.Connection myConnection=null;
try
{
dataSource = getDataSource(request);
if (dataSource == null)
{
System.out.println('datasource is null');
}
myConnection = dataSource.getConnection();
Statement stmt=myConnection.createStatement();
ResultSet rst=stmt.executeQuery('select username from test');
System.out.println('******************************************');
System.out.println('********Out Put from TestDataSource ******');
while(rst.next())
{
System.out.println('User Name is: ' + rst.getString('username'));
}
System.out.println('******************************************');
rst.close();
stmt.close();
// do what you wish with myConnection
}
catch (SQLException sqle)
{
getServlet().log('Connection.process', sqle);
}
finally
{
//enclose this in a finally block to make
//sure the connection is closed
try
{
myConnection.close();
}
catch (SQLException e)
{
getServlet().log('Connection.close', e);
}
}
return mapping.findForward('success');
}
private DataSource getDataSource(HttpServletRequest request) throws NamingException
{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup('java:comp/env/demodb');
return ds;
}
}
8. create a simple jsp page under directory WEB-INF
<%@ page language='java' contentType='text/html; charset=UTF-8'
pageEncoding='UTF-8'%>
<!DOCTYPE html PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>
<title>Insert title here</title>
</head>
<body>
OK
</body>
</html>
9. finally, hit the link http://
:8080/bonecp-struts/DataSource.do
you should be able to see an OK in the browser and if you tail the log in tomcat log directory (in this example, it is /var/log/tomcat5/catalina.out) , you should be able to see data is retrieve from the database and printed.
