gpt4 book ai didi

java - 优化JDBC中数据调用到JTable

转载 作者:行者123 更新时间:2023-11-29 00:07:03 25 4
gpt4 key购买 nike

目前我在 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);
}
}


}

最佳答案

恕我直言,不良性能的根源是您不必要地多次查询数据库以获取所需的数据(列、行、行号、列号等):

获取列号:

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应该足以实现您的目标。此外,正如已经建议的那样,使用 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();

关于java - 优化JDBC中数据调用到JTable,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/27049130/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com