I have an example using the Connection Pooling that comes with the Oracle JDBC drivers.
It's rough, need to clean up logging and such, but the end result is that
OracleConnectionManager.getConnection(); returns a pooled connection and the
connection props I have stored in WEB-INF/classes/db.properties, although I think you
can put it anywhere in the classpath and it will be found.
Here's the properties file:
## For OracleConnectionManager
stmtCacheSize=100
driverType=thin
serverName=server
networkProtocol=tcp
databaseName=name
port=1521
user=user
password=password
Here's OracleConnectionManager.java
(I'm probably infringing on a trademark here, so I'll just say that I'm not in any
way affiliated with Oracle and neither is this code.)
package dbutils;
import org.apache.log4j.helpers.Loader;
import java.sql.*;
import java.util.*;
import java.io.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.driver.*;
import javax.sql.*;
/**
* Facilitates use of pooled oracle db connections.
*
* @author David Durham
*
*/
public class OracleConnectionManager {
private static int stmtCacheSize = 100;
private static OracleConnectionPoolDataSource ocpds = null;
private static String connectString = null;
private static String username = null;
private static String password = null;
private OracleConnectionManager() {
//if (ocpds == null) {
createPool();
//}
}
private void createPool() {
try {
Properties dbinfo = new Properties();
InputStream in = new FileInputStream(new
File(Loader.getResource("db.properties").getFile()));
dbinfo.load(in);
this.stmtCacheSize =
Integer.parseInt(dbinfo.getProperty("stmtCacheSize"));
this.username = dbinfo.getProperty("user");
this.password = dbinfo.getProperty("password");
ocpds = new OracleConnectionPoolDataSource();
ocpds.setDriverType(dbinfo.getProperty("driverType"));
ocpds.setServerName(dbinfo.getProperty("serverName"));
ocpds.setNetworkProtocol(dbinfo.getProperty("networkProtocol"));
ocpds.setDatabaseName(dbinfo.getProperty("databaseName"));
ocpds.setPortNumber(Integer.parseInt(dbinfo.getProperty("port")));
ocpds.setUser(dbinfo.getProperty("user"));
ocpds.setPassword(dbinfo.getProperty("password"));
} catch (Exception e) {
System.err.print(e.getMessage());
}
}
/**
* gets a connection from the connection pool
* @return a connection from the connection pool
*/
public static java.sql.Connection getConnection() throws SQLException
if (ocpds == null) {
OracleConnectionManager ocm = new OracleConnectionManager();
}
PooledConnection pconn = null;
try {
pconn = ocpds.getPooledConnection();
((OraclePooledConnection)pconn).setStmtCacheSize(stmtCacheSize);
} catch ( Exception e ) {
System.err.println("Connection attempt failed: " +
e.getMessage());
}
return pconn.getConnection();
}
}
> -----Original Message-----
> From: Raible, Matt [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 10:18 AM
> To: 'Struts Users Mailing List'
> Subject: RE: db connection pool question
>
>
> After waiting 24 hours, I'm still experiencing the same
> problem with Oracle
> and a connection pool (DBCP) in Tomcat. I guess I'll try configuring
> Oracle's connection pool??
>
> Matt
>
> > -----Original Message-----
> > From: Raible, Matt
> > Sent: Tuesday, January 21, 2003 8:22 AM
> > To: 'Struts Users Mailing List'
> > Subject: RE: db connection pool question
> >
> >
> > Actually, the solution that I had only works for MySQL. I'm
> > having the same problem with Oracle. Funny - I just posted a
> > message to the commons-user group asking this same question.
> > I'll try adding the validationQuery parameter:
> >
> > <parameter>
> > <name>validationQuery</name>
> > <value>SELECT 'CRAP' FROM DUAL</value>
> > </parameter>
> >
> > Thanks,
> >
> > Matt
> >
> > > -----Original Message-----
> > > From: Pani, Gourav [mailto:[EMAIL PROTECTED]]
> > > Sent: Tuesday, January 21, 2003 7:58 AM
> > > To: 'Struts Users Mailing List'
> > > Subject: RE: db connection pool question
> > >
> > >
> > > please look at the thread with subject "Problem with JDBC & Struts
> > > Connection Pool (possible to recon nect?)". Matt Raible had
> > > a solution to
> > > that by setting autoReconnect=true
> > >
> > > -----Original Message-----
> > > From: david chan [mailto:[EMAIL PROTECTED]]
> > > Sent: Tuesday, January 21, 2003 9:56 AM
> > > To: Struts Users Mailing List
> > > Subject: db connection pool question
> > >
> > >
> > > Hi,
> > > I am using a connection pool from
> > > org.apache.commons.dbcp.BasicDataSourceFactory and the
> > > driver is oracle.jdbc.driver.OracleDriver. It works
> > > great until if the server idle for a few days, then
> > > the connection object seems broken with this error:
> > > ==== begin error mesg ===
> > > java.sql.SQLException: Io exception: Software caused
> > > connection abort: socket wr
> > > ite error at
> > > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
> > > at
> > > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
> > > at
> > > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:323)
> > > at
> > >
> oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:417)
> > > at
> > >
> oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:432)
> > > at
> > >
> >
> oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedState
> > > ment.java:182) at
> > >
> >
> oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleCon
> > > nection.java:602) at
> > >
> >
> oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection
> > > .java:538) at
> > > org.apache.commons.dbcp.DelegatingConnection.prepareStatement(
> > > DelegatingConn
> > > ection.java:197)
> > > ...
> > > ===== end of error mesg ====
> > >
> > > What happen and how can I prevent this?
> > > Thanks.
> > > David
> > >
> > >
> > >
> > > __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > > http://mailplus.yahoo.com
> > >
> > > --
> > > To unsubscribe, e-mail:
> > > <mailto:[EMAIL PROTECTED]>
> > > For additional commands, e-mail:
> > > <mailto:[EMAIL PROTECTED]>
> > >
> > > --
> > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
> <mailto:[EMAIL PROTECTED]>
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
