How do I change the while loop in this code to the range with range list style display page (like search results on google) for a ResultSet() in jsp?

Ads

 
 
 

Share on Google+Share on Google+

Jerome Lepinski
How do I change the while loop in this code to the range with range list style display page (like search results on google) for a ResultSet() in jsp?
2 Answer(s)      4 years and 11 months ago
Posted in : JSP-Servlet
<%@ page language="java" import="java.sql.*" %>
<html>
<head>
<title>Display data from database in jsp</title>
</head>
<body>
<table border="0" width="75%" cellspacing="0" cellpadding="0">
<tr>
<td width="100%">
<h2><font color="#FF0033">Display data from database in jsp</font></h2>
<form method="POST" >
<table border="1" width="75%" cellspacing="0" cellpadding="0" bgcolor="#CCFFCC">

<%
Connection con = null;
String url = "jdbc:mysql://192.168.10.211:3306/";;
String db = "amar";
String driver = "com.mysql.jdbc.Driver";
int rows = 0;
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"amar","amar123");
try{
Statement st = con.createStatement();
String query = "select * from user_Register";
ResultSet rs = st.executeQuery(query);

while (rs.next()) {
rows =rs.getInt(1);

%>
result set of unknown size

Display rows 1-10 range

Display links to
    first (after first range)
    previous (after first range)
    11-20
    21-30
    next (when applicable)
    last (when not on last range)

refreshes automatically like an eBay or Google search page. 
<%}

out.println("Total row is = "+rows);
rs.close();
con.close();

}
catch (SQLException ex){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
%>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>
Ads
View Answers

June 12, 2012 at 11:18 AM


The given jsp code retrieves the record from the database and display 10 records per page. Here is the code: pagination.jsp

<%@ page language="java" %>
<%@ page import="java.sql.*" %>
<%!
public int nullIntconvert(String str){
int num=0;
if(str==null) {
str="0";
}
else if((str.trim()).equals("null")) {
str="0";
}
else if(str.equals("")) {
str="0";
}
try{
num=Integer.parseInt(str);
}
catch(Exception e) { }
return num;
}
%>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
ResultSet rs1 = null;
ResultSet rs2 = null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;

int showRows=10;
int totalRecords=10;
int totalRows=nullIntconvert(request.getParameter("totalRows"));
int totalPages=nullIntconvert(request.getParameter("totalPages"));
int iPageNo=nullIntconvert(request.getParameter("iPageNo"));
int cPageNo=nullIntconvert(request.getParameter("cPageNo"));

int startResult=0;
int endResult=0;
if(iPageNo==0) {
iPageNo=0;
}
else{
iPageNo=Math.abs((iPageNo-1)*showRows);
}
String query1="SELECT SQL_CALC_FOUND_ROWS * FROM student limit "+iPageNo+","+showRows+"";
ps1=conn.prepareStatement(query1);
rs1=ps1.executeQuery();

String query2="SELECT FOUND_ROWS() as cnt";
ps2=conn.prepareStatement(query2);
rs2=ps2.executeQuery();
if(rs2.next()) {
totalRows=rs2.getInt("cnt");
}
%>
<html>
<h3>Pagination of JSP page</h3>
<body>
<form>
<input type="hidden" name="iPageNo" value="<%=iPageNo%>">
<input type="hidden" name="cPageNo" value="<%=cPageNo%>">
<input type="hidden" name="showRows" value="<%=showRows%>">
<table width="100%" cellpadding="0" cellspacing="0" border="1" >
<tr>
<td>Roll No</td>
<td>Name</td>
<td>Marks</td>
<td>Grade</td>
</tr>
<%
while(rs1.next()) {
%>
<tr>
<td><%=rs1.getInt("rollNo")%></td>
<td><%=rs1.getString("name")%></td>
<td><%=rs1.getInt("marks")%></td>
<td><%=rs1.getString("grade")%></td>
</tr>
<%
}
%>
<%
try{
if(totalRows<(iPageNo+showRows)) {
endResult=totalRows;
}
else{
endResult=(iPageNo+showRows);
}
startResult=(iPageNo+1);
totalPages=((int)(Math.ceil((double)totalRows/showRows)));
}
catch(Exception e){
e.printStackTrace();
}
%>


June 12, 2012 at 11:19 AM


continue..

<tr>
    <td colspan="3">
    <div>
    <%
    int i=0;
    int cPage=0;
    if(totalRows!=0) {
    cPage=((int)(Math.ceil((double)endResult/(totalRecords*showRows))));

    int prePageNo=(cPage*totalRecords)-((totalRecords-1)+totalRecords);
    if((cPage*totalRecords)-(totalRecords)>0){
    %>
    <a href="pagination.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>"> << Previous</a>
    <%
    }
    for(i=((cPage*totalRecords)-(totalRecords-1));i<=(cPage*totalRecords);i++){
    if(i==((iPageNo/showRows)+1)){%>
    <a href="pagination.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a>
    <%
    }
    else if(i<=totalPages){
    %>
    <a href="pagination.jsp?iPageNo=<%=i%>"><%=i%></a>
    <%
    }
    }
    if(totalPages>totalRecords && i<totalPages){
    %>
    <a href="pagination.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>"> >> Next</a>
    <%
    }
    }
    %>
    <b>Rows <%=startResult%> - <%=endResult%> Total Rows <%=totalRows%> </b>
    </div>
    </td>
    </tr>
    </table>
    </form>
    </body>
    </html>

In the above code,we have taken the database table student(rollNo,name,marks,grade).

For more information, visit the following link:

http://www.roseindia.net/jsp/paging.shtml


Ads









Related Tutorials/Questions & Answers:
Tutorials   
Java Spring Hibernate Struts Training fat image from db fat image from db fat image from db fat image from db unable to get value returned from javascript variable in page How to change image size before adding to mysql using jsp Design a registration.jsp page with input for user registration details Cart Quantity jsp servlet coding issue servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? servlet,jsp login registration forms without database in ecalipse ide using hashmap to store the registraion details ? image pdf marking pdf marking Multiple image upload using JSP Read code from excel sheet and upload into database using JSP Student java about search engine Convert BLOB to PDF and save it to a location how to copy files from remote to local machine using SFTP SSL in java? Like Team viewer Socket and Servert -Jsp how to copy files from remote location to local using java? How To Store Multilple Images In MySQL database dynamically with image uploader and then retrieve them in image gallery using java how to create SOAP based web service in java? how to fetch data from mysql database table and draw a bar chart on that data using in jsp how to create users in LDAP using java? how to authenticate user using TIVOLI api in java? how to create users using IBM Tivoi API in jsp/servelet? Java/J2ee Please explain this code as many packages and classes are unknown to me .! how to download the uploaded folder files using jsp Login Query image is display from path of mysql database please reply the code to store dynamic fields in database? Reloading issue in jsp How to get Button Id ? calling one jap page from another jsp page login login Problem to get connection from DAO class to JDBC Page Loading Time Fix table's column's name row(1st row of the table) so that it does not move up when the table is scrolled up to view more rows below Sending hidden values

Ads

 
Advertisement null

Ads