Ruben Laguna's blog

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 @Bean@s (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 @SwingWorker@s we keep the EDT (event dispatch thread) lightweight. The solution also avoids having too many running @SwingWorker@s 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).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
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")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    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();
    }// </editor-fold>                        



    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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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

Comments