Pass a resultset object from a servlet to jsp

[visu Nekk]

package com.choral.be;
 
import com.oreilly.servlet.MultipartRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import oracle.sql.BLOB;
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.OracleResultSet;
 
public class addCandidate extends HttpServlet
{
  public void service(HttpServletRequest req,HttpServletResponse res)
      throws ServletException,IOException
  {
      System.out.println("1");
      HttpSession session = req.getSession(true);
      PrintWriter out = res.getWriter();
      MultipartRequest multi = new MultipartRequest(req, "C:\\TEMP");
      ServletConfig config = null;
      ServletContext context = null;
      RequestDispatcher rd = null;
    try{
         System.out.println("2");
         config = getServletConfig();
         context = config.getServletContext();
          
    }catch(Exception e)
    {
      
    }
    String firstname = multi.getParameter("fname");
    String lastname = multi.getParameter("lname");
    String dt1 = multi.getParameter("dob1");
    String dt2 = multi.getParameter("dob2");
    String dt3 = multi.getParameter("dob3");
    String dateofbirth = dt1 + "-" + dt2 + "-" + dt3;
    String p1 = multi.getParameter("ph1");
    String p2 = multi.getParameter("ph2");
    String phone = p1 + " " + p2;
    String mail = multi.getParameter("email");
    String qualification = multi.getParameter("qualification");
    String skills = multi.getParameter("skill");
    String company = multi.getParameter("company");
    String designation = multi.getParameter("desgn");
    String e1 = multi.getParameter("exp1");
    String e2 = multi.getParameter("exp2");
    String experience = e1 + "." + e2;
    String salary = multi.getParameter("salary");
    String location = multi.getParameter("location");
    String dappf = multi.getParameter("desgnappfor");
    String expsal = multi.getParameter("expsal");
    String saloff = multi.getParameter("saloff");
    String r1 = multi.getParameter("rst1");
    String r2 = multi.getParameter("rst2");
    String result = r1 + " " + r2;
    String id1 = multi.getParameter("id1");
    String id2 = multi.getParameter("id2");
    String id3 = multi.getParameter("id3");
    String intdate = id1 + "-" + id2 + "-" + id3;
    String comments = multi.getParameter("comments");
 
    String sql1 = "select first_name,last_name,to_char(date_of_birth,'DD-MM-YYYY')"
+" from interviewee where first_name = '"
+firstname+"' and  last_name = '"+lastname+"' and  date_of_birth = to_date('"
+dateofbirth+"','DD-MM-YYYY')";
 
    String sql2 = "select first_name,last_name,to_char(date_of_birth,'DD-MM-YYYY'),phone,"
+"email,qualification,skill_set,company,designation,experience,salary,location,"
+"designation_applied_for,expected_salary,salary_offered,result,"
+"to_char(interview_date,'DD-MM-YYYY'),comments from interviewee where first_name = '"
+firstname+"' and last_name = '"+lastname+"' and  date_of_birth = to_date('"+dateofbirth+"','DD-MM-YYYY')";
 
 
    String sql = "insert into interviewee values('"+firstname+"','"+lastname+"',to_date('"+dateofbirth+"','DD-MM-YYYY'),'"
+phone+"','"+mail+"','"+qualification+"','"+skills+"','"+company+"','"
+designation+"','"+experience+"',"+salary+",'"+location+"','"+dappf+"','"
+expsal+"','"+saloff+"','"+result+"',to_date('"+intdate+"','DD-MM-YYYY'),'"
+comments+"',empty_blob())";
 
    String sql3 = "select cv from interviewee where first_name='"+firstname+"' and last_name='"
+lastname+"' and date_of_birth=to_date('"+dateofbirth+"','DD-MM-YYYY') for update";
 
               try
               {
                 System.out.println("3");
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 Connection con = DriverManager.getConnection("jdbc    :-o	    racle:thin:@10.11.32.86:1521:Resumedb","scott","tiger");
                 con.setAutoCommit(false);
                 Statement stmt = con.createStatement();
                 System.out.println("32");
                 ResultSet rs = stmt.executeQuery(sql1);
 
                         if(rs.next())
                         {
                           System.out.println("4");
                           res.sendRedirect("addError.jsp");
                           System.out.println("5");
                         }else
                         {
                           System.out.println("6");
                           stmt.executeUpdate(sql);
                           System.out.println("7");
                           rs = stmt.executeQuery(sql3);
                           System.out.println("8");
                           rs.next();
 
                           BLOB blob = ((OracleResultSet)rs).getBLOB("cv");
                           File file = multi.getFile("cv");
                           FileInputStream input = new FileInputStream(file);
                           OutputStream output = blob.getBinaryOutputStream();
                           int size = blob.getBufferSize();
                           byte[] buffer = new byte[size];
                           int length = -1;
                           System.out.println("9");
                          while ((length = input.read(buffer)) != -1)
                           output.write(buffer, 0, length);
                           input.close();
                           output.close();
                            System.out.println("10");
                           rs = stmt.executeQuery(sql2);
                           System.out.println("11");
                                  if(rs.next())
                                  {
                                    System.out.println("12");
                                    rd = context.getRequestDispatcher("/jsp/addSuccess.jsp");
                                    rd.forward(req,res);
                                    System.out.println("13");
                                  }else
                                  {
                                    res.sendRedirect("/jsp/addFail.jsp");
                                  }
                         }
 
               }catch(Exception e)
               {
                 out.println(e);
               }
  }
}

Using this code i attained a resultset object which i want to pass it to the jsp page for display.How to pass the object? Can anyone help me out?
Thanx in advance.
Visu N

[Edit - code tags added, broke up long lines]
[ December 16, 2005: Message edited by: David O'Meara ]

[Ulf Dittmer]

Have a look at the HttpServletRequest.setAttribute method.

[Rajeev Ravindran]

I wont recommend your standards !! I think it will be good if you seperate your application into different layers. You can have an action class, business layer, DAO layer and make use of VO objects to pass data between layers. Just a suggestion

To pass your resultset to jsp, make use of the HttpServletRequest object

in your servlet
request.setAttribute("resultset",rs);

and in your jsp

ResultSet rs = (ResultSet) request.getAttribute("resultset");

I hope this is what you are looking for !

Thanks,

[David O'Meara]

Visu, you now have 30 posts. Please learn to use the code tags to format your posts.

[David O'Meara]

request.setAttribute("resultset",rs);[/QB]

I disagree completely, do NOT pass the resiult set from the Servlet to the JSP. This is a dangerous practice since it can cause memory leaks since you will not be able to close the connection, and ResultSet objects are not serialisable so you will have problems in some containers.

You need to perform the query, load the database results into something else, then send these results to the JSP for display. This is where ORM layers such as Hibernate, Toplink etc come in so handy.

Also, please look at using PreparedStatements, they will also save you a lot of trouble in the future.

[Scott Selikoff]

I concur, result sets are not serializable and therefore should never be passed over any transport level. You an use auto-generation tool or IDE (like Eclipse) to create a simple DTO object with getters/setters and send as that, or an array of them.

On top of that, given you can vary the fetch size of the result set, I'm not even sure it would work without throwing serious errors.
[ December 16, 2005: Message edited by: Scott Selikoff ]

[Adeel Ansari]

ResultSet object becomes invalid/closed after closing its statement.

The best practice is to release DB resources as soon as you are finished with those to avoid sticking with database, just for nothing.

Docs: A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed.