Thread: Advise on mapping JTable to large PostgreSQL table requested?

Hi,

As here are people that know databases and Java....

I *must* (as in: I have no choice and it can't be solved another way by
customer demand) display a large table (> 1.100.000 records) in a JTable
( or something that looks like a JTable for the customer).

The customer wants to able to search the data and wants the focus (if
the search succeeds) to be on the record in the table.

AS a JTable works with rows, so I have written code that calculates the
row of the record in the query and maps any row request to an index in
the query.

To make it more clear (?;-):

1. Customer wants the record with 'some text' in column 'a'
2. I calculate the index:
select count(*) from table where a < 'some text'
3. I check if the index is in a local cache ( an AbstractTableModel
derived object that maintains a vector of cached record).
4. If not, I get 100 records before and after the requested record,
including the record itself and put them in the local cache so that
JTable can call AbstratTableModel.getValueAt(row,column) without the
need of accessing the database.
5. I set the JTable selection to the index found in step 2 and as long
as a requested row is in my cache I have a snappy respons.

The trouble is that step 2 takes a lot of time (it is proportional to
the size of the table?).

I assume that I am not the first person that tries something like this.
I want to know if my way of solving this problem is the most adequate,
or that there are other ways of achieving my goal (which is indeed
emulating an ISAM database).

TIA


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

On Tue, 6 Dec 2005, Joost Kraaijeveld wrote:

> I *must* (as in: I have no choice and it can't be solved another way by
> customer demand) display a large table (> 1.100.000 records) in a JTable
> ( or something that looks like a JTable for the customer).
>
> AS a JTable works with rows, so I have written code that calculates the
> row of the record in the query and maps any row request to an index in
> the query.
>
> 1. Customer wants the record with 'some text' in column 'a'
> 2. I calculate the index:
> select count(*) from table where a < 'some text'

There's got to be a better way of storing this index.  If this is readonly
data then you could easily number all of these in the database itself.  I
think the problem may be that you are trying to index all this data as an
array where you need an absolute offset.  Perhaps a different data
structure would make a better backing type?

> 4. If not, I get 100 records before and after the requested record,
> including the record itself and put them in the local cache so that
> JTable can call AbstratTableModel.getValueAt(row,column) without the
> need of accessing the database.

How do you determine the records before and after at the moment?

Kris Jurka

Couldn't you use a single ugly "SELECT *" with a cursor and then fill your local cache as the client is scrolling through the table?  It will scroll kind of slow the first time, but then i'll work nice. I'm not very into Swing, but for that you'd only need the number of rows  with something like "SELECT count(*)".