0

I have the java code to implemented jsp page, here how to view the pages 100

<%
         PreparedStatement preparedStatement = null;
       PreparedStatement preparedStatements = null;
       ResultSet rs4 = null, rss = null, rst = null;
       java.sql.Timestamp timest;
       DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

       Connection conn = null;
       Timestamp timestamp;
       Long output = null;
       long Min = 0, Max = 0, Final = 0, Min1 = 0, Max1 = 0, Final1 = 0, resultss = 0;
       String disHour = null, disMinu = null, disSec = null;
       String disHour3 = "", disMinu3 = "", disSec3 = "", disHour4 = "", disMinu4 = "", disSec4 = "";
       double addLong = 0, subLong = 0;
       double addLat = 0, subLat = 0;
       String cLocation = "", state = "", City = "", distance = "", speeds = "";
       Statement stmt1 = null, stmts = null;
       ResultSet rsts = null;
        Set<String> dates = new HashSet<String>();
        ArrayList<String> b=new ArrayList<String>();
        ArrayList<Long> n=new ArrayList<Long>();
        ArrayList<String> a=new ArrayList<String>();
        ArrayList<String> xy=new ArrayList<String>();
        ArrayList<String> xy1=new ArrayList<String>();
        ArrayList<String> xy2=new ArrayList<String>();
        ArrayList<String> xy3=new ArrayList<String>();
        ArrayList<String> xy4=new ArrayList<String>();
        ArrayList<String> xy5=new ArrayList<String>();
        ArrayList<String> xy6=new ArrayList<String>();
        ArrayList<String> xy7=new ArrayList<String>();
        ResultSet rsPagination = null;
        ResultSet rsRowCnt = null;

        PreparedStatement psPagination = null;
        PreparedStatement psRowCnt = null;

        int iShowRows =1000;  // Number of records show on per page
        int iTotalSearchRecords = 10000;
        // Number of pages index shown
        int iTotalRows=0;
        //int iTotalPages=10000;
        //int iPageNo=0;
        //int iTotalRows = nullIntconv(request.getParameter("iTotalRows"));
        int iTotalPages = nullIntconv(request.getParameter("iTotalPages"));
        int iPageNo = nullIntconv(request.getParameter("iPageNo"));
        int cPageNo = nullIntconv(request.getParameter("cPageNo"));

        int iStartResultNo = 0;
        int iEndResultNo = 0;

        if (iPageNo == 0) {
            iPageNo = 0;
            
        } else {
            iPageNo = Math.abs((iPageNo - 1) * iShowRows);
            System.out.println(iPageNo);
        }

         preparedStatement = connection.prepareStatement("select gpstime, Longi, Lat, speed from xydata where objectId= '" + Objectid + "'  AND clientId = '" + id + "' AND gpstime >= '" + from+"  " +result + "'  AND gpstime <= '" + to+"  " +result1 + "' AND speed > '3' order by gpstime asc limit " + iPageNo + "," + iShowRows + "");
         preparedStatements = connection.prepareStatement("select SQL_CALC_FOUND_ROWS Speed, MIN(navl.xydata.GpsTime) as mn, MAX(navl.xydata.GpsTime) as mx from navl.xydata where navl.xydata.ObjectId='" + Objectid + "' and navl.xydata.ClientId='" + id + "' and navl.xydata.GpsTime > '" + from+"  " +result + "' and  navl.xydata.GpsTime < '" + to+"  " +result1 + "' and speed='0' order by gpstime asc limit " + iPageNo + "," + iShowRows + "");
         rs4 = preparedStatement.executeQuery();
         rss = preparedStatements.executeQuery();

        //String sqlPagination = "select SQL_CALC_FOUND_ROWS navl.xydata.Lat,navl.xydata.Longi,MIN(navl.xydata.GpsTime) as mn, MAX(navl.xydata.GpsTime) as mx, timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,navl.xydata.GpsTime from navl.xydata where navl.xydata.ObjectId='" + Objectid + "' and navl.xydata.ClientId='" + id + "' and navl.xydata.GpsTime > '" + from+"  " +result + "' and  navl.xydata.GpsTime < '" + to+"  " +result1 + "' AND navl.xydata.Speed = '0' group by navl.xydata.Lat order by navl.xydata.GpsTime asc  limit " + iPageNo + "," + iShowRows + "";
        String sqlRowCnt = "SELECT FOUND_ROWS() as cnt";
        psRowCnt = connection.prepareStatement(sqlRowCnt);
        rsRowCnt = psRowCnt.executeQuery();

     //   if (rsRowCnt.next())
     //   {
     //       iTotalRows = rsRowCnt.getInt("cnt");
     //   }
%>
<%
              String home = System.getProperty ("user.dir") + "/domains/domain1/docroot/CKML";
              //String home = "/home/navl/glassfishv3/glassfish/domains/domain1/docroot" + "/CKML";
              String folder2 = "/KML";
              String cid = "Cid"+ id;
             // String oid = "Oid"+ Objectid;
              String EA = "/stopReports";
              FileWriter fw ;
              File fi = new File(home + folder2 + "/CKBI" + "/"+cid + EA);
              String filename = "";
              fi.mkdirs();
             


         PreparedStatement ps = null;
        if (from != null && from != "" && to != null && to != "")
            {
            filename = fi.getPath() + "/" + dis+".xls";
            fw = new FileWriter(filename);
            fw.append("Navyani Innovations Inc.");
            fw.append('\n');
            fw.append('\n');
            fw.append("Stop Time Report for " + dis);
            fw.append('\n');
            fw.append("From");
            fw.append('\t');
            fw.append("To");
            fw.append('\t');
            fw.append("Current Location");
            fw.append('\t');
            fw.append("City");
            fw.append('\t');
            fw.append("State");
            fw.append('\t');
            fw.append("Stop Time");
            fw.append('\n');

            if(rss.next())
          {
          speeds = rss.getString("speed");
         int speede=Integer.parseInt(speeds);

         String max=rss.getString("mx");
         String min=rss.getString("mn");
         System.out.println(min);
         while ( rs4.next() )
         {

            String gpstime = rs4.getString("GpsTime");
             String speed = rs4.getString("speed");
            if (dates.add(gpstime))
            {
              int spee=Integer.parseInt(speed);
                if(spee>3)
                {
               String result11 = gpstime.substring(0, 19);
               a.add(result11);
               java.util.Date date = sdf.parse(result11);
               timest = new java.sql.Timestamp(date.getTime());
               resultss=timest.getTime()/1000;
               n.add(resultss);
                 }

            }


         }



         int i=0;

         if(speede==0)
         {
            java.util.Date date1 = sdf.parse(min);
               timestamp = new java.sql.Timestamp(date1.getTime());
               Min=timestamp.getTime()/1000;
             String mini = min.substring(0, 19);

            java.util.Date date2 = sdf.parse(a.get(0));
               timestamp = new java.sql.Timestamp(date2.getTime());
               Max=timestamp.getTime()/1000;
               Final=Max-Min;
              int hours3 = (int) (Final / 3600),
                                 remainder3 = (int) (Final % 3600),
                                 minutes3 = remainder3 / 60,
                                 seconds3 = remainder3 % 60;

                             disHour3 = (hours3 < 10 ? "0" : "") + hours3;
                             disMinu3 = (minutes3 < 10 ? "0" : "") + minutes3;
                             disSec3 = (seconds3 < 10 ? "0" : "") + seconds3;


              if(minutes3>1)
              {
               int disMinu8=Integer.parseInt(disMinu3);
               int dismin1=disMinu8-1;
               String disM = (dismin1 < 10 ? "0" : "") + dismin1;
               xy.add(mini);
               b.add(a.get(0));
               xy1.add(""+disHour3 +":"+ disM+":"+disSec3);
               }
            for(i=0; i<n.size()-1;)
           {
            output = n.get(i+1)-n.get(i);
            int hours2 = (int) (output / 3600),
                                 remainder2 = (int) (output % 3600),
                                 minutes2 = remainder2 / 60,
                                 seconds2 = remainder2 % 60;

                             disHour = (hours2 < 10 ? "0" : "") + hours2;
                             disMinu = (minutes2 < 10 ? "0" : "") + minutes2;
                             disSec = (seconds2 < 10 ? "0" : "") + seconds2;
                if(minutes2>1)
              {
               int disMinu1=Integer.parseInt(disMinu);
               int dismin=disMinu1-1;
               String disMinu2 = (dismin < 10 ? "0" : "") + dismin;
               xy.add(a.get(i));
               b.add(a.get(i+1));
               xy1.add(""+disHour +":"+ disMinu2+":"+disSec);
               }
             i++;
           }

            java.util.Date date3 = sdf.parse(a.get(i));
               timestamp = new java.sql.Timestamp(date3.getTime());
               Min1=timestamp.getTime()/1000;


           java.util.Date date4 = sdf.parse(max);
               timestamp = new java.sql.Timestamp(date4.getTime());
               Max1=timestamp.getTime()/1000;
               String mamm = max.substring(0, 19);
               Final1=Max1-Min1;
              int hours4 = (int) (Final1 / 3600),
                                 remainder4 = (int) (Final1 % 3600),
                                 minutes4 = remainder4 / 60,
                                 seconds4 = remainder4 % 60;

                             disHour4 = (hours4 < 10 ? "0" : "") + hours4;
                             disMinu4 = (minutes4 < 10 ? "0" : "") + minutes4;
                             disSec4 = (seconds4 < 10 ? "0" : "") + seconds4;

           if(minutes4>1)
              {
               int disMinu7=Integer.parseInt(disMinu4);
               int dismi=disMinu7-1;
               String disMi = (dismi < 10 ? "0" : "") + dismi;
               xy.add(a.get(i));
               b.add(mamm);
               xy1.add(""+disHour4 +":"+ disMi+":"+disSec4);
               b.add(mamm);
               }
         }
         else
         {


           for(i=0; i<n.size()-1;)
              {
              output = n.get(i+1)-n.get(i);
              int hours3 = (int) (output / 3600),
                                 remainder3 = (int) (output % 3600),
                                 minutes3 = remainder3 / 60,
                                 seconds3 = remainder3 % 60;

                             disHour3 = (hours3 < 10 ? "0" : "") + hours3;
                             disMinu3 = (minutes3 < 10 ? "0" : "") + minutes3;
                             disSec3 = (seconds3 < 10 ? "0" : "") + seconds3;

               if(minutes3>1)
              {
               xy.add(a.get(i));
               b.add(a.get(i+1));
               xy1.add(""+disHour3 +":"+ disMinu3+":"+disSec3);
               }
             i++;
              }

         }

     for(int k=0; k<xy.size();)
        {
         String DateTime=xy.get(k);
         String DateTime1=b.get(k);
          stmt =connection.createStatement();
          String rrr = "SELECT xydata.Longi, xydata.Lat, xydata.GpsTime, xydata.ObjectId, xydata.ClientId FROM xydata WHERE xydata.ClientId =  '"+id+"' AND xydata.ObjectId =  '"+Objectid+"' AND xydata.GpsTime >  '"+DateTime+"' AND xydata.GpsTime <  '"+DateTime1+"' AND xydata.Speed =  '0' ORDER BY xydata.GpsTime ASC limit 1";
          rst = stmt.executeQuery(rrr);
         while(rst.next())
         {
               Lat =     rst.getString("Lat");
               Longi =   rst.getString("Longi");
                addLong = Double.parseDouble(Longi) + 0.08;
                subLong = Double.parseDouble(Longi) - 0.08;
                addLat = Double.parseDouble(Lat) + 0.08;
                subLat = Double.parseDouble(Lat) - 0.08;
                stmts =connection.createStatement();
                String query = "SELECT FULL_NAME_ND, ((ACOS(SIN('" + Lat + "' * PI() / 180) * SIN(geodb.geonames.LAT * PI() / 180) + COS('" + Lat + "' * PI() / 180) * COS(geodb.geonames.LAT * PI() / 180) * COS(('" + Longi + "' - geodb.geonames.LONGI) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance, FULL_NAME,SORT_NAME FROM geodb.geonames WHERE geodb.geonames.LONGI > '" + subLong + "' AND geodb.geonames.LONGI <  '" + addLong + "' AND geodb.geonames.LAT >  '" + subLat + "' AND geodb.geonames.LAT < '" + addLat + "' HAVING distance < '8' ORDER BY distance ASC Limit 1";
                rsts = stmts.executeQuery(query);
                if(rsts.next())
                {
                 City = rsts.getString("FULL_NAME");
                    cLocation = rsts.getString("FULL_NAME_ND");
                    state = rsts.getString("SORT_NAME");
                    distance = rsts.getString("distance");
                    //System.out.println(distance);
                    //System.out.println(distance.substring(0, 5));

                    xy2.add(City);
                    xy3.add(cLocation);
                    xy4.add(state);
                    xy5.add(distance);
                    xy6.add(Lat);
                    xy7.add(Longi);

                }


           }


          k++;
     }

          for(int m=0; m<xy.size();)
         {
               java.util.Date date6 = sdf.parse(xy.get(m));
               timestamp = new java.sql.Timestamp(date6.getTime());
               String M=timestamp.toLocaleString();

                java.util.Date date7 = sdf.parse(b.get(m));
               timestamp = new java.sql.Timestamp(date7.getTime());
               String Mi=timestamp.toLocaleString();
                xy5.get(m);
                System.out.println(xy5.get(m).substring(0, 5));
             System.out.println(M+ " To "  +Mi+" " +xy3.get(m)+" " +xy2.get(m)+ " "+xy4.get(m)+ "=> "+xy1.get(m));
        
        



        %>
        <tbody>
            <tr onclick="Location(<%=xy6.get(m) %>,<%=xy7.get(m)%>);" style="background-image:url(http://www.navyani.com/navl/images/redrow.png);text-align:left;" >
            <td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;" height="20" title="Date/Time"><span class="style8">&nbsp;<%=M%></span></td>
            <td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;" height="20" title="Date/Time"><span class="style8">&nbsp;<%=Mi%></span></td>
            <td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;"><span class="style8" title="Current Location">&nbsp;<%=xy3.get(m) %> (<%=xy5.get(m).substring(0, 5)%>)</span></td>
            <td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;"><span class="style8" title="City">&nbsp;<%=xy2.get(m)%></span></td>
            <td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;"><span class="style8" title="State">&nbsp;<%=xy4.get(m)%></span></td>
             <%
            fw.append(M+ "");
            fw.append('\t');
            fw.append(Mi+ "");
            fw.append('\t');
            fw.append(xy3.get(m)+"");
            fw.append('\t');
            fw.append(xy2.get(m)+"");
            fw.append('\t');
            fw.append(xy4.get(m)+"");
%>
<td style="height:18px; text-align:left; border-bottom-style: solid; border-bottom-width: thin;  border-bottom-color: silver;font-family: Arial;font-size: 8pt;font-style:normal;font-weight: normal;" height="20" title="Stop Time"><span class="style8">&nbsp;<%=xy1.get(m)%></span></td>

        </tr>


        <%
            fw.append('\t');
            fw.append(xy1.get(m)+ "hh:mm:ss");
            fw.append('\n');
            //arr.clear();
            m++; }
         xy6.clear();
         xy7.clear();
             fw.flush();
             fw.close();

        }
            }
        %>

how to write the code to view the pages 100 by 100(previous page and next page)1,2,3 like this

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by peter_budo
0

Give your self slap on hand for using database connectivity directly for page view.

Pagination article suggested by ~s.o.s~ in regards of pagination implementation

Edited by peter_budo: Spelling

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.