QuestionAsk Questions?
abhinav
how to retrieve data from mysql database in grid form using html and servlets? urgent....
View Answers
November 8, 2011 at 11:39 AM
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class PaginationInServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)throws IOException, ServletException{
PrintWriter out = response.getWriter();
Connection conn = null;
try{
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=5;
int totalRecords=5;
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");
System.out.println(totalRows);
}
out.println("<html><h3>Pagination of JSP page</h3><body><form>");
out.println("<input type='hidden' name='iPageNo' value='<%=iPageNo%>'>");
out.println("<input type='hidden' name='cPageNo' value='<%=cPageNo%>'>");
out.println("<input type='hidden' name='showRows' value='<%=showRows%>'>");
out.println("<table width='100%' cellpadding='0' cellspacing='0' border='1' >");
out.println("<tr><td>Roll No</td><td>Name</td><td>Marks</td><td>Grade</td></tr>");
while(rs1.next()){
out.println("<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();
}
out.println("<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){
out.println("<a href=PaginationInServlet?iPageNo="+prePageNo+"&cPageNo="+prePageNo+"'> << Previous</a>");
}
for(i=((cPage*totalRecords)-(totalRecords-1));i<=(cPage*totalRecords);i++){
if(i==((iPageNo/showRows)+1)){
out.println("<a href=PaginationInServlet?iPageNo="+i+"style=cursor:pointer;color: red><b>"+i+"</b></a>");
}
else if(i<=totalPages){
out.println("<a href=PaginationInServlet?iPageNo="+i+">"+i+"</a>");
}
}
if(totalPages>totalRecords && i<totalPages){
out.println("<a href=PaginationInServlet?iPageNo="+i+"&cPageNo="+i+"> >> Next</a>");
}
}
out.println("<b>Rows "+startResult+"-"+endResult+"Total Rows"+totalRows+"</b></div></td></tr></table></form></body></html>");
November 8, 2011 at 11:39 AM
continue..
try{
if(ps1!=null){
ps1.close();
}
if(rs1!=null){
rs1.close();
}
if(ps2!=null){
ps2.close();
}
if(rs2!=null){
rs2.close();
}
if(conn!=null){
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
catch(Exception ex){}
}
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;
}
}
For the above code, we have used following database table:
CREATE TABLE `student` ( `rollNo` bigint(40) NOT NULL auto_increment, `name` varchar(40) default NULL, `marks` varchar(40) default NULL, `grade` varchar(40) default NULL, PRIMARY KEY (`rollNo`) )
Related Pages:
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
