Refreshing JTable(updated: SQLException(Table dosen't exist))

[Dillinger4]

I have a JTable which displays records from a database. I want to run some order by queries to sort the records and have the new sorted ResultSet displayed in the JTable. What is the easiest way i can clear out the table and display the new records?

[Dillinger4]

Any ideas on why this Exception is getting thrown?

 import java.util.*; 
 import java.sql.*;
 import java.awt.*;  
 import javax.swing.*;
 import java.awt.event.*;  
 import javax.swing.table.*;   

 public class Gui{
   public static void main(String[] args){new Gui();}
   
   private JFrame jf; 
   private Container c; 
   private JTable jt;
   private Choice jc;   
   private JScrollPane jsp; 
   private BackEnd be; 
   private Object[] cols;   
  
  public void refreshTable(Object[][] table){
    for(int row = 0; row < table.length; row++){
      for(int col = 0; col < table[row].length; col++){
         jt.setValueAt(table[row][col], row,col);    
     }
    } 
   }
  public Gui(){
   cols = new Object[]{"Title","Director","Category"};  
   try{
    be = new BackEnd(); 
    be.setUp();
    jf = new JFrame("Inventory");
    jf.addWindowListener(new WindowMonitor());
    jt = new JTable(be.getData("*"),cols);  // default dispay * records
    jc = new Choice();
    jc.addItemListener(new ChoiceMonitor(jc,be,this)); 
    jc.add(cols[0].toString());
    jc.add(cols[1].toString());
    jc.add(cols[2].toString());
    c = jf.getContentPane();  
    jsp = new JScrollPane(jt); 
    c.add(jsp,BorderLayout.EAST);
    c.add(new JSeparator(JSeparator.VERTICAL));
    c.add(jc,BorderLayout.WEST);
    jf.setSize(600,150);  
    jf.setVisible(true);
    }catch(Exception e){
     e.printStackTrace(); 
    }
   } 
  }

 class WindowMonitor extends WindowAdapter{
  public void windowClosing(WindowEvent we){
     System.exit(0); 
  }
}
 class ChoiceMonitor implements ItemListener{
    private Choice choice;
    Gui gui;
    private BackEnd be; 
    
    public ChoiceMonitor(Choice choice,BackEnd be,Gui gui){
     this.choice = choice;
     this.gui = gui;
     this.be = be; 
    } 
    public void itemStateChanged(ItemEvent ie){ 
      try{
       gui.refreshTable(be.getData(choice.getItem(choice.getSelectedIndex())));
      }catch(Exception e){System.err.println(e + "itemStateChanged");} 
    }  
   }

 import java.util.*;
 import java.sql.ResultSet;
 import java.sql.Statement;   
 import java.sql.Connection;
 import java.sql.SQLException;  
 import java.sql.DriverManager;
 
 class BackEnd{
  
  private Connection c; 

  public void setUp(){ 
   try{  
  Class.forName("com.mysql.jdbc.Driver"); 
  c = DriverManager.getConnection("jdbc:mysql://localhost/test");
  }catch(SQLException sql){
    System.out.println(sql);
  }catch(Exception e){
     System.out.println(e);
  }
 }

  public Object[][] getData(String choice) throws Exception{
     Statement s = null; 
     ResultSet rs = null;
     int rows = 0; 
     Object[][] datatable = null;
     try{
     datatable = new Object[100][3];  
     s = c.createStatement();
     if(choice.equals("*")){
      rs = s.executeQuery("SELECT * from ifilms");
     }else if(choice.equals("Title")){
         rs = s.executeQuery("select * from iflims order by title");  
     }else if(choice.equals("Director")){
         rs = s.executeQuery("select * from iflims order by director");  
     }else if(choice.equals("Category")){
         rs = s.executeQuery("select * from iflims order by director");  
     }

     while(rs.next()){
       for(int cols = 0; cols < rs.getMetaData().getColumnCount(); cols++){
         datatable[rows][cols] = rs.getObject(cols + 1);
       }
       rows++; 
      }
     }finally{
      if(rs != null){
       try{
         rs.close();
       }catch(SQLException sql){System.err.println(sql + "getData");} 
      }
      if(s != null){
       try{
        s.close();
       }catch(SQLException sql){System.err.println(sql + "getData");} 
      }
     }
     return datatable;
    }
   }

[skarho]

got this mate.

/*
 * sample.java
 *
 * Created today
 */

package sample;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;

import java.sql.*;
import java.util.*;
/**
 *
 * @author  skarho
 */
public class sample extends javax.swing.JFrame {
    Connection cn=null;
    /** Creates new form sample */
    String[] cols={"TerritoryID","TerritoryDescription","RegionID"};
    public sample() {
        initComponents();
        
        combo.addItem(cols[0]);        
        combo.addItem(cols[1]);        
        combo.addItem(cols[2]);        
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            cn=DriverManager.getConnection("jdbc:mysql:///northwind");
            table.setModel(new sample.xTableModel(convertToObject(getData("")),cols));
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }

    Object[][] convertToObject(Vector v){
        Object o[][]=new Object[v.size()][((datatable)v.get(0)).rows.length];
        for(int i=0;i<v.size();i++){
            datatable dt=(datatable)v.get(i);
            for(int j=0;j<((datatable)v.get(i)).rows.length;j++){
                o[i][j]=dt.rows[j];
            }
        }
        return o;
    }
    
    public Vector getData(String orderby){
        Vector v=new Vector();
        try{
            Statement s=cn.createStatement();
            ResultSet rs=s.executeQuery("select * from territories "+orderby);
            while(rs.next()){
                datatable dt=new datatable();
                dt.rows=new Object[rs.getMetaData().getColumnCount()];
                for(int i=0;i<rs.getMetaData().getColumnCount();i++)
                    dt.rows[i]=rs.getObject(i+1);
                v.add(dt);
            }
            rs.close();
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            return v;
        }
    }
    class datatable extends Object{
        public Object[] rows;
    }    
    
    class xTableModel extends javax.swing.table.AbstractTableModel{
        String[] cols;
        Object[][] data;
        public xTableModel(Object data[][],String[] cols){
            this.cols=cols;
            this.data=data;
        }
        public int getColumnCount() {
            return cols.length;
        }
        public String getColumnName(int col){
            return cols[col];
        }
        public int getRowCount() {
            return data.length;
        }
        public Object getValueAt(int row, int col) {
            return data[row][col];            
        }
        public void setValueAt(Object value,int row,int col){
            data[row][col]=value;
        }
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    private void initComponents() {
        panel = new javax.swing.JPanel();
        combo = new javax.swing.JComboBox();
        scroll = new javax.swing.JScrollPane();
        table = new javax.swing.JTable();

        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(java.awt.event.WindowEvent evt) {
                exitForm(evt);
            }
        });

        panel.setLayout(new java.awt.BorderLayout());

        combo.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                comboActionPerformed(evt);
            }
        });

        combo.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                comboItemStateChanged(evt);
            }
        });

        panel.add(combo, java.awt.BorderLayout.NORTH);

        table.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        scroll.setViewportView(table);

        panel.add(scroll, java.awt.BorderLayout.SOUTH);

        getContentPane().add(panel, java.awt.BorderLayout.CENTER);

        pack();
    }

    private void comboActionPerformed(java.awt.event.ActionEvent evt) {
        // Add your handling code here:
    }

    private void comboItemStateChanged(java.awt.event.ItemEvent evt) {
        // Add your handling code here:
        try{
            Class.forName("com.mysql.jdbc.Driver");
            cn=DriverManager.getConnection("jdbc:mysql:///northwind");
            table.setModel(new sample.xTableModel(
                convertToObject(getData("order by "+evt.getItem().toString())),cols));
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }
    
    /** Exit the Application */
    private void exitForm(java.awt.event.WindowEvent evt) {
        System.exit(0);
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        new sample().show();
    }
    
    
    // Variables declaration - do not modify
    private javax.swing.JTable table;
    private javax.swing.JComboBox combo;
    private javax.swing.JScrollPane scroll;
    private javax.swing.JPanel panel;
    // End of variables declaration
    
}

but still i don't know why i should have another Class.blah blah on my itemSelection. the reason i put that is that it throws Exception if i don't put the Class... on my itemSelection. hope this helps.

[Dillinger4]

Thanks for the help. Still have the same problem though. A java.sql.SQLException gets thrown when i try to access the table more than once.

[Dillinger4]

Any ideas guys?

[brown monkey]

i rerun my code mate but it still works. dunno the details

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import java.util.*;

public class sample extends JFrame{
    String[] cols={"TerritoryID","TerritoryDescription","RegionID"};
    JPanel panel;
    JComboBox combo;
    JScrollPane scroll;
    JTable table;
    Connection cn;
    public sample(){
        initComponents();
    }
    void initComponents(){
        panel=new JPanel();
        combo=new JComboBox();
        scroll=new JScrollPane();
        table=new JTable();

        setDefaultCloseOperation(EXIT_ON_CLOSE);

        panel.setLayout(new BorderLayout());
        combo.addItemListener(new ItemListener(){
            public void itemStateChanged(ItemEvent e){
                comboItemStateChanged(e);
            }
        });
        combo.addItem(cols[0]);
        combo.addItem(cols[1]);
        combo.addItem(cols[2]);
        panel.add(combo,BorderLayout.NORTH);
        scroll.setViewportView(table);
        panel.add(scroll,BorderLayout.CENTER);
        getContentPane().add(panel,BorderLayout.CENTER);
        setSize(400,300);
        setTitle("Sample");

        try{
            Class.forName("com.mysql.jdbc.Driver");
            cn=DriverManager.getConnection("jdbc:mysql:///northwind");
            table.setModel(new xTableModel(
                convertToObject(getData("")),cols));
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }
    void comboItemStateChanged(ItemEvent e){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            cn=DriverManager.getConnection("jdbc:mysql:///northwind");
            table.setModel(new xTableModel(
                convertToObject(getData("order by "+e.getItem().toString())),cols));
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }
    Object[][] convertToObject(Vector v){
        Object[][] o=new Object[v.size()][((datatable)v.get(0)).rows.length];
        for(int i=0;i<v.size();i++){
            datatable dt=(datatable)v.get(i);
            for(int j=0;j<((datatable)v.get(i)).rows.length;j++){
                o[i][j]=dt.rows[j];
            }
        }
        return o;
    }
    public Vector getData(String orderby){
        Vector v=new Vector();
        try{
            Statement s=cn.createStatement();
            ResultSet rs=s.executeQuery("select * from territories "+orderby);
            while(rs.next()){
                datatable dt=new datatable();
                dt.rows=new Object[rs.getMetaData().getColumnCount()];
                for(int i=0;i<rs.getMetaData().getColumnCount();i++)
                    dt.rows[i]=rs.getObject(i+1);
                v.add(dt);
            }
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            return v;
        }
    }
    class datatable{
        public Object[] rows;
    }
    class xTableModel extends AbstractTableModel{
        String[] cols;
        Object[][] data;
        public xTableModel(Object[][] data,String[] cols){
            this.cols=cols;
            this.data=data;
        }
        public int getColumnCount(){ return cols.length; }
        public String getColumnName(int col){ return cols[col]; }
        public int getRowCount(){ return data.length; }
        public Object getValueAt(int row,int col){ return data[row][col]; }
        public void setValueAt(Object value,int row,int col){ data[row][col]=value; }
    }

    public static void main(String[] args){
        new sample().show();
    }
}

[Dillinger4]

The problem is happening in the nested if's that are contained in the getData(String choice) method which is in the BackEnd class. When i change the args from the calling method jt = new JTable(be.getData("title"),cols); (which is in the Gui constructor) to "title"everything works fine. The JTable is shown with the title field ordered. If the args from the calling method getData() are changed to "directory" or "catergory" the SQLException(Table dosen't exist) is thrown.

[Dillinger4]

Now within the getData(String choice) method i have

 rs = s.executeQuery("select * from iflims order by title");  
 rs = s.executeQuery("select * from iflims order by director"); 
 rs = s.executeQuery("select * from iflims order by catergory");

I took out the if statements and when i comment out two executeQuery()'s and run the one not commented out i still get the same exception thrown. It dosent matter which one i run. I am starting to think the problem is not with the code it's with the mysql db.

[brown monkey]

What exactly is the SQL Exception perhaps CB can point out where we did wrong.

[Dillinger4]

If this is the modified method(forgetting about testing for the contents of the choice param) the code should work but for some reason the sqlserver says that the table dosent exist. Thing is that when i had the conditional statements in to check for the param to execute the queries i got the code to work on and off. I remember the app cpmming up and displaying the JTable with the director col sorted.

 public Object[][] getData(String choice) throws Exception{
     Statement s = null; 
     ResultSet rs = null;
     int rows = 0; 
     Object[][] datatable = null;
     try{
     datatable = new Object[100][3];  
     s = c.createStatement();
     rs = s.executeQuery("select * from iflims order by director");  
  

     while(rs.next()){
       for(int cols = 0; cols < rs.getMetaData().getColumnCount(); cols++){
         datatable[rows][cols] = rs.getObject(cols + 1);
       }
       rows++; 
      }
     }finally{
      if(rs != null){
       try{
         rs.close();
       }catch(SQLException sql){System.err.println(sql + "getData");} 
      }
      if(s != null){
       try{
        s.close();
       }catch(SQLException sql){System.err.println(sql + "getData");} 
      }
     }
     return datatable;
    }

[Dillinger4]

All i had to do was check the table name. I kept wondering why SQLException(Table dosen't exist) was thrown.
rs = s.executeQuery("select * from iflims order by title"); should be
rs = s.executeQuery("select * from ifilms order by title");

Thanks for the help. If you ever need anything let me know.