Results 1 to 3 of 3
  1. #1
    evan087 is offline Member
    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Default Fetch database resultset in portions/blocks

    Hi everyone,

    I have connected my java application to a firebird database. I have a number of tables in the database that can become very big.

    My question:

    Is there a way to load the data from my SQL query to the jTable in blocks or portions rather than have the entire resultset saved to memory first? If I can make it populate the jTable in portions then there will be no significant delay when the user views a jTable.

    Snippet of code below:

    Java Code:
    public class DatabaseTableModel extends DefaultTableModel 
    {
      private final QueryRunner queryRunner = new QueryRunner();
      protected Vector column, data, row;
      private String columnNames = "", changes = "";
      private Object table;
      private Connection connection;
      protected ResultSet resultSet, rowCount;
      protected int numColumns = 0, rowsLoadedThisTime = 0;
      protected Boolean scrollBarNearBottom = true, finishedLoading = false;
      
      public DatabaseTableModel(Connection con, Object tableName) throws SQLException 
      {
          table = tableName;
          connection = con;
          
          String sql = "SELECT * FROM " + tableName;
    
          queryRunner.query(con, sql, new ResultSetHandler() 
          {
              public Object handle(ResultSet rs) throws SQLException 
              {
                  try
                  {
                    resultSet = rs;
                  
                    // extract the column names
                    numColumns = resultSet.getMetaData().getColumnCount();
                    column = new Vector();
                  
                    for (int i = 1; i <= numColumns; i++) 
                    {
                        column.add(resultSet.getMetaData().getColumnName(i));
                    }
                    
                    // extract the data
                    data = new Vector();
                    
                    loadMore();
                    
                  }
                  
                  catch(Exception e)
                  {
                      System.out.println(e);
                  }
                 
                  return null;
              }
           });
      }
      
      protected void loadMore()
      {     
          if (scrollBarNearBottom)        //Boolean flag triggered when scroll bar reaches bottom of scrollpane
          {    
             try
             {                
                finishedLoading = true;    //Boolean flag triggered when all data from SQL query has been loaded into jTable
             
                //if there are more results then fetch the next 50000 or fetch until last result is reached
                while ((resultSet.next() && (rowsLoadedThisTime < 50000)) ) 
                {
                    finishedLoading = false;
                    
                    row = new Vector();
                
                    for (int i = 1; i <= numColumns; i++) 
                    {
                    row.add(resultSet.getString(i));
                    }
                
                    data.add(row);
                    
                    rowsLoadedThisTime = rowsLoadedThisTime + 1;
                }
                
                rowsLoadedThisTime = 0;
                scrollBarNearBottom = false;
            
                setDataVector(data, column);
             }
          
             catch(Exception e)
             {
                System.out.println(e); 
             }       
          }
       }   
    }
    Any help will be greatly appreciated..

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,008
    Rep Power
    20

    Default Re: Fetch database resultset in portions/blocks

    You'll need to look at the SQL specifics for your database, but it might have a LIMIT keyword.
    In fact ROWS.

    Which you could, of course, have found easily yourself.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    evan087 is offline Member
    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Default Re: Fetch database resultset in portions/blocks

    Thank you very much for your advice. I have implemented what you suggested.

Similar Threads

  1. To fetch rows of ms acess database using combobox
    By metagoth in forum AWT / Swing
    Replies: 2
    Last Post: 03-23-2012, 09:54 AM
  2. Replies: 0
    Last Post: 05-15-2011, 07:30 PM
  3. Is resultset the mirror image of database table???
    By Stephen Douglas in forum New To Java
    Replies: 2
    Last Post: 04-10-2010, 03:01 PM
  4. how to fetch 20,000 records from excel sheet & insert into access database using Java
    By santosh_tamse in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 02-22-2009, 10:24 AM
  5. Replies: 11
    Last Post: 02-20-2009, 02:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •