Retriving Data in a tabular form

[Patrick Mugabe]

Ranchers!
I have a very small database which I am using to learn more about JDBC.
I am able to retrieve data but I would want it to come out in a tabular form. How can I do this.
Please can someone assist, probably with the aid of a simple example.
Thanks

[Nagendra Prasad]

By saying 'Tabular Form', i assume u mean display the result set u have obtained in a tabular form. For this u would need to use either servletsor JSP... and as such would be a question for those forums.
The result set u have obtained can be transformed (for the lack of a better word).. into a vector.
The servlet/JSP can iterate thru this vector and generate the relevant HTML to display like a table.

[Joe Ess]

Is 100 lines a simple example? This class gives you the framework of a GUI application. It displays a table and fills it with the results of a query. All the query code is commented out in the interest of showing a working example first. You should be able to make the indicated changes and get it to work with your database.

 
import java.util.*;
import java.awt.*;
import javax.swing.*;
import javax.swing.table.*;
/*
JDBCTest displays a GUI window with rows and columns 
*/
public class JDBCTest extends JFrame{
   
   DefaultTableModel dtm;
   
   public JDBCTest(){
      super("JDBCTest");            
      
   // initalize your JDBC driver with something like: 
      //new oracle.jdbc.driver.OracleDriver();
      
      dtm = new DefaultTableModel();
      JTable aTable = new JTable(dtm);
      JScrollPane jsp = new JScrollPane(aTable);
      this.getContentPane().add(jsp, BorderLayout.CENTER);
      
      this.setSize(200,200);
      this.show();                  
   }
   
   public static void main(String args[]){
      JDBCTest test = new JDBCTest();
      test.performQuery();
   }
   
   
// display the results of a db query   
   public void performQuery(){
      Vector colNames = new Vector();
      Vector data = new Vector();      
      try{
   // do your query here          
      //Connection conn = DriverManager.getConnection(some database settings);
      //Statement stmt = conn.createStatement();
      //ResultSet rs = stmt.executeQuery(sql);
   
   // get the colCount and column names from ResultSetMetaData
      //ResultSetMetaData rsmd = rs.getMetaData();
      //int colCount = rsmd.getColumnCount();
      int colCount = 4; // comment this out if you are using the column count from rsmd
      for (int i = 1; i <= colCount; i++){   
         colNames.add("column "+i); // replace with colNames.add(rsmd.getColumnName(i))
      }
            
   // get the data from the result set 
      for (int i = 1; i <= 15; i++){ // replace for loop with while(rs.next())
         Vector v = new Vector();
         for (int j = 1; j <= colCount; j++){
            v.add(""+i+","+j); // replace with v.add(rs.getObject(i))
         }
         data.add(v);
      }
      
   // update the JTable's Data Model   
      dtm.setDataVector(data, colNames);  
      } catch (Exception e){
         e.printStackTrace();
      } finally {
         // uncomment to clean up your database connection
         /*
         if (rs != null){
            System.out.println("Closing ResultSet");
            try{rs.close();}
            catch(Exception e){}
            rs = null;
         }    
         if (stmt != null){
            System.out.println("Closing Statement");
            try{stmt.close();}
            catch(Exception e){}
            stmt = null;
         }
         if (conn != null){
            System.out.println("Closing Connection");
            try{conn.close();}
            catch(Exception e){}
            conn = null;
         }    
         */
      }
   }   
}

[Joe Ess]

Woah. You will probably want to move the declarations of Connection, Statement and ResultSet to before the try block and set them to null. That's what I get for writing commented-out code.

[Patrick Mugabe]

Thanks so much, I will try it out tomorrow and let you know how it goes.

[Patrick Mugabe]

Hi,
The solution seems to work but I am having a problem on displaying the actual data from the table I am referencing.
It's coming from this section of code:
// get the data from the result set
for (int i = 1; i <= 15; i++){
// replace for loop with while(rs.next())
Vector v = new Vector();
for (int j = 1; j <= colCount; j++){
//v.add(""+i+","+j); // replace with v.add(rs.getObject(i))
v.add(rs.getObject(i))
}
data.add(v);
}
If I replace the for with a while loop, what then comes as an argument in:
v.add(rs.getObject(i)).
I am convinced this will work, I am just missing one simple thing.
Please assist

[Joe Ess]

The code to iterate through the result set should look like this (I had the loop variable "j" wrong, don't forget to either hardcode the colCount or get it out of the ResultSetMetaData):

 
      // get the data from the result set
      while (rs.next()){
         Vector v = new Vector();
         for (int j = 1; j <= colCount; j++){
            v.add(rs.getObject(j)
         }
         data.add(v);