Passing Database query results through a method to a JTable

[Brennen smith]

I am in the process of writing an application and am stumped on the best way to accomplish a task.

I have two classes one handles the Database (Connection and Query) the other class handles the GUI (Swing based) which includes a JTable. I can connect to the Database. but am having trouble figuring out how to send the results through a method so that they can be added to the JTable. What do I return through the method to get the results I am looking for?

I am using this to connect my GUI class to the Database class

DatabaseConnection database = new DatabaseConnection();

This is the query method from the Database class. My issues iare the JTable and JScrollPane are not recognized because they are part of the other class. Also, Much of this code was taken from a previous program I wrote that was not object oriented, the entire program was in one class.

public void Query(){ 
      
      
      try{
                //SQL query to send to database
                String query = "[QUERY HERE]";
                
                //invoke and store query results
                resultSet = statement.executeQuery(query);

                //invoke and store query metadata results
                metadata = resultSet.getMetaData(); 

                //store number of columns in queried database table
                numcols = metadata.getColumnCount(); 

                //create string array to hold column names
                 String[] col = new String[numcols];

                //read column names into string array
                for(int count = 0; count < numcols; count++)
                {			
                        col[count] = metadata.getColumnLabel(count + 1);
                }   			

                //create table model to display query results			
                DefaultTableModel dtm_search_model = new DefaultTableModel(null,col);

                //display resultSet(stored query data) in DefaultTable Model(JTable)						
                while (resultSet.next())
           {
               Object [] rowData = new Object[numcols];
               for (int i = 0; i < rowData.length; ++i)
               {
                   rowData[i] = resultSet.getObject(i+1);
               }
               dtm_search_model.addRow(rowData);

               //create a table to view search results
               JTable1 = new JTable(dtm_search_model);
               JTable1.setFillsViewportHeight(true);
               JScrollPane1.setViewportView(JTable1);

                }
                }
        catch(Exception exception)
        {
                exception.printStackTrace();
        }
     return;
  }

I apologize if this is a stupid question... I am just not sure of the logic to accomplish this.

[Winston Gutkowski]

This is the query method from the Database class. My issues iare the JTable and JScrollPane are not recognized because they are part of the other class.

Right. The method is completely inert.
Personally, I don't write many methods that return void unless they're solely for changing the state of an object (and sometimes not even then). Methods, in order to be useful, should generally take parameters and/or return some sort of value; otherwise, it's simply a lump of code.

In your case, you need to ask yourself: what should a method called query() (and it's query(), not Query()) return?
I don't know about you, but the results of the query sound like a good idea to me, so why not:
public ResultSet query(){...

Now, what are you going to query? The way you've written it, you have to plug a specific String into a variable. It will work, but it's going to be a bit of a hassle if you have to change the program every time you want to execute a different query, so why not just pass it to the method?:
public ResultSet query(String query){...

NOW you have a useful method that you can call from anywhere, viz:
ResultSet people = Database.query("SELECT * from PERSON");

This is the query method from the Database class. My issues iare the JTable and JScrollPane are not recognized because they are part of the other class.

They also have nothing to do with a database query, which could be run from anywhere.

When you're designing methods, you should make sure that they are (a) self-contained, and (b) do one specific thing. In your case, query() should retrieve the results of a supplied query and hand them back to you.

Using your original source code, modified as I suggested, it might look something like:

public ResultSet query(String query){   
   ResultSet results;
   try{
      // the following assumes that you have a method to return
      // the current db Connection
      Statement statement = getConnection().createStatement();
      results = statement.executeQuery(query);
   } catch(Exception e) {
      // NEVER ignore Exceptions. At the very least I usually
      // wrap them in an unchecked Exception, viz:
      throw new RuntimeException(e);
   }
   return results;
}

A bit simpler, no?
However, the job isn't done yet. You also need to convert the ResultSet to a TableModel, and to do that you need the column headings and the row data. OK, write methods to do that:

public static String[] headings(ResultSet resultSet){
   String[] col;
   try {
      ResultSetMetaData metadata = resultSet.getMetaData();
      col = new String[metadata.getColumnCount()];

      for(int count = 0; count < numcols; count++) {             
         col[count] = metadata.getColumnLabel(count + 1);
      }
   } catch(Exception e) {
      throw new RuntimeException(e);
   }  
   return col;
}

public static Object[][] data(ResultSet resultSet){
   // The code below is basically the same as yours; I've just used a
   // List for simplicity.
   List<Object[]> data = new ArrayList<Object[]>();
   try {
      int numcols = resultSet.getMetaData().getColumnCount();

      while (resultSet.next()) {  
         Object [] rowData = new Object[numcols];  
         for (int i = 0; i < rowData.length; ++i) {
            rowData[i] = resultSet.getObject(i+1);  
         }
         data.add(rowData);
      }
   } catch(Exception e) {
      throw new RuntimeException(e);
   }  
   return data.toArray();
}

Do you see what's happening? We're breaking down all the tasks you previously had lumped into one place. Each task does something specific, and doesn't rely on anything outside it. Furthermore, each method is a whole lot simpler. BTW, all of the above should probably be in your Database class.

Now you have the stuff to pull your data into a TableModel (and that should probably be done in your GUI class), viz:

public static TableModel queryAsModel(String query) {
   TableModel dtm_search_model;
   try {
      Database db = new Database(); // or however you do it
      ResultSet resultSet = db.query(query);
      dtm_search_model = new DefaultTableModel(
         Database.data(resultSet),
         Database.headings(resultSet));
   } catch(Exception e) {
      throw new RuntimeException(e);
   }
   return dtm_search_model;
}

And now, plugging it all together, you can do something like:

JTable1 = new JTable(
   queryAsModel("SELECT * from PERSON"));  
JTable1.setFillsViewportHeight(true);  
JScrollPane1.setViewportView(JTable1);

BTW, the above is only ONE way of doing it. There are lots of others, very possibly better than mine; but they all share the same basic technique:
break down the problem into bite-size independent chunks.
My usual rule of thumb is 50 lines or a page of code. If I see a method that's larger than that, I'll usually ask myself: can this be broken down further?
A major exception is Swing code, which has lots of tedious boilerplate; but it IS an exception.

HIH

Winston

[Brennen smith]

Thank you for the thorough response I see you points on the benefits of keeping is small. I guess you really answered my question which was to pass the result set and handle the passing to the table model in the GUI class. I was not sure if i could pass the result set. My object oriented and method technique has a long way to come. This will help a lot.

So, on the subject of using methods that return a value, should my Connect() and Disconnect() Methods return Boolean variables that tell weather the database is connected?



Cheers

[Winston Gutkowski]

So, on the subject of using methods that return a value, should my Connect() and Disconnect() Methods return Boolean variables that tell weather the database is connected?

In principle, there's nothing wrong with returning a boolean to indicate whether a method worked or not (Java is case-sensitive, so boolean and Boolean are not the same thing; and in this case boolean is probably better), but in the case of a method called connect() (by convention, method and field names start with lower-case letters; only Class and Interface names start with capital letters), it might be better to simply return the Connection object.

Assuming that your Database class is a bit like a manager for the things you want to do with a single database (not a bad idea, BTW), what you could do is have a method called isConnected() that checks its current state and returns a boolean exactly as you described. disconnect() could return a boolean to indicate whether it worked or not, but don't forget that if it didn't work, it will almost certainly throw an Exception, and it's often better to just let the standard error handling take care of it (see below). I see no reason why it shouldn't return a boolean anyway though; in fact, if I were writing it, I probably would.

BTW (from above) error handling is a large and complex subject, and the options that Java provides are quite comprehensive; but there is a good maxim that's worth remembering, at least for now:
A program should fail as quickly and noisily as possible.
That means:
1. Never ignore an exception.
2. Never, ever, ever write an empty catch block. Programmers who do should expect nasty things to be done to their private parts.
3. Always print the stacktrace, at the very least.
4. Make the failure message as descriptive as you possibly can. This may involve including some of the values that caused it to occur.

HIH

Winston

[Brennen smith]

Ok so I now understand your code and the idea behind it I have used basically the same concept.

public static Object[][]  data(ResultSet resultSet){

When I declare this class I am assuming i am returning Object[][] because it is a multidimensional array or an array of an array. So my problem now is the list declaration that returns the result set. Seen below.

List<Object[]> data = new ArrayList<Object[]>(); 

When I insert this line into my program I get the error: java.awt.list Does not take parameters I have never used list before. I tried a few ways to resolve the issue but I am getting no where.

[John Jai]

I guess Winston should have used java.util.List and not java.awt.List. Try changing that.

[Brennen smith]

will java.util.list work with arrays?

[Winston Gutkowski]

Brennen smith wrote:Ok so I now understand your code and the idea behind it I have used basically the same concept.

public static Object[][]  data(ResultSet resultSet){

When I declare this class I am assuming i am returning Object[][] because it is a multidimensional array or an array of an array.

Correct. It's also what the DefaultTableModel constructor (or the one I used) requires.

So my problem now is the list declaration that returns the result set.

List<Object[]> data = new ArrayList<Object[]>(); 

When I insert this line into my program I get the error: java.awt.list Does not take parameters I have never used list before. I tried a few ways to resolve the issue but I am getting no where.

John's quite right, I should have specified java.util.List (and it's 'List', not 'list'; you must be careful with this sort of thing because the compiler is not forgiving), which is an interface. java.awt.List is a class.

For future reference, when people refer to a List, they usually mean java.util.List, which can be used anywhere; java.awt.List is ONLY used in GUI code.

But sorry for the confusion.

Winston

[Winston Gutkowski]

Brennen smith wrote:will java.util.list work with arrays?

Depends what you mean, but List.toArray() converts the contents of the List to an array, and returns it.

Winston

[Brennen smith]

List<Object[]> data = new ArrayList<Object[]>(); 

return.data.toArray(); is highlighted with the exception

incompatible types
required java.lang.Object[][]
found: java.lang.Object[]

It almost seems like I need to do something like... ArrayList<Object[][]>(); but that doesn't work.

[Campbell Ritchie]

Sounds like something which would better fit another forum; let’s try databases first.

[Winston Gutkowski]

return.data.toArray(); is highlighted with the exception
incompatible types
required java.lang.Object[][]
found: java.lang.Object[]

Again, my apologies; I should have put:
return (Object[][]) data.toArray();
Not quite sure why the method doesn't do it itself, because an Object[][] is, in fact, what it returns; but it doesn't, so you have to put in an explicit cast.

Winston

PS: That doesn't work either. Will get back as soon as I have a solution.

[Winston Gutkowski]

Winston Gutkowski wrote:PS: That doesn't work either. Will get back as soon as I have a solution.

Here it is:
return data.toArray(new Object[0][0]);
Once again, my apologies. It's been a while since I did anything like that.

Winston

[Brennen smith]

Yeah that makes sense and no need to apologies you have given me more help than i ever expected. It seems to work I am just workign through a few minor issues connecting the database. I never have had to focus on OO programming so the calling and passing give me trouble. I am learning though. THanks

[Brennen smith]

dtm_search_model = new DefaultTableModel( db.data(resultSet),db.headings(resultSet));

I am getting a Null pointer exception. I believe it is because these values are pulled from methods so it sees some thing like...

dtm_search_model = new DefaultTableModel( null,null);

This goes back to my particular weakness with passing values. I have been reading up. Does this mean that the values being returned are not valid. or is the issue in this line of code?

public static String[] headings(ResultSet resultSet){  
       String[] col;  
       try {  
              ResultSetMetaData metadata = resultSet.getMetaData();  
              col = new String[metadata.getColumnCount()];  

              for(int count = 0; count < numcols; count++) 
              {               
                 col[count] = metadata.getColumnLabel(count + 1);  
              }  
           } 
       catch(Exception e) 
      {  
         throw new RuntimeException(e);  
      }    
      return col;  
   }  

public static Object[][]  data(ResultSet resultSet){
       List<Object[]> data = new ArrayList<Object[]>();  
       try {  
          int numcols = resultSet.getMetaData().getColumnCount();  
          while (resultSet.next()) 
              {    
              Object [] rowData = new Object[numcols];    
                for (int i = 0; i < rowData.length; ++i) 
                {  
                   rowData[i] = resultSet.getObject(i+1);    
                }  
              data.add(rowData);  
              }  
      } 
      catch(Exception e) 
      {  
         throw new RuntimeException(e);  
      }    
      return data.toArray(new Object[0][0]); 
   }  
      } 

[Winston Gutkowski]

Brennen smith wrote:This goes back to my particular weakness with passing values. I have been reading up. Does this mean that the values being returned are not valid. or is the issue in this line of code?

Very difficult to know without the actual stacktrace. Could you post it? If it's big, just the first 10 lines or so should do.

Winston

[Winston Gutkowski]

Brennen smith wrote:This goes back to my particular weakness with passing values. I have been reading up. Does this mean that the values being returned are not valid. or is the issue in this line of code?

public static String[] headings(ResultSet resultSet){
       ...

One other thing you could do (and which I probably should have done in the first place) is to validate the parameter, ie, add:

if (resultSet == null)
   throw new IllegalArgumentException("cannot get headings for a null ResultSet");

(or something like it) as the first lines of all methods that take a ResultSet.

Then, if you get an IllegalArgumentException when you run it, you know that the problem is likely in the query() method (and it could just be that it can't get a connection).

Winston