How to populate JTable from database on button event

Question

I have a text box that allows users to put in select type queries with the idea that when they click a button the result of the select statement will be shown in a JTable. I don't get any errors but also nothing is shown in the textPane when my button is pressed. The I have is below:

public class Console {

    String myquery="";

    private JFrame frame;
    private JTextField textField;


    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    Console window = new Console();
                    window.frame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }


    public Console() {
        initialize();
    }


    private void initialize() {
        frame = new JFrame();
        frame.setBounds(100, 100, 950, 800);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    JTextArea textAreaQuery = new JTextArea();
            JTable table_ResQues = new JTable();
            JButton btnNewButton = new JButton("Execute");
            btnNewButton.addActionListener(new ActionListener() {

                public void actionPerformed(ActionEvent e)
                {
                    String connectDB = "jdbc:ucanaccess:///Users/sebastianzeki/Documents/PhysJava/Physiology.mdb;";
                    System.out.println("Connection To Database Made");
                    Connection conn = null;

                        try {
                            conn = DriverManager.getConnection(connectDB);
                        } catch (SQLException e1) {
                            e1.printStackTrace();
                        }
                        Statement st = null;
                        try {
                            st = conn.createStatement();
                        } catch (SQLException e1) {
                            e1.printStackTrace();
                        }
                         myquery=textAreaQuery.getText();
                        String stg2 = "Select "+myquery;
                        ResultSet rs = null;
                        try {
                            rs = st.executeQuery(stg2);
                            table_ResQues.setModel(getDataFromDatabase);
                        } catch (SQLException e1) {
                            e1.printStackTrace();
                        }
                }
}

and the code to build the model:

  public TableModel getDataFromDatabase()
{
    DefaultTableModel model = new DefaultTableModel(5, 5);
    model.setValueAt("Hard", 0, 0);
    model.setValueAt("Coded", 1, 1);
    model.setValueAt("Data", 2, 2);

    return model;
}
    }

Answer 1

Here is my solution to your problem. I have retained your variable names though not all of them followed Java conventions. I think all you wanted was that the user would type SQL query in JTextArea and see the result of the query in a JTable.

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JSplitPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.table.AbstractTableModel;

public class Console {
   private JFrame frame; 
   private JTextArea textAreaQuery; 
   private JTable table_ResQues;
   private JButton btnNewButton;

public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
                Console window = new Console();
                window.frame.setVisible(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}

public Console() {
    initialize();
}

private void initialize() {
    frame = new JFrame("SQL Query");
    frame.setBounds(100, 100, 950, 800);
    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    textAreaQuery = new JTextArea();
    btnNewButton = new JButton("Execute");
    table_ResQues = new JTable();

    JPanel queryPanel=new JPanel(new BorderLayout()); // holds JTextArea and JButton

    queryPanel.add(new JScrollPane(textAreaQuery), BorderLayout.CENTER);

    JPanel btnPanel = new JPanel(new FlowLayout(FlowLayout.RIGHT));
    btnPanel.add(btnNewButton);
    queryPanel.add(btnPanel, BorderLayout.SOUTH);       

    JSplitPane splitPane = new JSplitPane(JSplitPane.VERTICAL_SPLIT, queryPanel,
            new JScrollPane(table_ResQues));
    splitPane.setOneTouchExpandable(true);
    splitPane.setDividerLocation(150);
    frame.setContentPane(splitPane);        
    btnNewButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            /*try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e2) {                   
                e2.printStackTrace();
            }*/
            String connectDB = "jdbc:ucanaccess:///Users/sebastianzeki/Documents/PhysJava/Physiology.mdb;";         
            Connection conn = null;
            Statement st = null;
            try {
                conn = DriverManager.getConnection(connectDB);
                //conn = DriverManager.getConnection("jdbc:mysql://localhost/cwcx", "root", "admin");// MySQL connection
                st = conn.createStatement();
                System.out.println("Connection To Database Made");
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            String myquery = textAreaQuery.getText();
            if(myquery.endsWith(";")){
                myquery=myquery.substring(0, myquery.length()-1);
            }
            ResultSet rs = null;
            try {
                rs = st.executeQuery(myquery);
                // extract column information
                ResultSetMetaData rsmd = rs.getMetaData(); 
                int columnCount = rsmd.getColumnCount();
                List<String> columnData = new ArrayList<String>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    columnData.add(rsmd.getColumnName(i));
                }
                // sql result data
                List<List<Object>> rowData = new ArrayList<List<Object>>();
                while (rs.next()) {
                    List<Object> row = new ArrayList<Object>(columnCount);
                    for (int i = 0; i < columnCount; i++) {
                        row.add(rs.getObject(i + 1));
                    }
                    rowData.add(row);
                }                   
                table_ResQues.setModel(new ListTableModel(rowData, columnData));                    
            } catch (SQLException e1) {
                JOptionPane.showMessageDialog(frame, e1.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                e1.printStackTrace();
            }finally{
                if(rs!=null){
                    try {
                        rs.close();
                    } catch (SQLException e1) {                         
                        e1.printStackTrace();
                    }
                }
                if(st!=null){
                    try {
                        st.close();
                    } catch (SQLException e1) {                         
                        e1.printStackTrace();
                    }
                }
                if(conn!=null){
                    try {
                        conn.close();
                    } catch (SQLException e1) {                         
                        e1.printStackTrace();
                    }
                }
            }
        }

    });

}

// this table model is created from two dimensional List rowData and single dimensional List columnData
private static class ListTableModel extends AbstractTableModel{     
    private static final long serialVersionUID = 1L;        
    private List<List<Object>> rowData;     
    private List<String> columnData;        
    public ListTableModel(List<List<Object>> rowData, List<String> columnData) {            
        this.rowData = rowData;
        this.columnData = columnData;
    }

    @Override
    public int getRowCount() {          
        return rowData.size();
    }

    @Override
    public int getColumnCount() {           
        return columnData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {       
        return rowData.get(rowIndex).get(columnIndex);
    }

    @Override
    public String getColumnName(int column) {           
        return columnData.get(column);
    }

    @Override
    public Class<?> getColumnClass(int columnIndex) {
        Object obj=rowData.get(0).get(columnIndex);
        return obj.getClass();
    }       
  }

}

On my system, I have tested it with MySQL database. But I have commented out that part. You may try this on your system without any modification. Please do tell me whether you wanted to achieve this solution or not.

Answer 2

Ive removed the local variable

No, you removed the instance variable. Did you actually try this with your real code or just edit the question?

I have no idea how to provide a testable database for this question

I already suggested you create a method to simplify the logic. Somethng like:

public TableModel getDataFromDatabase()
{
    DefaultTableModel model = new DefaultTableModel(5, 5);
    model.setValueAt("Hard", 0, 0);
    model.setValueAt("Coded", 1, 1);
    model.setValueAt("Data", 2, 2);

    return model;
}

Then in your ActionListener you simple do something like:

table_ResQues.setModel( getDataFromDataBase() );

Once you get this basic logic working you move the SQL logic into the getDataFromDatabase() method.

So now you create your SSCCE showing how you actually create the frame and add the components to the frame. The code should be compilable and executable.

Edit:

You have been told to display a table is a scrollpane. It is no extra effort to do this. Instead of using:

panel.add(table);

you use:

panel.add( new JScrollPane( table ) );

I would also suggest that to test your layout you can use code like the following to display a dummy table:

//JTable table_ResQues = new JTable();
JTable table_ResQues = new JTable(5,5);

Then when you use the setModel() method, only the data is affected, not the layout of the table.

I cant help but feel this is a fireTableDataChanged problem though.

I doubt it. That method is invoked by the TableModel when you change the data in the model. It is not used in this case because you are using the setModel(...) method. This will cause the table to repaint() itself automatically.