Class for retrieving datasource:
package mypackage.name.here
import javax.sql.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import org.apache.commons.dbcp.*;
import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.*;
public class DatabaseUtil {
static PoolingDataSource dataSource = null;
public static final String DB_PROPERTIES =
"myextension.properties";
// Returns a DataSource that implements connection pooling
public static synchronized DataSource getDataSource(String
configDir) throws Exception {
if (dataSource == null){
Properties p = new Properties();
try{
File cfg = new File(configDir, DB_PROPERTIES);
p.load(new FileInputStream(cfg));
} catch (IOException e) {
System.out.println(e);
}
String type = p.getProperty("dbType");
String url = p.getProperty("url");
String ip = p.getProperty("dbIP");
String name = p.getProperty("dbName");
String port = p.getProperty("dbPort");
String sid = p.getProperty("dbSid");
String fullUrl = null;
if (type.trim().equals("ms")){
// MSSQL
fullUrl = url+ip+"/"+name;
} else {
// ORACLE
fullUrl = url+ip+":"+port+":"+sid;
}
BasicDataSource ds = new BasicDataSource();
ds.setUrl(fullUrl);
ds.setDriverClassName(p.getProperty("driver"));
ds.setUsername(p.getProperty("usr"));
ds.setPassword(EncDoc.decode(p.getProperty("pass")));
ds.setMaxActive(100);
// Create a PoolableDataSource
ObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new
DataSourceConnectionFactory(ds);
PoolableObjectFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory, connectionPool, null,
null, false, true);
dataSource = new PoolingDataSource(connectionPool);
}
return dataSource;
}
}
Sample JSP page:
...
DataSource dataSource = DatabaseUtil.getDataSource(configDir);
Connection myConnect = null;
Statement myStatement = null;
ResultSet myResults = null;
try{
myConnect = dataSource.getConnection();
myStatement = myConnect.createStatement();
...
myQuery = "select count (*) as 'total' from admin where courseID =
'"+courseID+"'";
myStatement.executeQuery(myQuery);
myResults = myStatement.getResultSet();
while (myResults.next()) count = myResults.getInt("total");
...
}catch (SQLException e) {
out.println("SQL Error: <br><br>"+e);
} finally {
try { myResults.close(); } catch(Exception e) {}
try { myStatement.close(); } catch(Exception e) { }
try { myConnect.close(); } catch(Exception e) { }
}
Thanks all.
