Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
Message-Id: <5.1.1.6.0.20030527112554.024fcec8@10.0.0.1>
Date: Tue, 27 May 2003 11:31:32 +0100
To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
From: William Wragg <william@datapro.co.uk>
Subject: RE: DB Connection
In-Reply-To: <FOEAIBGFFGDFGCLKAEFKKEEGCMAA.jan@diekleinedomain.de>
References: <C19D2E05B25FA94E943431666541E5B71C8C83@server01.hh.uptime.de>
Mime-Version: 1.0
Content-Type: multipart/mixed; x-avg-checked=avg-ok-577A4EFC;
 boundary="=======438B5572======="

--=======438B5572=======
Content-Type: text/plain; x-avg-checked=avg-ok-577A4EFC; charset=us-ascii;
 format=flowed
Content-Transfer-Encoding: 8bit

Hi,

 > -----Original Message-----
 > At 11:07 27/05/2003, you wrote:
 >
 > Thanks for the help. It looks like I have to redesign my db layer :( It 
would
 > be really helpfull to have someone comment on what I have done so far and am
 > planing to do now. So far I used a specific class to build a "where-clause",
 > connect to the db, execute my query and store the ResultSet within the
 > instance of that class. The "common" ResultSet would be READ_ONLY and I used
 > the data within it after the connection has been closed. I understand from
 > earlier replies that closing the Connection without closing the ResultSet
 > doesn't work in terms of connection pooling either.
 > This leads me to the conclusion, that I have to copy the extracted data into
 > an Array for further processing. Is that a reasonable approach or is there a
 > better practice?
 >
 > Again, any help & comments would be very much appreciated!
 >
 > Jan


This is what I do, I use an ArrayList. To get you started here is a simple 
RowSet object (basically a simplified ResultSet). There are many ways of 
doing this, using dyna beans etc... but this should get you started:

package com.db;

import java.util.*;
import java.sql.*;

// PUBLIC METHODS:
//   void setRowSet(ResultSet rs)
//   void addToRowSet(ResultSet rs)
//   void clear()
//   ArrayList getRecord(int record), ArrayList getRecord()
//   String getField (int record, int field), String getField (int field)
//   int getRecordCount()
//   int getFieldCount()
//   int getCurrentRecord()
//   void setCurrentRecord(int currentRecord)
//   boolean next()
//   boolean previous()
//   void first()
//   void last()
//
// The record pointer is set to before the first record so that a next() is 
required
// to move the pointer to the first record.
// The pointer will move after the last record to show that the last record 
has been
// found.
// The above are the same as for a ResultSet, and allow easy looping 
through all the
// records with a while() statement.
//
// e.g.
//
//   RowSet aRowSet = new RowSet();
//   aRowSet.setRowSet(... give a ResultSet ...);
//   while (aRowSet.next()) {
//     ... Do things with the current record
//   }
//
public class RowSet {

   ArrayList list = new ArrayList();
   int recordCount = 0;
   int fieldCount = 0;
   int currentRecord = 0;

   // setRowSet(ResultSet rs)
   // use a result set to populate a list. Each record is added
   // to the list as a seperate list. Each field is added as a string.
   //
   public void setRowSet (ResultSet rs) throws SQLException {
     clear();
     ResultSetMetaData rsmd = rs.getMetaData();
     fieldCount = rsmd.getColumnCount();

     while (rs.next()) {
       ArrayList listRecord = new ArrayList();
       for (int num = 1; num <= fieldCount; num++) {
         listRecord.add(rs.getString(num));
       }
       list.add(listRecord);
     }

     recordCount = list.size();
   }


   // addToRowSet(ResultSet rs):
   // use a result set to populate an already populated list. Each record 
is added
   // to the list as a seperate list and must ahve the same number of 
fields, as the
   // records already added to the list. Each field is added as a string.
   //
   public void addToRowSet (ResultSet rs) throws SQLException {
     ResultSetMetaData rsmd = rs.getMetaData();
     if (recordCount == 0 ) {
       fieldCount = rsmd.getColumnCount();
     }

     if (rsmd.getColumnCount() == fieldCount) {
       while (rs.next()) {
         ArrayList listRecord = new ArrayList();
         for (int num = 1; num <= fieldCount; num++) {
           listRecord.add(rs.getString(num));
         }
         list.add(listRecord);
       }

       recordCount = list.size();
     }
   }


   // clear():
   // Clear the list and reset the fieldCount, recordCount and currentRecord
   //
   public void clear () {
     list.clear();
     recordCount = 0;
     fieldCount = 0;
     currentRecord = 0;
   }


   // getRecord(int record), getRecord():
   // record indexed from 1. Returns the record specified by record as an 
ArrayList
   //
   public ArrayList getRecord (int record) {
     if (record > recordCount) {
       record = recordCount;
     } else if (record < 1) {
       record = 1;
     }

     if (recordCount != 0 ) {
       return (ArrayList)list.get(record - 1);
     } else {
       return new ArrayList();
     }
   }


   // getRecord(int record), getRecord():
   // using currentRecord. Returns the record specified by the 
currentRecord as an ArrayList.
   //
   public ArrayList getRecord () {
     if (recordCount != 0 && currentRecord >= 1 && currentRecord <= 
recordCount) {
       return (ArrayList)list.get(currentRecord - 1);
     } else {
       return new ArrayList();
     }
   }


   // getField (int record, int field), getField (int field):
   // record indexed from 1, field indexed from 1. Returns the field 
specified by field
   // and record as a String.
   //
   public String getField (int record, int field) {
     if (record > recordCount) {
       record = recordCount;
     } else if (record < 1) {
       record = 1;
     }

     if (field > fieldCount) {
       field = fieldCount;
     } else if (field < 1) {
       field = 1;
     }

     if (recordCount != 0 ) {
       return (String)((ArrayList)list.get(record - 1)).get(field - 1);
     } else {
       return new String();
     }
   }


   // getField (int record, int field), getField (int field):
   // using currentRecord and field indexed from 1. Returns the field 
specified by field
   // and currentRecord as a String.
   //
   public String getField (int field) {
     if (field > fieldCount) {
       field = fieldCount;
     } else if (field < 1) {
       field = 1;
     }

     if (recordCount != 0 && currentRecord >= 1 && currentRecord <= 
recordCount) {
       return (String)((ArrayList)list.get(currentRecord - 1)).get(field - 1);
     } else {
       return new String();
     }
   }


   // getRecordCount():
   // Return the value of the total number of records as an integer.
   //
   public int getRecordCount () {
     return recordCount;
   }


   // getFieldCount():
   // Return the value of the total number of fields as an integer.
   //
   public int getFieldCount () {
     return fieldCount;
   }


   // getCurrentRecord():
   // indexed from 1 to recordCount. Return the index for the
   // current record as an integer.
   //
   public int getCurrentRecord () {
     return currentRecord;
   }


   // setCurrentRecord(int currentRecord):
   // indexed from 1 to recordCount. Set the index for the
   // current record.
   //
   public void setCurrentRecord (int currentRecord) {
     if (recordCount != 0) {
       if (currentRecord > (recordCount + 1)) {
         this.currentRecord = recordCount + 1;
       } else if (currentRecord < 0) {
         this.currentRecord = 0;
       } else {
         this.currentRecord = currentRecord;
       }
     }
   }


   // next():
   // Move to the next record by incrementing the currentRecord.
   // The current record index will go no higher than the recordCount + 1.
   // Returns true until currentRecord is after last record.
   //
   public boolean next () {
     if (recordCount != 0) {
       if(currentRecord < recordCount) {
         currentRecord++;
         return true;
       } else if (currentRecord == recordCount) {
         currentRecord++;
         return false;
       }
     }

     return false;
   }


   // previous():
   // Move to the previous record by decrementing the currentRecord.
   // The current record index will go no lower than 0.
   // Returns true until currentRecord is after first record.
   //
   public boolean previous () {
     if (recordCount != 0) {
       if(currentRecord > 1) {
         currentRecord--;
         return true;
       } else if (currentRecord == 1) {
         currentRecord--;
         return false;
       }
     }

     return false;
   }


   // first():
   // Move to the first record.
   //
   public void first () {
     if(recordCount != 0) {
       currentRecord = 1;
     }
   }


   // last():
   // Move to the last record.
   //
   public void last () {
     if (recordCount != 0) {
       currentRecord = recordCount;
     }
   }
}

--=======438B5572=======
Content-Type: text/plain; charset=us-ascii; x-avg=cert;
 x-avg-checked=avg-ok-577A4EFC
Content-Disposition: inline


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.483 / Virus Database: 279 - Release Date: 19/05/2003


--=======438B5572=======
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
--=======438B5572=======--