CODEWITHSUNDEEP
javamysqlswingprepared-statementresultset
user2221125
user2221125

Reputation: 156

ResultSet populating JTable column names but not data

Note: this is not a duplicate, I have already checked out: I can't get my JTable to show anything and I get the Jtable but no data

Although these seem to have very similar problems, they aren't the same as what I'm experiencing. Let me explain:

I have a GUI that has several JTables being populated from a MySQL database. On my initial GUI, the table is working fine. However, at one point I take the user to a new JFrame. On this frame, for some reason, the table shows up and it's populated with the column names, but no data. I can't for the life of me figure out why.

Here is the code from my first GUI that WORKS:

 iTable = new JTable(writeItemResult(res4));
 iTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
 iTable.getColumnModel().getColumn(0).setPreferredWidth(27);
 iTable.getColumnModel().getColumn(1).setPreferredWidth(375);
 iTable.getColumnModel().getColumn(2).setPreferredWidth(150);
 iTable.getColumnModel().getColumn(0).setResizable(false);
 sorter = new TableRowSorter<TableModel>(iTable.getModel());
 iTable.setRowSorter(sorter);

 JScrollPane scrollpane3 = new JScrollPane(iTable, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
 scrollpane3.setPreferredSize(new Dimension(500, 150));
 c.fill = GridBagConstraints.HORIZONTAL;
 c.gridx = 0;
 c.gridy = 16;
 c.gridwidth = 6;
 pane.add(scrollpane3, c);

My writeItemResult method:

public static DefaultTableModel writeItemResult (ResultSet res4) throws SQLException {

    ResultSetMetaData metaData = res4.getMetaData();

    Vector<String> columnNames = new Vector<String>();
    int columnCount = metaData.getColumnCount();
    for (int column = 1; column <= columnCount; column++) {
        columnNames.add(metaData.getColumnName(column));
    }

    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
    while (res4.next()) {
        Vector<Object> vector = new Vector<Object>();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            vector.add(res4.getObject(columnIndex));
        }
        data.add(vector);
    }
    return new DefaultTableModel(data, columnNames);
}

My res4 resultSet:

statement2 = connect.createStatement();
res4 = statement2.executeQuery("SELECT id, prompt, type FROM items ORDER BY prompt");

So, as I stated the above code WORKS fine for my first JFrame. The entire code for my first JFrame is too large so I won't bother pasting it unless specifically asked.

Here is the entire code for my second JFrame, which, as I said returns the table and column names but no data:

} if (e.getSource() == design) {
        int str = fTable.getSelectedRow();
        int stc = fTable.getSelectedColumn();

        if (stc != 0) {
            JOptionPane.showMessageDialog(null, "Error: Please click the Form ID of the form you wish to modify.", "Error!", JOptionPane.ERROR_MESSAGE);
            return;
        }  

         selObj = (Object) fTable.getModel().getValueAt(str, stc);

         try {
            p_statement6.setString(1, selObj.toString());
            res6 = p_statement6.executeQuery();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

         try {
            while (res6.next()){
                    formID1 = res6.getString("id");
                    formName1 = res6.getString("title");
                }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

         int width = 900;
         int height = 900;
         designForm = new JFrame("Designer");
         designForm.setSize(width,height);


         pane = designForm.getContentPane();
         pane.setLayout(new GridBagLayout());
            GridBagConstraints c = new GridBagConstraints();

            if (shouldFill) {
                //natural height, maximum width
                c.fill = GridBagConstraints.HORIZONTAL;
            }

         des_name = new JLabel("<html><small>Designing: "+formName1+"</small></html>");
         c.fill = GridBagConstraints.HORIZONTAL;
         c.gridx = 0;
         c.gridy = 0;
         c.gridwidth = 1;
         pane.add(des_name, c); 

         test = new JButton("Test");
         c.gridx = 0;
         c.gridy = 1;
         c.gridwidth = 1;
         pane.add(test, c);
         test.addActionListener(this);

         designForm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
         Dimension sD = Toolkit.getDefaultToolkit().getScreenSize();
         designForm.setLocation((sD.width - width)/2, (sD.height-height)/2);
         designForm.setResizable(true);
         designForm.setVisible(true);

  } if (e.getSource() == test) {
      try {
             iTable = new JTable(writeItemResult(res4));
             iTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
             iTable.getColumnModel().getColumn(0).setPreferredWidth(27);
             iTable.getColumnModel().getColumn(1).setPreferredWidth(330);
             iTable.getColumnModel().getColumn(2).setPreferredWidth(140);
             iTable.getColumnModel().getColumn(0).setResizable(false);
             sorter = new TableRowSorter<TableModel>(iTable.getModel());
             iTable.setRowSorter(sorter);

             JScrollPane scrollpane4 = new JScrollPane(iTable, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
             scrollpane4.setPreferredSize(new Dimension(500, 150));

             JOptionPane.showMessageDialog(null, scrollpane4);

  } catch (Exception e1) {
      e1.printStackTrace();
  }
  }

If you're curious, p_statement6 is:

p_statement6 = connect.prepareStatement("SELECT * from checklists where id = ?");

I get no errors for this by the way.

Image of broken table: broken

Image of working table:working

So, that's everything I feel that's applicable. If you need more information feel free to ask.

Solution

Thanks to @peeskillet, the solution to my problem was very simple: I was trying to use an exhausted result set. By simply setting the result set Scroll_Insensitive and Concur_Updatable, the problem was resolved.

Upvotes: 0

Views: 1563

Answers (3)

dic19
dic19

Reputation: 17971

Here:

int str = fTable.getSelectedRow();
int stc = fTable.getSelectedColumn();

...

selObj = (Object) fTable.getModel().getValueAt(str, stc); // wrong indexes!!

These indexes belong to the view. If you want to get the value from the table model then you need to convert them to their respective model indexes:

selObj = fTable.getModel().getValueAt(fTable.convertRowIndexToModel(str), fTable.convertColumnIndexToModel(stc));

Or you can get the right selected value directly from the table:

selObj = fTable.getValueAt(str, stc);

As selObj.toString() is the query parameter, a wrong value might cause an empty ResultSet. Consequently the table won't be populated.

See:

Edit

I would also check this:

p_statement6 = connect.prepareStatement("SELECT * from checklists where id = ?");
p_statement6.setString(1, selObj.toString()); // is id a VARCHAR or is NUMERIC?

You say you don't have any error here but the prepared stament will be parsed in something like this:

SELECT * from checklists where id = '1';

If id column is a VARCHAR then it's ok but if it's some numeric type then you will probably get a SQLException.

If this is the case then you need to cast selObj as Integer and use PreparedStatement.setInt(index, value) instead of setString():

p_statement6.setInt(1, (Integer)selObj);

Upvotes: 1

ravibagul91
ravibagul91

Reputation: 20755

Replace 

selObj = (Object) fTable.getModel().getValueAt(str, stc);

By

selObj = (Object) fTable.getValueAt(str, stc);

AND

iTable = new JTable(writeItemResult(res4));

By

iTable = new JTable(writeItemResult(res6));

Upvotes: 0

Paul Samsotha
Paul Samsotha

Reputation: 209012

Looks like you're trying to use the same ResultSet for both tables.

 new JTable(writeItemResult(res4));

The two tables are both using res4. The problem with this is that the first will work fine, but it exhausts the data from the ResultSet. The data had been run through. So trying to can rs.next() in the second writeItemResult(res4) will return nothing but the column headers, as that's just meta data

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable.The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. 

   Statement stmt = con.createStatement(
                                  ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_UPDATABLE);
   ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
   // rs will be scrollable, will not show changes made by others,
   // and will be updatable
  • static final int TYPE_SCROLL_INSENSITIVE - The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
  • static final int CONCUR_READ_ONLY - The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.

So you must do the above to make the ResultSet scrollable. Then you need to use

  • void beforeFirst() throws SQLException - Moves the cursor to the front of this ResultSet object, just before the first row. This method has no effect if the result set contains no rows.

So if you just put res4.beforeFirst() in your writeItemResult() it should work for the second tables also.

Also, on a side note, if you want the tables to use the same data, why not just have them share the same TableModel?

table2.setModel(table1.getModel());

Upvotes: 1

Related Questions