JTable getting values form MySQL Database

Question

I need to get all the values from a Database and put it inside the JTable, filling all rows. My code just puts inside the JTable only the last row and misses all previous. Anyone got an idea of how to get all the values from the DB and insert them all in the JTable?

try{
            Class.forName("com.mysql.jdbc.Driver");
            conn3 = DriverManager.getConnection("jdbc:mysql://localhost/test1?user=me&password=123456");
            stmt3 = conn3.createStatement();
            rs3 = stmt3.executeQuery("SELECT * FROM teams");
            String str1 = null,str2 = null,str3 = null,str4 = null,str5 = null;
            while (rs3.next())
            {
                str1 = rs3.getString(1);
                str2 = rs3.getString(2);
                str3 = rs3.getString(3);
                str4 = rs3.getString(4);
                str5 = rs3.getString(5);
            }

            Object[][] data = {{str1,str2,str3,str4,str5}};
            String[] columnNames = {"id","Name","Players","Point","Position" };
            table = new JTable(data,columnNames);
            table.setCellSelectionEnabled(true);
            table.setColumnSelectionAllowed(true);
            table.setFillsViewportHeight(true);
            table.setSurrendersFocusOnKeystroke(true);
            table.setBounds(10, 321, 297, 143);
            frame.getContentPane().add(table);
            rs3.close();
            stmt3.close();
            conn3.close();
    }
    catch (SQLException se)
    {
        System.out.println( "SQL Exception:" );
           while( se != null )
           {
              System.out.println( "State  : " + se.getSQLState()  );
              System.out.println( "Message: " + se.getMessage()   );
              System.out.println( "Error  : " + se.getErrorCode() );
              se = se.getNextException();
           }
    }
    catch (ClassNotFoundException ex)
    {
           System.out.println("Error: unable to load driver class!");
    }

1) `table.setBounds(..);` Swing GUIs might have to work on different platforms, using different PLAFs, on different screen sizes and resolutions with different default settings for font size. As such, they are not conducive to exact placement of components. Instead use layout managers, or [combinations of layout managers](http://stackoverflow.com/a/5630271/418556) as well as [layout padding and borders](http://stackoverflow.com/q/17874717/418556) for white space. 2) `frame.getContentPane().add(table);` Add the table at app. start-up. When the DB query is complete, set a new table model. — Andrew Thompson, Jul 25 '14 at 14:18

score 2 · Answer 1 · edited May 23 '17 at 12:11

while (rs3.next())
        {
            str1 = rs3.getString(1);
            str2 = rs3.getString(2);
            str3 = rs3.getString(3);
            str4 = rs3.getString(4);
            str5 = rs3.getString(5);
        }

You are overwriting again and again the same variables. You should use a list or an array instead. Something like:

while (rs3.next())
        {
            //Given that all of your str are declared as List<String> strX = new List<String>();
            str1.add(rs3.getString(1));
            str2.add(rs3.getString(2));
            str3.add(rs3.getString(3));
            str4.add(rs3.getString(4)); 
            str5.add(rs3.getString(5));
        }

But for inserting ResultSet in a table there are plenty of tutorials.

EDIT: If you don't want to use a table model you can change the logic of your data retrieving. You must provide to the table a matrix[row][col]. Which are your rows? And which your colums?

Rows are the "nexts" of the result set, while colums are 1 to 5. You need to know how many rows are there (a bit tricky):

if (rs3.next()) {
        rs3.last();
        int rows = rs3.getRow();
    }
rs3.beforeFirst();

Now create the matrix:

String[][] matrix = new String[rows][4]; //Your Object[][] data.

int index = 0;
while(rs3.next()){
    matrix[i][0] = rs3.getString(1);
    matrix[i][1] = rs3.getString(2);
    matrix[i][2] = rs3.getString(3);
    matrix[i][3] = rs3.getString(4); 
    matrix[i][4] = rs3.getString(5);
    i++;
}
String[] columnNames = {"id","Name","Players","Point","Position" };
table = new JTable(matrix, columnNames);

USEFUL: Another useful (but not strictly requested) method to retrieve the object data is using customized mappings or a more complicate Object-relational mapping framework like Hibernate.

Yeah but then, the Object[][] data = {{str1[i],str2[i],str3[i],str4[i],str5[i]}}; would keep writing all the values to the first row... — vagg77, Jul 25 '14 at 14:24
That's unrelated on how you get the rows values. To correctly insert the values in the table you should create a [TableModel](http://docs.oracle.com/javase/tutorial/uiswing/components/table.html#data) and manipulate the data. — Narmer, Jul 25 '14 at 14:27

score 1 · Answer 2 · answered Jul 26 '14 at 01:12

Don't use a 2D Array to hold the data. You don't know how many rows of data will be in the database, so you don't know how large to make the Array.

Check out Table From Database for a couple of different suggestions. One approach involves using Vectors so you can use the DefaultTableModel.

score 0 · Accepted Answer · answered Jul 25 '14 at 14:44

The OP code is using a 2d array of Objects as the data for the JTable, but never moving data into that array. We first need to count how many rows are in the table so that we can size the array:

try {
        Class.forName("com.mysql.jdbc.Driver");
        conn3 = DriverManager.getConnection("jdbc:mysql://localhost/test1?user=me&password=123456");
        stmt3 = conn3.createStatement();
        ResultSet rowCounter = stmt3.executeQuery("SELECT COUNT(*) FROM teams");
        rowCounter.next();
        int numRows = rowCounter.getInt(1);
        stmt3.close();            
        Object[][] data = new Object[numRows][5];
        stmt3 = conn3.createStatement();
        rs3 = stmt3.executeQuery("SELECT * FROM teams");
        int row = 0;
        while (rs3.next()) {
            data[row][0] = rs3.getString(1);
            data[row][1] = rs3.getString(2);
            data[row][2] = rs3.getString(3);
            data[row][3] = rs3.getString(4);
            data[row][4] = rs3.getString(5);
            row++;
        }
        String[] columnNames = {"id","Name","Players","Point","Position" };
        table = new JTable(data,columnNames);
        table.setCellSelectionEnabled(true);
        table.setColumnSelectionAllowed(true);
        table.setFillsViewportHeight(true);
        table.setSurrendersFocusOnKeystroke(true);
        table.setBounds(10, 321, 297, 143);
        frame.getContentPane().add(table);
        rs3.close();
        stmt3.close();
        conn3.close();
}
catch (SQLException se) {
    //handle this
}
catch (ClassNotFoundException ex) {
       System.out.println("Error: unable to load driver class!");
}

Upvote every answer that was helpful and choose one answer to "accept. " — Thorn, Jul 28 '14 at 08:15
If i want to refresh/update the JTable, how will i do this since i havent used TableModel and therefore cannot use fireTableChanged() method? — vagg77, Jul 28 '14 at 11:52
I have a button that adds a team to a database and when i press it, it wont refresh my JTable — vagg77, Jul 28 '14 at 11:54
The code you posted is the quick and dirty way to get a JTable up. If you want to add rows, you need vectors or lists and you should extend TableModel to store your data. Other answers mention this. Also check out the Java Swing tutorial from Oracle. — Thorn, Jul 28 '14 at 12:16

JTable getting values form MySQL Database

3 Answers3