EJB design: Getting Next, Previous, First, Last sets of Data using EJB

  1. Hi,

    I am trying to get chunks of data from the database using EJB. I have a client(swing GUI) that contains Next, First, Last, Previous buttons. when the user clicks on those buttons, the gui passes a query object (which contains the sqlquery, the fetchLimit(no of rows to retrieve) ) to the bean. the bean inturn should return the number of rows requested to the gui. Has anybody came across this kind of implementation to get chunks of data from the database using a session bean or/and entitybean?. I would like to hear about the scalability of the application asthe number of users grow ( I think we have to keep the database connection open during the life of the client, if we have too many users, that might result in too many open connections).

  2. You can use a version of the ejbCreate() method to return a collection (or is it an enumeration? it's been awhile..) of EntityBeans. I'm not sure how to limit the number beans retuned by some arbitrary number though.
  3. Hi,
    If your data are not updatable and your sqlquery is static
    you better return to the gui an Enumeration of Objects(that wrap your data) and close the connection.
    then you can scroll the list using java technics to access ordered/indexed lists.
    And you're rigth keeping an open database connection per session is not scalable if you get more and more users connected.
  4. There are several ways you could do this. First off, yes, for scalability reasons you do not want to keep a database connection open.

    One way would be to create a find method that had the necessary parameters to query for an limited number of rows based on the full set and a greater than or less than value for the current ordering.

    So that you could issue a query like unto:

    SELECT * FROM Messages WHERE topicId = 480325632 AND created >= "2000/11/24 12:30:13" ORDER BY created ASC

    However, it is my opionion that Entity Beans are not well suited for this use case. It would be better to use a session bean and a more full featured relational to object mapping framework.

  5. How about using a session bean to connect to the DB and retrieve all the rows accord with the query statement, and stored them in a vector of helper class(not entity bean), then the DB connection can be released, the client can maintain a variable of current displaying page number, and using it, we can easily calculate the corresponding index of the vector in session bean, and get whatever page you want next(next,prev,first...)

    It's just a thought, haven't been implemented, hope it helps.
  6. Hi all,

    Why u not use a session bean to do this, It will execute an SQL query (select query) and then return a set of value objects (the helper class which have been serialize), eahc object will be one of record and have individual get method to get the fields of that record.

    u can also return a CachedRowSet to your client.

  7. But Does the CachedRowSet can hold large number of Rows, what if the resultset from a sqlquery return thousands of rows??