How to populate a very large recordset into JTable?

Discussion in 'Java' started by sun9999@yahoo.com, Jun 29, 2005.

  1. Guest

    Dear all,

    I got a very large recordset (around 1 billion records) require to
    populate it into the JTable. As we all know, the standard way of
    populating JTable are either using Vectors or TableModel. However, it
    is not possible to load 1 billion records all at once. I do think many
    peoples will challenge me why I will have such big recordset and I
    should make some filtering or re-design the usage of the system. In
    fact, my real question is like this:
    Are there any way to populate the JTable regardless about the size of
    the recordset.

    Actually, I did some searching in google before I post this message. I
    know some peoples using the method of sliding windows to solve this
    problems. However, the implementation seems quite complicated. I will
    very appreciate if any peoples have the similar experiences or ideas
    that can share over here. Thanks in advance.


    Regards,
    Sunny
     
    , Jun 29, 2005
    #1
    1. Advertising

  2. wrote:
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system.


    Indeed, your idea is rather stupid. Already from a GUI point of view, it
    will blow up in your face. Just to give you one reason why it will blow
    up (there are many more):

    Swing does almost all calculations with signed integers. The largest
    signed integer number is roughly 2.1 billion. A single JTable row has an
    initial height of 16 pixel. So 1 billion rows would mean a table height
    of 16 billion pixels. A number which is out of the integer range. So
    Swing will not be able to calculate your component's size correctly.
    Thus it will not layout or display the component correctly. Wrapping the
    stuff in a JScrollPane will not change this. The JScrollPane needs the
    total size to adjust the viewport.

    Many, many other calculations will for sure also fail.

    But even if we assume for a moment that it will work, usability will be
    a nightmare. If we assume you operate in full-screen mode, then your
    vertical scroll bar (depending on your screen size) has maybe something
    in the order of 1000 distinct positions (because of the vertical screen
    resolution). So, by sliding the scroll bar you will not be able to
    navigate with more precision than chunks of 1 million rows. Within a
    chunk of such 1 million rows the user than has to step from row to row
    to get a desired row. Which means by average 500000 steps. Ouch, that
    hurts. If we assume a super-fast user who just needs 0.1 second per row
    that navigation would take 50000 seconds. Which is a little bit more
    than 13 hours. 13 hours of constantly stepping. Have fun.

    /Thomas

    --
    The comp.lang.java.gui FAQ:
    ftp://ftp.cs.uu.nl/pub/NEWS.ANSWERS/computer-lang/java/gui/faq
    http://www.uni-giessen.de/faq/archiv/computer-lang.java.gui.faq/
     
    Thomas Weidenfeller, Jun 29, 2005
    #2
    1. Advertising

  3. Chris Uppal Guest

    Thomas Weidenfeller wrote:

    > If we assume you operate in full-screen mode, then your
    > vertical scroll bar (depending on your screen size) has maybe something
    > in the order of 1000 distinct positions (because of the vertical screen
    > resolution).


    Incidentally, using the same argument, but running the arithmetic in reverse
    suggests that the maximum number of rows that can usefully be scrolled over is
    around 60000. At that point, assuming 1000 pixels of vertical resolution, and
    a row height of 16, moving the scrollbar by one pixel will correspond to
    scrolling the next window's worth of data into view. (From a usability POV it
    may be better not to scroll a whole window's worth, in which case the maximum
    should be lowered, but on the other hand, a row height of 16 is pretty high --
    in any case the maximum is going to be somewhere in the region of 10k-100k
    rows).

    Depending on various factors (such as row size, and the speed that the database
    can provide data) it may be perfectly reasonable to read the whole 50k rows
    into memory before displaying them in the table. So -- providing that the
    dataset is small enough to be /viewed/ in a table -- there may be no need at
    all for "fancy" implementation techniques such as sliding windows.

    Populating the table lazily may be a viable compromise -- simple to code, cuts
    down the start-up time, but doesn't actually save memory (unless the user
    doesn't look at the whole table).

    -- chris
     
    Chris Uppal, Jun 29, 2005
    #3
  4. F'up set to cljg

    Chris Uppal wrote:
    > Incidentally, using the same argument, but running the arithmetic in reverse
    > suggests that the maximum number of rows that can usefully be scrolled over is
    > around 60000. At that point, assuming 1000 pixels of vertical resolution, and
    > a row height of 16, moving the scrollbar by one pixel will correspond to
    > scrolling the next window's worth of data into view.


    What I didn't consider was that it is rather hard for many people to
    slide a scrollbar by exactly one pixel. Todays cheap imprecise mouses
    make it even difficult for skilled users. So a feasible number is maybe
    1/2 or 1/5 of the rows.

    > (From a usability POV it
    > may be better not to scroll a whole window's worth, in which case the maximum
    > should be lowered, but on the other hand, a row height of 16 is pretty high --


    The 16 is Sun's hardcoded default, which JTable.getRowHeight() will
    return unless you hit it over the head. Sun not even adjusts the default
    row height to the size of the font used in the rows. One of the many
    things which are wrong with JTable.

    > Depending on various factors (such as row size, and the speed that the database
    > can provide data) it may be perfectly reasonable to read the whole 50k rows
    > into memory before displaying them in the table. So -- providing that the
    > dataset is small enough to be /viewed/ in a table -- there may be no need at
    > all for "fancy" implementation techniques such as sliding windows.


    I never did a sliding window implementation with JTable or in Swing. I
    did it some time ago for a custom-made tree widget which was used to
    navigate over a huge amount of data in a DB. It is rather simple if you
    have control over both ends, DB and widget implementation. I would
    imagine it is a real mess if you have to retro-fit it to a JTable.

    > Populating the table lazily may be a viable compromise -- simple to code, cuts
    > down the start-up time, but doesn't actually save memory (unless the user
    > doesn't look at the whole table).


    You could think about some caching and expiration mechanism in the
    TreeModel. However, anticipating user interaction and thus deciding
    which data would make most sense to remove from the cache could be "fun".

    /Thomas

    --
    The comp.lang.java.gui FAQ:
    ftp://ftp.cs.uu.nl/pub/NEWS.ANSWERS/computer-lang/java/gui/faq
    http://www.uni-giessen.de/faq/archiv/computer-lang.java.gui.faq/
     
    Thomas Weidenfeller, Jun 29, 2005
    #4
  5. Filip Larsen Guest

    Sunny wrote

    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once.


    In addition to what others have written, you can try run the following
    example. I have not researched exactly what breaks when the number of
    rows in the example is increased by one more to 0x07FFFFFF, that is to
    134217727 rows.

    === HugeTable.java ===

    import javax.swing.JFrame;
    import javax.swing.JScrollPane;
    import javax.swing.JTable;
    import javax.swing.SwingUtilities;
    import javax.swing.table.AbstractTableModel;

    public class HugeTable extends JFrame implements Runnable {

    public static void main(String[] args) {
    SwingUtilities.invokeLater(new HugeTable());
    }

    class HugeTableModel extends AbstractTableModel {

    public int getColumnCount() {
    return 5;
    }

    public int getRowCount() {
    return 0x07FFFFFE; // apparent maximum row count
    }

    public Object getValueAt(int rowIndex, int columnIndex) {
    return new Integer(rowIndex/(columnIndex+1));
    }

    }

    HugeTable() {
    super("Huge TableModel Demo");
    this.setDefaultCloseOperation(EXIT_ON_CLOSE);
    getContentPane().add(new JScrollPane(new JTable(new
    HugeTableModel())));
    }

    public void run() {
    pack();
    show();
    }
    }

    ======================


    Regards,
    --
    Filip Larsen
     
    Filip Larsen, Jun 29, 2005
    #5
  6. Pete Barrett Guest

    On 29 Jun 2005 02:24:04 -0700, wrote:

    >Dear all,
    >
    >I got a very large recordset (around 1 billion records) require to
    >populate it into the JTable. As we all know, the standard way of
    >populating JTable are either using Vectors or TableModel. However, it
    >is not possible to load 1 billion records all at once. I do think many
    >peoples will challenge me why I will have such big recordset and I
    >should make some filtering or re-design the usage of the system. In
    >fact, my real question is like this:
    >Are there any way to populate the JTable regardless about the size of
    >the recordset.
    >
    >Actually, I did some searching in google before I post this message. I
    >know some peoples using the method of sliding windows to solve this
    >problems. However, the implementation seems quite complicated. I will
    >very appreciate if any peoples have the similar experiences or ideas
    >that can share over here. Thanks in advance.
    >
    >

    If you can open a cursor on your table, you may be able to move the
    cursor around the table in response to the movement of the scroll bars
    (or any other navigation controls you have, because precisely
    positioning scroll bars with even 1 million records would be awkward),
    and only read in the data which would be immediately displayed.

    I emphasize I've never done this, because I've never had to display a
    ResultSet so large, but investigating a solution along those lines is
    what would spring to mind if I did have to.

    Pete Barrett
     
    Pete Barrett, Jun 29, 2005
    #6
  7. Joan Guest

    What database are you using?

    <> wrote in message
    news:...
    > Dear all,
    >
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system. In
    > fact, my real question is like this:
    > Are there any way to populate the JTable regardless about the size of
    > the recordset.
    >
    > Actually, I did some searching in google before I post this message. I
    > know some peoples using the method of sliding windows to solve this
    > problems. However, the implementation seems quite complicated. I will
    > very appreciate if any peoples have the similar experiences or ideas
    > that can share over here. Thanks in advance.
    >
    >
    > Regards,
    > Sunny
    >
     
    Joan, Jun 30, 2005
    #7
  8. Guest

    Hi All,

    First of all, thanks for the replies. (ESPECIALLY THOMAS ^_^;)
    Basically, one billion recordset is just an extreme example to emphasis
    my problems: How to poplulate a JTable no matter how big is the
    recordset.

    As I mentioned, I did some research/searching in the Google before I
    posted this message.

    Please have a look of the following links:
    http://groups-beta.google.com/group...rogramming/browse_frm/thread/dda6ec54df79d17e
    http://groups-beta.google.com/group/comp.lang.java.gui/browse_frm/thread/1686b4684d90000b

    If you read the first link, you should able to find a customer story
    that stated by Franco Lombardo. I extract the story here for those lazy
    readers:

    Ok, you are right: my description of the problem is ambiguous. Let's
    write a customer story.
    I want a stand-alone application that reproduces the behaviour of
    Microsoft'a Access queries. I want a window in which to write any SQL
    SELECT statement, then a button that runs the query and shows the
    results in a table. If the query extracts, say, 1,000,000 records, the
    table has to show the results.
    If I have enough time to click on the scroll-barr for a whole day, the
    table has to show me all the records. Nothing more than MS Access
    query, or the old black-and-green STRSQL command of AS400."
    I think this is unambigous: I have a precise behaviour of another
    program to reproduce.
    Now, as a programmer, I can see that a swing program using a JTable
    could solve the problem. Obviously I can't load in memory the whole
    resultset if it is too big. How can I solve the customer problem?

    Actually, the above "Customer story" is exactly a live scenario for my
    question. Within the thread of Franco Lombardo, it also stated about
    the idea of sliding windows.

    Regards,
    Sunny
     
    , Jun 30, 2005
    #8
  9. wrote:
    > First of all, thanks for the replies. (ESPECIALLY THOMAS ^_^;)
    > Basically, one billion recordset is just an extreme example to emphasis
    > my problems: How to poplulate a JTable no matter how big is the
    > recordset.


    You are acting like a kindergarten child here. It has been pointed out
    to you that already from a usability point of view a JTable "no matter
    how big" is absolute nonsense. It has also been demonstrated that a "no
    matter how big" table will at some point lead to internal arithmetic
    overflow in the Swing GUI system. The layout managers, the scroll bars,
    the scroll pane, the view port, the JTable, etc. will all blow up at
    some point. You can stomp with your feed and cry "but I want to", but
    that won't change reality.


    > I want a stand-alone application that reproduces the behaviour of
    > Microsoft'a Access queries. I want a window in which to write any SQL
    > SELECT statement, then a button that runs the query and shows the
    > results in a table. If the query extracts, say, 1,000,000 records, the
    > table has to show the results.


    Are you claiming that Access can display tables "no matter how big"?
    Wow, then Microsoft should stop selling software and instead go into the
    infinite improbability drive business.

    But wait, you just changed your story to a number which is three orders
    of magnitude smaller than your original number. Do you recognize that 1
    million is fare away from "no matter how big"? Even your original 1
    billion was fare away from "no matter how big"?

    > I think this is unambigous: I have a precise behaviour of another
    > program to reproduce.


    No, because you have no idea about the limitations of that other program
    (and it for sure has limitations). This is not precision at all. Instead
    of precision you are just making up numbers. When people pointed out to
    you that these numbers are ridiculous you pulled out new made-up
    numbers. But you couldn't leave it at that, you had to invalidate them
    by asking for "no matter how big".

    I won't bother to set a Followup-To this time. You ignored the last one,
    like you ignored the last advice. You and even added more groups. I have
    the vague feeling that you just want to troll. I will fix that on my
    side in my newsreader.

    /Thomas
    --
    The comp.lang.java.gui FAQ:
    ftp://ftp.cs.uu.nl/pub/NEWS.ANSWERS/computer-lang/java/gui/faq
    http://www.uni-giessen.de/faq/archiv/computer-lang.java.gui.faq/
     
    Thomas Weidenfeller, Jun 30, 2005
    #9
  10. Chris Uppal Guest

    Thomas Weidenfeller wrote:

    > wrote:
    > > First of all, thanks for the replies. (ESPECIALLY THOMAS ^_^;)
    > > Basically, one billion recordset is just an extreme example to emphasis
    > > my problems: How to poplulate a JTable no matter how big is the
    > > recordset.

    >
    > You are acting like a kindergarten child here. [...]
    > You can stomp with your feed and cry "but I want to", but
    > that won't change reality.


    I think you are misreading the OP here, and misreading him/her badly.

    Note that many DB toolsets include tools that allow you to run arbitrary
    queries. Sure they will fail if someone attempts to read the results of a
    sufficiently large query, but that doesn't make them useless -- far from it.
    It just means that it's the users' responsibility to use them sensibly.

    For more technical commentary see my other reply in this thread.

    -- chris
     
    Chris Uppal, Jun 30, 2005
    #10
  11. Chris Uppal Guest

    Thomas Weidenfeller wrote:

    > F'up set to cljg


    Original x-posting restored:
    c.l.j.g -- debatable whether I have anything to say that's relevant to that
    NG, but since you left it in, so will I.
    c.l.j.db -- this is as relevant to DB as it is to GUI.
    c.l.j.p -- 'cos that's the only group I read of the three, and I would
    rather see the replies (in any) to my posts.


    > What I didn't consider was that it is rather hard for many people to
    > slide a scrollbar by exactly one pixel. Todays cheap imprecise mouses
    > make it even difficult for skilled users. So a feasible number is maybe
    > 1/2 or 1/5 of the rows.


    Agreed. On this machine (a laptop with a touch pad, or whatever they're
    called) it is quite easy to control positioning at the pixel level, but I
    wouldn't claim that's the general case.

    There's another reason for wanting the minimum scroll quantum to be
    significantly less than one screensfull. If the scrolling "jumps" by more than
    a smallish fraction of the whole window then it's easy to loose the sense of
    continuity -- of the /same/ data being displayed in a different position --
    since there isn't enough overlap between the "before" and "after" images for
    pattern matching to pick up the similarities. That makes it easy to get lost
    in the data, to loose your sense of where you are in the list of rows. Perhaps
    that problem could be mitigated by using some sort of "smooth scrolling" rather
    than simply jumping when the scrollbar moves by a small amount. I have no idea
    how to implement that in a JTable, or even whether it's feasible at all without
    more-or-less completely re-writing the display logic.


    > > (From a usability POV it
    > > may be better not to scroll a whole window's worth, in which case the
    > > maximum should be lowered, but on the other hand, a row height of 16 is
    > > pretty high --

    >
    > The 16 is Sun's hardcoded default, which JTable.getRowHeight() will
    > return unless you hit it over the head. Sun not even adjusts the default
    > row height to the size of the font used in the rows. One of the many
    > things which are wrong with JTable.


    Fair enough. I haven't used JTable very much (only once, from unreliable
    memory), and so was talking more in the abstract than about JTable-specifics.


    > > Depending on various factors (such as row size, and the speed that the
    > > database can provide data) it may be perfectly reasonable to read the
    > > whole 50k rows into memory before displaying them in the table. So --
    > > providing that the dataset is small enough to be /viewed/ in a table --
    > > there may be no need at all for "fancy" implementation techniques such
    > > as sliding windows.

    >
    > I never did a sliding window implementation with JTable or in Swing. I
    > did it some time ago for a custom-made tree widget which was used to
    > navigate over a huge amount of data in a DB. It is rather simple if you
    > have control over both ends, DB and widget implementation. I would
    > imagine it is a real mess if you have to retro-fit it to a JTable.


    I suspect that an intermediate custom TableModel wouldn't be difficult --
    that's to say that plumbing the sliding window logic into a JTable looks easy
    enough (though I haven't tried it), it's the sliding window logic itself that
    might be tricky.

    The problem with any kind of sliding window is that it relies on random access
    to the underlying result set, and that will be to varying degrees
    problematical. The way the database works, the nature of the cursor (if that's
    the right word) -- forward-only, etc -- and the implementation of the DB driver
    will all affect the feasibility of such techniques. I have tried to use a
    sliding window technique with a JTable-like grid component (I wrote the
    component so I know that it didn't "misuse" the underlying model -- I can't
    claim that the same is true for JTable, it may be), and found it impossible to
    make it work acceptably. The problem was that with the databases I was talking
    to, I couldn't find any way to make scrolling /backwards/ up the table work
    without unacceptable delays as the DB was apparently forced to re-run the query
    to discover what row was at index, say, 85678. The problem could be mitigated
    by using a very large sliding window, but then we are getting into the
    territory of just copying /all/ the data into the model -- and that's a lot
    simpler to implement.

    In the end I found that -- where possible -- just lazy-loading all the data
    sequentially worked best from every point of view except memory consumption.
    In cases where that turned out to be too much of an issue, I found that using
    an LRU list of large "pages" (each several thousand cached rows) worked better
    than a sliding window since it caused less "thrashing" of the cached data as
    the user scrolled around. It was easier to implement too ;-)

    Incidentally, I also found that the ODBC driver for one of my databases, a
    PostgreSQL db, was caching so much row data itself, that a completely
    transparent implementation (not caching anything myself, but just going back to
    the result set for any needed data) worked very well for even rather large
    result sets. Unfortunately the same technique worked /exceedingly/ badly for
    other combinations of DB and ODBC driver. So I think that there is no single
    "best" solution, and not even a single acceptable solution -- the caching
    strategy /has/ to be controllable in some way -- perhaps by a pluggable
    strategy object, or (what I settled on) a choice of different caching wrappers
    around the real result set object.

    -- chris
     
    Chris Uppal, Jun 30, 2005
    #11
  12. Chris Uppal wrote:
    ....
    > In the end I found that -- where possible -- just lazy-loading all the data
    > sequentially worked best from every point of view except memory consumption.
    > In cases where that turned out to be too much of an issue, I found that using
    > an LRU list of large "pages" (each several thousand cached rows) worked better
    > than a sliding window since it caused less "thrashing" of the cached data as
    > the user scrolled around. It was easier to implement too ;-)

    ....

    Perhaps one could avoid memory problems by doing a lazy copy to a
    temporary file. Use the file as the data source for the table model.
    Always keep the first n rows of the result set in the file, when n is
    the highest row number the table model has requested. In terms of the
    paging view, the file would be the backing store from which pages would
    be swapped into memory.

    A large forwards jump, such as viewing the last row of the result set,
    would be slow as the intervening data was copied from the result set to
    the file. It may still be faster that having the database rerun the
    query if the user later goes backwards.

    Patricia
     
    Patricia Shanahan, Jun 30, 2005
    #12
  13. bugbear Guest

    wrote:
    > If the query extracts, say, 1,000,000 records, the
    > table has to show the results.
    > If I have enough time to click on the scroll-barr for a whole day, the
    > table has to show me all the records. Nothing more than MS Access
    > query, or the old black-and-green STRSQL command of AS400."


    Can the user scroll back up, having scrolled down?
    That alters things a little.

    BugBear
     
    bugbear, Jun 30, 2005
    #13
  14. Chris Uppal Guest

    Patricia Shanahan wrote:

    > > In the end I found that -- where possible -- just lazy-loading all the
    > > data sequentially worked best from every point of view except memory
    > > consumption. [...]

    >
    > Perhaps one could avoid memory problems by doing a lazy copy to a
    > temporary file. Use the file as the data source for the table model.
    > Always keep the first n rows of the result set in the file, when n is
    > the highest row number the table model has requested. In terms of the
    > paging view, the file would be the backing store from which pages would
    > be swapped into memory.


    An interesting idea. Somehow I think it might infuriate relational theory
    purists -- an additional point in its favour ;-)

    It suggests that a more general "architecture" using something like a decorator
    pattern might be worthwhile (assuming there's the need in the first place, of
    course). For instance my LRU-based suggestion could be layered on top of your
    temp file.


    > A large forwards jump, such as viewing the last row of the result set,
    > would be slow as the intervening data was copied from the result set to
    > the file. It may still be faster that having the database rerun the
    > query if the user later goes backwards.


    Especially as it would be possible to use a forwards-only cursor, which I have
    seen be as much as a couple of orders of magnitude faster than a more general
    cursor with random access.

    -- chris
     
    Chris Uppal, Jul 1, 2005
    #14
  15. Chris Uppal wrote:
    > Patricia Shanahan wrote:
    >
    >
    >>>In the end I found that -- where possible -- just lazy-loading all the
    >>>data sequentially worked best from every point of view except memory
    >>>consumption. [...]

    >>
    >>Perhaps one could avoid memory problems by doing a lazy copy to a
    >>temporary file. Use the file as the data source for the table model.
    >>Always keep the first n rows of the result set in the file, when n is
    >>the highest row number the table model has requested. In terms of the
    >>paging view, the file would be the backing store from which pages would
    >>be swapped into memory.

    >
    >
    > An interesting idea. Somehow I think it might infuriate relational theory
    > purists -- an additional point in its favour ;-)
    >
    > It suggests that a more general "architecture" using something like a decorator
    > pattern might be worthwhile (assuming there's the need in the first place, of
    > course). For instance my LRU-based suggestion could be layered on top of your
    > temp file.


    Indeed. However, it may not be needed. The operating system may provide
    enough in the way of prefetching and caching for a file to make an
    explicitly managed cache superfluous.

    >>A large forwards jump, such as viewing the last row of the result set,
    >>> would be slow as the intervening data was copied from the result set to
    >>> the file. It may still be faster that having the database rerun the
    >>> query if the user later goes backwards.

    >
    >
    > Especially as it would be possible to use a forwards-only cursor, which I have
    > seen be as much as a couple of orders of magnitude faster than a more general
    > cursor with random access.


    Good point.

    Patricia
     
    Patricia Shanahan, Jul 1, 2005
    #15
  16. David Harper Guest

    wrote:
    > Dear all,
    >
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system. In
    > fact, my real question is like this:
    > Are there any way to populate the JTable regardless about the size of
    > the recordset.
    >
    > Actually, I did some searching in google before I post this message. I
    > know some peoples using the method of sliding windows to solve this
    > problems. However, the implementation seems quite complicated. I will
    > very appreciate if any peoples have the similar experiences or ideas
    > that can share over here. Thanks in advance.


    Everyone seems to be focussing on the negative aspect here, so here are
    a few positive pointers.

    1. Look more closely at the TableModel interface in the
    javax.swing.table package. This is how a JTable interrogates a tabular
    data model -- it allows the JTable to request just the group of rows and
    columns of the table that are visible.

    Create a class which implements this interface and which translates the
    getValueAt method call into some (hopefully optimised) JDBC calls.

    You'll need to cache *some* values, but you can take advantage of the
    fact that the JTable is always going to display a contiguous set of rows
    of your database table.

    2. Whilst it's true, as Thomas Weidenfeller has said, that users will
    not have the manual dexterity to move a scroll bar by a single pixel,
    the JScrollBar and JScrollPane classes provide a method to scroll
    up/down by a page when the user clicks in the "trough" below or above
    the slider.

    3. DbVisualizer, from minq.se, uses a JTable to display the contents of
    a database table. It can show 10,000 rows of a database table without
    any problem, and I've used it to show many more than that.

    David Harper
    Cambridge, England
     
    David Harper, Jul 5, 2005
    #16
  17. ["Followup-To:" header set to comp.lang.java.gui.] On 2005-07-05,
    David Harper penned:
    >
    > Everyone seems to be focussing on the negative aspect here, so here
    > are a few positive pointers.
    >
    > 1. Look more closely at the TableModel interface in the
    > javax.swing.table package. This is how a JTable interrogates a
    > tabular data model -- it allows the JTable to request just the group
    > of rows and columns of the table that are visible.
    >
    > Create a class which implements this interface and which translates
    > the getValueAt method call into some (hopefully optimised) JDBC
    > calls.


    As I recently learned, if you want to also allow the user to
    meaningfully sort by column header, you'll also want to change its
    behavior to query the data source. By meaningfully, I mean sort the
    entire data set, not just the "page" you're looking at.

    --
    monique
     
    Monique Y. Mudama, Jul 5, 2005
    #17
  18. wrote:
    > Dear all,
    >
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system. In
    > fact, my real question is like this:
    > Are there any way to populate the JTable regardless about the size of
    > the recordset.
    >
    > Actually, I did some searching in google before I post this message. I
    > know some peoples using the method of sliding windows to solve this
    > problems. However, the implementation seems quite complicated. I will
    > very appreciate if any peoples have the similar experiences or ideas
    > that can share over here. Thanks in advance.
    >
    >
    > Regards,
    > Sunny
    >

    Create a custom TableModel, where you use database cursor access into
    the resultset. I think this is JDBC 2.0, but most drivers do support this.

    You don't even have to "cache" the data in a List or Vector. You can
    let the driver do it.

    Basically, your table model will scroll the resultset cursor to the row.
    column you want (requested by the JTable). It's fairly painless to do.
    I successfully display a 80,000 row (or more) resultset in a JTable in a
    matter of seconds. (Doesn't matter whether I have 80 rows or 80,000
    rows). The only time it's slow is if I grab the thumb and move to the
    bottom of the table, but then again, 1 pixel movement can be _a_lot_ of
    rows.

    -Bryan
     
    Bryan E. Boone, Jul 5, 2005
    #18
  19. steve Guest

    On Wed, 29 Jun 2005 17:24:04 +0800, wrote
    (in article <>):

    > Dear all,
    >
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system. In
    > fact, my real question is like this:
    > Are there any way to populate the JTable regardless about the size of
    > the recordset.
    >
    > Actually, I did some searching in google before I post this message. I
    > know some peoples using the method of sliding windows to solve this
    > problems. However, the implementation seems quite complicated. I will
    > very appreciate if any peoples have the similar experiences or ideas
    > that can share over here. Thanks in advance.
    >
    >
    > Regards,
    > Sunny
    >


    it is fairly easy,but you need to do some work. ( what is real fun is
    allowing edits on the table), you can get the lot into about 40k of source
    code.

    you need to implement table model, and whilst you are at it also implement
    a column sorter. ( watch out for dates)



    you will also need to decide how the records are filtered /ordered ( FROM
    THE DATABASE)



    take oracle for example.

    select * from atable WHERE ROWCOUNT<50 ( or something like that)



    this may or may not return the records in the same order, each time you issue
    the query. It depends on how the DB is feeling at any given moment.


    This is important, since you will need to keep 2 pointers ,
    1St record in display window
    last record in display window.

    but be careful that if you use the ORDER BY XYZ, that XYZ is indexed.
    or you will end up with a large sort on your hands ,and a rapidly expanding
    temp tablespace.



    if your records are not ordered, then it''s gonna be messy , when you need
    to get a previous window or get a future window.

    personally , i would note even allow the user to scroll a window of that
    size. I would break the data down, then allow a few menu selection boxes to
    filter the records. ( users generally do not just look at that many records
    unless they are LOOKING FOR SOMETHING), your task is to find the something
    and display it.



    the other thing to consider is that you could not even cache the records
    locally(if you wanted a real time view) ,because changes to the database
    would not be reflected in your scroll window, unless you continually re-get
    the results.
     
    steve, Jul 5, 2005
    #19
  20. steve Guest

    On Wed, 29 Jun 2005 17:24:04 +0800, wrote
    (in article <>):

    > Dear all,
    >
    > I got a very large recordset (around 1 billion records) require to
    > populate it into the JTable. As we all know, the standard way of
    > populating JTable are either using Vectors or TableModel. However, it
    > is not possible to load 1 billion records all at once. I do think many
    > peoples will challenge me why I will have such big recordset and I
    > should make some filtering or re-design the usage of the system. In
    > fact, my real question is like this:
    > Are there any way to populate the JTable regardless about the size of
    > the recordset.
    >
    > Actually, I did some searching in google before I post this message. I
    > know some peoples using the method of sliding windows to solve this
    > problems. However, the implementation seems quite complicated. I will
    > very appreciate if any peoples have the similar experiences or ideas
    > that can share over here. Thanks in advance.
    >
    >
    > Regards,
    > Sunny



    here

    http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/jdbc20/
    ScrollableResSetSample/ScrollableResSetSample.java.html

    >
     
    steve, Jul 5, 2005
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Raymond Arthur St. Marie II of III

    very Very VERY dumb Question About The new Set( ) 's

    Raymond Arthur St. Marie II of III, Jul 23, 2003, in forum: Python
    Replies:
    4
    Views:
    518
    Raymond Hettinger
    Jul 27, 2003
  2. shanx__=|;-

    very very very long integer

    shanx__=|;-, Oct 16, 2004, in forum: C Programming
    Replies:
    19
    Views:
    1,719
    Merrill & Michele
    Oct 19, 2004
  3. Abhishek Jha

    very very very long integer

    Abhishek Jha, Oct 16, 2004, in forum: C Programming
    Replies:
    4
    Views:
    468
    jacob navia
    Oct 17, 2004
  4. Peter

    Very very very basic question

    Peter, Feb 8, 2005, in forum: C Programming
    Replies:
    14
    Views:
    542
    Dave Thompson
    Feb 14, 2005
  5. Hung Huynh
    Replies:
    8
    Views:
    347
    Bob Barrows
    Sep 24, 2003
Loading...

Share This Page