DBCP & DBUtils

[Mike Zatko]

Hello,
   I have a few questions/problems with DBCP and the DBUtils. Hopefully 
a few you have some pointers.

Anyway, here is my dilemna:

1. DBCP - I am going through the examples called 
ManualPoolingDriverExample.java version 1.5. There are two methods in it 
called printDriverStats() and shutdownDriver(). The former uses what 
appears to be a protected method called getConnectionPool(String) in the 
Class PoolingDriver.

   public static void printDriverStats() throws Exception {
       PoolingDriver driver = (PoolingDriver) 
DriverManager.getDriver("jdbc:apache:commons:dbcp:");
       System.out.println("driver: " + driver.toString());
       ObjectPool connectionPool = driver.getConnectionPool("example");
      
       System.out.println("NumActive: " + connectionPool.getNumActive());
       System.out.println("NumIdle: " + connectionPool.getNumIdle());
   }

The latter has a similar problem with the PoolingDriver Class except 
instead of being protected, eclipse tells me its undefined. The method 
is closePool(String).

   public static void shutdownDriver() throws Exception {
       PoolingDriver driver = (PoolingDriver) 
DriverManager.getDriver("jdbc:apache:commons:dbcp:");
          //driver = null;
       driver.closePool("example");
   }

What should I do? I've commented these methods out to get the app to 
run, but this bugs me. Also, I should be able to explicitly close the 
Pool correct? I've spent a good deal of time looking at the api docs, 
but couldn't really find anything that could help me. Forgive me, for I 
am a newbie to the Commons.

2. DBUtils - These sound real neat and I'd really like to use them, but 
I can't find any decent documentation to get a beginner started. The 
example page (http://jakarta.apache.org/commons/dbutils/examples.html) 
may be enough to remind a developer how things work, but it's rough for 
me because it leaves alot of questions and guessing.

I've used the JOCLPoolingDriverExample.java from DBCP as a basis for my 
playing with the Utils. Following the directions on the example page, I 
wasn't able to get it to work. Nor do I really understand the way its 
supposed to work.

I gather the QueryRunner is designed to execute an sql query and give 
you an object you define instead of a ResultSet. The example nor the 
apis really tell you what the ResultSetHandler is doing. And what kind 
of bean are you supposed to setup? I mean, if you do a SELECT * on some 
table you can get alot of rows back, but with the QueryRunner you only 
get a single object back. And I'm guessing you define that object 
yourself. But, is that bean supposed to represent a row of the table 
returned from your query? If it is, how do you access all of the rows. 
Obviously, I'm confused and couldn't find anything to help. The only way 
I got it to work was as follows:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class JOCLPoolingDriverExample {

   public static void main(String[] args) {

       Connection conn = null;
       Statement stmt = null;
       ResultSet rset = null;
       try {
           System.out.println("Creating connection.");
           conn = 
DriverManager.getConnection("jdbc:apache:commons:dbcp:/db");
          
           QueryRunner runner = new QueryRunner();
           ResultSetHandler h = new BeanHandler(OrderBean.class) {
               public Object handle(ResultSet rs) throws SQLException {
                  
                   //OrderBean orderBean = new OrderBean();
                   ArrayList orders = new ArrayList();
                  
                   while(rs.next())
                   {
                       OrderBean orderBean = new OrderBean();
                       orderBean.setOrderId(rs.getString(1));
                       orderBean.setTotalAmount(rs.getString(2));
                       orders.add(orderBean);
                   }

                   return orders;
               }
           };
          
           ArrayList p =
               (ArrayList)runner.query(conn, "SELECT ORDER_ID, 
TOTAL_AMT FROM ORDERS", h);
          
           Iterator iter = p.iterator();
           while(iter.hasNext())
           {
               System.out.println((OrderBean)iter.next());
           }
       } catch(SQLException e) {
           e.printStackTrace();
       } finally {
           try { rset.close(); } catch(Exception e) { }
           try { stmt.close(); } catch(Exception e) { }
           try { conn.close(); } catch(Exception e) { }
       }
   }
}

I doubt this is at all correct as its really not doing anything for me. 
It's just as much work. From the example, it infers that the 
ResultSetHandler class will work some magic in dumping your query into 
an Object. Can anyone help?

Thanks,
Mike Zatko
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]