RE: Connection Pooling built into Tomcat? - Amit Wadhwa - com.mysql.lists.java

12 messages in com.mysql.lists.javaRE: Connection Pooling built into Tom...

From	Sent On	Attachments
Paul Higgins	13 Feb 2004 23:26
Paul Higgins	13 Feb 2004 23:35
Paul Higgins	15 Feb 2004 10:55
SMaric	16 Feb 2004 14:24
Amit Wadhwa	18 Feb 2004 16:54
Amit Wadhwa	18 Feb 2004 17:41
Steve Forsyth	18 Feb 2004 18:03
Amit Wadhwa	19 Feb 2004 05:36
SMaric	19 Feb 2004 07:57
Stefan	20 Feb 2004 16:01
Jim	29 Feb 2004 11:27
Loren Siebert	29 Feb 2004 11:40

Subject:	RE: Connection Pooling built into Tomcat?
From:	Amit Wadhwa (amit...@hathway.com)
Date:	02/19/2004 05:36:21 AM
List:	com.mysql.lists.java

i did this.. --->

<Resource name="jdbc/mydb" scope="Shareable" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/mydb"> <parameter> <name>validationQuery</name> <value>select count(*) from security</value> </parameter> <parameter> <name>url</name> <value>jdbc:mysql://127.0.0.1/visilicone </value> // visilicone is the db name i have </parameter> <parameter> <name>password</name> <value>root</value> </parameter> <parameter> <name>maxActive</name> <value>50</value> </parameter> <parameter> <name>maxWait</name> <value>5000</value> </parameter> <parameter> <name>driverClassName</name> <value>org.gjt.mm.mysql.Driver</value> // i use the mm driver </parameter> <parameter> <name>username</name> <value>root</value> </parameter> <parameter> <name>maxIdle</name> <value>10</value> </parameter> </ResourceParams>

and this in my source code..

import java.io.*; import java.sql.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; import javax.naming.*; import javax.sql.*;

public Connection getDBConnection() { Context ctx = null; Context envCtx = null; DataSource ds = null; Connection conn = null; try { ctx = new InitialContext();; if (ctx == null) {throw new Exception("Boom - No Context");} envCtx = (Context) ctx.lookup("java:comp/env"); ds = (DataSource) envCtx.lookup("jdbc/mydb"); conn = ds.getConnection(); } catch (Exception e) { e.printStackTrace(System.out); } return conn;

and on my tomcat console i get:

javax.naming.NameNotFoundException: Name mydb is not bound in this Context at org.apache.naming.NamingContext.lookup(Unknown Source) at org.apache.naming.NamingContext.lookup(Unknown Source) at org.apache.naming.NamingContext.lookup(Unknown Source) at org.apache.naming.NamingContext.lookup(Unknown Source) at resiljsp.general.JSPLib.getDBConnection(JSPLib.java:455) at resiljsp.general.JSPLib.isValidRequest(JSPLib.java:244) at org.apache.jsp.JSPDiscForumHome$jsp._jspService(JSPDiscForumHome$jsp. java:72) at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknow n Source) at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source) at org.apache.jasper.servlet.JspServlet.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unkn own Source) at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown Sour ce) at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardContextValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.valves.CertificatesValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardContext.invoke(Unknown Source) at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.valves.AccessLogValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source) at org.apache.catalina.core.StandardPipeline.invokeNext(Unknown Source) at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source) at org.apache.catalina.core.ContainerBase.invoke(Unknown Source) at org.apache.catalina.connector.http.HttpProcessor.process(Unknown Source) at org.apache.catalina.connector.http.HttpProcessor.run(Unknown Source) at java.lang.Thread.run(Thread.java:536)

what am i doing wrong? this is exactly the error i got the last time....

when i use this -->

public Connection getDBConnection() { JSPConf conf = new JSPConf(); Connection con = null; try { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); con = DriverManager.getConnection(conf.getVal("getDBString1"),conf.getVal("getDBSt ring2"),conf.getVal"getDBString3")); } catch(Exception e){e.printStackTrace(System.out);} return (con); }

it works ok.. please help.. thanks a bunch in advance..

- Amit

-----Original Message----- From: Steve Forsyth [mailto:stev...@hotmail.com] Sent: Thursday, February 19, 2004 7:34 AM To: amit...@hathway.com; ja...@lists.mysql.com Subject: RE: Connection Pooling built into Tomcat?

Here is a clip from my server.xml:

(obviously, you will have to fill in the username/password and host/database) (Also, ECTrack is the name I gave it.... you name it whatever you want, and then you reference that name when you go to retrieve the DataSource from the context)

<Resource name="jdbc/ECTrack" scope="Shareable" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/ECTrack"> <parameter> <name>validationQuery</name> <value>select count(*) from security</value> </parameter> <parameter> <name>url</name> <value>jdbc:mysql://[host]/[database] </value> </parameter> <parameter> <name>password</name> <value>[TheDatabasePassword]</value> </parameter> <parameter> <name>maxActive</name> <value>50</value> </parameter> <parameter> <name>maxWait</name> <value>5000</value> </parameter> <parameter> <name>driverClassName</name> <value>com.mysql.jdbc.Driver</value> </parameter> <parameter> <name>username</name> <value>[TheDatabaseUserName]</value> </parameter> <parameter> <name>maxIdle</name> <value>10</value> </parameter> </ResourceParams>

This belongs in your Context.

You can then use the following code to pull the DataSource out of the context and get a connection from the pool.

Context ctx = new InitialContext(); if (ctx == null) { throw new Exception("Boom - No Context"); } Context envCtx = (Context) ctx.lookup("java:comp/env");

DataSource ds = (DataSource) envCtx.lookup("jdbc/ECTrack");

Connection conn = ds.getConnection();

From: Amit Wadhwa <amit...@hathway.com>

To: Steve Forsyth <stev...@hotmail.com>, ja...@lists.mysql.com Subject: RE: Connection Pooling built into Tomcat? Date: Thu, 19 Feb 2004 07:11:58 +0530

hi, i went thru the tomcat docs with the context and stuff and tried to configure. gives me all sorts of errors, even if i copy and paste the same code about jdbc realms and context and resource params and all that stuff into my code My server.xml file does already have some default thingie saying

<!-- <Realm className="org.apache.catalina.realm.JDBCRealm" debug="99" driverName="org.gjt.mm.mysql.Driver"

connectionURL="jdbc:mysql://localhost/authority?user=test;password=test" userTable="users" userNameCol="user_name" userCredCol="user_pass" userRoleTable="user_roles" roleNameCol="role_name" /> -->

and

<Environment name="maxExemptions" type="java.lang.Integer" value="15"/> <Parameter name="context.param.name" value="context.param.value" override="false"/> <Resource name="jdbc/EmployeeAppDb" auth="SERVLET" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/TestDB"> <parameter><name>user</name><value>sa</value></parameter> <parameter><name>password</name><value></value></parameter> <parameter><name>driverClassName</name> <value>org.hsql.jdbcDriver</value></parameter> <parameter><name>driverName</name> <value>jdbc:HypersonicSQL:database</value></parameter> </ResourceParams>

so i know my tomcat supports connection pooling (right?)

would you be able to tell me the config changes to my server.xml AND how to use it in my java source? would be great thanks for the reply, -Amit

-----Original Message----- From: Steve Forsyth [mailto:stev...@hotmail.com] Sent: Thursday, February 19, 2004 6:42 AM To: amit...@hathway.com Subject: RE: Connection Pooling built into Tomcat?

Amit,

The DBCP connection pool comes with more recent versions of Tomcat.... I think 4.x and above. You can setup the connection pool in many ways but one of the ways is to use server.xml in the Tomcat conf directory to start the connection pool and then all you have to do is have your connection been pull it out of the context. You should be able to google your way through the setup process.

From: Amit Wadhwa <amit...@hathway.com>

To: ja...@lists.mysql.com Subject: Connection Pooling built into Tomcat? Date: Thu, 19 Feb 2004 06:24:53 +0530

Hi All, Is there any built in connection pooling for mysql using tomcat 4.0? or do i have to write a seperate connection manager for it?? opening and closing a connection on a site hitting hundreds every minute is making my app hog resources.. -Amit