JAVA-DBCP-UNIX Experts

[Saeed <sa...@yahoo.com>]

Dear JAVA-DBCP-UNIX Experts,
  I'm my all-life Cobol guy. I have recently started working on Java side. I apologize if I use improper java terminologies/terms. Currently I'm trying to bring Oracle data in my Cobol programs through Java objects on SCO UNIX. 
    
   Firstly, I have written set of test (cobol/java) programs using jdbc to access oracle db, which is working perfectly. I've read accessing oracle db through jdbc call, which creates new connection for each user is time consuming and costly.    
   So finally, I modified & implemented my test bed with DBCP "share pool" of open connections. And this approach is also working fine, accessing database in my Cobol program successfully.
   
  ******************MY ENVIRONMENT******************
  I'm running Cobol & Java programs (using JDK 1.4) on SCO UNIX, trying to access Oracle db located on Windows server. MOST IMPORTANTLY, THERE IS NOT ANY KIND OF JAVA APPLICATION SERVER or WEB SERVER on my platform. That means I'm using COBOL/JAVA/DBCP (pooling concept) in standalone mode under UNIX.
  ******************MY ENVIRONMENT******************
   
  Questions -
  1. How can I know this connection pooling concept is working on my Unix environment? It looks like the program is creating connections on each user's call to db?
  2. I need a strategy (if possible some utility) to confirm connection pooling in my environment? how can i test this?
  3. Since I'm not using any kind of application & web server, is this thing fits under standalone platform like the one i described above? Am I using in a right way?
  4. When do you think I should call shutdownDataSource method?
   
  Your great help will be appreciated.
  Best regards, Sayeed
   
  my config file ---
  <properties>
 <entry key="driverClassName">oracle.jdbc.driver.OracleDriver</entry>
 <entry key="username">usernm</entry>
 <entry key="password">usrpswd</entry>
 <entry key="url">jdbc:oracle:thin:@host:1521:dbsid</entry>
 <entry key="maxActive">50</entry>
 <entry key="initialSize">5</entry>
 <entry key="maxIdle">1</entry>
 <entry key="maxWait">3000</entry>
 <entry key="testOnBorrow">true</entry>
 <entry key="validationQuery">select * from dual</entry> 
</properties>
   
  my java file ...
   
  import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
  import javax.sql.DataSource;
  import org.apache.commons.dbcp.BasicDataSource;
import org.jdom.Attribute;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.xml.sax.helpers.DefaultHandler;
  public class DataSourceTest {
    // DefaultHandler contain no-op implementations for all SAX events.
  // This class should override methods to capture the events of interest.
  static class MyHandler extends DefaultHandler {
  }
    public static void main(String[] args) throws IOException, Exception {
     // First we set up the BasicDataSource.
      // Normally this would be handled auto-magically by
      // an external configuration, but in this example we'll
      // do it manually.
      //
      System.out.println("Setting up data source.");
      DataSource dataSource = getDataSource();
      System.out.println("Done.");
      
      System.out.println("Print Data Source Statistics.");
      printDataSourceStats(dataSource);
      //
      // Now, we can use JDBC DataSource as we normally would.
      //
      Connection conn = null;
      Statement stmt = null;
      ResultSet rset = null;
        try {
          System.out.println("Creating connection.");
          conn = dataSource.getConnection();
          System.out.println("Creating statement.");
          stmt = conn.createStatement();
          System.out.println("Executing statement.");
         // rset = stmt.executeQuery(args[1]);
          rset = stmt.executeQuery("SELECT desc from tabdesc");
          System.out.println("Results:");
          int numcols = rset.getMetaData().getColumnCount();
          while(rset.next()) {
              for(int i=1;i<=numcols;i++) {
                  System.out.print("\t" + rset.getString(i));
              }
              System.out.println("");
          }
          System.out.println("Print Data Source Statistics after open connection.");
          printDataSourceStats(dataSource);
      } catch(SQLException e) {
          e.printStackTrace();
      } finally {
          try { rset.close(); } catch(Exception e) { }
          try { stmt.close(); } catch(Exception e) { }
          try { conn.close(); } catch(Exception e) { }
      }
      System.out.println("Print Data Source Statistics after closing connection.");
      printDataSourceStats(dataSource);
      System.out.println("Print Data Source Statistics after closing data source.");
      shutdownDataSource(dataSource);
      printDataSourceStats(dataSource);
      
  }
    public static DataSource getDataSource()throws IOException, Exception{
      BasicDataSource bds = new BasicDataSource();
      Properties datasourceProperties=new Properties();
      DefaultHandler handler = new MyHandler();
      parseConfigFile(datasourceProperties, "datasource-config.xml", handler, false);
      bds.setDriverClassName(datasourceProperties.getProperty("driverClassName"));
      bds.setUsername(datasourceProperties.getProperty("username"));
      bds.setPassword(datasourceProperties.getProperty("password"));
      bds.setUrl(datasourceProperties.getProperty("url"));
      bds.setMaxActive(new Integer(datasourceProperties.getProperty("maxActive")).intValue());
      bds.setInitialSize(new Integer(datasourceProperties.getProperty("initialSize")).intValue());
      bds.setMaxIdle(new Integer(datasourceProperties.getProperty("maxIdle")).intValue());
      bds.setMaxWait(new Integer(datasourceProperties.getProperty("maxWait")).intValue());
      bds.setTestOnBorrow(new Boolean(datasourceProperties.getProperty("testOnBorrow")).booleanValue());
      bds.setValidationQuery(datasourceProperties.getProperty("validationQuery"));
      System.out.println("MAXActive: " + bds.getMaxActive());
      System.out.println("MAXIdle: " + bds.getMaxIdle());
        System.out.println("NumActive: " + bds.getNumActive());
      System.out.println("NumIdle: " + bds.getNumIdle());
      return bds;
  }
  public static void parseConfigFile(Properties datasourceProperties, String filename, DefaultHandler handler, boolean validating) throws JDOMException, IOException {
   Document incomDoc =null;
   SAXBuilder builder=null;
   List childList=null; 
    Element propItem=null;
    String propItemKey=null;
    String propItemVal=null;
 Attribute attribute = null;
      try {
       builder = new SAXBuilder(false);
       incomDoc = builder.build(filename);
    
    Element rootElement = incomDoc.getRootElement();
    childList = rootElement.getChildren();
      for (Iterator i = childList.iterator();i.hasNext();) {
          propItem  = (Element)i.next();
          attribute=propItem.getAttribute("key");
    propItemKey = attribute.getValue();
       propItemVal=propItem.getText();
       datasourceProperties.setProperty(propItemKey, propItemVal);             
             }
      } catch(Exception e)
   {
   e.printStackTrace();
   }
  }
    public static void printDataSourceStats(DataSource ds) throws SQLException {
      BasicDataSource bds = (BasicDataSource) ds;
      System.out.println("NumActive: " + bds.getNumActive());
      System.out.println("NumIdle: " + bds.getNumIdle());
  }
    public static void shutdownDataSource(DataSource ds) throws SQLException {
      BasicDataSource bds = (BasicDataSource) ds;
      bds.close();
  }
}

   

       
---------------------------------
Ahhh...imagining that irresistible "new car" smell?
 Check outnew cars at Yahoo! Autos.