JTable bound to a database with lazy loading

I’ve been doing experiments with JTables bound to database tables in the past. But I was not satisfied with the result to be honest. The pagination helps but there is still noticeable pauses each time a database query has to be issued. So I started looking into other possibilities.

I come up with the idea of doing lazy loading and presenting some fake data until the data is really retrieved from the database, I did a small proof of concept and seems to work ok.

The solution goes like this. It uses Beans Binding to bind a JTable to a custom List like in the previous post. This custom list (ResultListDerby in the example below) returns Beans (the Bean is called Customer in the example) that are initially empty. By empty I mean that all its properties return something like “<data not retrieved from the database yet>”. So the JTable will be initially full of “<data not retrieved yet>”.

The picture above shows the JTable showing the List of Customers. Each row represents a Customer instance.

Having empty/fake Customer beans allow a fast drawing to screen but it’s not very useful. Now, if at the same point as each Customer is created we create SwingWorker and schedule it to run in a Executor then we can get something more meaningful. So what we do in the SwingWorker? The SwingWorker, you guessed it, will retrieve the data from the database and update the bean (look at ResultListDerby.getItem() method). When the bean (Customer.java) is updated it will notify its listeners of the changes, automatically by means of bound properties , in this case JTableBinding will be notified and the JTable that will repaint the cells to reflect the changes in the Bean. (Check how bound properties are implemented with of PropertyChangeSupport in Customer.java below). The rows will be updated one by one, as soon as the Executor completes a SwingWorker task a row will be updated.

This solution is free from annoying GUI freezes. The beans are always created fast because there is no need to go to the database to create them and that keeps the GUI liveness high. By moving the database access to SwingWorkers we keep the EDT (event dispatch thread) lightweight. The solution also avoids having too many running SwingWorkers at the same time by using a ExecutorService instead of executing them directly via the SwingWorker.execute() method (Check ResultListDerby.getItem() to see how it’s done).

package jtablepagination;

import java.lang.ref.WeakReference;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.AbstractList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.SwingWorker;

/****
*
* @author Ruben Laguna <ruben.laguna at gmail.com>
*/
public class MainWindow extends javax.swing.JFrame {

  /**** Creates new form MainWindow */
  public MainWindow() {
    initComponents();
  }

  /**** This method is called from within the constructor to
  * initialize the form.
  * WARNING: Do NOT modify this code. The content of this method is
  * always regenerated by the Form Editor.
  */
  @SuppressWarnings("unchecked")
  private void initComponents() {
    bindingGroup = new org.jdesktop.beansbinding.BindingGroup();

    list1 = getList();
    jScrollPane1 = new javax.swing.JScrollPane();
    jTable1 = new javax.swing.JTable();

    setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

    org.jdesktop.swingbinding.JTableBinding jTableBinding = org.jdesktop.swingbinding.SwingBindings.createJTableBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, list1, jTable1);
    org.jdesktop.swingbinding.JTableBinding.ColumnBinding columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${id}"));
    columnBinding.setColumnName("id");
    columnBinding.setColumnClass(Integer.class);
    columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${firstName}"));
    columnBinding.setColumnName("first name");
    columnBinding.setColumnClass(String.class);
    columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${lastName}"));
    columnBinding.setColumnName("last name");
    columnBinding.setColumnClass(String.class);
    bindingGroup.addBinding(jTableBinding);
    jTableBinding.bind();
    jScrollPane1.setViewportView(jTable1);

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
    layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(layout.createSequentialGroup()
      .addContainerGap()
      .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 464, Short.MAX_VALUE)
      .addContainerGap())
    );
    layout.setVerticalGroup(
    layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(layout.createSequentialGroup()
      .addContainerGap()
      .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 344, Short.MAX_VALUE)
      .addContainerGap())
    );

    bindingGroup.bind();

    pack();
  }

  private List<Customer> getList() {
    List<Customer> toReturn = new ResultListDerby();
    return toReturn;
  }

  /****
  * @param args the command line arguments
  */
  public static void main(String args[]) {
    java.awt.EventQueue.invokeLater(new Runnable() {
      public void run() {
        new MainWindow().setVisible(true);
      }
    });
  }

  // Variables declaration - do not modify
  private javax.swing.JScrollPane jScrollPane1;
  private javax.swing.JTable jTable1;
  private java.util.List<Customer> list1;
  private org.jdesktop.beansbinding.BindingGroup bindingGroup;
  // End of variables declaration
}

class ResultListDerby extends AbstractList {

  private Connection connection;
  private final ExecutorService ex = Executors.newSingleThreadExecutor();
  private int size = -1;
  //maintain a cache with the Customer instances already created and alive
  private Map<Integer, WeakReference<Customer>> cache = new HashMap<Integer, WeakReference<Customer>>();

  ResultListDerby() {
    try {
      Class.forName("org.apache.derby.jdbc.ClientDriver");
      this.connection = DriverManager.getConnection("jdbc:derby://localhost:1527/customer", "nbuser", "nbuser");
    } catch (Exception ex) {
      Logger.getLogger(ResultListDerby.class.getName()).log(Level.SEVERE, null, ex);
      throw new RuntimeException(ex);
    }
  }

  public int size() {
    if (this.size == -1) {
      try {
        final ResultSet resultset = connection.createStatement().executeQuery("SELECT COUNT (ID) FROM CUSTOMERS");
        resultset.next();
        final int toReturn = resultset.getInt(1);
        this.size = toReturn;
      } catch (SQLException ex) {
        Logger.getLogger(ResultListDerby.class.getName()).log(Level.SEVERE, null, ex);
        throw new RuntimeException(ex);
      }
    }
    return this.size;
  }

  public Customer get(int rowIndex) {
    //this way we ensure that we don't create several Customer instances
    //for the same id. Otherwise it would be confusing for beansbindings.
    Customer toReturn = null;
    if (null != this.cache.get(rowIndex)) {
      toReturn = this.cache.get(rowIndex).get();
    }
    if (null == toReturn) {
      toReturn = getItem(rowIndex);
      this.cache.put(rowIndex, new WeakReference<Customer>(toReturn));
    }

    return toReturn;
  }

  private Customer getItem(final int j) {
    final Customer customer = new Customer(j);

    Runnable task = new SwingWorker() {

    private String firstNameValue;
    private String lastNameValue;

    @Override
    protected Object doInBackground() throws Exception {
      //this is always executed in a different thread from the current thread
      //it doesn't matter if the current thread is the EDT or a thread in the Executor
      final java.sql.Statement stmt = connection.createStatement();
      ResultSet executeQuery = stmt.executeQuery("SELECT ID, FIRST_NAME,LAST_NAME FROM CUSTOMERS ORDERDESC OFFSET " + j + " ROWS FETCH NEXT ROWS ONLY");
      executeQuery.next();
      firstNameValue = executeQuery.getString(2);
      lastNameValue = executeQuery.getString(3);
      return null;
    }

    @Override
    protected void done() {
      //this in the other hand will always be executed on the EDT.
      //This has to be done in the EDT because currently JTableBinding
      //is not smart enough to realize that the notification comes in another
      //thread and do a SwingUtilities.invokeLater. So we are force to execute this
      // in the EDT. Seee http://markmail.org/thread/6ehh76zt27qc5fis and
      // https://beansbinding.dev.java.net/issues/show_bug.cgi?id=60

      customer.setFirstName(firstNameValue);
      customer.setLastName(lastNameValue);
      Logger.getLogger(ResultListDerby.class.getName()).info("updating customer " + customer);
    }
  };

  //NOTE that we don do task.execute()
  //posting the task to an Executor gives more control on
  //how many threads are created.
  ex.execute(task);
  return customer;
  }
}

and Customer.java

package jtablepagination;

import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeSupport;
import java.util.logging.Logger;

/****
*
* @author Ruben Laguna <ruben.laguna at gmail.com>
*/
public class Customer {

  private static final Logger LOG = Logger.getLogger(Customer.class.getName());
  private static final String NOT_RETRIEVED_YET = "<not retrieved yet>";
  private int id = 0;
  public static final String PROP_ID = "id";
  protected String firstName = NOT_RETRIEVED_YET;
  public static final String PROP_FIRSTNAME = "firstName";
  protected String lastName = NOT_RETRIEVED_YET;
  public static final String PROP_LASTNAME = "lastName";
  private PropertyChangeSupport propertyChangeSupport = new PropertyChangeSupport(this);

  Customer(int j) {
    this.id = j;
  }

  public int getId() {
    return id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    String oldFirstName = this.firstName;
    this.firstName = firstName;
    propertyChangeSupport.firePropertyChange(PROP_FIRSTNAME, oldFirstName, firstName);
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    String oldLastName = this.lastName;
    this.lastName = lastName;
    if (propertyChangeSupport.hasListeners(PROP_LASTNAME)) {
      LOG.info("notifying!!!");
    } else {
      LOG.info("there is no listeners for the property");
    }

    propertyChangeSupport.firePropertyChange(PROP_LASTNAME, oldLastName, lastName);
  }

  public void addPropertyChangeListener(PropertyChangeListener listener) {
    LOG.info("add listener to customer " + this.id);
    propertyChangeSupport.addPropertyChangeListener(listener);
  }

  public void removePropertyChangeListener(PropertyChangeListener listener) {
    propertyChangeSupport.removePropertyChangeListener(listener);
  }

  @Override
  public String toString() {
    return "id: " + id + " firstName: " + firstName;
  }
}

References

SwingWorker: http://java.sun.com/javase/6/docs/api/javax/swing/SwingWorker.html
WeakReference: http://java.sun.com/javase/6/docs/api/java/lang/ref/WeakReference.html
Bound Properties: http://java.sun.com/docs/books/tutorial/javabeans/properties/bound.html