Re: JTable and ResultSet TableModel with big resultset - Mailing list pgsql-jdbc

Hi, thanks for your help!

I'll read the documentation about using cursor with postgres.

But now, I have a new idea (always RMI or servlet based):

1) Using the "data blocks" (client cache) as you said
2) When the client execute a query like "select * from mytable order by
myfield", it sends the request to the RMI server, and the server execute the
query, get the resultset and creates a "pagination structures":

with "pagination structures" I mean that the server scroll the entire
resultset and every 1000 tuples it records the myfield value in a separate
structure. In this way the client application can do requests as: "give me
mytable page number 100 order by myfield" and the server can do this with
the following query:

if 'myfield' is for example the 'name' field:

        //get pagination values for page 100
        val1 = pagination_structure[99]; //val1 = Peter (for example)
        val2 = pagination_structure[100]; //val2 = Ralph

execute the query:

    select * from mytable where name >= 'Peter' and name <= 'Ralph' order by
name;

this query will return 1000 tuples.
NOTE: the pagination structure is used in order to avoid the use of
limit/offset keywords that are very expensive for the dbms

and so on....
if the client app. request the following to the RMI (or servlet based)
server:

give me the mytable page 1 order by name where the name like 'A%' and
surname like '%n'

the server will do the following:
1)execute the query: select * from mytable where name like 'A%' and surname
like '%n' order by name

2) creates the pagination structures: read all tuples of this resultset and
every 1000 tutples records the "name value".

3) returns the requested client page executing:

        //get the first page getting the pagination structure value [0] (the
first 'name' value of the resultset) and [1] (the 'name' value after 1000
rows)
        val1 = pagination_structure[0]; //val1 = Aabh
        val2 = pagination_structure[1]; //val2 = Ahhh

(between the name 'Aabh' and 'Ahhh' there are 998 rows)

execute: select * from mytable where (name >= 'Aabh' and name <= 'Ahhh') and
name like 'A%' and surname like '%n' order by name

If in the JDBC maling list there is somebody that have a best solution using
cursor... is welcome!
I'll read
 http://www.postgresql.org/docs/7.3/static/plpgsql-cursors.html
but I never used the cursor directly ...

Thanks.
Stefano

----- Original Message -----
From: <afgag@metroweb.co.za>
To: <stefano.bonnin@comai.to>
Sent: Tuesday, October 31, 2006 5:48 PM
Subject: RE: JTable and ResultSet TableModel with big resultset


> Stefano
>
> I'm a total Java/JDBC/SQL/PostgreSQL newbie, so take anything I say with a
> pinch of salt!
>
> I sympathise with your problem - had a similar problem while trying to
> dowload LOBs from PostgreSQL using ResultSet.getBinaryStream ...
>
> Have you tried looking at cursors?
>
> I haven't used them myself, but the PostgreSQL website claims that they're
> used for this kind of thing.  Check out
>
> http://www.postgresql.org/docs/7.3/static/plpgsql-cursors.html
>
> which gives some code.  I would try to keep the caching on the client and
> not on the server, but that's a newbie's thought :-)
>
> Your real headache is going to come once you get the caching sorted out
> and then try to implement it.   If I understand it correctly, you want the
> user to be able to "transparently" scroll through the data on the
> database, without actually having all the data on the client at any one
> time.  This is a pain, since Swing was not really designed with this sort
> of thing in mind (at least, I don't think so).  The trick is getting all
> the data to display on the JTable, without actually ever having all the
> data in the TableModel.
>
> This can be done, but it's painful.  Here's how I'd try and do it:
> Subclass DefaultTableModel, and put a RowSelectionListener on the JTable
> to keep tabs on where the user has currently scrolled to.  As the user
> nears the end of the cached block of data, get another block from the
> server and load it into the TableModel.  Then message the JTable and tell
> it that more data has been added, to that the view is updated.  Then
> remove the oldest block of data from the TableModel WITHOUT messaging the
> JTable, so that it still thinks all the data is still there.
>
> Eg, have 3 blocks of data: Up, Middle and Down.  When user scrolls from
> Middle to Down, get FurtherDown, message the JTable, and then delete Up
> without messaging.  If the user then scrolls from Down back into Middle,
> delete FurtherDown with messaging, and reload Up without messaging.
>
>
> As I said, this is how I'd do it, but I don't know too much about these
> things.  If you have a better idea of how to do this in Java, please let
> me know.  It's an interesting problem and one that can crop up quite a lot
> I'm sure !
>
>
> Hope this helps.
>
> Jacques du Toit
>
>
> PS: I have no idea how to get my reply to your post to register on the
> JDBC mailing list !!  Could you tell me that too?  Thanks
>
>
>
>
> Your email :
>
> Hi, I have a question about JTable and a ResultSet TableModel.
>
> I have to develop a swing JTable application that gets the data from a
> ResultSetTableModel where the user can update the jtable data.
> The problem is the following:
>
> the JTable have to contain the whole data of the source database table.
> Currently I have defined a
>
> a TYPE_SCROLL_SENSITIVE & CONCUR_UPDATABLE statement.
>
> The problem is that when I execute the query the whole ResultSet is
> "downloaded" on the client side application (my jtable) and I could
> receive (with big resultsets) an "out of memory error"...
>
> I have investigate about the possibility of load (in the client side) only
> a small subset of the resultset but with no luck. In the maling lists I
> see that the only way to load the resultset incrementally is to define a
> forward only resultset with autocommit off, and using setFetchSize(...).
> But this solution doesn't solve my problem because if the user scrolls the
> entire table, the whole resultset will be downloaded...
>
> In my opinion, there is only one solution:
>
> - create a small JTable "cache structure" and update the structure with
> "remote calls" to the server ...
> in other words I have to define on the server side a "servlet environment"
> that queries the database, creates the resultset and gives to the jtable
> only the data subsets that it needs... (alternatively I could define an
> RMI client/server distribuited applications...)
>
> This is my solution, somebody can help me?
> Are there others solutions for my problem?
>
> Thanks in advance,
>     Stefano
>
>
>
>
> --
> Internal Virus Database is out-of-date.
> Checked by AVG Free Edition.
> Version: 7.1.405 / Virus Database: 268.13.11/496 - Release Date:
24/10/2006
>