优化在JDBC上调用JTable的数据

Question

目前，我在MySQL服务器中有数据，并且正在通过JDBC将数据调用到JTable上。但是，共有1369行，并且似乎有太多数据无法加载。加载通常需要5分钟。无论如何，有没有优化流程？这是我的代码（我事先为一个混乱的代码道歉）：

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

恕我直言，坏习惯的根源是您不必要地查询数据库的多重时间来获取所需的数据（列，行，行号，列号等）：

要获取列号：

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

要获取行号：

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

要获取行（这是最糟糕的原因，因为它在循环内）：

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

如何解决

只需查询数据库一次。单个ResultSet及其关联的ResultSetMetaData应该足以实现您的目标。另外，正如已经建议的那样，使用a
SwingWorker在单独的线程中进行数据库调用。例如：

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();