Author: sumedha
Date: Tue Dec 18 02:24:18 2007
New Revision: 11377
Log:
connection pooling using DBCP
Added:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBCPConnectionManager.java
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Config.java
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Property.java
Modified:
trunk/commons/data-services/pom.xml
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBConstants.java
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBDeployer.java
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBUtils.java
Modified: trunk/commons/data-services/pom.xml
==============================================================================
--- trunk/commons/data-services/pom.xml (original)
+++ trunk/commons/data-services/pom.xml Tue Dec 18 02:24:18 2007
@@ -4,7 +4,7 @@
<modelVersion>4.0.0</modelVersion>
<groupId>org.wso2.dataservice</groupId>
<artifactId>wso2data-service</artifactId>
- <packaging>jar</packaging>
+ <packaging>jar</packaging>
<version>SNAPSHOT</version>
<name>WSO2 Data Service</name>
<url>http://wso2.org</url>
@@ -22,6 +22,17 @@
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
+ <dependency>
+ <groupId>commons-dbcp</groupId>
+ <artifactId>commons-dbcp</artifactId>
+ <version>${commons.dbcp.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>commons-pool</groupId>
+ <artifactId>commons-pool</artifactId>
+ <version>${commons.pool.version}</version>
+ </dependency>
+
</dependencies>
</dependencyManagement>
@@ -98,6 +109,14 @@
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
+ <dependency>
+ <groupId>commons-dbcp</groupId>
+ <artifactId>commons-dbcp</artifactId>
+ </dependency>
+ <dependency>
+ <groupId>commons-pool</groupId>
+ <artifactId>commons-pool</artifactId>
+ </dependency>
</dependencies>
<scm>
@@ -226,5 +245,7 @@
<commons.httpclient.version>3.0.1</commons.httpclient.version>
<opencsv.version>1.8</opencsv.version>
<poi.version>3.0-FINAL</poi.version>
+ <commons.dbcp.version>1.2.2</commons.dbcp.version>
+ <commons.pool.version>1.3</commons.pool.version>
</properties>
</project>
Added:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBCPConnectionManager.java
==============================================================================
--- (empty file)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBCPConnectionManager.java
Tue Dec 18 02:24:18 2007
@@ -0,0 +1,155 @@
+package org.wso2.ws.dataservice;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.dbcp.ConnectionFactory;
+import org.apache.commons.dbcp.DriverManagerConnectionFactory;
+import org.apache.commons.dbcp.PoolableConnectionFactory;
+import org.apache.commons.dbcp.PoolingDataSource;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.commons.pool.ObjectPool;
+import org.apache.commons.pool.impl.GenericObjectPool;
+import org.wso2.ws.dataservice.beans.Config;
+
+public class DBCPConnectionManager {
+ private static final Log log =
LogFactory.getLog(DBCPConnectionManager.class);
+ public static DataSource datasource = null;
+ private static GenericObjectPool pool = null;
+
+
+ public static DataSource getDatasource() {
+ return datasource;
+ }
+ public static void setDatasource(DataSource datasource) {
+ DBCPConnectionManager.datasource = datasource;
+ }
+
+ /**
+ * @param config - contains configuration parameters to create
connection pool
+ */
+ public DBCPConnectionManager(Config config) {
+ try {
+ connectToDB(config);
+ } catch (Exception e) {
+ log.error("Error occured connecting to database " +
+ "using connection pooling manager", e);
+ }
+ }
+
+
+ protected void finalize() {
+ try {
+ super.finalize();
+ } catch (Throwable e) {
+ log.error("Error occured when finalizing.", e);
+ }
+ }
+
+ /**
+ * @param config - contains connection parameters such as JDBC
URL,username,password,etc
+ *
+ */
+ private void connectToDB(Config config) {
+ String driverClass =
config.getPropertyValue(DBConstants.DRIVER);
+ String jdbcURL = config.getPropertyValue(DBConstants.PROTOCOL);
+ String userName = config.getPropertyValue(DBConstants.USER);
+ String password = config.getPropertyValue(DBConstants.PASSWORD);
+ String minPool =
config.getPropertyValue(DBConstants.MIN_POOL_SIZE);
+ String maxPool =
config.getPropertyValue(DBConstants.MAX_POOL_SIZE);
+
+ // set numeric values for min & max pool sizes
+ int minPoolSize = 0; // default values
+ int maxPoolSize = 100; // default values
+ try {
+ if (minPool != null) {
+ minPoolSize =
Integer.valueOf(minPool).intValue();
+ }
+ if (maxPool != null) {
+ maxPoolSize =
Integer.valueOf(maxPool).intValue();
+ }
+ } catch (NumberFormatException e) {
+ log.error("Non-numeric value found for max/min pool
size", e);
+ }
+
+ try {
+ java.lang.Class.forName(driverClass).newInstance();
+ } catch (ClassNotFoundException e) {
+ log.error("Error locating class "+driverClass, e);
+ } catch (InstantiationException e) {
+ log.error("Error instantiating class "+driverClass, e);
+ } catch (IllegalAccessException e) {
+ log.error("Illegal access to class "+driverClass, e);
+ }
+
+ try {
+ DBCPConnectionManager.datasource =
setupDataSource(jdbcURL,
+ userName, password, minPoolSize,
maxPoolSize);
+ } catch (Exception e) {
+ log.error("Error occured while creating datasource.",
e);
+ }
+ }
+
+ /**
+ * Create dataSource object using given parameters
+ */
+ public static DataSource setupDataSource(String connectionURL,
+ String username, String password, int minIdle, int
maxActive)
+ throws Exception {
+ GenericObjectPool connectionPool = new GenericObjectPool(null);
+ connectionPool.setMinIdle(minIdle);
+ connectionPool.setMaxActive(maxActive);
+
+ DBCPConnectionManager.pool = connectionPool;
+ ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(
+ connectionURL, username, password);
+ PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(
+ connectionFactory, connectionPool, null, null,
false, true);
+ PoolingDataSource dataSource = new
PoolingDataSource(connectionPool);
+ return dataSource;
+ }
+
+ public static void printDriverStats(){
+ ObjectPool connectionPool = DBCPConnectionManager.pool;
+ log.info("NumActive: " + connectionPool.getNumActive());
+ log.info("NumIdle: " + connectionPool.getNumIdle());
+ }
+
+ /**
+ * getLockedProcessCount - gets the number of currently locked
processes
+ * @return Number of locked processes
+ */
+ public int getLockedProcessCount() {
+ int num_locked_connections = 0;
+ Connection con = null;
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ try {
+ con = DBCPConnectionManager.datasource.getConnection();
+ pstmt = con.prepareStatement("SHOW PROCESSLIST");
+ rs = pstmt.executeQuery();
+ while (rs.next()) {
+ if (rs.getString("State") != null
+ &&
rs.getString("State").equals("Locked")) {
+ num_locked_connections++;
+ }
+ }
+ log.info("Number of locked connections :
"+num_locked_connections);
+ } catch (Exception e) {
+ log.error("Error occured while getting
lockedProcessCount",e);
+ } finally {
+ try {
+ rs.close();
+ pstmt.close();
+ con.close();
+ } catch (java.sql.SQLException e) {
+ log.error("Error closing connection.",e);
+ }
+ }
+ return num_locked_connections;
+ }
+}
\ No newline at end of file
Modified:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBConstants.java
==============================================================================
---
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBConstants.java
(original)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBConstants.java
Tue Dec 18 02:24:18 2007
@@ -20,6 +20,12 @@
public static final String PROTOCOL =
"org.wso2.ws.dataservice.protocol";
public static final String USER = "org.wso2.ws.dataservice.user";
public static final String PASSWORD =
"org.wso2.ws.dataservice.password";
+ public static final String MIN_POOL_SIZE =
"org.wso2.ws.dataservice.minpoolsize";
+ public static final String MAX_POOL_SIZE =
"org.wso2.ws.dataservice.maxpoolsize";
+
+ //Keys for parameters in AxisService
+ public static final String CONNECTION_CONFIG =
"org.wso2.ws.dataservice.config";
+
public static final String CSV_DATASOURCE = "csv_datasource";
public static final String EXCEL_DATASOURCE = "excel_datasource";
public static final String JNDI_DATASOURCE = "jndi_datasource";
Modified:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBDeployer.java
==============================================================================
---
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBDeployer.java
(original)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBDeployer.java
Tue Dec 18 02:24:18 2007
@@ -52,6 +52,7 @@
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ws.commons.schema.utils.NamespaceMap;
+import org.wso2.ws.dataservice.beans.Config;
public class DBDeployer implements Deployer, DeploymentConstants {
private static final Log log = LogFactory.getLog(DBDeployer.class);
@@ -209,12 +210,16 @@
// thing is missing we will see an error at Deployment time.
HashMap props = getProperties(connection);
ArrayList reqProps = new ArrayList();
- //Do we really need to populate this list ?
+ //TODO : Do we really need to populate this list ?
reqProps.add(DBConstants.DRIVER);
reqProps.add(DBConstants.PROTOCOL);
reqProps.add(DBConstants.USER);
reqProps.add(DBConstants.PASSWORD);
reqProps.add(DBConstants.DB_CONFIG_ELEMENT);
+
+ //load parameters in <config> section
+ Config config = getConfigProperties(connection);
+ axisService.addParameter(DBConstants.CONNECTION_CONFIG , config);
for (int i = 0; i < reqProps.size(); i++) {
String propName = (String) reqProps.get(i);
@@ -243,7 +248,7 @@
if(dataSourceType.equals(DBConstants.DATASOURCE_TYPE_RDBMS)){
Connection dbConnection;
try {
- dbConnection = DBUtils.createConnection(axisService);
+ dbConnection =
DBUtils.createConnection(axisService.getName(),config);
if (dbConnection != null) {
axisService.addParameter(DBConstants.DB_CONNECTION,
dbConnection);
} else {
@@ -403,6 +408,11 @@
return (String) convertionMap.get(sqlType);
}
+
+ /**
+ * @deprecated : use getConfigProperties() instead
+ *
+ */
private HashMap getProperties(OMElement confiObj) {
HashMap props = new HashMap();
for (Iterator iter = confiObj.getChildrenWithName(new
QName("property")); iter.hasNext();) {
@@ -413,6 +423,15 @@
return props;
}
+ private Config getConfigProperties(OMElement confiObj) {
+ Config config = new Config();
+ for (Iterator iter = confiObj.getChildrenWithName(new
QName("property")); iter.hasNext();) {
+ OMElement element = (OMElement) iter.next();
+ config.addProperty((element.getAttributeValue(new QName("name"))),
element.getText());
+ }
+ return config;
+ }
+
private ArrayList processService(DeploymentFileData currentFile,
AxisServiceGroup axisServiceGroup,
ConfigurationContext configCtx)
throws AxisFault {
Modified:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBUtils.java
==============================================================================
---
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBUtils.java
(original)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/DBUtils.java
Tue Dec 18 02:24:18 2007
@@ -63,6 +63,7 @@
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.wso2.ws.dataservice.beans.CSVQuery;
+import org.wso2.ws.dataservice.beans.Config;
import org.wso2.ws.dataservice.beans.ExcelQuery;
import org.wso2.ws.dataservice.beans.Result;
@@ -215,7 +216,8 @@
if(log.isDebugEnabled()){
log.debug("Database connection is closed.Trying to
re-establish.");
}
- return createConnection(axisService);
+ Config config =
(Config)axisService.getParameterValue(DBConstants.CONNECTION_CONFIG);
+ return createConnection(axisService.getName(),config);
}else{
//existing connection is not closed. Return it.
return conn;
@@ -240,11 +242,16 @@
}
conn = (Connection)dbConnectionParam.getValue();
conn = checkDBConnectionStatus(axisService, conn);
+
+ log.info("Using connection object :
"+Integer.toHexString(conn.hashCode()));
+
conn.setAutoCommit(false);
PreparedStatement preparedStatement =
getProcessedPreparedStatement(inputValues,
params, paramOrder, conn, sqlQuery);
int responseCode = -1;
ResultSet rs = null;
+
+ DBCPConnectionManager.printDriverStats();
if (isDML) {
responseCode =
preparedStatement.executeUpdate();
} else {
@@ -363,17 +370,18 @@
}
-// finally {
-// try {
-// if (conn != null) {
-// conn.commit();
-// conn.close();
-// }
-// } catch (SQLException e) {
-// log.error(e.getMessage());
-// throw new AxisFault("Exception occurred while trying to
commit.", e);
-// }
-// }
+ finally {
+ try {
+ if (conn != null) {
+ conn.commit();
+ conn.close();
+ }
+ } catch (SQLException e) {
+ log.error(e.getMessage());
+ throw new AxisFault(
+ "Exception occurred while
trying to commit.", e);
+ }
+ }
return resultElement;
}
@@ -855,18 +863,26 @@
}
- public static Connection createConnection(AxisService axisService)
throws AxisFault {
+ public static Connection createConnection(String serviceName,Config
config) throws AxisFault {
try{
- log.info("Creating database connection for
"+axisService.getName());
+ log.info("Creating database connection for
"+serviceName);
//Try to load the JDBC driver class. If class not found
throw an error.
- Class.forName((String)
axisService.getParameterValue(DBConstants.DRIVER)).newInstance();
- Connection conn ;
+
Class.forName(config.getPropertyValue(DBConstants.DRIVER)).newInstance();
+ Connection conn = null;
Properties props = new Properties();
- props.put("user",
axisService.getParameterValue(DBConstants.USER));
- props.put("password",
axisService.getParameterValue(DBConstants.PASSWORD));
-
- conn = DriverManager.getConnection((String) axisService
-
.getParameterValue(DBConstants.PROTOCOL), props);
+ props.put("user",
config.getPropertyValue(DBConstants.USER));
+ props.put("password",
config.getPropertyValue(DBConstants.PASSWORD));
+
+
+ if(config.getPropertyValue(DBConstants.MIN_POOL_SIZE)
!= null
+ ||
config.getPropertyValue(DBConstants.MAX_POOL_SIZE) != null){
+ //user has set connection pool size(s). Get
connection from pooling manager
+ DBCPConnectionManager dbcpConnectionManager =
new DBCPConnectionManager(config);
+ conn =
dbcpConnectionManager.getDatasource().getConnection();
+
+ }else{
+ conn = DriverManager.getConnection((String)
config.getPropertyValue(DBConstants.PROTOCOL), props);
+ }
return conn;
} catch (SQLException e) {
log.error("Error occured while connecting to
database",e);
@@ -877,12 +893,12 @@
throw new AxisFault("JDBC driver not available in
classpath : "+e.getMessage());
}
catch (InstantiationException e) {
- log.error("Error occurred during instantiating
"+(String) axisService.getParameterValue(DBConstants.DRIVER), e);
- throw new AxisFault("Error occurred during
instantiating "+(String) axisService.getParameterValue(DBConstants.DRIVER), e);
+ log.error("Error occurred during instantiating
"+config.getPropertyValue(DBConstants.DRIVER), e);
+ throw new AxisFault("Error occurred during
instantiating "+config.getPropertyValue(DBConstants.DRIVER), e);
}
catch (IllegalAccessException e) {
- log.error("Illegal Access error during loading
"+(String) axisService.getParameterValue(DBConstants.DRIVER), e);
- throw new AxisFault("Illegal Access error during
loading "+(String) axisService.getParameterValue(DBConstants.DRIVER), e);
+ log.error("Illegal Access error during loading
"+config.getPropertyValue(DBConstants.DRIVER), e);
+ throw new AxisFault("Illegal Access error during
loading "+config.getPropertyValue(DBConstants.DRIVER), e);
}
}
Added:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Config.java
==============================================================================
--- (empty file)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Config.java
Tue Dec 18 02:24:18 2007
@@ -0,0 +1,33 @@
+package org.wso2.ws.dataservice.beans;
+
+import java.util.ArrayList;
+import java.util.Iterator;
+
+public class Config {
+ ArrayList properties = new ArrayList();
+
+ public ArrayList getProperties() {
+ return properties;
+ }
+
+ public void setProperties(ArrayList properties) {
+ this.properties = properties;
+ }
+
+ public void addProperty(String name,String value){
+ Property property = new Property(name,value);
+ properties.add(property);
+ }
+
+ public String getPropertyValue(String propertyName){
+ Iterator propertyItr = properties.iterator();
+ for (; propertyItr.hasNext();) {
+ Property property = (Property) propertyItr.next();
+ if(property.getName().equals(propertyName)){
+ return property.getValue();
+ }
+ }
+ return null;
+ }
+
+}
Added:
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Property.java
==============================================================================
--- (empty file)
+++
trunk/commons/data-services/src/main/java/org/wso2/ws/dataservice/beans/Property.java
Tue Dec 18 02:24:18 2007
@@ -0,0 +1,33 @@
+package org.wso2.ws.dataservice.beans;
+
+/*
+ * Represents property elements in config section.
+ * eg :
+ * <config>
+ * <property
name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
+ * <property
name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/classicmodels</property>
+ * <property name="org.wso2.ws.dataservice.user">sumedha</property>
+ * <property name="org.wso2.ws.dataservice.password">sumedha</property>
+ * </config>
+ */
+public class Property {
+ private String name;
+ private String value;
+
+ public Property(String name, String value) {
+ this.name = name;
+ this.value = value;
+ }
+ public String getName() {
+ return name;
+ }
+ public void setName(String name) {
+ this.name = name;
+ }
+ public String getValue() {
+ return value;
+ }
+ public void setValue(String value) {
+ this.value = value;
+ }
+}
_______________________________________________
Commons-dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/commons-dev
