Re: Java code help...

There may be a mildly happy medium between accessing ResultSets by column
names foregoing speed and accessing ResultSets by column indexes losing
flexibility to weather DB column order changes.

If for a query, you know the columns that you have to use and the order in
which they will come up, store this mapping information in a text file,
initialize an integer array once and use the array for looking up columns.
Now, you have access by index slowed down only by the amount of time per
integer array lookup.
Reading the INI file should usually be a onetime task per program invocation
and the integer array is acccessible across multiple DB calls of the same
query.  In the DB call use  the appropriate index to get columns that are
needed. Whenver the DB table changes the INI file changes and has to be
re-read. But code does not change and you still get the performance you
need, with the possible overhead of the array lookup. Note that the data
structure is an integer array and not a HashMap or something that uses
Strings/Objects for keys.

This approach may be overkill for queries like
SELECT E.EMPNUM, E.EMPNAME, S.SALAMT
FROM EMP E, SALARY S
WHERE E.EMPNAME LIKE 'JOHN%' AND E.EMPNUM=S.EMPNUM
in which the columns AND their order are specified in the query used: You
can straight away use indexes here.

A variant of this approach can be used to minimize the impact of column name
changes.

HTH.

Regards,
Bharath.


----- Original Message -----
From: "Fred Kulack" <kulack@us.ibm.com>
To: <java400-l@midrange.com>
Sent: Friday, November 16, 2001 2:43 PM
Subject: RE: Java code help...


>
> On 11/16/2001 at 11:30:14 AM, java400-l-admin@midrange.com wrote:
> Sorry, but I do not agree with you,
> your solution is not portable. If the DB is created on another RDB the
> columns position may change and the "encapsulated" object will return the
> wrong data...
> --- end of excerpt ---
>
> Everything is always a compromise, and you
> can always do anything more than one way.
>
> Good design represents both portability and performance.
> That said, I think this solution is both portable and
> higher performing.
>
> I think the main points of Rich's posts are:
> 1) You pay extra expense from using column names in the
>    ResultSet, that you can portably eliminate using
>    encapsulation.
> 2) You should encapsulate your application from the format of your
>    DATA. This usually means encapsulating it from the column positions
>    (AS WELL AS) the column names, the tables, the schemas, and the
>    JDBC objects used to access them.
>    Using an appropriate level of abstraction prevents app changes
>    due to data model from effecting your application.
> 3) #1 and #2 can also help in the fact that if you "select *"
>    and don't use EVERY single column, you are then wasting
>    storage, CPU cycles, and Java object.
>
> This doesn't mean you can't rely on the names in
> some places and still get the benefits of the
> name == portability idea.
>
> I believe this was Rich's point also.
>
>
> class DataGetter {
>  public static int COL_FLOG  = 1;
>  public static int COL_A     = 2;
>  public static int COL_DEAD  = 3;
>  public static int COL_HORSE = 4;
>
>  ResultSet rs;
>
>  public String getData() {
>       ...
>       // Note, construct the query so that regardless of
>       // table layout/format/DB, we get the columns back
>       // in an architected/portable order (i.e. PUBLIC
>       // interfaces (including the column numbers above don't change)
>       rs = executeQuery("SELECT FLOG, A, DEAD, HORSE FROM BLAH");
>  }
>
>  .. Other methods left out ...
>
>  public getFlog() {
>     return rs.getString(COL_FLOG);
>  }
>  public getA() {
>     return rs.getString(COL_A);
>  }
>  public getDead() {
>     return rs.getString(COL_DEAD);
>  }
>  public getHorse() {
>     return rs.getString(COL_HORSE);
>  }
> }
>
>
>
>
>
> In every single ethnic, religious or racial group, there are a
> very few truly evil people. For each of those people there
> are many, many, many good people.
> Assuming anything (evilness or capability for evil) about the
> particular group is bigotry and idiocy. Don't do it.  -- Me
>
> Fred A. Kulack  -  AS/400e  Java and Java DB2 access, Jdbc, JTA, etc...
> IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
> mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
> AOL Instant Messenger: Home:FKulack  Work:FKulackWrk
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>