Re: still not clear with connection pooling in tomcat
Hi I also struggled for the Tomcat Connection Pooling problem for one week and i got the solution. write the follwing code snippets in respective files: -web.xml-- resource-ref res-ref-namejdbc/TestDB/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/res-auth /resource-ref --server.xml--- write the following code in server.xml in Context tag Resource name=jdbc/TestDB auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/TestDB parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valuewriter the driver class which you are using/value /parameter parameter nameurl/name valuewrite the url here/value /parameter parameter nameusername/name value/value /parameter parameter namepassword/name value/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/name value-1/value /parameter parameter nameremoveAbandoned/name valuetrue/value /parameter parameter nameremoveAbandonedTimeout/name value300/value /parameter parameter namelogAbandoned/name valuetrue/value /parameter /ResourceParams and also add the following code in the file in which you are using database code and import javax.naming.* and javax.sql.* packages.. Context ctx=new InitialContext(); DataSource ds=(DataSource)ctx.lookup(java:/comp/env/jdbc/TestDB); Connection con=ds.getConnection(); Statement st = con.createStatement(); Make sure that commons-dbcp,commons-collections and commons-pool files in common/lib directory. I hope that this mail will help you in finding out the solution. regards, -Ajay Kumar On Apr 10, 2005 1:20 AM, Krishnakant Mane [EMAIL PROTECTED] wrote: thanks a lot doug, if I have any problem I will trouble again. just a couple of questions. there were two parameters in the email you sent the other day. I did not get the meaning of those parameters stating that active connections should be 100 and the other with 30 as the value. what is the difference between max active and inactive connections? and just a curious question, is tomcat really used on heavy duty commertial websites? thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- Ajay - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
hello Mr. Ajay Kumar, thanks a lot. I am using tomcat 5.0.25 and I have the context file in the conf/Catalina/localhost directory. in that file where should I put the code u provided in server.xml after the tag is over or before the tag is closed? thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
thanks a lot doug, if I have any problem I will trouble again. just a couple of questions. there were two parameters in the email you sent the other day. I did not get the meaning of those parameters stating that active connections should be 100 and the other with 30 as the value. what is the difference between max active and inactive connections? and just a curious question, is tomcat really used on heavy duty commertial websites? thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
thanks doug. I got my application right. only one problem remains. where do I initialise the connection. and what should I do in the init method of the servlet. or is it that I wont need to use the init method at all? thanks again Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
Since the Conn method is static you do not need to initialize it. The DBUtil class actually handles all that for you. Just send your query string to the method there and get back the instance of the data results class. No init needed. Doug - Original Message - From: Krishnakant Mane [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org Sent: Friday, April 08, 2005 5:06 AM Subject: Re: still not clear with connection pooling in tomcat thanks doug. I got my application right. only one problem remains. where do I initialise the connection. and what should I do in the init method of the servlet. or is it that I wont need to use the init method at all? thanks again Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
never mind doug, in fact it seams that your asking questions reflects nothing but professionalism and perfection. that's the way to do it. by the way the relation to databases is one to one with web apps. one web app is going to refer to only one database. so that is the way it is going to be. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
No problem. With a one to one, set up the dataresource in the context element of the app. Place this after your context element. You will need to either declare one in the server.xml, in your app war file or create a file yourapp.xml and place it in the Tomcat dir: tomcat_home/conf/Catalina/localhost or the matching host for the app. Resource name=jdbc/TestDB auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/TestDB parameter namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namemaxActive/name value100/value /parameter parameter namemaxIdle/name value30/value /parameter parameter namemaxWait/name value1/value /parameter parameter nameusername/name valuejavauser/value /parameter parameter namepassword/name valuejavadude/value /parameter parameter namedriverClassName/name valuecom.mysql.jdbc.Driver/value /parameter parameter nameurl/name valuejdbc:mysql://localhost:3306/javatest?autoReconnect=true/value /parameter /ResourceParams Make sure the resource name= resourceParams name= and the name called out in the class I sent in the last email all match including case. Set the url to match the database name in place of javatest. And use the user and password you created for the database. I create a user in the database and limit the rights to only the one databse and with limited rights. Add this to the web.xml for the app: resource-ref descriptionDB Connection/description res-ref-namejdbc/TestDB/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/res-auth /resource-ref Again match the res-ref-name to the name used above for the resource. And that should get things talking if you have set up all the database and users. The above examples are from: http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html Good luck. - Original Message - From: Krishnakant Mane [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org Sent: Wednesday, April 06, 2005 8:08 AM Subject: Re: still not clear with connection pooling in tomcat never mind doug, in fact it seams that your asking questions reflects nothing but professionalism and perfection. that's the way to do it. by the way the relation to databases is one to one with web apps. one web app is going to refer to only one database. so that is the way it is going to be. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
hello doug, I will be using this tomcat server for many web applications. and yes the mysql instance will be the same. I just run one mysqld instance for my server. there will be different databases and different web applications. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
I hate to keep asking questions, but I want to make sure to give you the
correct setup. Other wise you will have to change it later on.
Let's say you have three databases:
data1
data2
data3
And three apps:
app1
app2
app3
If app1 hits data1
and app2 hits data2
and .
You are on a one to one app to database.
If app1 hit data1
and app2 hits data1
and app3 hits data3
and app4 hits data2
You are many to one and one to one.
If you have
app1 hits data1
and app1 hits data2
then you have a special case.
For one to ones you can create you data resource in the context element for
the app as no other app will need access to the database.
For many to one you will want to put your resource in the server.xml as a
Globalrsource and links in the context for each app that will access that
database.
You can have more than one Globalresource.
And here are some class examples for using the datasource.
Servlet for getting connection.
***
package yourPackage;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
public class Conn {
/**Takes desired database as a string and returns a connection.
*/
public static Connection getConn(String dBase) {
Connection connection = null;
String osName = System.getProperty(os.name);
try {
//Start of Tomcat connect
Context ctx = new InitialContext();
if (ctx == null) {
System.err.println(Conn.getConn ctx is null);
throw new Exception(Boom - No Context);
DataSource ds = (DataSource) ctx.lookup(java:comp/env/jdbc/ + dBase);
if (ds != null)
connection = ds.getConnection();
//End of Tomcat connect
} catch (Exception e) {
System.err.println(Conn.getConn + e);
}
return connection;
}
}
Class to make db calls
package yourPackage;
import java.sql.*;
public class DBUtil {
/** Retrieves results from query as a DBResults class.
*/
public static DBResults getQueryResults(String query, String dBase) {
Connection connection = Conn.getConn(dBase);
Statement statement = null;
ResultSet resultSet = null;
DBResults dbResults = null;
boolean good = false;
try {
DatabaseMetaData dbMetaData = connection.getMetaData();
String productName = dbMetaData.getDatabaseProductName();
String productVersion = dbMetaData.getDatabaseProductVersion();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
ResultSetMetaData resultsMetaData = resultSet.getMetaData();
int columnCount = resultsMetaData.getColumnCount();
String[] columnNames = new String[columnCount];
// Column index starts at 1 (a la SQL) not 0 (a la Java).
for (int i = 1; i columnCount + 1; i++) {
columnNames[i - 1] = resultsMetaData.getColumnName(i).trim();
}
dbResults =
new DBResults(
connection,
productName,
productVersion,
columnCount,
columnNames);
while (resultSet.next()) {
String[] row = new String[columnCount];
// Again, ResultSet index starts at 1, not 0.
for (int i = 1; i columnCount + 1; i++) {
String entry = resultSet.getString(i);
if (entry != null) {
entry = entry.trim();
}
row[i - 1] = entry;
}
dbResults.addRow(row);
}
good = true;
} catch (SQLException sqle) {
System.err.println(Error connecting: + sqle);
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
System.err.println(DataBaseUtilities Error closing resultset: + e);
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.err.println(DataBaseUtilities Error closing statement: + e);
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.err.println(DataBaseUtilities Error closing connection: + e);
}
connection = null;
}
if (good)
return (dbResults);
else
return (null);
}
}
/** Runs update query.
*/
public static void setUpdate(String query, String dBase) {
Connection connection = Conn.getConn(dBase);
Statement statement = null;
boolean good = false;
try {
statement = connection.createStatement();
statement.executeUpdate(query);
} catch (SQLException sqle) {
System.err.println(Error connecting: + sqle);
} finally {
// Always make sure statements are closed,
// and the connection is returned to the pool
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.err.println(DataBaseUtilities Error closing statement: + e);
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.err.println(DataBaseUtilities Error closing connection: + e);
}
connection = null;
}
}
}
}
And to handle the data from the resultset that would otherwise be
unaccessable it is places in an DBResults object.
package yourPackage;
import java.sql.*;
import java.util.*;
/** Class to store completed results of a JDBC Query.
* Differs from a ResultSet in several
Re: still not clear with connection pooling in tomcat
hello doug, I am sending the following details on the questions you asked. and thanks for your help. I am using mysql as the database back-end and tomcat version 5. I have the mysql jdbc the latest stable version. and the server is right now going to support a single application but I plan to serve more apps in a few days. Please help me with the info I need. I will be obliged if u could give an example. I am again thankfull to you as you exactly understood my problem and the confusion I faced with the articles on the net. Please provide a servlet based example not a jsp one. thanks again Krishnakant Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
Because there are different versions, and the setup is different, I need exact version of Tomcat. What was the file name you downloaded? Was it a 5.0.xx or 5.5.x for they are different? Doug - Original Message - From: Krishnakant Mane [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org Sent: Monday, April 04, 2005 2:16 AM Subject: Re: still not clear with connection pooling in tomcat hello doug, I am sending the following details on the questions you asked. and thanks for your help. I am using mysql as the database back-end and tomcat version 5. I have the mysql jdbc the latest stable version. and the server is right now going to support a single application but I plan to serve more apps in a few days. Please help me with the info I need. I will be obliged if u could give an example. I am again thankfull to you as you exactly understood my problem and the confusion I faced with the articles on the net. Please provide a servlet based example not a jsp one. thanks again Krishnakant Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
doug, thanks again for ur response. Well it is not 5.5. It is 5.0.xx Thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
Will more than one of the apps be using the same database? That is the same tables and data? Even if they are using the same database engine IE MySQL on the server, will it be the same database instance? Doug - Original Message - From: Krishnakant Mane [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org Sent: Monday, April 04, 2005 7:25 AM Subject: Re: still not clear with connection pooling in tomcat doug, thanks again for ur response. Well it is not 5.5. It is 5.0.xx Thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: still not clear with connection pooling in tomcat
hello all, Thanks for your help on this issue. however I still can't get my problem solved. 10 different articles give 10 different solutions. however my problem is simple. I have a streight forward question. I want to implement connection pooling in the tomcat 5 web container and I have the jakarta commons packages as well as the mysql jdbc jar file in the proper place. I found one good example in the tomcat 5 docs, but it talks about an example in jsp. I exclusively use servlets and I want to know how I use connection pooling in the servlet. where do I use the Initial context and where do I initialise it? where will the datasource come into question? and how I use the connection object with the datasource? all this I want to know in context of a servlet not jsp. Please help me and please no more references to other documents I have read enough of those articles and I am getting more and more confused. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: still not clear with connection pooling in tomcat
In the beginning a programmer created ... Seriously it goes like this: To use pooling in Tomcat you can forget a lot of what is in the java api as Tomcat uses this to provide pooling for you. Steps to take: 1.Determine the database you are going to use. 2.Get the JDBC drivers for that database and put them in common/lib 3.Determine if the database pool is going to serve one application or many. 4.If many, set up a Global Resource in server.xml 5.If many, add a resource link to the context for each app 4a. If one, set up a resource in the context. 5a.If one, do nothing. 6.Create a class that makes a call to retrieve the resource from the context. 7.From that resource a datasource is received. 8.Ask for a connection from the datasource. 9.Tomcat will give you a connection from the pool it creates when it sets up the resource. 10.Use this connection as you would one from a datamanager. 11.Close the connection when you are done and Tomcat will put it back in the pool. 12.DO NOT call DataManager as you will bypass the connection pool. Now after reading this, to help us give you more help please fill in the details above on 1,2 and 3. Give us the exact version of Tomcat. And OS version. Doug - Original Message - From: Krishnakant Mane [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org Sent: Sunday, April 03, 2005 8:47 AM Subject: RE: still not clear with connection pooling in tomcat hello all, Thanks for your help on this issue. however I still can't get my problem solved. 10 different articles give 10 different solutions. however my problem is simple. I have a streight forward question. I want to implement connection pooling in the tomcat 5 web container and I have the jakarta commons packages as well as the mysql jdbc jar file in the proper place. I found one good example in the tomcat 5 docs, but it talks about an example in jsp. I exclusively use servlets and I want to know how I use connection pooling in the servlet. where do I use the Initial context and where do I initialise it? where will the datasource come into question? and how I use the connection object with the datasource? all this I want to know in context of a servlet not jsp. Please help me and please no more references to other documents I have read enough of those articles and I am getting more and more confused. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: still not clear with connection pooling in tomcat
everything you need is here: http://jakarta.apache.org/tomcat/tomcat-5.5-doc/jndi-datasource-examples-howto.html -Original Message- From: Krishnakant Mane [mailto:[EMAIL PROTECTED] Sent: Sunday, April 03, 2005 7:05 AM To: tomcat-user@jakarta.apache.org Subject: still not clear with connection pooling in tomcat hello all, I am still a bit confused on the pooling issue in tomcat. I will be very happy if some one could explain connection pooling with reference to my problems listed below. 1. there are the jakarta commons libraries like the dbcp etc in the catalina/common/lib directory. how do I take advantage of these libraries to use pooled connections? Wat I mean is that I found some resources on google which did explain connection pooling but those articles tryed to use some other methods. I know how to setup the tags in server.xml but don't know what to do in a servlet and where to do it after the commons jars are made available. 2. there were two tags in server.xml out of which one is for the maximum pool of connections eg 100 500 etc. and the other tag is related to innactive connections. can some one explain? I am confused with these two tags. 3. right now I am not using connection pooling and I am opening connections in the init methods of my servlets. what changes should I make in my servlets after a connection pool is established? 4. where should I initialise the context and lookup the connection from the data source? I will be happy if some one gives an example of just the code snippad of using a pooled connection in a servlet. thanks Krishnakant. Send instant messages to your online friends http://uk.messenger.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
