Properties File: db.properties
#db.connection.driver = oracle.jdbc.OracleDriver
#db.connection.url = jdbc:oracle:thin:@//localhost:1521/[sid]
#db.connection.driver = net.sourceforge.jtds.jdbc.Driver
#db.connection.url = jdbc:jtds.sqlserver://localhost:1433/[database]
#db.connection.driver = com.mysql.jdbc.Driver
#db.connection.url = jdbc:mysql://localhost:3306/[database]
#PostgreSQL Database -
http://jdbc.postgresql.org/download.html
#db.connection.driver =
org.postgresql..Driver
#db.connection.url =
jdbc:postgresql://localhost:5432/[database]
db.connection.username =
[username]
db.connection.password =
[password]
db.connection.defaultpool = dbpool
db.connection.max.active = 5
db.connection.max.idle = 5
db.connection.max.wait = 10000
class: DBManager.java
package com.pd.db;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;
/**
* @author PD
*
*/
public class DBManager {
private static ConnectionFactory connectionFactory = null;
private static int DEFAULT_MAX_ACTIVE = 30;
private static int DEFAULT_MAX_IDLE = 30;
private static int DEFAULT_MAX_WAIT = 10000;
private static Properties props = null;
public static void load(Properties props) throws Exception {
DBManager.props = props;
//
// Load JDBC Driver class.
//
String driverName = getProperty("db.connection.driver");
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new Exception(
"Could not find suitable classes to load driver "
+ driverName, e);
}
//
// First, we'll need a ObjectPool that serves as the
// actual pool of connections.
//
// We'll use a GenericObjectPool instance, although
// any ObjectPool implementation will suffice.
//
GenericObjectPool connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(Integer.parseInt(getProperty("db.connection.max.active", String.valueOf(DEFAULT_MAX_ACTIVE))));
connectionPool.setMaxIdle(Integer.parseInt(getProperty("db.connection.max.idle", String.valueOf(DEFAULT_MAX_IDLE))));
connectionPool.setMaxWait(Integer.parseInt(getProperty("db.connection.max.wait", String.valueOf(DEFAULT_MAX_WAIT))));
//
// Next, we'll create a ConnectionFactory that the
// pool will use to create Connections.
// We'll use the DriverManagerConnectionFactory,
// using the connect string passed in the command line
// arguments.
//
connectionFactory = new DriverManagerConnectionFactory(getProperty("db.connection.url"), getProperty("db.connection.username"), getProperty("db.connection.password"));
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);
//
// Finally, we create the PoolingDriver itself...
//
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool(getProperty("db.connection.defaultpool", "dbpool"), connectionPool);
fireTestQuery();
}
/**
* Call this to get the Connection objects in each DAO
*
* @return java.sql.Connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
if (connectionFactory != null) {
conn = connectionFactory.createConnection();
}
} catch (SQLException e) {
System.out.println("Connection already in use. Close connection and creat new Connection");
e.printStackTrace();
throw e;
}
return conn;
}
private static void fireTestQuery() throws Exception {
Connection conn = null;
PreparedStatement statement = null;
ResultSet results = null;
try {
conn = getConnection();
statement = conn.prepareStatement(getProperty("db.connection.query.test", "SELECT now()"));
results = statement.executeQuery();
if (!results.next())
throw new Exception("Not connected ...");
else {
System.out.println("TEST QUERY RESULTS No of rows :: "
+ results.getRow());
}
} catch (SQLException sqle) {
throw new Exception(
"Test Query failed during start up because of SQLException ",
sqle);
} finally {
try {
if (results != null) {
results.close();
results = null;
}
if (statement != null) {
statement.close();
statement = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ignore) {
}
}
}
private static String getProperty(String key) throws Exception {
return getProperty(key, null);
}
private static String getProperty(String key, String defaultValue) throws Exception {
String str = props.getProperty(key);
if (str == null || str.trim().length() == 0) {
if(defaultValue != null) {
System.out.println("Using default value " + defaultValue + " due to missing entry - " + key);
return defaultValue;
} else {
System.err.println("DBManager :: Could not load driver :: Missing Entry - " + key);
throw new Exception("DBManager :: Could not load driver :: Missing Entry - " + key);
}
}
return str;
}
public static void main(String[] args) {
Properties props = new Properties();
try {
props.load(new FileInputStream("db.properties"));
load(props);
} catch (Exception e) {
e.printStackTrace();
}
}
}
