I have reduce some JSP code by using the SQL script to get and return the number of rows from database. I am using MySQL as the database and using the LIMIT clause to retrieve the number of rows to display on single page.
<%@ 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>
<%
int id = 0;
String fullname = "";
String country = "";
int recordPerPage = 2;
int gotoPage = 0;
boolean moreResult = false;
Connection conn = null;
Statement stat = null;
//get page
try {
gotoPage = Integer.parseInt(request.getParameter("gotoPage"));
} catch (NumberFormatException e ) {
gotoPage = 0;
}
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();
String offset = String.valueOf(gotoPage * recordPerPage);
String rows = String.valueOf(recordPerPage);
//execute query using SELECT and LIMIT clause.
//LIMIT accept 2 argument.
//the first represent the offset of the first row to return
//the second represent the maximum number of rows to return
String sql = "SELECT id, fullname, country FROM customer LIMIT "+offset+" , "+rows;
ResultSet resultSet = stat.executeQuery(sql);
out.println("<table border='1'><th>ID</th><th>Name</th><th>Country</th>");
int totalRecord = 0;
for (; resultSet.next(); totalRecord++) {
id = (int)resultSet.getInt("id");
fullname = (String)resultSet.getString("fullname");
country = (String)resultSet.getString("country");
out.println("<tr><td>"+id+"</td><td>"+fullname+"</td><td>"+country+"</td></tr>");
}
out.println("</table>");
out.println("<br>");
if(totalRecord == recordPerPage)
moreResult = true;
//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();
}
if ( gotoPage == 0 ) {
out.print("Previous");
} else {
out.print("<a href=\"limit.jsp?gotoPage="+(gotoPage - 1)+"\"><b>Previous</b></font></a>");
}
out.print(" ");
if (!moreResult) {
out.print("Next");
} else {
out.print("<a href=\"limit.jsp?gotoPage="+(gotoPage + 1)+"\"><b>Next</b></a>");
}
%>
</form>
</body>
</html>
No comments:
Post a Comment