regarding connection pool

[Rajeev Ravindran]

hi,
im using TOmcat 4.0 with MS-Access as backend..
i tried a conenction pool class found on www.javaexchange.com? ..
it is a servlet which makes the pools,so i invokes this servlet on "<load-on-startup/>" and it works fine...but i have a situation, can i call this servlet instace from a bean..i mean , im using beans for database manipulation and so needs the Connection in the bean..i think one method is to pass the Connection object which i get in the servelt to the constuctor of the bean..is it a good approach ??? does any1 have any other suggestions ???
thnaks,
raj

[Peter den Haan]

I must say that, based on the materials on the site, I am not impressed by DBConnectionBroker. Today's JDBC API has a standard way of acquiring connections which can be pooled or even part of a distributed transaction: the DataSource. It is standardised, making your application much more portable, and also easier to use: you give a connection back to the pool simply by close()ing it like you do with a normal connection.
So does this answer your question? It does, actually, because your question now becomes: where do I get my DataSource from? There are a number of alternatives.

The J2EE way is to ask your application server to set up a DataSource for you. Every app server should be able to do this. This has two advantages: (a) it's the least amount of work and (b) assigning a database to the application becomes a deployment task. You can use JNDI to look up this DataSource anywhere in your application.

You can use a connection pool that exposes a DataSource interface, such as Apache Jakarta DBCP. This gives you complete control over how you instantiate it, where you bind it, and so forth.

Some JDBC drivers come with a connection pool implementation. Of course this makes you driver-dependent, but often that's OK.

Finally, you may sometimes still find that you need a Connection or DataSource in a layer of your application that doesn't have easy access to it. You can pass a Connection as a simple method argument

public void doWork(Connection conn) {
   PreparedStatement stmt = conn.prepareStatement(WORK_SQL);
   try {
      // ... blah ...
   } finally {
      stmt.close();
   }
}

Alternatively, if you need to make a number of method calls as part of a transaction and want to avoid the Connection cluttering up all your methods, you can use an instance variable

public class FooBarDAO {
   private final Connection conn;
   FooBarDAO(Connection conn) {
      super();
      this.conn = conn;
   }
   public void doWork() {
      PreparedStatement stmt = conn.prepareStatement(WORK_SQL);
      try {
         // ... blah ...
      } finally {
         stmt.close();
      }
   }
}

An instance of this FooBarDAO would typically be created as part of an ongoing transaction, and forgotten about once the transaction is complete.
You would treat a DataSource a bit differently. A DataSource is just a factory for Connection objects and your code needs just a single DataSource that it uses whenever it needs to access the database. You'd use a DataSource if you want the work to be done in an independent transaction (please note that this is not true at all in a managed environment such as an EJB container). You could conceivably use a static variable somewhere to store the DataSource used by your application, but I must admit I don't really like that much; just look the thing up in JNDI at initialisation time if you can.
Does that help?
- Peter

[Rajeev Ravindran]

hi peter,
thanks for ur expert advice..i truly appretiate ur instant response..
peter, does any latest version of Tomcat server has default Connection pool facility ?? as far as i know, Tomcat 4.0, doesnt support Connection pool facility.. if the server handles the connection pool as in Weblogic , its the better option right ??? here my company is sticking on Tomcat, so i shld find an better alternative using Tomcat itself..once again thanks for reply...
Thanks,
raj.

[Peter den Haan]

Originally posted by r rajeev:
peter, does any latest version of Tomcat server has default Connection pool facility ?? as far as i know, Tomcat 4.0, doesnt support Connection pool facility..

Raj, did you follow the links in my reply above? The second one outlines step by step how to set up a DataSource in Tomcat 4 and bind it in JNDI...
- Peter

[Rajeev Ravindran]

hi peter,
sorry, my mistake..i checked the 1st and 3rd url, but missed the 2nd one..yep, the 2nd url is the one i was looking for... THANKS
raj..

[Rajeev Ravindran]

hi peter,
i need ur help again..okey, im working on the connection pool tutorial provided by jakarte..your 2nd url..
Tomcat DBCP
now my prob is my DBTest.java is not getting comliled, its throwing 3 errors.
i wll copy the o/p of my compiler

 
--------------- Compiler Output -------------
Tomcat\webapps\DBTest\WEB-INF\classes\foo\DBTest.java:4: package javax.sql does not exist
import javax.sql.*;
^
Tomcat\webapps\DBTest\WEB-INF\classes\foo\DBTest.java:18: cannot resolve symbol
symbol  : class DataSource  
location: class foo.DBTest
      DataSource ds = 
      ^
Tomcat\webapps\DBTest\WEB-INF\classes\foo\DBTest.java:19: cannot resolve symbol
symbol  : class DataSource  
location: class foo.DBTest
            (DataSource)ctx.lookup(
 

i have kept all the jar files in proper place..cld u tell me the name of the jar files which have the DataSouce class and javax.sql.* package ?? from where can i download it ?? i extracted all the jar files in my system to check if the DataSouce class exists in any of them, i found one DataSouce class in activation.jar, but i think that is not the exact jar file which i shld use here coz
getConnection();
method is not there in that Class and it throws another error..
hope my prob is clear for u. plz help..
thanks,
raj

[Rajeev Ravindran]

sorry sorry, i got the file from somewhere and copiled the file....huh i dont have patience...i wll get back to u if i find any more problems..
raj

[Peter den Haan]

Originally posted by r rajeev:
now my prob is my DBTest.java is not getting comliled, its throwing 3 errors.

If you're still using JDK 1.3, then you need the JDBC Optional Package (as you found out already). JDK 1.4 and above include this as part of the core platform.
- Peter

[Rajeev Ravindran]

hi peter,
im still facing problems..this time is with configuring server.xml ..its throwing errors
i have posted my query in Tomcat forum , plz look at it
Configuring DBCP ??

Thanks for ur replies,
raj