Here are some simple codes to display record within a page. User will click on the button to browse through the specific records.
<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>
<%!
static final String ctxstr = "java:comp/env/jdbc/navigationDB";
%>
<html>
<head>
<title>Database Navigation using Next and Previous button</title>
</head>
<body>
<form>
<h1>Database Navigation using Next and Previous link</h1>
<%
String fullname = "";
String country = "";
int counter = 0;
//total result for each page
int recordsPerPage = 5;
//the default page
int gotoPage = 1;
//get page
try {
gotoPage = Integer.parseInt(request.getParameter("gotoPage"));
} catch (NumberFormatException e ) {
gotoPage = 1;
}
Connection conn = null;
Statement stat = null;
try {
//lookup for the JNDI datasource
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(ctxstr);
//create the database connection
conn = ds.getConnection();
stat = conn.createStatement();
//execute query
String sql = "SELECT fullname, country FROM customer";
ResultSet resultSet = stat.executeQuery(sql);
//skip the records which not require within the page
int row = (gotoPage - 1) * recordsPerPage + 1;
for(int x=1; x<row; x++)
resultSet.next();
out.println("<table border='1'><th>Name</th><th>Country</th>");
//display only record which require within the page
while ( (counter < recordsPerPage) && resultSet.next() ) {
fullname = (String)resultSet.getString("fullname");
country = (String)resultSet.getString("country");
out.println("<tr><td>"+fullname+"</td><td>"+country+"</td></tr>");
counter++;
}
out.println("</table>");
out.println("<br>");
//create next and previous link
boolean moreResult = resultSet.next();
if ( gotoPage == 1 ) {
out.print("Previous");
} else {
out.print("<a href=\"index.jsp?gotoPage="+(gotoPage - 1)+"\"><b>Previous</b></font></a>");
}
out.print(" ");
if (!moreResult) {
out.print("Next");
} else {
out.print("<a href=\"index.jsp?gotoPage="+(gotoPage + 1)+"\"><b>Next</b></a>");
}
//close connection
resultSet.close();
stat.close();
stat = null;
conn.close();
conn = null;
} catch(SQLException e) {
out.println("An error occured: " + e.toString());
return;
} finally{
if(conn != null)
conn.close();
}
%>
</form>
</body>
</html>
Next Record
if (!rs.isLast()) {
rs.next();
//retrieve the value
}
Previous Record
if (!rs.isFirst()) {
rs.previous();
//retrieve the value
}
Last Record
rs.last();
First Record
rs.first();
Goto Record
rs.absolute();
By keeping the ResultSet open, each code above can be define within the respective buttons’ ActionListener().
No comments:
Post a Comment