Search code examples
javasqlselectpaginationprintwriter

Java pagination issue print Printwriter

I tried to do a pagination in Java using PrintWriter. I used this as model: https://www.javatpoint.com/pagination-in-servlet. As you can see in the example code is done in order to work with 3 pages and 5 elements each page.

        out.print("<a href='ViewServlet?page=1'>1</a> ");  
        out.print("<a href='ViewServlet?page=2'>2</a> ");  
        out.print("<a href='ViewServlet?page=3'>3</a> ");

int total=5; I don't think it makes any sense to copy all my code since reading from database works.. I just want to have 1 element on each page and have as many pages as elements was found. So I use

String query = "select * from tabL where name like CONCAT( '%',?,'%') limit "
                    + (start-1)+","+total;

with 1 for start and 1 for total.. ResulSet will have size 1 but if I use this

    out.print("<a href='ViewServlet?page=1'>1</a> ");  
    out.print("<a href='ViewServlet?page=2'>2</a> ");  
    out.print("<a href='ViewServlet?page=3'>3</a> ");

It prints other elements as well. So please help how could be done to have as many pages as elements are found and 1 element each page.

Do I need to have a select without limit and use the size of resulset obtained with that, is there a better solution ?

PS: someone please explain me how if I put limit with 1,1 ResulSet has size 1 but it can print 3 elements at least.

I would need to know at least the exact number of elements which were retrieved even with limit 1.

Thank you.

Solution

  • To find the total count you can use a count-query:

    String countQuery = "select count(*) from tabL where name like CONCAT( '%',?,'%')";

    When you execute this query with the same parameter as the query for the data you will get a ResultSet with one row and one column.

    You can retrieve it with

    Connection con = ...; // you probably already have a Connection
PreparedStatement ps = con.prepareStatement(countQuery);
ps.setString(1, name); // the name pattern that you're searching for
ResultSet rs = ps.executeQuery();
int count = 0;
if (rs.next()) {
    count = rs.getInt(1);
}