Sorry, Josh.
I forgot to include this:
<ResourceParams name="jdbc/myoracle">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
Although you might very well have to use DataSourceFactory, not
BasicDataSourceFactory
-----Original Message-----
From: Josh Gooding [mailto:josh.good...@gmail.com]
Sent: Friday, October 30, 2009 1:23 PM
To: Tomcat Users List
Subject: Re: ConnectionPool question
Wait a second. What I am seeing from you Filip and what I have in my
context.xml are similar:
<Context>
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Resource
name="jdbc/RealmDB" auth="Container" type="javax.sql.DataSource"
username="root" password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/monk"
maxActive="-1" maxIdle="5" maxWait="15"
removeAbandoned="true" removeAbandonedTimeout="15"
testWhileIdle="false" timeBetweenEvictionRunsMillis="54000"/>
<Realm
className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jdbc/RealmDB" localDataSource="true"
digest="MD5"
userTable="user" userNameCol="user_name" userCredCol="password"
userRoleTable="tcrole" roleNameCol="role_name" /> </Context>
My ConnectionPool class:
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
import aardvark.exceptions.AardvarkResourceException;
public class ConnectionPool implements ServletContextListener {
@Override
public void contextDestroyed(ServletContextEvent arg0) {}
@Override
public void contextInitialized(ServletContextEvent ci) {}
public static synchronized Connection getConnection() throws
AardvarkResourceException {
try {
DataSource ds = (DataSource) new
InitialContext().lookup("java:/comp/env/jdbc/RealmDB");
return ds.getConnection();
} catch (Exception e) {
throw new AardvarkResourceException(e);
}
}
public static synchronized void recycleConnection(Connection c) {
try {
if (!c.isClosed()) {
c.close();
}
} catch (SQLException e) {
// eat the exception, not much else to do.
}
}
}
Honestly, this should be a very simple fix to replace the current coding with
the new ConnectionPooling stuff. If looks like the old project lead used the
javax.sql ConnectionPool over Tomcat's ConnectionPool coding. I wonder why?
Oh well I'm going to implement this and also change the recycleConnection to
close the statement and resultset as well. That way I can run the
recycleConnection method and it will take care of everything instead of having
more spaghetti code. Doing this much should increase the servers performance.
On Fri, Oct 30, 2009 at 12:12 PM, Filip Hanik - Dev Lists < devli...@hanik.com>
wrote:
> look at jdbc-pool.html it has all the info, here are examples out of
> it
>
> Configuration
>
>
> <Resource name="jdbc/TestDB"
> auth="Container"
> type="javax.sql.DataSource"
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> testWhileIdle="true"
> testOnBorrow="true"
> testOnReturn="false"
> validationQuery="SELECT 1"
> validationInterval="30000"
> timeBetweenEvictionRunsMillis="30000"
> maxActive="100"
> minIdle="10"
> maxWait="10000"
> initialSize="10"
> removeAbandonedTimeout="60"
> removeAbandoned="true"
> logAbandoned="true"
> minEvictableIdleTimeMillis="30000"
> jmxEnabled="true"
> jdbcInterceptors=
>
> "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
> username="root"
> password="password"
> driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://localhost:3306/mysql"/>
>
>
>
>
> Code:
>
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.Statement;
>
> import org.apache.tomcat.jdbc.pool.DataSource;
> import org.apache.tomcat.jdbc.pool.PoolProperties;
>
> public class SimplePOJOExample {
>
> public static void main(String[] args) throws Exception {
> PoolProperties p = new PoolProperties();
> p.setUrl("jdbc:mysql://localhost:3306/mysql");
> p.setDriverClassName("com.mysql.jdbc.Driver");
> p.setUsername("root");
> p.setPassword("password");
> p.setJmxEnabled(true);
> p.setTestWhileIdle(false);
> p.setTestOnBorrow(true);
> p.setValidationQuery("SELECT 1");
> p.setTestOnReturn(false);
> p.setValidationInterval(30000);
> p.setTimeBetweenEvictionRunsMillis(30000);
> p.setMaxActive(100);
> p.setInitialSize(10);
> p.setMaxWait(10000);
> p.setRemoveAbandonedTimeout(60);
> p.setMinEvictableIdleTimeMillis(30000);
> p.setMinIdle(10);
> p.setLogAbandoned(true);
> p.setRemoveAbandoned(true);
>
>
> p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.Connect
> ionState;"+
>
> "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
> DataSource datasource = new DataSource();
> datasource.setPoolProperties(p);
>
> Connection con = null;
> try {
> con = datasource.getConnection();
> Statement st = con.createStatement();
> ResultSet rs = st.executeQuery("select * from user");
> int cnt = 1;
> while (rs.next()) {
> System.out.println((cnt++)+". Host:"
> +rs.getString("Host")+
> " User:"+rs.getString("User")+"
> Password:"+rs.getString("Password"));
> }
> rs.close();
> st.close();
> } finally {
> if (con!=null) try {con.close();}catch (Exception
> ignore) {}
>
> }
> }
>
> }
>
>
> On 10/30/2009 09:54 AM, Filip Hanik - Dev Lists wrote:
>
>>
>>
>> http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/
>> apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log
>>
>> Filip
>>
>> On 10/30/2009 08:34 AM, Josh Gooding wrote:
>>
>>> Hey what API holds the statementFinalizer?
>>>
>>> On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding<josh.good...@gmail.com
>>> >wrote:
>>>
>>> AHHHHHH, I will read the API for the StatementFinalizer. I was
>>> looking
>>>> at
>>>> something to do that. Thank you Filip!
>>>>
>>>>
>>>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>>>> devli...@hanik.com> wrote:
>>>>
>>>> hi Josh, calling Connection.close() does not close statements and
>>>>> resultsets.
>>>>> There is an interceptor you can configure called
>>>>> StatementFinalizer that does exactly that during the close call.
>>>>>
>>>>> Filip
>>>>>
>>>>>
>>>>>
>>>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>>>
>>>>> I wrote some code on top of the Tomcat's ConnectionPool class.
>>>>> In
>>>>>> regular
>>>>>> Java based programming if I close a ResultSet with
>>>>>> connection.close(), this frees up both the statement and
>>>>>> resultset's memory associated with the connection if it was still
>>>>>> open. If I close a connection with Tomcat's ConnectionPool, does
>>>>>> it also close the statement and resultset's associated with that
>>>>>> particular connection or do I need to manually close them?
>>>>>>
>>>>>> I know best practice is to not rely on anything to be closed
>>>>>> automatically, but I inherited a code base and I am looking at
>>>>>> making some pretty significant changes to fix some problems, and
>>>>>> this is one of them.
>>>>>>
>>>>>> Thanks in advance,
>>>>>>
>>>>>> - Josh
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> ------------------------------------------------------------------
>>>>> --- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
>>>>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>>>>
>>>>>
>>>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
