123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418 |
- ---
- title: Using JDBC with Lazy Query Container and FilteringTable
- order: 3
- layout: page
- ---
-
- [[using-jdbc-with-lazy-query-container-and-filteringtable]]
- = 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.
-
- [source,java]
- ....
- import org.vaadin.addons.lazyquerycontainer.Query;
- import org.vaadin.addons.lazyquerycontainer.QueryDefinition;
- import org.vaadin.addons.lazyquerycontainer.QueryFactory;
-
- import com.vaadin.data.Container.Filter;
- import com.vaadin.data.Item;
- import com.vaadin.data.util.ObjectProperty;
- import com.vaadin.data.util.PropertysetItem;
- import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;
- import com.vaadin.data.util.sqlcontainer.query.generator.filter.QueryBuilder;
-
- /**
- * Query for using the database's device-status table as a data source
- * for a Vaadin container (table).
- */
- public class DeviceStatusQuery implements Query {
- private static final Logger log = LoggerFactory.getLogger(DeviceStatusQuery.class);
-
- /**
- * The table column names. Use these instead of typo-prone magic strings.
- */
- public static enum Column {
- hostname, loc_id, update_when, net_ip, lan_ip, lan_mac, hardware,
- opsys, image, sw_ver, cpu_load, proc_count, mem_usage, disk_usage;
-
- public boolean is(Object other) {
- if (other instanceof String)
- return this.toString().equals(other);
- else
- return (this == other);
- }
- };
-
- public static class Factory implements QueryFactory {
- private int locId;
-
- /**
- * Constructor
- * @param locId - location ID
- */
- public Factory(int locId) {
- this.locId = locId;
- }
-
- @Override
- public Query constructQuery(QueryDefinition def) {
- return new DeviceStatusQuery(def, locId);
- }
- }//class Factory
-
- /////// INSTANCE ///////
-
- private String countQuery;
- private String fetchQuery;
- /** Borrow from SQLContainer to build filter queries */
- private StatementHelper stmtHelper = new StatementHelper();
-
- /**
- * Constructor
- * @param locId - location ID
- * @param userId - ID of user viewing the data
- */
- private DeviceStatusQuery(QueryDefinition def, int locId) {
- Build filters block List<Filter> filters = def.getFilters();
- String filterStr = null;
- if (filters != null && !filters.isEmpty())
- filterStr = QueryBuilder.getJoinedFilterString(filters, "AND", stmtHelper);
-
- // Count query
- StringBuilder query = new StringBuilder( "SELECT COUNT(*) FROM device_status");
- query.append(" WHERE loc_id=").append(locId);
-
- if (filterStr != null)
- query.append(" AND ").append(filterStr);
-
- this.countQuery = query.toString();
-
- // Fetch query
- query = new StringBuilder(
- "SELECT hostname, loc_id, update_when, net_ip, lan_ip, " +
- "lan_mac, hardware, opsys, image, sw_ver, cpu_load, " +
- "proc_count, mem_usage, disk_usage FROM device_status");
- query.append(" WHERE loc_id=").append(locId);
-
- if (filterStr != null)
- query.append(" AND ").append(filterStr);
-
- // Build Order by
- Object[] sortIds = def.getSortPropertyIds();
- if (sortIds != null && sortIds.length > 0) {
- query.append(" ORDER BY ");
- boolean[] sortAsc = def.getSortPropertyAscendingStates();
- assert sortIds.length == sortAsc.length;
-
- for (int si = 0; si < sortIds.length; ++si) {
- if (si > 0) query.append(',');
-
- query.append(sortIds[si]);
- if (sortAsc[si]) query.append(" ASC");
- else query.append(" DESC");
- }
- }
- else query.append(" ORDER BY hostname");
-
- this.fetchQuery = query.toString();
-
- log.trace("DeviceStatusQuery count: {}", this.countQuery);
- log.trace("DeviceStatusQuery fetch: {}", this.fetchQuery);
- }//constructor
-
- @Override
- public int size() {
- int result = 0;
- try (Connection conn = Database.getConnection()) {
- PreparedStatement stmt = conn.prepareStatement(this.countQuery);
- stmtHelper.setParameterValuesToStatement(stmt);
- ResultSet rs = stmt.executeQuery();
- if (rs.next()) result = rs.getInt(1);
-
- stmt.close();
- }
- catch (SQLException ex) {
- log.error("DB access failure", ex);
- }
-
- log.trace("DeviceStatusQuery size=\{}", result);
- return result;
- }
-
- @Override
- public List<Item> loadItems(int startIndex, int count) {
- List<Item> items = new ArrayList<Item>();
- try (Connection conn = Database.getConnection()) {
- String q = this.fetchQuery + " LIMIT " + count + " OFFSET " + startIndex;
- PreparedStatement stmt = conn.prepareStatement(q);
- stmtHelper.setParameterValuesToStatement(stmt);
-
- ResultSet rs = stmt.executeQuery();
- while (rs.next()) {
- PropertysetItem item = new PropertysetItem();
- // Include the data type parameter on ObjectProperty any time the value could be null
- item.addItemProperty(Column.hostname,
- new ObjectProperty<String>(rs.getString(1), String.class));
- item.addItemProperty(Column.loc_id,
- new ObjectProperty<Integer>(rs.getInt(2), Integer.class));
- item.addItemProperty(Column.update_when,
- new ObjectProperty<Timestamp>(rs.getTimestamp(3), Timestamp.class));
- item.addItemProperty(Column.net_ip,
- new ObjectProperty<String>(rs.getString(4)));
- item.addItemProperty(Column.lan_ip,
- new ObjectProperty<String>(rs.getString(5)));
- item.addItemProperty(Column.lan_mac,
- new ObjectProperty<String>(rs.getString(6)));
- item.addItemProperty(Column.hardware,
- new ObjectProperty<String>(rs.getString(7)));
- item.addItemProperty(Column.opsys,
- new ObjectProperty<String>(rs.getString(8)));
- item.addItemProperty(Column.image,
- new ObjectProperty<String>(rs.getString(9)));
- item.addItemProperty(Column.sw_ver,
- new ObjectProperty<String>(rs.getString(10)));
- item.addItemProperty(Column.cpu_load,
- new ObjectProperty<String>(rs.getString(11)));
- item.addItemProperty(Column.proc_count,
- new ObjectProperty<Integer>(rs.getInt(12)));
- item.addItemProperty(Column.mem_usage,
- new ObjectProperty<Integer>(rs.getInt(13)));
- item.addItemProperty(Column.disk_usage,
- new ObjectProperty<Integer>(rs.getInt(14)));
-
- items.add(item);
- }
- rs.close();
- stmt.close();
- }
- catch (SQLException ex) {
- log.error("DB access failure", ex);
- }
-
- log.trace("DeviceStatusQuery load {} items from {}={} found", count,
- startIndex, items.size());
- return items;
- } //loadItems()
-
- /**
- * Only gets here if loadItems() fails, so return an empty state.
- * Throwing from here causes an infinite loop.
- */
- @Override
- public Item constructItem() {
- PropertysetItem item = new PropertysetItem();
- item.addItemProperty(Column.hostname, new ObjectProperty<String>(""));
- item.addItemProperty(Column.loc_id, new ObjectProperty<Integer>(-1));
- item.addItemProperty(Column.update_when,
- new ObjectProperty<Timestamp>(new Timestamp(System.currentTimeMillis())));
- item.addItemProperty(Column.net_ip, new ObjectProperty<String>(""));
- item.addItemProperty(Column.lan_ip, new ObjectProperty<String>(""));
- item.addItemProperty(Column.lan_mac, new ObjectProperty<String>(""));
- item.addItemProperty(Column.hardware, new ObjectProperty<String>(""));
- item.addItemProperty(Column.opsys, new ObjectProperty<String>(""));
- item.addItemProperty(Column.image, new ObjectProperty<String>(""));
- item.addItemProperty(Column.sw_ver, new ObjectProperty<String>(""));
- item.addItemProperty(Column.cpu_load, new ObjectProperty<String>(""));
- item.addItemProperty(Column.proc_count, new ObjectProperty<Integer>(0));
- item.addItemProperty(Column.mem_usage, new ObjectProperty<Integer>(0));
- item.addItemProperty(Column.disk_usage, new ObjectProperty<Integer>(0));
-
- log.warn("Shouldn't be calling DeviceStatusQuery.constructItem()");
- return item;
- }
-
- @Override
- public boolean deleteAllItems() {
- throw new UnsupportedOperationException();
- }
-
- @Override
- public void saveItems(List<Item> arg0, List<Item> arg1, List<Item> arg2) {
- throw new UnsupportedOperationException();
- }
- }
- ....
-
- 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.
-
- [source,java]
- ....
-
- import org.tepi.filtertable.FilterDecorator;
- import org.tepi.filtertable.numberfilter.NumberFilterPopupConfig;
- import org.vaadin.addons.lazyquerycontainer.LazyQueryContainer;
-
- import com.vaadin.data.Property;
- import com.vaadin.server.Resource;
- import com.vaadin.shared.ui.datefield.Resolution;
- import com.vaadin.ui.DateField;
- import com.vaadin.ui.AbstractTextField.TextChangeEventMode;
-
- /**
- * Filterable table of device statuses.
- */
- public class DeviceStatusTable extends FilterTable {
- private final
- String[] columnHeaders = {"Device", "Site", "Last Report", "Report IP",
- "LAN IP", "MAC Adrs", "Hardware", "O/S", "Image", "Software", "CPU"
- "Load", "Processes", "Memory Use", "Disk Use"};
-
- /**
- * Configuration this table for displaying of DeviceStatusQuery data.
- */
- public void configure(LazyQueryContainer dataSource) {
- super.setFilterGenerator(new LQCFilterGenerator(dataSource));
- super.setFilterBarVisible(true);
- super.setSelectable(true);
- super.setImmediate(true);
- super.setColumnReorderingAllowed(true);
- super.setColumnCollapsingAllowed(true);
- super.setSortEnabled(true);
-
- dataSource.addContainerProperty(Column.hostname, String.class, null, true, true);
- dataSource.addContainerProperty(Column.loc_id, Integer.class, null, true, false);
- dataSource.addContainerProperty(Column.update_when, Timestamp.class, null, true, true);
- dataSource.addContainerProperty(Column.net_ip, String.class, null, true, true);
- dataSource.addContainerProperty(Column.lan_ip, String.class, null, true, true);
- dataSource.addContainerProperty(Column.lan_mac, String.class, null, true, true);
- dataSource.addContainerProperty(Column.hardware, String.class, null, true, true);
- dataSource.addContainerProperty(Column.opsys, String.class, null, true, true);
- dataSource.addContainerProperty(Column.image, String.class, null, true, true);
- dataSource.addContainerProperty(Column.sw_ver, String.class, null, true, true);
- dataSource.addContainerProperty(Column.cpu_load, String.class, null, true, true);
- dataSource.addContainerProperty(Column.proc_count, Integer.class, null, true, true);
- dataSource.addContainerProperty(Column.mem_usage, Integer.class, null, true, true);
- dataSource.addContainerProperty(Column.disk_usage, Integer.class, null, true, true);
-
- super.setContainerDataSource(dataSource);
- super.setColumnHeaders(columnHeaders);
- super.setColumnCollapsed(Column.lan_mac, true);
- super.setColumnCollapsed(Column.opsys, true);
- super.setColumnCollapsed(Column.image, true);
- super.setFilterFieldVisible(Column.loc_id, false);
- }
-
- @Override
- protected String formatPropertyValue(Object rowId, Object colId, Property<?> property) {
- if (Column.loc_id.is(colId)) {
- // Example of how to translate a column value
- return Hierarchy.getLocation(((Integer) property.getValue())).getShortName();
- } else if (Column.update_when.is(colId)) {
- // Example of how to format a value.
- return ((java.sql.Timestamp) property.getValue()).toString().substring(0, 19);
- }
-
- return super.formatPropertyValue(rowId, colId, property);
- }
-
- /**
- * Filter generator that triggers a refresh of a LazyQueryContainer
- * whenever the filters change.
- */
- public class LQCFilterGenerator implements FilterGenerator {
- private final LazyQueryContainer lqc;
-
- public LQCFilterGenerator(LazyQueryContainer lqc) {
- this.lqc = lqc;
- }
-
- @Override
- public Filter generateFilter(Object propertyId, Object value) {
- return null;
- }
-
- @Override
- public Filter generateFilter(Object propertyId, Field<?> originatingField) {
- return null;
- }
-
- @Override
- public AbstractField<?> getCustomFilterComponent(Object propertyId) {
- return null;
- }
-
- @Override
- public void filterRemoved(Object propertyId) {
- this.lqc.refresh();
- }
-
- @Override
- public void filterAdded(Object propertyId, Class<? extends Filter> filterType, Object value) {
- this.lqc.refresh();
- }
-
- @Override
- public Filter filterGeneratorFailed(Exception reason, Object propertyId, Object value) {
- return null;
- }
- }
- }
- ....
- 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:
-
- [source,java]
- ....
- final DeviceStatusTable table = new DeviceStatusTable();
- table.setSizeFull();
-
- DeviceStatusQuery.Factory factory = new DeviceStatusQuery.Factory(locationID);
- final LazyQueryContainer statusDataContainer = new LazyQueryContainer(factory,
- /*index*/ null, /*batchSize*/ 50, false);
- statusDataContainer.getQueryView().setMaxCacheSize(300);
- table.configure(statusDataContainer);
-
- layout.addComponent(table);
- layout.setHeight(100f, Unit.PERCENTAGE); // no scrollbar
-
- // Respond to row click
- table.addValueChangeListener(new Property.ValueChangeListener() {
- @Override
- public void valueChange(ValueChangeEvent event) {
- Object index = event.getProperty().getValue();
- if (index != nulll) {
- int locId = (Integer) statusDataContainer.getItem(index)
- .getItemProperty(DeviceStatusQuery.Column.loc_id).getValue();
- doSomething(locId);
- table.setValue(null); //visually deselect
- }
- }
- });
- ....
-
- And finally, since we're using `SQLContainer`{empty}'s `QueryBuilder`, depending on
- your database you may need to include something like this once during
- your application startup:
-
- [source,java]
- ....
- import com.vaadin.data.util.sqlcontainer.query.generator.filter.QueryBuilder;
- import com.vaadin.data.util.sqlcontainer.query.generator.filter.StringDecorator;
-
- // Configure Vaadin SQLContainer to work with MySQL
- QueryBuilder.setStringDecorator(new StringDecorator("`","`"));
- ....
|