3 Replies Latest reply on Dec 15, 2004 2:00 PM by Adrian Brock

    ResultSet persistance with a Stateful Session Bean

    Tyler Black Newbie

      JBoss 4.0

      Like many others, I've been trying to persist a ResultSet accross multiple method calls to a Stateful Session Bean. The old problem of only wanting to display a page of results at a time, and calling the Stateful Session Bean to get the next/previous page.

      Of course, the CacheConnectionManager is closing the open connection when the method that initially opened it returns. I realize after some reading that this is an anit-pattern and will not scale well. However, what are the alternatives?

      I could use a stateless bean and make calls to the database passing limit and offset values, however this doesn't scale well with larger results and calls will get slower the further the user travels (with a greater offset). Granted, this may scale better than what I'm trying to do with persisting ResultSets.

      Is there not a better solution?

      Thanks,

      Tyler

        • 1. Re: ResultSet persistance with a Stateful Session Bean
          Adrian Brock Master

          To keep connections open across requests you need to use
          BMT and unshared connections with an external transaction.

          You are correct this does not scale. Each client has there own unshared connection.
          You will quickly run out of connections as you add more clients.

          This is true for any design that links an open connection to a client across
          invocations.
          e.g. what happens when the client goes to lunch instead of clicking "next page"

          The usual technique is to either use page/size (direct support in MYSQL)
          or store the primary key of the last value which you can use for subsequent queries:
          When the user clicks next, you add
          AND pk > pkOfLastEntryOnPreviousPage
          to the query.

          • 2. Re: ResultSet persistance with a Stateful Session Bean
            Tyler Black Newbie

            Thanks for the reply.

            We're using PostgreSQL as our database, so limit/offset it is! We're also sorting the results returned by something other than the primary key, so the pk really has no relevance in the context that the results are being returned. However, that's a neat idea, and than you for it.

            Cheers,

            Tyler

            • 3. Re: ResultSet persistance with a Stateful Session Bean
              Adrian Brock Master

              That can be done with UNIONs

              ... AND orderby1 = lastValue1 AND orderby2 = lastValue2 AND pk > lastPK
              UNION
              ... AND orderby1 = lastValue1 AND orderby2 > lastValue2
              UNION
              ... AND orderby1 > lastValue1

              Or if UNIONs don't perform very well on your DB you can brute force it
              running them as individual queries until the page is full.