下载 SQuirreL SQl ，看看表的实现。

有些事情要注意：

数据库表不是Java JTables。数据库中的表实际上是一个集合（诅咒使用错误的术语的傻瓜），每个项目都有属性（通常称为列，它不是JColumn，它解释了为什么很难映射这两个）

一套可以增长到任何大小。它没有内在的秩序。你可以对它进行许多设置操作，如：union，difference，sub set。

因此，它不是一个表，特别是不UI表。

没有简单的UI范例将set映射到table。您可以

1. 通过结果加载N条记录和页面。




2. 当用户向下滚动时，您可以加载更多




3. 您可以对该集合的大小进行计数，并相应地调整滚动条。当用户滚动数据时，它将从DB中获取并显示。

优点+ cons： p>

解决方案1最容易实现，用户最讨厌的一个。为什么他们需要等待再次看到数据向后退？如果每次提取需要15秒，这是特别令人沮丧的。页面...等待...页面... oops！在那里！该死的！等待等待...返回...等待... aaargh。

数据库通常很难找到页面数据。对于某些查询，性能可能是灾难性的。

解决方案2很容易实现，特别是如果您可以保留 ResultSet 永远开放但是100％的时间，你不能。如果保持开放几个小时或一天，它将开始失败。之后，DB会认为哦，它已经死了，Jim，并关闭连接，你的用户将收到一个很好的错误消息，你会得到一个愤怒的用户。

所以你也需要在这里页面，但不是经常。积极的一面是，用户不必再等待他们已经拥有的数据。一个巨大的一点：如果该集合包含数百万行，用户可以直观地了解，他们需要从向下滚动的不同角度来攻击问题。最终，他们会厌倦，要求一个更好的解决方案（而不是因为你的愚蠢程序不能在不到0.0000000001s中显示1500万行）而生气。

所以我通常会尝试一个解决方案：

1. 读取最多1000行此外，如果读取行超过1秒，则在100行后停止（所以用户至少有一些数据）。通常情况下，查询速度很慢，并且读取结果集几乎没有时间，但我喜欢在这里进行防御。




2. 每列的顶部是一个过滤器可以直接映射到SQL的order by。这样，我可以确保如果您希望按列排序数据，则按照所有值排序（而不仅仅是您可以在屏幕上看到的数据）。

这允许用户将大量数据切成有意义的子集。

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

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.