CODEWITHSUNDEEP
javasqloracle-databasejspservlets
Tom
Tom

Reputation: 771

fetch record in oracle in auto increment way from 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

I want to fetch these above records one after another when a user clicks on NEXT button like this:

home.jsp-----------------

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

After clicking NEXT button 1st row (Apple and Mango) is fetched from servlet NewServlet.java:

NewServlet.java---------------

 ResultSet rs=st.executeQuery("Select * from abc"); 

      if (rs.next()) {   // i have used if in stead of while                  
      rs.getString("F");
       rs.getString("S");
           }
request.setAttribute("fi", rs.getString("F"));   
request.setAttribute("fi1", rs.getString("S"));  
request.getRequestDispatcher("result.jsp").forward(request, response);

result.jsp-----------

  String abc="", abc1=""
 abc= (String) request.getAttribute("fi");
  abc1= (String) request.getAttribute("fi1");
 out.println(abc);// displays Apple
  out.println(abc1);// displays Mango

Here I have added the NEXT button and when user clicks NEXT button next row that is tiger and lion ought to be fetched from database and to be displayed ,how it is possible????What type of modification should I do in sql statement in NewServlet.java?? or in the 2 jsp pages??

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

Thanks in advance

Upvotes: 0

Views: 624

Answers (1)

Titus
Titus

Reputation: 22474

You can try something like this:

home.jsp

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

result.jsp

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

NewServlet.java

String query;
if(request.getParameter("count").equals("first")){
     query = "SELECT * FROM abc ORDER BY [someColumn] FETCH FIRST 1 ROWS ONLY"
}else{
     query = "SELECT * FROM abc ORDER BY [someColumn] OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY" 
}

Like I said in the comments section, you can do database operation directly in the jsp pages and even if you couldn't this is a unusual way to achieve what you want.

My suggestion will be to have only one jsp page which retrieves the value from the database and use the offset based on a request parameter.

for example:

single.jsp

  <% 
     String count = request.getParameter("count");
     int c;
     if(count!= null && conut.match("\\d+")){
         c = Integer.parseInt(count);
         c++;
     }else{
         c = 0;
     }
     String query = "SELECT * FROM abc ORDER BY [someColumn] OFFSET "+String.valueOf(c)+" ROWS FETCH NEXT 1 ROWS ONLY"
  %>
   <form action="single.jsp" method="post">   
     <input type="text" name="count"  value="<%=c%>"  style="display:none;">   
     <input type="submit" value="NEXT">   
   </form>

I haven't used jsp in a while, the syntax may be off.

Upvotes: 1

Related Questions