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);
}
}
}
Contents
hide
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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();All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0