public synchronized Connection connection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
/** Create dataSource object using given parameters */
public 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);
connectionPool.setMaxWait(1000 * 60);
pool = connectionPool;
ConnectionFactory connectionFactory =
new DriverManagerConnectionFactory(connectionURL, username, password);
PoolableConnectionFactory factory =
new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);
pool.setFactory(factory);
PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
dataSource.setAccessToUnderlyingConnectionAllowed(true);
return dataSource;
}
private void initPool() {
PoolConfig pconfig = Db.getInstance().getPoolConfig();
int poolSize = Db.supports(Capability.ROW_LEVEL_LOCKING) ? pconfig.mPoolSize : 1;
// if no row lock, we need serial access to the underlying db file
// still need external synchronization for now since other connections to zimbra.db can occur
// through DbPool
mConnectionPool =
new GenericObjectPool(null, poolSize, GenericObjectPool.WHEN_EXHAUSTED_BLOCK, -1, poolSize);
ConnectionFactory cfac = ZimbraConnectionFactory.getConnectionFactory(pconfig);
boolean defAutoCommit = false, defReadOnly = false;
new PoolableConnectionFactory(cfac, mConnectionPool, null, null, defReadOnly, defAutoCommit);
try {
PoolingDataSource pds = new PoolingDataSource(mConnectionPool);
pds.setAccessToUnderlyingConnectionAllowed(true);
Db.getInstance().startup(pds, pconfig.mPoolSize);
mDataSource = pds;
} catch (SQLException e) {
ZimbraLog.system.error("failed to initialize offline db pool", e);
}
}
public void testCreatePool() throws Exception {
final ObjectFactory objectFactory = ClassicEngineBoot.getInstance().getObjectFactory();
final IDatabaseDialectService dialectService = objectFactory.get(IDatabaseDialectService.class);
final DatabaseTypeHelper databaseTypeHelper =
new DatabaseTypeHelper(dialectService.getDatabaseTypes());
final DatabaseConnection con = new DatabaseConnection();
con.setId("Memory");
con.setName("Memory");
con.setAccessType(DatabaseAccessType.NATIVE);
con.setDatabaseType(databaseTypeHelper.getDatabaseTypeByShortName("GENERIC"));
con.setUsername("pentaho_user");
con.setPassword("password");
final HashMap<String, String> attrs = new HashMap<String, String>();
attrs.put(DatabaseConnection.ATTRIBUTE_CUSTOM_DRIVER_CLASS, "org.hsqldb.jdbcDriver");
attrs.put(DatabaseConnection.ATTRIBUTE_CUSTOM_URL, "jdbc:hsqldb:mem:SampleData");
con.setAttributes(attrs);
final PoolingDataSource poolingDataSource = PooledDatasourceHelper.setupPooledDataSource(con);
final Connection connection = poolingDataSource.getConnection();
connection.close();
}
public DbConnection getConnection() throws ServiceException {
Connection dbconn = null;
DbConnection conn = null;
try {
dbconn = mDataSource.getConnection();
if (dbconn.getAutoCommit() != false) {
dbconn.setAutoCommit(false);
}
conn = new DbConnection(dbconn);
Db.getInstance().postOpen(conn);
} catch (SQLException e) {
try {
if (dbconn != null && !dbconn.isClosed()) {
dbconn.close();
}
} catch (SQLException e2) {
ZimbraLog.sqltrace.warn("DB connection close caught exception", e);
}
throw ServiceException.FAILURE("getting database connection", e);
}
return conn;
}
public static DataSource getDatasource() throws SQLException {
if (dataSource != null) {
return dataSource;
}
try {
// Register basic JDBC driver
Class driverClass = Class.forName(ConfigurationManager.getProperty("db.driver"));
Driver basicDriver = (Driver) driverClass.newInstance();
DriverManager.registerDriver(basicDriver);
// Read pool configuration parameter or use defaults
// Note we check to see if property is null; getIntProperty returns
// '0' if the property is not set OR if it is actually set to zero.
// But 0 is a valid option...
int maxConnections = ConfigurationManager.getIntProperty("db.maxconnections");
if (ConfigurationManager.getProperty("db.maxconnections") == null) {
maxConnections = 30;
}
int maxWait = ConfigurationManager.getIntProperty("db.maxwait");
if (ConfigurationManager.getProperty("db.maxwait") == null) {
maxWait = 5000;
}
int maxIdle = ConfigurationManager.getIntProperty("db.maxidle");
if (ConfigurationManager.getProperty("db.maxidle") == null) {
maxIdle = -1;
}
boolean useStatementPool = ConfigurationManager.getBooleanProperty("db.statementpool", true);
// Create object pool
ObjectPool connectionPool =
new GenericObjectPool(
null, // PoolableObjectFactory
// - set below
maxConnections, // max connections
GenericObjectPool.WHEN_EXHAUSTED_BLOCK,
maxWait, // don't
// block
// more than 5
// seconds
maxIdle, // max idle connections (unlimited)
true, // validate when we borrow connections from pool
false // don't bother validation returned connections
);
// ConnectionFactory the pool will use to create connections.
ConnectionFactory connectionFactory =
new DriverManagerConnectionFactory(
ConfigurationManager.getProperty("db.url"),
ConfigurationManager.getProperty("db.username"),
ConfigurationManager.getProperty("db.password"));
//
// Now we'll create the PoolableConnectionFactory, which wraps
// the "real" Connections created by the ConnectionFactory with
// the classes that implement the pooling functionality.
//
String validationQuery = "SELECT 1";
// Oracle has a slightly different validation query
if ("oracle".equals(ConfigurationManager.getProperty("db.name"))) {
validationQuery = "SELECT 1 FROM DUAL";
}
GenericKeyedObjectPoolFactory statementFactory = null;
if (useStatementPool) {
// The statement Pool is used to pool prepared statements.
GenericKeyedObjectPool.Config statementFactoryConfig = new GenericKeyedObjectPool.Config();
// Just grow the pool size when needed.
//
// This means we will never block when attempting to
// create a query. The problem is unclosed statements,
// they can never be reused. So if we place a maximum
// cap on them, then we might reach a condition where
// a page can only be viewed X number of times. The
// downside of GROW_WHEN_EXHAUSTED is that this may
// allow a memory leak to exist. Both options are bad,
// but I'd prefer a memory leak over a failure.
//
// FIXME: Perhaps this decision should be derived from config parameters?
statementFactoryConfig.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
statementFactory = new GenericKeyedObjectPoolFactory(null, statementFactoryConfig);
}
PoolableConnectionFactory poolableConnectionFactory =
new PoolableConnectionFactory(
connectionFactory,
connectionPool,
statementFactory,
validationQuery, // validation query
false, // read only is not default for now
false); // Autocommit defaults to none
//
// Finally, we create the PoolingDataSource itself...
//
PoolingDataSource poolingDataSource = new PoolingDataSource();
//
// ...and register our pool with it.
//
poolingDataSource.setPool(connectionPool);
dataSource = poolingDataSource;
return poolingDataSource;
} catch (Exception e) {
// Need to be able to catch other exceptions. Pretend they are
// SQLExceptions, but do log
log.warn("Exception initializing DB pool", e);
throw new SQLException(e.toString(), e);
}
}
