• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2373
  • Last Modified:

ResultSet into vector

Hello,
In this code below, i am trying to store all the the values in resultSet into a vector.
But for some reason only the first data in the tables i am selecting from are being
stored( I know this by printing the items in the vectors.)
Why this behavior?

============ CODE =================
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import java.util.*;

/**
 *
 * @author  Agbeko Komla
 */
public class Refresh implements ActionListener

{
    private StudentPanel studentFields;
    private Connection connection ;
   
    /** Creates a new instance of Refresh */
    public Refresh(Connection con, StudentPanel s)
    {
        connection = con;
        studentFields = s;
    }
   
    public void actionPerformed(ActionEvent e)
    {
        Statement s = null;
        ResultSet lastRs = null;
        ResultSet firstRs  = null;
        Vector lastNames = new Vector();
        Vector firstNames = new Vector();
       
       try
       {
                s = connection.createStatement();
                lastRs = s.executeQuery("SELECT [Last Name] FROM Student");
       
                if( lastRs.next() )
                lastNames =  getNextRow(lastRs, lastRs.getMetaData()) ;
           
           
          s.close();

              s = connection.createStatement();
              firstRs = s.executeQuery("SELECT [First Name] FROM Student");
              if( firstRs.next() )
               firstNames = getNextRow(firstRs, firstRs.getMetaData()) ;
                    // System.out.println("first: " + firstRs.getString(1) );

       
       studentFields.initVectorOfNames(lastNames, firstNames);
       //firstRs.close();
      // firstRs.close();
       s.close();
      System.out.println( printaList(lastNames) );
      System.out.println( printaList(firstNames) );
       }
       catch (SQLException sqlex)
       {
           sqlex.printStackTrace();
       }
}
    public Vector getNextRow(ResultSet rs, ResultSetMetaData meta)
   throws SQLException
{
    Vector currentRow = new Vector();
    for(int i = 1; i <= meta.getColumnCount(); i++)
   
        switch(meta.getColumnType(i) )
        {
            case Types.DOUBLE:
            {
                double x = rs.getDouble(i);
                int u = (int)x;
               
                currentRow.addElement( u + "" );
                 break;
            }
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                currentRow.addElement(rs.getString(i) );
                break;
            case Types.INTEGER:
                currentRow.addElement( new Long( rs.getLong( i )) );
                break;
           case Types.DATE:
                currentRow.addElement( rs.getDate( i ) );
                break;
            case Types.TIME:
                 currentRow.addElement(rs.getTime( i ) );
                 break;
        case Types.TIMESTAMP:
             currentRow.addElement(rs.getTimestamp( i ) );
                break;
        case Types.OTHER:
        case Types.JAVA_OBJECT:
             currentRow.addElement(rs.getObject( i ) );
              break;
        }
   
        return currentRow;
}
      public String printaList(java.util.List l)
    {
        String s= "";
        for(int i = 0; i < l.size(); i++)
        s +=  (String)l.get(i)+ " ";
        return s;
    }
}
0
komlaaa
Asked:
komlaaa
1 Solution
 
LeviterCommented:
Okay, here goes.......

You do this:

                lastRs = s.executeQuery("SELECT [Last Name] FROM Student");
       
                if( lastRs.next() )
                lastNames =  getNextRow(lastRs, lastRs.getMetaData()) ;

So lastRs will contain a resultset with multiple records. You however do not loop over all the records, but check if there is one and if so, you get that one. Then you do nothing anymore with the lastRS. The same is done for the firstRs.

Another thing is, that you return a new Vector each time you call the getNextRow method and assign it to the variable lastNames (in this case). So even if you loop through all the records in the resultset, you will only get one.

What you should do, is at least loop through the resultset.... something like this (not tested):

                lastRs = s.executeQuery("SELECT [Last Name] FROM Student");

                while (lastRs.next()) {
                      Vector lastName =  getNextRow(lastRs, lastRs.getMetaData()) ;
                      lastNames.add(lastName);
                }

The method getNextRow returns a new Vector containing multiple elements (if the metadata contains more then one element). So what you get in the above example is a Vector containing Vectors.

This would be the 'quick' way to get it working.....

There is however a 'nicer' way. You should get the first and last name with only one query:

               select [last name], [first name] from student

Loop over this and create a new class called something like 'Student' which has two members named 'lastName' and 'firstName'. Loop over the resultset and create a new instance of the 'Student' class for each record and set the members. This instance should be added to a Vector of students. That way you get a more clear and better insight....

Look at this (incomplete example):


       try
       {
                s = connection.createStatement();
                rs = s.executeQuery("SELECT [Last Name], [First Name] FROM Student");

               Vector students = new Vector();
       
                while( rs.next() ) {
                           Student student = new Student();

                           student.setLastName(rs.getString("Last Name"));
                           student.setFirstName(rs.getString("First Name"));

                          students.add(student);
               }

               s.close();
 
               System.out.println( printaList(students) );
       }
       catch (SQLException sqlex)
       {
           sqlex.printStackTrace();
       }
0
 
CEHJCommented:
You can use this convenience method:



 public TableModel resultSetToTableModel(ResultSet rs) {
          try {
               ResultSetMetaData metaData = rs.getMetaData();
               int numberOfColumns = metaData.getColumnCount();
               Vector columnNames = new Vector();
               // Get the column names
               for (int column = 0; column < numberOfColumns; column++) {
                    columnNames.addElement(metaData.getColumnLabel(column + 1));
               }
               // Get all rows.
               Vector rows = new Vector();
               while (rs.next()) {
                    Vector newRow = new Vector();
                    for (int i = 1; i <= numberOfColumns; i++) {
                         newRow.addElement(rs.getObject(i));
                    }
                    rows.addElement(newRow);
               }
               return new DefaultTableModel(rows, columnNames);
          }
          catch (Exception e) {
               e.printStackTrace();
               return null;
          }
     }
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now