CODEWITHSUNDEEP
javasqloracle-databasejspservlets
Tom
Tom

Reputation: 771

fetch 2nd row, 3rd row....200 row of a table in oracle by servlet

I have a table named as abc in oracle and 4 records as follows:

   F         S        
  apple    mango 
  tiger    lion
  oak      banana
  pink     orange

home.jsp

 <form action="NewServlet" method="post">
 <input type="text" name="count"  value="first" style="display:none;">
 <input type="submit" value="NEXT">
 </form>

NewServlet.java

if(request.getParameter("count").equals("first")){// when i clicked on next button having value first

    rs=st.executeQuery("SELECT * FROM abc ORDER BY 'F','S' FETCH FIRST 1 ROWS ONLY"); 
    }// fetches apple and mango
    else{
rs=st.executeQuery("SELECT * FROM abc ORDER BY 'F','S' OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY");// when i clicked on next button having value second (in display.jsp)
        }// fetches tiger and lion

request.getRequestDispatcher("display.jsp").forward(request, response);

display.jsp

<form action="NewServlet" method="post">
<input type="text" name="count"  value="second" style="display:none;">
<input type="submit" value="NEXT">
</form>

 String abc="", abc1=""
 abc= (String) request.getAttribute("fi");// prints apple 1st time and tiger 2nd time but 3rd and 4th record is not fetched
 abc1= (String) request.getAttribute("fi1");//prints mango 1st time and lion 2nd time but 3rd and 4th record is not fetched

How to fetch 3rd and 4th record and so on up to 100th row when I will keep clicking on NEXT button which is present in both home.jsp and display.jsp?

Upvotes: 3

Views: 644

Answers (1)

Titus
Titus

Reputation: 22474

You can use a count variable which gets passed as a request attribute. For example:

NewServlet.java

String count = request.getParameter("count");
int c = 0;
String query;
if(count != null && count.matches("\\d+")){
    c = Integer.parseInt(count);
    query = "SELECT * FROM abc ORDER BY 'F','S' OFFSET "+String.valueOf(c)+" ROWS FETCH NEXT 1 ROWS ONLY";
}else{
   query = "SELECT * FROM abc ORDER BY 'F','S' FETCH FIRST 1 ROWS ONLY";
}
request.setAttribute("count",++c);

display.jsp

<input type="text" name="count"  value="<%=(Integer)request.getAttribute("count")%>" style="display:none;">

home.jsp

 <form action="NewServlet" method="post">
 <input type="submit" value="NEXT">
 </form>

Remove the count input from home.jsp so that the first time you call the servlet the count parameter will be null.

Upvotes: 2

Related Questions