How to load exact number of rows to jTable from MySQL and paginate the remaining?

Question

I am trying to load data from MySQL into my Application, where I have a jTable. I want to load only 100 rows using a query, and I can do that successfully as of now. furthermore, I want to paginate that data, in such a way that if I have the next button, and I click on that, the table should be updated with 100 more records and so on.

This method loads the data into the table:


public void showTable(){
        try{ 

            how.res = how.stat.executeQuery("select * from students order by name ASC limit 100");

            while(how.res.next()){
                String id = how.res.getString(1);
                String name = how.res.getString(2);
                String contact = how.res.getString(3);

                Object[] content = {id,name,contact};
                DefaultTableModel model = (DefaultTableModel)jTable1.getModel();
                model.addRow(content);

            }
        }catch (Exception e){

        }
    }

I call this method on form Initialization, and now I only get 100 rows into the table, what should I write for the next button to load next 100 rows and update the table?

What I tried:


private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        // TODO add your handling code here:
        try{ 
            numClick+=100;
            how.res = how.stat.executeQuery("select * from students offset"+numClick+"");

            while(how.res.next()){
                String id = how.res.getString(1);
                String name = how.res.getString(2);
                String contact = how.res.getString(3);

                Object[] content = {id,name,contact};
                DefaultTableModel model = (DefaultTableModel)jTable1.getModel();
                model.addRow(content); 
            }
        }catch (Exception e){

        }  
    }

But this does not give me any result.

Here is the Connection:

public class JoinConnection {

    public Connection con;
    public Statement stat;
    public ResultSet res;

    public JoinConnection(){

        systemConnection();
    }

    public void systemConnection(){
        try{
        Class.forName("com.mysql.jdbc.Driver");
        con =(com.mysql.jdbc.Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/library","root","");
                stat = (Statement) con.createStatement();
        }catch(ClassNotFoundException | SQLException e){System.out.println(e);}
    }
}

I am using NetBeans with MySQL. if the question is not clear, please tell me for clarification.

EDIT: Thanks to @George Z. We solved the problem by changing the button method into something like below. I am editing the question because I can not write the answer in comments:

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         

    try{ 
        numClick+=100;
        DefaultTableModel model = (DefaultTableModel)jTable1.getModel();
        model.setRowCount(0);
        how.res = how.stat.executeQuery("select * from students limit "+numClick+" , 100");

            while(how.res.next())
            {
            String id = how.res.getString(1);
            String name = how.res.getString(2);
            String contact = how.res.getString(3);

            Object[] content = {id,name,contact};
            model.addRow(content);
            }
        }catch (SQLException e){
        e.printStackTrace();
    }  
}

George Z. · Accepted Answer · 2019-04-19T23:38:11.353

1

An opinion-tip: Do not swallow exceptions, and do not face any specific type of exception as Exception. In your case, you should be catching them as SQLException and at least print their stacktrace.

Since your first page - query is "select * from students order by name ASC limit 100" the next page - next query should be something like "select * from students order by name ASC limit "+numClick+" , 100" with numClick increased by 100 in every page. Of course numClick variable should be handled by a PreparedStatement. Read here how to use prepared statements.

In order to understand LIMIT take a look at this answered question in addition.

Another way that you could give a chance, if your data is kind of small, is to load everything in memory (a data structure), and then populate the data from there.

When you change page, you will have to current data from the table. A simple search will guide you in this question.

edited Apr 19 '19 at 23:38

answered Apr 19 '19 at 23:21

George Z.

6,643
4
27
47

That is OK, works now! but it does not update the table, the next 100 is continuously added. I want the next 100 to overwrite the previous ones in the table. – Hefaz Apr 19 '19 at 23:34
should I use my second query in preparedStatement then? – Hefaz Apr 20 '19 at 00:10
@Hefaz Yes, but i did not give much attention to it, because this is a different story. – George Z. Apr 20 '19 at 00:11
Thank you so much. The problem is solved now. I did it without a prepared Statement. I will edit the method for the button in the question. – Hefaz Apr 20 '19 at 00:51

score 0 · Answer 2 · answered Apr 20 '19 at 00:12

You could use LIMIT 0, 100

SELECT * FROM students ORDER by name ASC LIMIT 0, 100    // Returns 1   -> 100
SELECT * FROM students ORDER by name ASC LIMIT 100, 101  // Returns 101 -> 201
SELECT * FROM students ORDER by name ASC LIMIT 200, 101  // Returns 201 -> 301

How to load exact number of rows to jTable from MySQL and paginate the remaining?

2 Answers2