3 Replies Latest reply on Aug 7, 2012 12:13 PM by dadams07

    Pagination with orcale and jsp

    951986
      hi, i m new to java can any one tell me how to do pagination with my this codes


      String sqlCount = null;
      String sql = null;

      sql = "select * from HOME_LEASE_ ";
      sqlCount = "select count(*)from HOME_LEASE_ ";

      Enumeration e = request.getParameterNames();
      int i = 0;
      while(e.hasMoreElements()){

      String name = (String)e.nextElement();
      String search = new String(request.getParameter(name)).toUpperCase();

      if(i != 0 && (!search.isEmpty())){
      sql = sql +" and " + name +" like'" + search + "%'" ;
      sqlCount = sqlCount + " and " name" like '" + search + "%'" ;

      }else if((!search.isEmpty()) && (i == 0) ){
      sql = sql + " where " + name +" like '" + search + "%'" ;
      sqlCount = sqlCount + " where " + name+" like '" + search + "%'" ;
      ++i;
      }
      }

      if((sql != null) && (sqlCount != null)){
      DataAccess dB = new DataAccess();
      Connection conn = dB.getConnection();
      Statement stmtCount = dB.createStmt(conn);
      ResultSet rsCount = dB.executeQuery(sqlCount,stmtCount);
      rsCount.next();
      Statement stmt = dB.createStmt(conn);
      ResultSet rs = dB.executeQuery(sql,stmt);

      <% if(rs == null)
      out.println("<h3>No data found</h3>");
      else
      //out.println(rs.next());
      while(rs.next()) {
      //out.println("inside the result set");
      out.println("<tr>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("phase") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("file_no") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getString("se") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getString("str") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getString("qrno") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getString("typ") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("perno") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getString("name") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("total_plot_area_ocp") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("TOT_PREM_AREA") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getInt("total_construction") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getLong("chargbl_construction") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getLong("add_open_construction") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getLong("TOT_PREM_AREA") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getLong("plinth") + "</a></td>");
      out.println( "<td><a href=survey.jsp?phase="+ rs.getString("phase")+"&file_no="+rs.getString("FILE_NO")+">"+ rs.getLong("std_plot_area") + "</a></td>");
      out.println("</tr>");
      }
      dB.close(rsCount);
      dB.close(stmtCount);
      dB.close(rs);
      dB.close(stmt);
      dB.close(conn);
      }else{
      out.println("<center style=\"background-color:red\">Please enter values in text fields<h1></h1></center>");
      }
      %>
        • 1. Re: Pagination with orcale and jsp
          939520
          After examining your code, I think:

          You are not using JSP/HTML properly (using java to write out HTML). You can spend a tremendous amount of time with trial and error approaches in learning a technology and still not get much real understanding for your effort. I suggest a better use of those hours would be spent in reading a book on Java first, then a book on JSP. For a book on JSP, go to amazon.com and search for 'Javaserver pages'. Find a book that isn't too old and that has good reviews. Here is one (although its a bit dated):

          http://www.amazon.com/JavaServer-Pages-Edition-Hans-Bergsten/dp/0596005636/ref=sr_1_1?s=books&ie=UTF8&qid=1338821741&sr=1-1

          As far your database code goes, I suggest you read on-line articles on JDBC on how to get/use/close a database connection in a try/catch/finally block. The article will probably also help you write better SQL too. I don't think your question on pagination should be tackled until you learn JSP.
          1 person found this helpful
          • 2. Re: Pagination with orcale and jsp
            gimbal2
            Excellent advice. This online book is very good and free. Still old but then again servlet & JSP technology is very old.

            http://pdf.coreservlets.com/
            • 3. Re: Pagination with orcale and jsp
              dadams07
              A more robust solution, if you're not forced to use the old JSP technology, is Wicket. Its IDataProvider interface handles the issue quite nicely with its iterator() method. We use it extensively, with a Javascript/Ajax front end to handle user page navigation.