Using JDBC with Lazy Query Container and FilteringTable

Introduction

Populating display tables from a database is a deceptively complicated operation, especially when mixing multiple techniques together. This page provides an example of one way to efficiently load data from a SQL database table into a filterable UI, using the Lazy Query Container and FilteringTable add-ons.

Note: Do not use the SQLContainer package. This is buggy and will have your database and garbage collector crunching in loops.

Query and QueryFactory implementation

The place to start is the Lazy Query Container’s (LQC) Query interface. This is where the interface with your database happens. This example access a database table with computer statistics. It’s read-only. How to log and access your JDBC connection differs in each environment; they are treated generically here. Only select imports are included.

Java

  1. import org.vaadin.addons.lazyquerycontainer.Query;
  2. import org.vaadin.addons.lazyquerycontainer.QueryDefinition;
  3. import org.vaadin.addons.lazyquerycontainer.QueryFactory;
  5. import com.vaadin.data.Container.Filter;
  6. import com.vaadin.data.Item;
  7. import com.vaadin.data.util.ObjectProperty;
  8. import com.vaadin.data.util.PropertysetItem;
  9. import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;
  10. import com.vaadin.data.util.sqlcontainer.query.generator.filter.QueryBuilder;
  12. /**
  13.  * Query for using the database's device-status table as a data source
  14.  * for a Vaadin container (table).
  15.  */
  16. public class DeviceStatusQuery implements Query {
  17.   private static  final Logger log = LoggerFactory.getLogger(DeviceStatusQuery.class);
  19.   /**
  20.    * The table column names. Use these instead of typo-prone magic strings.
  21.    */
  22.   public static enum Column {
  23.     hostname, loc_id, update_when, net_ip, lan_ip, lan_mac, hardware,
  24.     opsys, image, sw_ver, cpu_load, proc_count, mem_usage, disk_usage;
  26.     public boolean is(Object other) {
  27.       if (other instanceof String)
  28.         return this.toString().equals(other);
  29.       else
  30.         return (this == other);
  31.     }
  32.   };
  34.   public static class Factory implements QueryFactory {
  35.     private int locId;
  37.     /**
  38.      * Constructor
  39.      * @param locId - location ID
  40.      */
  41.     public Factory(int locId) {
  42.       this.locId = locId;
  43.     }
  45.     @Override
  46.     public Query constructQuery(QueryDefinition def) {
  47.       return new DeviceStatusQuery(def, locId);
  48.     }
  49.   }//class Factory
  51.   /////// INSTANCE ///////
  53.   private String countQuery;
  54.   private String fetchQuery;
  55.   /** Borrow from SQLContainer to build filter queries */
  56.   private StatementHelper stmtHelper = new StatementHelper();
  58.   /**
  59.    * Constructor
  60.    * @param locId - location ID
  61.    * @param userId - ID of user viewing the data
  62.    */
  63.   private DeviceStatusQuery(QueryDefinition def, int locId) {
  64.     Build filters block List<Filter> filters = def.getFilters();
  65.     String filterStr = null;
  66.     if (filters != null && !filters.isEmpty())
  67.       filterStr = QueryBuilder.getJoinedFilterString(filters, "AND", stmtHelper);
  69.     // Count query
  70.     StringBuilder query = new StringBuilder( "SELECT COUNT(*) FROM device_status");
  71.     query.append(" WHERE loc_id=").append(locId);
  73.     if (filterStr != null)
  74.       query.append(" AND ").append(filterStr);
  76.     this.countQuery = query.toString();
  78.     // Fetch query
  79.     query = new StringBuilder(
  80.       "SELECT hostname, loc_id, update_when, net_ip, lan_ip, " +
  81.       "lan_mac, hardware, opsys, image, sw_ver, cpu_load, " +
  82.       "proc_count, mem_usage, disk_usage FROM device_status");
  83.     query.append(" WHERE loc_id=").append(locId);
  85.     if (filterStr != null)
  86.       query.append(" AND ").append(filterStr);
  88.     // Build Order by
  89.     Object[] sortIds = def.getSortPropertyIds();
  90.     if (sortIds != null && sortIds.length > 0) {
  91.       query.append(" ORDER BY ");
  92.       boolean[] sortAsc = def.getSortPropertyAscendingStates();
  93.       assert sortIds.length == sortAsc.length;
  95.       for (int si = 0; si < sortIds.length; ++si) {
  96.         if (si > 0) query.append(',');
  98.         query.append(sortIds[si]);
  99.         if (sortAsc[si]) query.append(" ASC");
  100.         else query.append(" DESC");
  101.       }
  102.     }
  103.     else query.append(" ORDER BY hostname");
  105.     this.fetchQuery = query.toString();
  107.     log.trace("DeviceStatusQuery count: {}", this.countQuery);
  108.     log.trace("DeviceStatusQuery fetch: {}", this.fetchQuery);
  109.   }//constructor
  111.   @Override
  112.   public int size() {
  113.     int result = 0;
  114.     try (Connection conn = Database.getConnection()) {
  115.       PreparedStatement stmt = conn.prepareStatement(this.countQuery);
  116.       stmtHelper.setParameterValuesToStatement(stmt);
  117.       ResultSet rs = stmt.executeQuery();
  118.       if (rs.next()) result = rs.getInt(1);
  120.       stmt.close();
  121.     }
  122.     catch (SQLException ex) {
  123.       log.error("DB access failure", ex);
  124.     }
  126.     log.trace("DeviceStatusQuery size=\{}", result);
  127.     return result;
  128.   }
  130.   @Override
  131.   public List<Item> loadItems(int startIndex, int count) {
  132.     List<Item> items = new ArrayList<Item>();
  133.     try (Connection conn = Database.getConnection()) {
  134.       String q = this.fetchQuery + " LIMIT " + count + " OFFSET " + startIndex;
  135.       PreparedStatement stmt = conn.prepareStatement(q);
  136.       stmtHelper.setParameterValuesToStatement(stmt);
  138.       ResultSet rs = stmt.executeQuery();
  139.       while (rs.next()) {
  140.         PropertysetItem item = new PropertysetItem();
  141.         // Include the data type parameter on ObjectProperty any time the value could be null
  142.         item.addItemProperty(Column.hostname,
  143.           new ObjectProperty<String>(rs.getString(1), String.class));
  144.         item.addItemProperty(Column.loc_id,
  145.           new ObjectProperty<Integer>(rs.getInt(2), Integer.class));
  146.         item.addItemProperty(Column.update_when,
  147.           new ObjectProperty<Timestamp>(rs.getTimestamp(3), Timestamp.class));
  148.         item.addItemProperty(Column.net_ip,
  149.           new ObjectProperty<String>(rs.getString(4)));
  150.         item.addItemProperty(Column.lan_ip,
  151.           new ObjectProperty<String>(rs.getString(5)));
  152.         item.addItemProperty(Column.lan_mac,
  153.           new ObjectProperty<String>(rs.getString(6)));
  154.         item.addItemProperty(Column.hardware,
  155.           new ObjectProperty<String>(rs.getString(7)));
  156.         item.addItemProperty(Column.opsys,
  157.           new ObjectProperty<String>(rs.getString(8)));
  158.         item.addItemProperty(Column.image,
  159.           new ObjectProperty<String>(rs.getString(9)));
  160.         item.addItemProperty(Column.sw_ver,
  161.           new ObjectProperty<String>(rs.getString(10)));
  162.         item.addItemProperty(Column.cpu_load,
  163.           new ObjectProperty<String>(rs.getString(11)));
  164.         item.addItemProperty(Column.proc_count,
  165.           new ObjectProperty<Integer>(rs.getInt(12)));
  166.         item.addItemProperty(Column.mem_usage,
  167.           new ObjectProperty<Integer>(rs.getInt(13)));
  168.         item.addItemProperty(Column.disk_usage,
  169.           new ObjectProperty<Integer>(rs.getInt(14)));
  171.         items.add(item);
  172.       }
  173.       rs.close();
  174.       stmt.close();
  175.     }
  176.     catch (SQLException ex) {
  177.       log.error("DB access failure", ex);
  178.     }
  180.     log.trace("DeviceStatusQuery load {} items from {}={} found", count,
  181.         startIndex, items.size());
  182.     return items;
  183.   } //loadItems()
  185. /**
  186.  * Only gets here if loadItems() fails, so return an empty state.
  187.  * Throwing from here causes an infinite loop.
  188.  */
  189.  @Override
  190.  public Item constructItem() {
  191.   PropertysetItem item = new PropertysetItem();
  192.   item.addItemProperty(Column.hostname, new ObjectProperty<String>(""));
  193.   item.addItemProperty(Column.loc_id, new ObjectProperty<Integer>(-1));
  194.   item.addItemProperty(Column.update_when,
  195.     new ObjectProperty<Timestamp>(new Timestamp(System.currentTimeMillis())));
  196.   item.addItemProperty(Column.net_ip, new ObjectProperty<String>(""));
  197.   item.addItemProperty(Column.lan_ip, new ObjectProperty<String>(""));
  198.   item.addItemProperty(Column.lan_mac, new ObjectProperty<String>(""));
  199.   item.addItemProperty(Column.hardware, new ObjectProperty<String>(""));
  200.   item.addItemProperty(Column.opsys, new ObjectProperty<String>(""));
  201.   item.addItemProperty(Column.image, new ObjectProperty<String>(""));
  202.   item.addItemProperty(Column.sw_ver, new ObjectProperty<String>(""));
  203.   item.addItemProperty(Column.cpu_load, new ObjectProperty<String>(""));
  204.   item.addItemProperty(Column.proc_count, new ObjectProperty<Integer>(0));
  205.   item.addItemProperty(Column.mem_usage, new ObjectProperty<Integer>(0));
  206.   item.addItemProperty(Column.disk_usage, new ObjectProperty<Integer>(0));
  208.   log.warn("Shouldn't be calling DeviceStatusQuery.constructItem()");
  209.     return item;
  210.   }
  212.   @Override
  213.   public boolean deleteAllItems() {
  214.     throw new UnsupportedOperationException();
  215.   }
  217.   @Override
  218.   public void saveItems(List<Item> arg0, List<Item> arg1, List<Item> arg2) {
  219.     throw new UnsupportedOperationException();
  220.   }
  221. }

Using the Query with FilteringTable

Now that we have our Query, we need to create a table to hold it. Here’s one of many ways to do it with FilteringTable.

Java

  1. import org.tepi.filtertable.FilterDecorator;
  2. import org.tepi.filtertable.numberfilter.NumberFilterPopupConfig;
  3. import org.vaadin.addons.lazyquerycontainer.LazyQueryContainer;
  5. import com.vaadin.data.Property;
  6. import com.vaadin.server.Resource;
  7. import com.vaadin.shared.ui.datefield.Resolution;
  8. import com.vaadin.ui.DateField;
  9. import com.vaadin.ui.AbstractTextField.TextChangeEventMode;
  11. /**
  12.  * Filterable table of device statuses.
  13.  */
  14. public class DeviceStatusTable extends FilterTable {
  15.   private final
  16.   String[] columnHeaders = {"Device", "Site", "Last Report", "Report IP",
  17.       "LAN IP", "MAC Adrs", "Hardware", "O/S", "Image", "Software", "CPU"
  18.       "Load", "Processes", "Memory Use", "Disk Use"};
  20.   /**
  21.    * Configuration this table for displaying of DeviceStatusQuery data.
  22.    */
  23.   public void configure(LazyQueryContainer dataSource) {
  24.     super.setFilterGenerator(new LQCFilterGenerator(dataSource));
  25.     super.setFilterBarVisible(true);
  26.     super.setSelectable(true);
  27.     super.setImmediate(true);
  28.     super.setColumnReorderingAllowed(true);
  29.     super.setColumnCollapsingAllowed(true);
  30.     super.setSortEnabled(true);
  32.     dataSource.addContainerProperty(Column.hostname, String.class, null, true, true);
  33.     dataSource.addContainerProperty(Column.loc_id, Integer.class, null, true, false);
  34.     dataSource.addContainerProperty(Column.update_when, Timestamp.class, null, true, true);
  35.     dataSource.addContainerProperty(Column.net_ip, String.class, null, true, true);
  36.     dataSource.addContainerProperty(Column.lan_ip, String.class, null, true, true);
  37.     dataSource.addContainerProperty(Column.lan_mac, String.class, null, true, true);
  38.     dataSource.addContainerProperty(Column.hardware, String.class, null, true, true);
  39.     dataSource.addContainerProperty(Column.opsys, String.class, null, true, true);
  40.     dataSource.addContainerProperty(Column.image, String.class, null, true, true);
  41.     dataSource.addContainerProperty(Column.sw_ver, String.class, null, true, true);
  42.     dataSource.addContainerProperty(Column.cpu_load, String.class, null, true, true);
  43.     dataSource.addContainerProperty(Column.proc_count, Integer.class, null, true, true);
  44.     dataSource.addContainerProperty(Column.mem_usage, Integer.class, null, true, true);
  45.     dataSource.addContainerProperty(Column.disk_usage, Integer.class, null, true, true);
  47.     super.setContainerDataSource(dataSource);
  48.     super.setColumnHeaders(columnHeaders);
  49.     super.setColumnCollapsed(Column.lan_mac, true);
  50.     super.setColumnCollapsed(Column.opsys, true);
  51.     super.setColumnCollapsed(Column.image, true);
  52.     super.setFilterFieldVisible(Column.loc_id, false);
  53.   }
  55.   @Override
  56.   protected String formatPropertyValue(Object rowId, Object colId, Property<?> property) {
  57.     if (Column.loc_id.is(colId)) {
  58.       // Example of how to translate a column value
  59.       return Hierarchy.getLocation(((Integer) property.getValue())).getShortName();
  60.     } else if (Column.update_when.is(colId)) {
  61.       // Example of how to format a value.
  62.       return ((java.sql.Timestamp) property.getValue()).toString().substring(0, 19);
  63.     }
  65.     return super.formatPropertyValue(rowId, colId, property);
  66.   }
  68.   /**
  69.    * Filter generator that triggers a refresh of a LazyQueryContainer
  70.    * whenever the filters change.
  71.    */
  72.   public class LQCFilterGenerator implements FilterGenerator {
  73.     private final LazyQueryContainer lqc;
  75.     public LQCFilterGenerator(LazyQueryContainer lqc) {
  76.       this.lqc = lqc;
  77.     }
  79.     @Override
  80.     public Filter generateFilter(Object propertyId, Object value) {
  81.       return null;
  82.     }
  84.     @Override
  85.     public Filter generateFilter(Object propertyId, Field<?> originatingField) {
  86.       return null;
  87.     }
  89.     @Override
  90.     public AbstractField<?> getCustomFilterComponent(Object propertyId) {
  91.       return null;
  92.     }
  94.     @Override
  95.     public void filterRemoved(Object propertyId) {
  96.       this.lqc.refresh();
  97.     }
  99.     @Override
  100.     public void filterAdded(Object propertyId, Class<? extends Filter> filterType, Object value) {
  101.       this.lqc.refresh();
  102.     }
  104.     @Override
  105.     public Filter filterGeneratorFailed(Exception reason, Object propertyId, Object value) {
  106.       return null;
  107.     }
  108.   }
  109. }

Put them together on the UI

Now we have our Container that reads from the database, and a Table for displaying them, lets put the final pieces together somewhere in some UI code:

Java

  1. final DeviceStatusTable table = new DeviceStatusTable();
  2. table.setSizeFull();
  4. DeviceStatusQuery.Factory factory = new DeviceStatusQuery.Factory(locationID);
  5. final LazyQueryContainer statusDataContainer = new LazyQueryContainer(factory,
  6.   /*index*/ null, /*batchSize*/ 50, false);
  7. statusDataContainer.getQueryView().setMaxCacheSize(300);
  8. table.configure(statusDataContainer);
  10. layout.addComponent(table);
  11. layout.setHeight(100f, Unit.PERCENTAGE); // no scrollbar
  13. // Respond to row click
  14. table.addValueChangeListener(new Property.ValueChangeListener() {
  15.   @Override
  16.   public void valueChange(ValueChangeEvent event) {
  17.     Object index = event.getProperty().getValue();
  18.     if (index != nulll) {
  19.       int locId = (Integer) statusDataContainer.getItem(index)
  20.           .getItemProperty(DeviceStatusQuery.Column.loc_id).getValue();
  21.       doSomething(locId);
  22.       table.setValue(null); //visually deselect
  23.     }
  24.   }
  25. });

And finally, since we’re using SQLContainer‘s QueryBuilder, depending on your database you may need to include something like this once during your application startup:

Java

  1. import com.vaadin.data.util.sqlcontainer.query.generator.filter.QueryBuilder;
  2. import com.vaadin.data.util.sqlcontainer.query.generator.filter.StringDecorator;
  4. // Configure Vaadin SQLContainer to work with MySQL
  5. QueryBuilder.setStringDecorator(new StringDecorator("`","`"));