Paging sql table in Java

Question

I'm working on a Java Web application that prints a table from a mysql database. I need to make it possible for a user to enter a position from which wants to print data. I do not know how to position in the table and print it. This is the code I have:

PreparedStatement sql= veza.prepareStatement("SELECT * FROM table;");
ResultSet rs = sql.executeQuery();
while (rs.next()) {
     for (int i = positionFrom; i <= positionTo; i++) {
          // ??
          int id = rs.getInt("id");
          String content= rs.getString("content");
          Date time= rs.getTimestamp("time");
          list.add(new List(id, content, time));
     }

 }

score 1 · Answer 1 · answered Apr 28 '18 at 14:52

1

Why not limit your query result by 'offset' and 'limit' ? look at the answers here as an example.

answered Apr 28 '18 at 14:52

khodayar J

914
8
16

while a brief answer, I agree – Paul Bastide Apr 28 '18 at 14:54

score 0 · Answer 2 · answered Apr 28 '18 at 16:48

If you do insist on providing the entire table contents to the User then allowing that User to select the desired table rows for further processing elsewhere in your code, you may find that this little method can help you:

public String[] getJTableSelectedRowsData (JTable theTable) {
    // Gets the data contained within all Selected Table Rows.
    // Each array element within the returned string array will
    // contain a Pipe delimited data string of each selected row.
    int[] selectedRows = theTable.getSelectedRows();
    String[] resultArray = new String[selectedRows.length];

    DefaultTableModel dtm = (DefaultTableModel) theTable.getModel();
    int nCol = dtm.getColumnCount();

    for (int i = 0; i < selectedRows.length; i++) {
        String resultString = "";
        for (int j = 0 ; j < nCol ; j++) { 
            resultString+= resultString.equals("") ? dtm.getValueAt(selectedRows[i],j).toString() :
                                               "|" + dtm.getValueAt(selectedRows[i], j).toString(); 
        }
        resultArray[i] = resultString;
    }
    return resultArray;
}

Of course you can modify this method to return whatever you want. As an example of how you might use this method:

String[] selectedRows = getJTableSelectedRowsData(jTable1);

for (String selectedRow : selectedRows) {
    System.out.println(selectedRow);
}

If it is indeed just from a specific row within the table to the end of the table then you might want to try something like this:

public String[] getAllRowsFromSelected(JTable theTable) {
    // Gets the data contained within all table rows starting 
    // from thefirst selected Table Row to the end of table.
    // Each array element within the returned string array will
    // contain a Pipe delimited data string of each retrieved row.
    int selectedRow = theTable.getSelectedRow();
    if (selectedRow < 0) { selectedRow = 0; }
    int totalRows = theTable.getRowCount();
    String[] resultArray = new String[((totalRows - selectedRow))];

    DefaultTableModel dtm = (DefaultTableModel) theTable.getModel();
    int nCol = dtm.getColumnCount();

    int counter = 0;
    String resultString;
    for (int i = selectedRow; i < totalRows; i++) {
        resultString = "";
        for (int j = 0 ; j < nCol ; j++) { 
            resultString+= resultString.equals("") ? dtm.getValueAt(i,j).toString() :
                                               "|" + dtm.getValueAt(i, j).toString(); 
        }
        resultArray[counter] = resultString;
        counter++;
    }
    return resultArray;
}

Again, you can modify this method to return whatever you like. As an example of how you might use this method:

String[] desiredRows = getAllRowsFromSelected(jTable1);
for (String desiredRow : desiredRows) {
    System.out.println(desiredRow);
}

It is however much easier to have your query retrieve the exact records desired.

Paging sql table in Java

2 Answers2