Hi All. I am using Apache commons Datasource to create a connection pool to connect with a Oracle database. It takes at least 5 or more tries to establish the connection pool for the first time. Once the connection pool is established and then the all other following requests work fine. Again if there are no requests for a long time, the connection pool timeout occurs and then again the error while establishing the connection, The errors I am getting while establishing the connection for the first time are as follows :
- Connection Already Closed
- The network adapter could not establish the connection
- Socket Error : Read time out
All the above seem to be occurring due to network issues, but I checked every time when the error occurred and the network connection was fine, I was able successfully establish connection on the first try through other jdbc client.
I have attached below the piece of Java code that I use to establish the connection,
public class DataSource {
private static String DB_URL = "jdbc:oracle:thin:@db-host:1521:orcl";
private static String DRIVER_CLASS = "oracle.jdbc.OracleDriver";
private static DataSource datasource;
private BasicDataSource ds;
/**
* Set Connection Parameters
* @throws IOException
* @throws SQLException
* @throws PropertyVetoException
*/
private DataSource() throws IOException, SQLException, PropertyVetoException {
ds = new BasicDataSource();
ds.setDriverClassName(DRIVER_CLASS);
ds.setUsername(CommonProperties.getInstance().get("DB_USERNAME"));
ds.setPassword(CommonProperties.getInstance().get("DB_PASSWORD"));
ds.setUrl(DB_URL);
ds.setMinIdle(5);
ds.setMaxIdle(20);
ds.setMaxOpenPreparedStatements(180);
}
/**
* get Instance
* @throws IOException
* @throws SQLException
* @throws PropertyVetoException
*/
public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
if (datasource == null) {
datasource = new DataSource();
return datasource;
} else {
return datasource;
}
}
/**
* get Connection
* @return Connection object
* @throws SQLException
*
*/
public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}
}
The getConnection() method will return a connection from the connection pool if it exists or create a new connection pool. Let me know if you guys need any additional info,
Thanks in Advance.! Hope this gets resolved. Any help is appreciated.