<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%!
public String paging_next_previous(int prev, int next, int records, String css_className)
{
String paging_str = "";
paging_str = paging_str + "";
paging_str = paging_str + " | ";
if (prev >= 0)
{
paging_str = paging_str + "<< Previous";
}
else
{
paging_str = paging_str + "";
}
paging_str = paging_str + " | ";
paging_str = paging_str + " ";
if (next < records)
{
paging_str = paging_str + "Next >>";
}
else
{
paging_str = paging_str + "";
}
paging_str = paging_str + " | ";
paging_str = paging_str + "
";
return paging_str;
}
%>
<%
int pgsize = 10; // how many records will be displayed on a page
int records = 0; // total number of records in the db
int offset = 0; // page offset starting record number of the page
if (request.getParameter("os") != null)
{
//when user clicks on next or prev link we need to get offset value from request
offset = Integer.parseInt(request.getParameter("os"));
}
String whereClause = "";
/*
Let I have a table called test_table it has following columns
id int
name varchar
email varchar
*/
Connection conn;
try{
//Connection creation Code here.
Statement stmt = conn.createStatement();
String sql = "SELECT count(*) as records FROM test_table " + whereClause;
ResultSet results = stmt.executeQuery(sql);
if (results.next())
{
records = results.getInt("records");
}
results.close();
stmt.close();
stmt = conn.createStatement();
sql = "SELECT * FROM test_table "+ whereClause +" LIMIT "+ offset +", "+ pgsize;
results = stmt.executeQuery(sql);
int next = offset + pgsize;
int prev = (int)offset - pgsize;
%>
Paging Demo
<%}catch(Exception e){%>
Error Occured: <%=e.getMessage()%>
<%}finally{
conn.close();
}%>