JTable and ResultSet TableModel with big resultset
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
Is the user going to need to see so many results that it causes out of memory errors?
Here are some ideas:
1. Put a limit on the total number of results
2. Use paging on the table.
3. Give the user the ability to narrow the results with some sort of filter or search criteria etc.
>Is the user going to need to see so many results that it causes out of >memory errors?
Yes, the JTable have to display all database table data (initially) to the user and then, the user, can choose to introduce some filters... for very large table, if the user scroll the entire table the out of memory is very probable...
>Here are some ideas:
>1. Put a limit on the total number of results
>2. Use paging on the table.
no limit and no paging, I want a JTable like an excel work sheet...
>3. Give the user the ability to narrow the results with some sort of filter >or search criteria etc.
yes, but initially there are no filters...
In my opinion, the only way, to do this is the servlet or RMI solution... with a distribuite caching TableModel... see
http://www.javaworld.com/javaworld/javatips/jw-javatip137.html
for more details.
I hope that somebody in this maling list worked on distribuites caching TableModel or ...
if there are more simple solutions... are welcome!
but not solutions 1,2,3 (above)
thanks
What is the largest resultset size you will have? You could increate the java heap size. And if it is super large and this will not work then I repeat my first statement: that much data will not be useful to anyone to look at, the size of the Jtable would make it almost unusable.
> no limit and no paging, I want a JTable like an excel work sheet...
Excel works sheets have a limit. I think it is like 65,000 rows or something.
The database table currently is about 80000 rows but the next year will be 200000 and so on ...
I know that excel has this limit but my JTable have to display more data than a simple excel work sheet.
I explain in more detail my solution:
whith a distribuited TableModel the whole tablemodel data are on the server side and not on the client (jtable).
The local JTable TableModel gets the values from a local (limited, 1000rows for example) structure, and when the user scroll up and down the jtable the TableModel updates this structure...
For example: initially the local JTable structure contains the rows from 0 to 1000;
the user scroll down, when the cell 800 (for example) have to be displayed the method:
getValueAt(800,...)
is called.
This method will update the table structure. Now, for example, the table structure will contain data for example from row 500 to row 1500 (the data from 0 to 499 are deleted)
In this way the local table model dimension will be indipendent from the real database table dimension ...
I hope that my solution is more clear now...
under these conditions the only solutions that can work have to implement a local tablemodel with limited dimension...
Another solution without servlet and rmi that I have found is the following:
update the local limited tablemodel structure quering the database server with select .... limit ... offset
but, the select ... limit ... offset is very dangerous when the offset is high because the database server have to do a sequential scan of all previuous records ...
with servlet (or RMI) solution instead, the entire resultset is on the server and I have only to request the data from the current resultset from row N to row N+1000 without no queries...
Thanks
> The database table currently is about 80000 rows but
> the next year will be 200000 and so on ...
> I know that excel has this limit but my JTable have
> to display more data than a simple excel work sheet.
Your solution seems difficult but it might work. I am afraid it will be very messy though. I still don't think any user would want to see a quarter million rows in one table? Why don't you show an empty table of results with filters. Then once the user selects a filter you retrieve the results?I really would try to get your requirement of one table with hundreds of thousands of rows changed because it will reduce the complexity of your program and I don't think your customer knows what they are asking for.
Why not use the abstract table model and create a jtable that traverses a resultset rather than displaying data cashed in the model.
It is my understanding that a resultset is stored on the server with only pointers to the resultset data stored locally and when movement in the resultset occurs only the relevant data is transfered to local storage.
rykk
> Why not use the abstract table model and create a
> jtable that traverses a resultset rather than
> displaying data cashed in the model.
> It is my understanding that a resultset is stored on
> the server with only pointers to the resultset data
> stored locally and when movement in the resultset
> occurs only the relevant data is transfered to local
> storage.
>
> rykk
If you are going that route check out default table model. I am not sure of the details but that sounds like it might be true.
OP,
My point is still the same: even if you got it to work it will be near useless and very frustrating for users to be viewing a table of a quarter million rows, I don't think your customer know what they are asking for. I believe there is a much more user friendly design.
I will not disagree depending on the application there may be a much more user-friendly design.
Ignoring the initial load time - Often from a user perspective it is extremely annoying to have to enter or select windowing criteria every time an action is required. For example lookup tables with quick keys or functionally that position the jtable could be the other side of the coin.
The user could care less about the efficient use of resources ?they want results.
I know this comment will elicit lots of comments.
rykk
> I will not disagree depending on the application
> there may be a much more user-friendly design.
> Ignoring the initial load time - Often from a user
> perspective it is extremely annoying to have to enter
> or select windowing criteria every time an action is
> required. For example lookup tables with quick keys
> or functionally that position the jtable could be the
> other side of the coin.
> The user could care less about the efficient use of
> resources ?they want results.
That is a good point, it is annoying to have to enter the criteria before you see results. But when you are talking about this many rows, I am sure they are going to be filtering it at some point. I was just concerned that the OP was going to go through all the trouble to display all the rows when the user might not ever need. Of course I am just guessing because I have no idea what the data is or what the user will be doing.
