java - Displaying data from database in JTable

Question

I'm writing a program using jdbc that will be an interface to database(smth like CRUD aplication). I assume that I have to write a class(e.g. DBCLass) that will do all the operations with database(select, update, insert, delete and maybe some other logic that will be reduced to these operations). User interface consists of a set of tables and a few buttons. To use a Jtable I need to implement a class(e.g Model) which is a subclass of AbstractTableModel. So this class will display my data to the user. I need to implement such model for all tables in my database schema. I don't want to write the logic in that classes that display data to the user and I think it is not very good thing to write the logic code in such classes. But it is also incorrect to load all the data from the db table to memory(e.g. ArrayList) and then display it in Model. So, I want an advise which is the best way to solve such problem.

edit: A small example:

Statement stmt = ....;
ResaultSet rs = stmt.executeQuery("SELECT * FROM table1");

javadoc says that executeQuery method returns a ResultSet object that contains the data produced by the given query. So If we have a lot of data(which size is more than permited size to our virtual machine), our program will fail. So my question is still relevant

Answer 1

Download the source for SQuirreL SQl and have a look at the table implementation.

Some things to note:

Database tables aren't Java JTables. A table in a database is in fact a set (curse the fool who used the wrong term) with items and each item has properties (usually called "columns" which isn't a JColumn which explains why it's so hard to map the two).

A set can grow to any size. It has no intrinsic order. You can do lots of set operations on it like: union, difference, sub set.

Therefore, it's not a table, especially not a UI table.

There is no easy UI paradigm which maps "set" to "table". You can

1. Load N records and page through the results.

2. You can load more as the user scrolls down

3. You can count the size of the set and adjust the scrollbar accordingly. As the user scrolls through the data, it is fetched from the DB and displayed.

Pros + cons:

Solution 1 is most simple to implement and the one which users hate most. Why do they need to wait to see data again when the go backwards? This is especially frustrating if each fetch takes 15 seconds. Page ... wait ... page ... oops! There it was! Damn! Wait wait wait ... back ... wait ... aaargh.

Also databases often have a hard time to page data. For some queries, performance can be disastrous.

Solution 2 is simple to implement, especially if you can keep the ResultSet open forever. But 100% of the time, you can't. It will start to fail if you keep it open for a couple of hours or a day. After that time, the DB will think "oh, it's dead, Jim" and close the connection and your user will get a nice error message and you will get an angry user.

So you need to page here, too, but not as often. On the positive side, users need not wait again for data they already have. One huge point: If the set contains millions of rows, users intuitively understand that they need to attack the problem from a different angle as they scroll down. Eventually, they will get tired and ask for a better solution (instead of being angry at you because your stupid program can't display 15 million rows in less than 0.0000000001s).

Solution 3 is worse than #2, again. If the table grows, the UI will become unusable: Even looking at the scroll know will move you to a random place in the table. So it makes your mouse useless. And your users angry.

So I usually try a solution which:

1. Reads 1000 rows, max. Plus I stop after 100 rows (so the user has at least some data) if reading the rows takes more than 1 second. Usually, the query is slow and reading the result set takes virtually no time, but I like being defensive here.

2. On top of each column is a filter and an "order by" which can be mapped directly to SQL. That way, I can make sure that if you want the data sorted by a column, it's sorted by all values (and not only those which you can see on the screen).

This allows users to chop huge amounts of data into meaningful sub sets.