How to return ResultSet from EJB to Servlet?

Discussions

EJB programming & troubleshooting: How to return ResultSet from EJB to Servlet?

  1. How to return ResultSet from EJB to Servlet? (4 messages)

    Hello:

      I want to get ResultSet by search data from Oracle DB,my steps are follows:first I visit Servlet with IE,then call EJB from Servlet,at last search data from Oracle through EJB.But I got many errors,
    //Servlet file which named "GetUserIdentity.java"

    public class GetUserIdentity extends HttpServlet
    {
       public void doPost(HttpServletRequest request,HttpServletResponse response)
         throws ServletException,IOException
     {
       try
      {
       Properties prop=new Properties();
       prop.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
       prop.put(Context.PROVIDER_URL,"t3://192.168.39.152:7001");
       Context ctx=new InitialContext(prop);
       Object objref=ctx.lookup("CheckPassword");
       CheckPasswordHome home=(CheckPasswordHome)javax.rmi.PortableRemoteObject.narrow(
          objref,CheckPasswordHome.class);
       CheckPassword ss=home.create();
       ResultSet message;
       message=ss.TableInfo();
       ...
       }
       catch(Exception e)
      {
       e.printStackTrace();
      }
      }
    }

    //EJB file which named "CheckPassword.java"
    package loginEJB;
    import java.rmi.RemoteException;
    import java.sql.ResultSet;
    import java.util.Enumeration;
    import java.util.Properties;

    public interface CheckPassword extends javax.ejb.EJBObject
    {
     ResultSet TableInfo() throws java.rmi.RemoteException;
    }

    //The file "CheckPasswordEJB.java"

    import java.sql.Statement;
    import java.sql.ResultSet;
    import java.util.Enumeration;
    import java.util.Properties;
    public class CheckPasswordEJB implements javax.ejb.SessionBean
    {
     public void ejbCreate()
     {
      System.out.println("ejbCreate() called");
     }
     ...
      public ResultSet TableInfo() throws java.rmi.RemoteException
     {
      String driverClass="oracle.jdbc.driver.OracleDriver";
      String url="jdbc:oracle:thin:@as_server:1526:db817";
      Connection connection;
      try
      {
       // Oracle Client - Server Thin Driver
       Class.forName(driverClass);
      }
      catch(ClassNotFoundException e)
      {
       e.printStackTrace();
      }
      try
      {
       connection=DriverManager.getConnection(url,"eino","eino");
       Statement stmt=connection.createStatement();
       stmt.executeQuery("select * from table_info_tbl");
       ResultSet rs=stmt.getResultSet();
       return rs;
      }
      catch(SQLException e)
      {
       e.printStackTrace();
       return null;
      }
     }
    }

    When I visit EJB,I got errors:
    Starting service Tomcat-Standalone
    Apache Tomcat/4.0.1
    Starting service Tomcat-Apache
    Apache Tomcat/4.0.1
    java.rmi.MarshalException: error marshalling return; nested exception is:
            java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl


    Start server side stack trace:
    java.rmi.MarshalException: error marshalling return; nested exception is:
            java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl

    java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl
            at java.io.ObjectOutputStream.outputObject(ObjectOutputStream.java:1148)

            at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:366)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:102)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:108)
            at loginEJB.CheckPasswordEJB_28r6w3_EOImpl_WLSkel.invoke(Unknown Source)

            at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:296)
            at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerR
    ef.java:93)
            at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.jav
    a:265)
            at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest
    .java:22)
            at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
            at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
    End server side stack trace
    ; nested exception is:
            java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl


    Start server side stack trace:
    java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl
            at java.io.ObjectOutputStream.outputObject(ObjectOutputStream.java:1148)

            at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:366)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:102)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:108)
            at loginEJB.CheckPasswordEJB_28r6w3_EOImpl_WLSkel.invoke(Unknown Source)

            at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:296)
            at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerR
    ef.java:93)
            at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.jav
    a:265)
            at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest
    .java:22)
            at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
            at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
    End server side stack trace

    java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl

    Start server side stack trace:
    java.io.NotSerializableException: oracle.jdbc.driver.OracleResultSetImpl
            at java.io.ObjectOutputStream.outputObject(ObjectOutputStream.java:1148)

            at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:366)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:102)
            at weblogic.common.internal.ChunkedObjectOutputStream.writeObject(Chunke
    dObjectOutputStream.java:108)
            at loginEJB.CheckPasswordEJB_28r6w3_EOImpl_WLSkel.invoke(Unknown Source)

            at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:296)
            at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerR
    ef.java:93)
            at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.jav
    a:265)
            at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest
    .java:22)
            at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
            at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
    End server side stack trace

            <
    I don't know why my program is error.
    Any idea will be appreciated! How to correct ?
    My Email: zhangsc at neusoft dot com
    Edward
  2. hi Edward,

    The problem is that a ResultSet object is not serializable and so cannot be marshalled throught the network.

    I suggest the following.

    1. execute the query in your EJB and get the result set
    2. Construct a vector of value objects. The value objects are reflections of your resultset row.
    3. return this vector to the servlet.

    rgds
    Mani
  3. your answer lies here ...

    http://archives.postgresql.org/pgsql-interfaces/1998-09/msg00015.php
  4. iam sorry this is the correct link
    http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=3&t=000451

    The problem lies when marshelling the data the object has to be serialized as the object accessing the ObjectOutputStream should maintain the state and the serrialized objects does.
  5. It's can be done very easy with CachedRowSet.
    Oracle has it, also Sun and some other vendors.