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;
}
    }

but also nothing is shown in the textPane - I assume you mean "JTable". The basic code looks reasonable. You are using the setModel(...) method to update the table which is correct assuming the table has been added to a JScrollPane and the scrollPane added to the frame. So assuming the ResultSetMetaData and ResultSet actually return some data it should work. — camickr
– camickr, Commented Jun 2, 2016 at 4:48
@pvg, Also, don't use Vector - the DefaultTableModel stores the data in a Vector, so yes the code should be using a Vector. — camickr
– camickr, Commented Jun 2, 2016 at 4:49
I have no scrollPane - well you need a scroll pane to see the column headers of the table, but the first step is to verify the data returned from the SQL query. You also need to verify that you don't have two references to your table. One that is an instance variable and one that is a local variable. You need to update the table that if visible on the frame. — camickr
– camickr, Commented Jun 2, 2016 at 4:51
@pvg, then you need to create a custom TableModel and that could introduce addition errors. The DefaultTableModel is perfectly fine for displaying data from a database. You can customize it later once the basic code is working. Don't complicate the process if you don't have to. — camickr
– camickr, Commented Jun 2, 2016 at 4:55
@pvg, the AbstractTableModel does not cover that. You need to implement all the methods to store the data. That is where you would introduce problems. Keep the code simple until that basic problem has been solved!!! — camickr
– camickr, Commented Jun 2, 2016 at 4:57

camickr · Accepted Answer · 2016-06-02 15:12:12Z

1

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.

edited Jun 2, 2016 at 15:12

answered Jun 2, 2016 at 5:34

camickr

325k21 gold badges174 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sebastian Zeki Over a year ago

OK edited. I cant help but feel this is a fireTableDataChanged problem though. I think the JTable is populated with nothing when the frame loads up and then when the button is pressed it has no way of knowing that the data has changed so nothing new is added?

camickr Over a year ago

You completely miss the point of the getDataFromDatabase() method. There is no need for any SQL code at all.

Sebastian Zeki Over a year ago

OK. Looks like I just had to add it to a JscrollPane. Thanks @camickr

Sanjeev Saha · Accepted Answer · 2016-06-02 18:02:56Z

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.

@SebastianZeki Since this answer is not a solution to your problem, I am going to remove it.

Collectives™ on Stack Overflow

How to populate JTable from database on button event

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related