Collectors of arrays

[Jacek Kempski <jk...@jacekkempski.com>]

Hello,

i must be making a mistake somewhere. I try to pass to a template a model of a
JDBC ResultSet. This is a vector. The template should then iterate through the
elements of vector which are arrays of strings representing data rows. Then i have
to retrieve single fields from the array. So far it didn't work and i had to
remodel the whole thing from two dimensions to one dimension, which works, but i
don't like it. Could somebody please give me the hint ?

Thanks,

jack

jk@jacekkempski.com
jacek@camline.com

Re: Collectors of arrays

[Jacek Kempski <jk...@jacekkempski.com>]

Hi,

Sometimes You run into troubles with metadata i.e. when You select DECODE,
TRANSLATE or some other stuff. The workaround would be to use AS, but then You
use a typical presentation utility (column header changed for easy reading) for
iteration controlling. I just would like to be able to get deeper into the
collector structure with usual, Java typical methods, not with qualifying things
by their names.

Thanks for advice anyway,

jack

jk@jacekkempski.com
jacek@camline.com

Fedor Karpelevitch schrieb:

> Any problem with accessing columns by number?
> And, of course,  you can use ResultSetMetadata (granted that it works).
>
> Fedor.
>
> On Tuesday 15 May 2001 20:32, you wrote:
> > Thanks Geir,
> >
> > the problem is: the query is dynamically generated through some XML service
> > names resolving, so that I do not know in advance how many columns i get
> > and, more important , what the names of the columns are. Thus i can't
> > access directly the member of the collector by its name. But i'll take a
> > very close look at Your approach today, maybe i'll find a workaround for
> > that issue.
> >
> > jack
> > jk@jacekkempski.com
> > jacek@camline.com
> >
> > "Geir Magnusson Jr." schrieb:
> > > Jacek Kempski wrote:
> > > > Hello,
> > > >
> > > > i must be making a mistake somewhere. I try to pass to a template a
> > > > model of a JDBC ResultSet. This is a vector. The template should then
> > > > iterate through the elements of vector which are arrays of strings
> > > > representing data rows. Then i have to retrieve single fields from the
> > > > array. So far it didn't work
> > >
> > > What didn't work?  That sounds fine until the end bit.
> > >
> > > I would have used a HashMap for each row, so you could access data by
> > > field name.
> > >
> > > > and i had to remodel the whole thing from
> > > > two dimensions to one dimension, which works, but i don't like it.
> > > > Could somebody please give me the hint ?
> > >
> > > I think I understand - you can just use Maps for the rows, rather than
> > > strings.
> > >
> > > Then you can do things like (modulo checking for nulls and other good
> > > things - I am just making this up as I go along...)
> > >
> > > in code :
> > >
> > > <do your SQL select>
> > >
> > > ArrayList rowset = new ArrayList();
> > >
> > > while(rs.next() )
> > > {
> > >   HashMap hm = new HashMap();
> > >
> > >   hm.put("name", rs.getString("name") );
> > >   hm.put("address", rs.getString("address") );
> > >
> > >   rowset.add( hm );
> > > }
> > >
> > > context.put("rowset", rowset );
> > >
> > > and in template
> > >
> > > #foreach( $row in $rowset )
> > >   <tr>
> > >     <td>$row.name</td><td>$row.address</td>
> > >   </tr>
> > > #end
> > >
> > > A sexier way is to automate this using the ResultSetMetadata :)
> > >
> > > geir
> > >
> > > --
> > > Geir Magnusson Jr.                           geirm@optonline.net
> > > System and Software Consulting
> > > Developing for the web?  See http://jakarta.apache.org/velocity/
> > > "still climbing up to the shoulders..."
>
> ----------------------------------------
> Content-Type: text/html; charset="us-ascii"; name="Attachment: 1"
> Content-Transfer-Encoding: 7bit
> Content-Description:
> ----------------------------------------

Re: Collectors of arrays

[Fedor Karpelevitch <fe...@home.com>]

Any problem with accessing columns by number?
And, of course,  you can use ResultSetMetadata (granted that it works).

Fedor.

On Tuesday 15 May 2001 20:32, you wrote:
> Thanks Geir,
>
> the problem is: the query is dynamically generated through some XML service
> names resolving, so that I do not know in advance how many columns i get
> and, more important , what the names of the columns are. Thus i can't
> access directly the member of the collector by its name. But i'll take a
> very close look at Your approach today, maybe i'll find a workaround for
> that issue.
>
> jack
> jk@jacekkempski.com
> jacek@camline.com
>
> "Geir Magnusson Jr." schrieb:
> > Jacek Kempski wrote:
> > > Hello,
> > >
> > > i must be making a mistake somewhere. I try to pass to a template a
> > > model of a JDBC ResultSet. This is a vector. The template should then
> > > iterate through the elements of vector which are arrays of strings
> > > representing data rows. Then i have to retrieve single fields from the
> > > array. So far it didn't work
> >
> > What didn't work?  That sounds fine until the end bit.
> >
> > I would have used a HashMap for each row, so you could access data by
> > field name.
> >
> > > and i had to remodel the whole thing from
> > > two dimensions to one dimension, which works, but i don't like it.
> > > Could somebody please give me the hint ?
> >
> > I think I understand - you can just use Maps for the rows, rather than
> > strings.
> >
> > Then you can do things like (modulo checking for nulls and other good
> > things - I am just making this up as I go along...)
> >
> > in code :
> >
> > <do your SQL select>
> >
> > ArrayList rowset = new ArrayList();
> >
> > while(rs.next() )
> > {
> >   HashMap hm = new HashMap();
> >
> >   hm.put("name", rs.getString("name") );
> >   hm.put("address", rs.getString("address") );
> >
> >   rowset.add( hm );
> > }
> >
> > context.put("rowset", rowset );
> >
> > and in template
> >
> > #foreach( $row in $rowset )
> >   <tr>
> >     <td>$row.name</td><td>$row.address</td>
> >   </tr>
> > #end
> >
> > A sexier way is to automate this using the ResultSetMetadata :)
> >
> > geir
> >
> > --
> > Geir Magnusson Jr.                           geirm@optonline.net
> > System and Software Consulting
> > Developing for the web?  See http://jakarta.apache.org/velocity/
> > "still climbing up to the shoulders..."

----------------------------------------
Content-Type: text/html; charset="us-ascii"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 
----------------------------------------

Re: Collectors of arrays

[Joaquim Carvalho <jr...@bookmarc.pt>]

I have a similar sitiuation: I do as follows, probably not the best way but
it is working:

I make a vector with the column names.
I make a vector for each row with just the values in the row.
I add everything to a result Vector, so that the first element of the vector
is a vector with the colum names
and the subsequent elements are the rows of results, by the order returned.

The template is then very easy to read because it has a outer loop (the row
loop) and a Inner loop ( the column
loop). I test the first row to display the column names in different colors.

I have limited experience with Java and even less so with velocity, so any
comments
to the code bellow are welcome.

Here is the java code that produces the result (note that I deal with a
limited set of data types)


 /** Processes a SQL query. Returns the results in a vector of vectors. The
first vector contains the column names. Each of the others contain data from
the fetched rows.
       */
 public Vector sqlQuery(String sqltext) throws SQLException {

      Statement sql = this.getConnection().createStatement();
      ResultSet rs = null;
      Vector results=new Vector();

      rs = sql.executeQuery(sqltext);

      ResultSetMetaData metadata = rs.getMetaData();

      int nc = metadata.getColumnCount();

  // store the column names in the first object
  Vector colNames = new Vector();
  for (int i = 1 ; i < nc+1 ; i++ )
   colNames.add(metadata.getColumnName(i));
  results.add(colNames);

      int colType;
      int intValue;
      String stringValue="";
     Vector row = new Vector();
      while( rs.next() ){

      for ( int i = 1; i < nc+1; i++) {

       colType = metadata.getColumnType(i);
       if (  colType == java.sql.Types.INTEGER || colType ==
java.sql.Types.SMALLINT ) {
        intValue = rs.getInt(i);
        if ( ! rs.wasNull() ) row.add(new Integer(intValue));
        else row.add("");
       } else {
        stringValue = rs.getString(i);
        if ( ! rs.wasNull() ) row.add(stringValue);
        else row.add("");
       }
      }
      results.add(row.clone());
      row.clear();
  }
  rs.close();
      sql.close();
  return results;

 }


And here is the template that echoes the results, alternating bgcolor on the
table fow each row.

<TABLE BORDER=0 CELLSPACING=2 CELLPADDING=3 ALIGN=CENTER>
#set ($rownumber = 0)
#set ($colNames = $results.get(0))
#foreach ($row in $results)

   #if ($rownumber == 0)
    #set ($rowcolor="#000055")
 #else
       #set ($oddeven = $rownumber % 2)
   #if ($oddeven == 0) #set ($rowcolor = "#bae4ff") #else #set ($rowcolor =
"WHITE") #end
 #end
 <TR BGCOLOR="$rowcolor">
 #set ($colnumber = 0)
 #foreach ($col in $row)
           <TD VALIGN="TOP">
    $col&nbsp;
            </TD>
   #set ($colnumber = $colnumber + 1)
 #end
 </TR>
 #set($rownumber = $rownumber + 1)
#end
</TABLE>

----- Original Message -----
From: Jacek Kempski
To: velocity-user@jakarta.apache.org
Sent: Wednesday, May 16, 2001 4:32 AM
Subject: Re: Collectors of arrays


Thanks Geir,
the problem is: the query is dynamically generated through some XML service
names resolving, so that I do not know in advance how many columns i get
and, more important , what the names of the columns are. Thus i can't access
directly the member of the collector by its name. But i'll take a very close
look at Your approach today, maybe i'll find a workaround for that issue.

Re: Collectors of arrays

[Jacek Kempski <jk...@jacekkempski.com>]

Thanks Geir,

the problem is: the query is dynamically generated through some XML service
names resolving, so that I do not know in advance how many columns i get
and, more important , what the names of the columns are. Thus i can't access
directly the member of the collector by its name. But i'll take a very close
look at Your approach today, maybe i'll find a workaround for that issue.

jack
jk@jacekkempski.com
jacek@camline.com

"Geir Magnusson Jr." schrieb:

> Jacek Kempski wrote:
> >
> > Hello,
> >
> > i must be making a mistake somewhere. I try to pass to a template a
> > model of a JDBC ResultSet. This is a vector. The template should then
> > iterate through the elements of vector which are arrays of strings
> > representing data rows. Then i have to retrieve single fields from the
> > array. So far it didn't work
>
> What didn't work?  That sounds fine until the end bit.
>
> I would have used a HashMap for each row, so you could access data by
> field name.
>
> > and i had to remodel the whole thing from
> > two dimensions to one dimension, which works, but i don't like it.
> > Could somebody please give me the hint ?
>
> I think I understand - you can just use Maps for the rows, rather than
> strings.
>
> Then you can do things like (modulo checking for nulls and other good
> things - I am just making this up as I go along...)
>
> in code :
>
> <do your SQL select>
>
> ArrayList rowset = new ArrayList();
>
> while(rs.next() )
> {
>   HashMap hm = new HashMap();
>
>   hm.put("name", rs.getString("name") );
>   hm.put("address", rs.getString("address") );
>
>   rowset.add( hm );
> }
>
> context.put("rowset", rowset );
>
> and in template
>
> #foreach( $row in $rowset )
>   <tr>
>     <td>$row.name</td><td>$row.address</td>
>   </tr>
> #end
>
> A sexier way is to automate this using the ResultSetMetadata :)
>
> geir
>
> --
> Geir Magnusson Jr.                           geirm@optonline.net
> System and Software Consulting
> Developing for the web?  See http://jakarta.apache.org/velocity/
> "still climbing up to the shoulders..."

Re: Collectors of arrays

["Geir Magnusson Jr." <ge...@optonline.net>]

Jacek Kempski wrote:
> 
> Hello,
> 
> i must be making a mistake somewhere. I try to pass to a template a
> model of a JDBC ResultSet. This is a vector. The template should then
> iterate through the elements of vector which are arrays of strings
> representing data rows. Then i have to retrieve single fields from the
> array. So far it didn't work 

What didn't work?  That sounds fine until the end bit.

I would have used a HashMap for each row, so you could access data by
field name.

> and i had to remodel the whole thing from
> two dimensions to one dimension, which works, but i don't like it.
> Could somebody please give me the hint ?

I think I understand - you can just use Maps for the rows, rather than
strings.

Then you can do things like (modulo checking for nulls and other good
things - I am just making this up as I go along...)

in code :

<do your SQL select>

ArrayList rowset = new ArrayList();

while(rs.next() )
{
  HashMap hm = new HashMap();

  hm.put("name", rs.getString("name") );
  hm.put("address", rs.getString("address") );
 
  rowset.add( hm );
}

context.put("rowset", rowset );

and in template
   
#foreach( $row in $rowset )
  <tr>
    <td>$row.name</td><td>$row.address</td>
  </tr>
#end


A sexier way is to automate this using the ResultSetMetadata :)

geir

-- 
Geir Magnusson Jr.                           geirm@optonline.net
System and Software Consulting
Developing for the web?  See http://jakarta.apache.org/velocity/
"still climbing up to the shoulders..."