commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz" <phil.ste...@gmail.com>
Subject Re: JAVA-DBCP-UNIX Experts
Date Fri, 11 May 2007 04:44:22 GMT
On 5/8/07, Saeed <sayeed_4932@yahoo.com> wrote:
> 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?

Looking at your config below, this is most likely because you have
maxIdle set to 1.  That means the pool will only allow one connection
to be idle at a time. This will cause lots of connection churn,
because each time a connection is returned to the pool, if there is
another one idle, the returned connection will be closed.  In general,
it is better to set maxIdle to a higher number.  The best setting
depends on load patterns, etc.  The default is 8.  The pool is really
an "idle object pool" when you get down to it, so setting maxIdle to 1
is really setting the pool size to 1.

>   2. I need a strategy (if possible some utility) to confirm connection pool in my environment?
how can i test this?

Well, you can examine output of getNumActive and getNumIdle while
testing / debugging.  These will tell you respectively how many
connections are checked out and how many are idle at a given time.
You can also monitor the database or look at the database logs.  Be
careful leaving these diagnostics in for production, because the
getNumXxx methods are synchronized so can be bad for performance.

>   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?

It is fine to use it standalone.  Code looks OK.

>   4. When do you think I should call shutdownDataSource method?

I think you mean close, and you should call it when your application shuts down.

Phil
>
>   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.

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message