Caching, Paging and Refreshing

To decrease the amount of queries made to the database, SQLContainer uses internal caching for database contents. The caching is implemented with a size-limited LinkedHashMap containing a mapping from [classname]#RowId#s to [classname]#RowItem#s. Typically developers do not need to modify caching options, although some fine-tuning can be done if required.

Container Size

The SQLContainer keeps continuously checking the amount of rows in the connected database table in order to detect external addition or removal of rows. By default, the table row count is assumed to remain valid for 10 seconds. This value can be altered from code; with setSizeValidMilliSeconds() in SQLContainer.

If the size validity time has expired, the row count will be automatically updated on:

  • A call to getItemIds() method

  • A call to size() method

  • Some calls to indexOfId(Object itemId) method

  • A call to firstItemId() method

  • When the container is fetching a set of rows to the item cache (lazy loading)

Page Length and Cache Size

The page length of the SQLContainer dictates the amount of rows fetched from the database in one query. The default value is 100, and it can be modified with the setPageLength() method. To avoid constant queries it is recommended to set the page length value to at least 5 times the amount of rows displayed in a Vaadin Table; obviously, this is also dependent on the cache ratio set for the Table component.

The size of the internal item cache of the SQLContainer is calculated by multiplying the page length with the cache ratio set for the container. The cache ratio can only be set from the code, and the default value for it is 2. Hence with the default page length of 100 the internal cache size becomes 200 items. This should be enough even for larger [classname]#Table#s while ensuring that no huge amounts of memory will be used on the cache.

Refreshing the Container

Normally, the SQLContainer will handle refreshing automatically when required. However, there may be situations where an implicit refresh is needed, for example, to make sure that the version column is up-to-date prior to opening the item for editing in a form. For this purpose a refresh() method is provided. This method simply clears all caches, resets the current item fetching offset and sets the container size dirty. Any item-related call after this will inevitably result into row count and item cache update.

Note that a call to the refresh method will not affect or reset the following properties of the container:

  • The QueryDelegate of the container

  • Auto-commit mode

  • Page length

  • Filters

  • Sorting

Cache Flush Notification Mechanism

Cache usage with databases in multiuser applications always results in some kind of a compromise between the amount of queries we want to execute on the database and the amount of memory we want to use on caching the data; and most importantly, risking the cached data becoming stale.

SQLContainer provides an experimental remedy to this problem by implementing a simple cache flush notification mechanism. Due to its nature these notifications are disabled by default but can be easily enabled for a container instance by calling enableCacheFlushNotifications() at any time during the lifetime of the container.

The notification mechanism functions by storing a weak reference to all registered containers in a static list structure. To minimize the risk of memory leaks and to avoid unlimited growing of the reference list, dead weak references are collected to a reference queue and removed from the list every time a SQLContainer is added to the notification reference list or a container calls the notification method.

When a SQLContainer has its cache notifications set enabled, it will call the static notifyOfCacheFlush() method giving itself as a parameter. This method will compare the notifier-container to all the others present in the reference list. To fire a cache flush event, the target container must have the same type of QueryDelegate (either TableQuery or FreeformQuery) and the table name or query string must match with the container that fired the notification. If a match is found the refresh() method of the matching container is called, resulting in cache flushing in the target container.

Note: Standard Vaadin issues apply; even if the SQLContainer is refreshed on the server side, the changes will not be reflected to the UI until a server round-trip is performed, or unless a push mechanism is used.