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.ConnectionState;"+
>
> "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
>
>
