Large Result Sets

Discussions

General J2EE: Large Result Sets

  1. Large Result Sets (3 messages)

    Hi All,

    I have a query which returns around 1000 records

    Because of this scenario I have decided to implement a "prev" "next" type of mechanism for my JSP page.

    In order to do this and avoid running the query over and over again (when ever a prev or next button is pressed), I plan to store this 1000 records (held in a Vector) in directory service using JNDI api's.

    Therefore whenever the user clicks on the prev or next button I will do a lookup and get my vector back (thus getting all the records) and render (say 25) records on to the JSP.

    Let me know if using LDAP for this is a good idea or not.

    Keep in mind that I will have to do a lookup everytime the user presses a "prev" or "next" button.

    Would be highly obliged if you can let me know your take on this

    Thanks for your time

    -Chetan




    Threaded Messages (3)

  2. Large Result Sets[ Go to top ]

    Why not save the results in the session and retrieve the required results on pressing 'next' or 'prev'. You could even use a statefull session bean for this purpose.
    Bharat
     
  3. Large Result Sets[ Go to top ]

    I do not want to store it in the HTTP Session because this Vector will have huge data and in a clustered environment this could become a performance issue.

    I like the stateful session idea but I am trying to find out which solution is better (in terms of performance and workload ) the Stateful Session Bean solution or the JNDI type of solution.

    Let me know what you think.

    Thanks again for your time.

    -Chetan.

  4. Large Result Sets[ Go to top ]

    Hello,

    You could create a class that would be responsible for returning a partial result set from the database using JDBC.

    The idea of creating a session bean or storing results in a session or JNDI context doesn't seem to be attractive to me because it will be a big load on server with a great amount of users. Besides, your result set will be the more outdated the longer user browses your result set. Instead, such a result set could be cached at the database level. These goals would be achieved this way:

    1. A single result set is necessary to store for all users,
    regargdless of their number.
    2. The extracted data is always fresh.

    To achieve this, I would use PreparedStatement to execute a query that will be compiled and cached at the database level, and ResultSet.absolute/ResultSet.next to extract data from the result set.

    I would implement a method something like

    import java.sql.*;
    import java.util.*;
    import javax.naming.*;

    // This class is responsible for
    // management of line items data.

    // This class should be used within JNDI context,
    // for example in a EJB or in a web-app.

    public class LineItemManager {

    protected String tableName;
    protected String allFields = "id, name, description";

    public LineItemManager() throws NamingException {

      Context ctx = new InitialContext();
      //Lookup a table name from the environment
      tableName = ( String )ctx.lookup( "java:comp/env/LineItem.TableName" );
    }

    public Collection getLineItems( Connection, connection, int start, int count ) throws SQLException, XYZException {

      // We use vector to return the resulting
      // Collection.
      Vector v = new Vector();
      
      // Use JDBC to get the data
      String query = "SELECT " + allFields + " FROM " + tableName;

      // Prepared statement will make sure
      // the statement is compiled once
      // to improve performance
      PreparedStatement pst = connection.prepareStatement( query );
      ResultSet rs = pst.executeQuery();
      if ( start > 0 ) rs.absolute( start );
      while ( rs.next || count > 0 ) {
        count--;
        // extract the next line item record
        LineItem li = getLineItem( rs );
      }
      return v;

    }

    public LineItem getLineItem( ResultSet rs ) throws RemoteException {
      LineItem li = new LineItem();
      li.id = rs.getInt( 1 );
      li.name = rs.getString( 2 );
      li.description = rs.getString( 3 );
      return li;
    }

    }

    Hope this helps,

    Sincerely,
    Sergei Batiuk.