HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Servlets & JDBC"
Volume: 5 Issue: 4, Page 102

	

Listing 1: Connecting to the Database 

Connection newCon = null;
try{
  Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
  newCon  = DriverManager.getConnection( "jdbc:odbc:nformant200", "username", "password" );
 }catch(Exception E){
  System.out.println( "[jdbcServlet.service():" + E + "]" );
  _res.setStatus( HttpServletResponse.SC_NO_CONTENT );
  return;
}

Listing 2: Running the Query

Statement Statmt;
ResultSet Res;
PrintWriter Out = new PrintWriter( _res.getOutputStream() );

try{
  Statmt = newCon.createStatement();
  Res    = Statmt.executeQuery( "SELECT * FROM USER_TABLE" );

  ResultSetMetaData RM = Res.getMetaData();
  while (Res.next()){
    String columnString = "";
    for ( int x=0; x < RM.getColumnCount(); x++ ){
      columnString += Res.getString(x+1) + " ";
    }
        
    Out.println( columnString );
  }

  Res.close();
  Statmt.close();
  newCon.close();
}catch(SQLException E){
  System.out.println( "[jdbcServlet.service():" + E + "]" );
  _res.setStatus( HttpServletResponse.SC_NO_CONTENT );
  return;
}

Listing 3: Setting Up the Connection Pool

public class dbBroker. {

  private static dbBroker Broker = null;
 
  public synchronized static void getInstance(){
    if ( Broker == null )
      Broker = new dbBroker();
  }
}


Listing 4: Setting Up the Wrapper Class for a Connection

class dbConnection extends Object {
  public Connection Con;
  public boolean    bActive;
  public long       timeTaken;
  public long       averageTime;
  public long       maxTime;
  public int        hitRate;

  public dbConnection( Connection _Con ){
    Con     = _Con;
    bActive = false;
    timeTaken   = 0;
    averageTime = 0;
    hitRate     = -1;
    maxTime     = -1;
  }

  public void setInActive(){
    bActive = false;
    long t = System.currentTimeMillis() - timeTaken;
    if ( t < 120000 )
      averageTime += t;

    timeTaken   = 0;
    if ( t > maxTime )
      maxTime = t;
  }

  public void setActive(){
    bActive = true;
    timeTaken   = System.currentTimeMillis();
    hitRate++;
  }

  public long getAverage(){
    if ( hitRate == 0 ) return 0;

 return averageTime/(long)hitRate;
  }

  public String toString(){
    return "[Hit: " + hitRate + "] [Avg.: " + getAverage() + "] 
            [Use: " + bActive + "] [Max: " + maxTime + "]";
  }
}

Listing 5: Creating the Instance of dbBroker

private dbBroker(){
  Properties INI = new Properties();
  try{
    INI.load( new FileInputStream("dbbroker.ini") );
    dbDriver  = INI.getProperty( "driver" );
    dbName    = INI.getProperty( "database" );
    dbUser    = INI.getProperty( "username" );
    dbPassword= INI.getProperty( "password" );
    noCon     = Integer.parseInt(INI.getProperty("connections"));
  } catch (Exception E){
    System.out.println( "[dbBroker:" + E + "]" );
    System.out.println( "[dbBroker: Please ensure you have the following fields: " 
 ); System.out.println(
  "[dbBroker: driver =  
  
    " ); System.out.println( "[dbBroker:
    database  =   
    "     ); System.out.println( "[dbBroker:
    username    =   
    " ); System.out.println( "[dbBroker:
    password     = 
     
          
             " );
    System.out.println( "[dbBroker: connections= 
     " );System.out.println( "[dbBroker:
    in a filenamed dbbroker.ini]"
    ); } dbList= 
    new Vector(); //--Attempt to
    open the database connections Connection Con; for (
  int

  x  =  

        0;
  x <
  noCon; x++ ){ Con= 
             
       
 openConnection(); if
  (
  Con ! = 
   
   
  null )  dbList.addElement( new dbConnection(Con)   );

  }
    }
	    
Listing 6: Opening  the Connection to the Database     
             
 private
Connection openConnection(){
Connection newCon = null; try{ Class.forName( dbDriver

);
  newCon = DriverManager.getConnection(
  dbName,     dbUser, dbPassword ); }catch(Exception E){ System.out.println(

  "[dbBroker.openConnection():" +  E
  + "]"
    ); newCon = 
      null; }System.out.println( "[dbBroker.openConnection(): Success " ); return
      newCon; }    
    
        
 Listing 7: Getting a Connection   
  
      public
    static      Connection
    pop(){  synchronized(
    Broker    ){
    dbConnection  dbCon;
    for      (;;){
    dbCon     = 
  

    Broker.getFreeConnection();
    if  (
    dbCon ! =   
        null )
      break; if (

    dbCon   = 
         
       = 
  

  null && Broker.dbList.size()
    ! = 0
    ){    try{
    Broker.wait();
  }catch(Exception

  E){} } }
    if ( Broker.dbList.size() ==    

  0 ){ System.out.println( "[dbBroker.pop: No

free connections"
  ); return  null; }else{
  dbCon.setActive();
    return dbCon.Con; } }
    }  
	   

Listing 8:  giving the Connection Back 
     
          
        public static void push( Connection _Con ){
    if ( Broker= 
      = 
    null || _Con= 
      = null
    ) return; synchronized(Broker){ //--
    Need to check the validity of the connection
  dbConnection

  dbCon  =  

        
  Broker.getConnection( _Con
  ); if ( dbCon=     
     = 
     null ) return; //-- Check
      the status of the
  connection
try{

dbCon.Con.commit(); dbCon.Con.clearWarnings(); }catch(Exception E){ Broker.closeConnection( dbCon.Con ); }

if ( Broker.isClosed(dbCon.Con)
  ){ dbCon.Con = 
  
    Broker.openConnection(); if (
    dbCon.Con  =     
   
          
     = 
  
     null ){
  System.out.println( "[dbBroker.push:
Failed


to reopen a dead connection]"

); Broker.dbList.removeElement( dbCon );
  return; } }else{
    dbCon.setInActive(); }
    Broker.notifyAll(); }
      } 
 
     
    </TT> 
       
      </PRE> 
       
       
   <PRE>     
      
         
<TT>       
     
   Listing 9:Checking the Connections   
        
         public
    void

    run(){ int debugCount =  
           
       0;
  for (;;){ debugCount++;  if ( debugCount%30 ==   

   
           0 ){
    Enumeration E = dbList.elements(); dbConnection dbCon;
    while (E.hasMoreElements()){ dbCon =    

          
    
      (dbConnection)E.nextElement();
      System.out.println(
    "[dbBroker.run(): "
      + dbCon.toString() );
    }

    } try{ Thread.currentThread().sleep( 60000
      );   }catch(Exception
      E1){} } } 
	  
	  
   Listing 10: Setting  Up the Connection Pool  
             
public void service( HttpServletRequest _req, HttpServletResponse _res) 
throws ServletException, IOException{
  dbBroker.getInstance();
  Connection newCon = dbBroker.pop();
  Statement Statmt;
  ResultSet Res;
  PrintWriter Out = new PrintWriter( _res.getOutputStream()   );

  try{
    Statmt = newCon.createStatement();
    Res    = Statmt.executeQuery( "SELECT * FROM USER_TABLE" );

    ResultSetMetaData RM = Res.getMetaData();

    while (Res.next()){
      String columnString = "";

      for ( int x=0; x < RM.getColumnCount(); x++ ){
        columnString += Res.getString(x+1) + " ";
      }

      Out.println( columnString );
    }

    Res.close();
    Statmt.close();
    dbBroker.push( newCon );
  }catch(SQLException E){
    System.out.println( "[jdbcServlet.service():" + E + "]" );
    _res.setStatus( HttpServletResponse.SC_NO_CONTENT );
    return;
  }

  Out.flush();
}



 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.