Optimize data calling in JDBC onto JTable

Question

currently I have data in MySQL server and I am calling the datas onto the JTable through JDBC. However there are 1369 rows and it seems that it has too much data for it to load. It usually takes 5 minutes to load. Are there anyways to optimize the process? This is my code(I apologize in advance for a messy code):

public class DataTable {
private String databaseName = "*****";
private String tableName = "******";
public void showDatabase(){
    Connection conn = null;
    DatabaseMetaData meta = null;
    Statement stmt = null;
    ResultSet rs = null;
    int k = 0;
    try{
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String connectionUrl = "jdbc:mysql://localhost:3306/" + databaseName;
        String connectionUser = "*****";
        String connectionPassword = "*****";
        conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
        stmt = conn.createStatement();
        meta = conn.getMetaData();
        dataSets(stmt, meta);


    }catch(Exception e){
        e.printStackTrace();
    } finally{
        try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

//return the column size of the table
public int getColumnNumber(DatabaseMetaData meta, Statement stmt) throws SQLException
{
    //ResultSet rs = meta.getColumns(null, null, "practiceexample", null);
    ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnsNumber = rsmd.getColumnCount();
    return columnsNumber;
}


//return the rowNumber of the tables
public int getRowNumber(Statement stmt) throws SQLException
{
    ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
    int rowCount = 0;
    while(rs.next()){
        rowCount = rs.getInt(1);
    }

    return rowCount;
}

public void dataSets(Statement stmt, DatabaseMetaData meta) throws SQLException
{
    String[] columnNames = new String[getColumnNumber(meta, stmt)];
    String[][] dataSets = new String[getRowNumber(stmt)][columnNames.length];


    ResultSet column = meta.getColumns(null, null, tableName, null);

    int i = 0;
    while(column.next())
    {
        columnNames[i] = column.getString("COLUMN_NAME");
        //columnNames.add(i, column.getString("COLUMN_NAME"));
        i++;
    }

    for(int j = 0; j < dataSets.length; j++)
    {
        String[] singleRowData = new String[columnNames.length]; 
        ResultSet data = null;
        for(int k = 0; k < columnNames.length; k++)
        {
            String columnName = columnNames[k];
            data = stmt.executeQuery("SELECT " + columnName + 
                    " FROM " + tableName + " LIMIT " + j + ", " + 1);
            while(data.next())
            {
                singleRowData[k] = data.getString(columnName);
            }

        }

        dataSets[j] = singleRowData;
    }


    SimpleTable table = new SimpleTable(columnNames, dataSets);
    javax.swing.SwingUtilities.invokeLater(new Runnable() {
        public void run() {
            table.createAndShowGUI();
        }
    });
}

class SimpleTable{
    String[] columns;
    String[][] dataSets;

    public SimpleTable(String[] columns, String[][] dataSets){
        this.columns = columns;
        this.dataSets = dataSets;
    }

    public void createAndShowGUI(){
        JPanel gui = new JPanel(new BorderLayout(3, 3));

        final JTable table = new JTable(new DefaultTableModel(dataSets, columns));
        final JScrollPane scrollPane = new JScrollPane(table, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED
                , JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
        Dimension dimension = table.getPreferredSize();
        scrollPane.setPreferredSize(new Dimension(dimension.width, table.getRowHeight() * 30));

        JPanel navigation = new JPanel(new FlowLayout(FlowLayout.CENTER));

        JButton next = new JButton(">");
        next.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e)
            {
                int height = table.getRowHeight() * (20-1);
                JScrollBar bar = scrollPane.getVerticalScrollBar();
                bar.setValue(bar.getValue() + height);
            }
        });

        JButton previous = new JButton("<");
        previous.addActionListener( new ActionListener(){
            public void actionPerformed(ActionEvent ae) {
                int height = table.getRowHeight()*(20-1);
                JScrollBar bar = scrollPane.getVerticalScrollBar();
                bar.setValue( bar.getValue()-height );
            }
        } );

        navigation.add(previous);
        navigation.add(next);

        gui.add(scrollPane, BorderLayout.CENTER);
        gui.add(navigation, BorderLayout.SOUTH);

        JOptionPane.showMessageDialog(null, gui);
    }
}


}

Answer 1

IMHO the root of the bad permormance is you unnecessarily query the database mutliple times to get the data (columns, rows, rows number, columns number, etc) you need:

To get columns number:

ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);

To get rows number:

ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);

To get rows (this is the worst beacuse it's inside a loop):

data = stmt.executeQuery("SELECT " + columnName + " FROM " + tableName + " LIMIT " + j + ", " + 1);

How to solve it

Just query the database once. A single ResultSet and its associated ResultSetMetaData should be enough to accomplish your goal. Additionaly, and as already suggested, use a SwingWorker to do database calls in a separate thread. For example:

final JTable table = new JTable();

SwingWorker<Void, TableModel> worker = new SwingWorker<Void, TableModel> () {

    @Override
    protected Void doInBackground() throws Exception {

        ResultSet resultSet = stmt.executeQuery("SELECT * FROM " + tableName);
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount(); // columns number
        String[] columnNames = new String[columnCount];
        for (int i = 1; i <= columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i); // fill columns names
        }

        resultSet.last();
        int rowCount = resultSet.getRow(); // get rows number
        resultSet.beforeFirst();

        Object[][] data = new Object[rowCount][columnCount];
        int currentRow = 0;
        while (resultSet.next()) {
            for (int currentColumn = 1; currentColumn <= columnCount; currentColumn++) {
                data[currentRow][currentColumn - 1] = resultSet.getObject(currentColumn); // fill data set
             }
             currentRow++;
        }

        TableModel model = new DefaultTableModel(data, columnNames);
        publish(model);

        return null;
    }

    @Override
    protected void process(List<TableModel> chunks) {
        TableModel model = chunks.get(0);
        table.setModel(model);
    }
}

worker.execute();