Editing

Editing the items ( RowItem#s) of SQLContainer can be done similarly to editing the items of any Vaadin container. [classname]#ColumnProperties of a RowItem will automatically notify SQLContainer to make sure that changes to the items are recorded and will be applied to the database immediately or on commit, depending on the state of the auto-commit mode.

Adding items

Adding items to an SQLContainer object can only be done via the addItem() method. This method will create a new Item based on the connected database table column properties. The new item will either be buffered by the container or committed to the database through the query delegate depending on whether the auto commit mode (see the next section) has been enabled.

When an item is added to the container it is impossible to precisely know what the primary keys of the row will be, or will the row insertion succeed at all. This is why the SQLContainer will assign an instance of TemporaryRowId as a RowId for the new item. We will later describe how to fetch the actual key after the row insertion has succeeded.

If auto-commit mode is enabled in the SQLContainer, the addItem() method will return the final RowId of the new item.

Fetching generated row keys

Since it is a common need to fetch the generated key of a row right after insertion, a listener/notifier has been added into the QueryDelegate interface. Currently only the TableQuery class implements the RowIdChangeNotifier interface, and thus can notify interested objects of changed row IDs. The events fill be fired after commit() in TableQuery has finished; this method is called by SQLContainer when necessary.

To receive updates on the row IDs, you might use the following code (assuming container is an instance of SQLContainer). Note that these events are not fired if auto commit mode is enabled.

  1. app.getDbHelp().getCityContainer().addListener(
  2. new QueryDelegate.RowIdChangeListener() {
  3. public void rowIdChange(RowIdChangeEvent event) {
  4. System.err.println("Old ID: " + event.getOldRowId());
  5. System.err.println("New ID: " + event.getNewRowId());
  6. }
  7. });

Version column requirement

If you are using the TableQuery class as the query delegate to the SQLContainer and need to enable write support, there is an enforced requirement of specifying a version column name to the TableQuery instance. The column name can be set to the TableQuery using the following statement:

  1. tq.setVersionColumn("OPTLOCK");

The version column is preferrably an integer or timestamp typed column in the table that is attached to the TableQuery. This column will be used for optimistic locking; before a row modification the TableQuery will check before that the version column value is the same as it was when the data was read into the container. This should ensure that no one has modified the row inbetween the current user’s reads and writes.

Note! TableQuery assumes that the database will take care of updating the version column by either using an actual VERSION column (if supported by the database in question) or by a trigger or a similar mechanism.

If you are certain that you do not need optimistic locking, but do want to enable write support, you may point the version column to, for example, a primary key column of the table.

Auto-commit mode

SQLContainer is by default in transaction mode, which means that actions that edit, add or remove items are recorded internally by the container. These actions can be either committed to the database by calling commit() or discarded by calling rollback().

The container can also be set to auto-commit mode. When this mode is enabled, all changes will be committed to the database immediately. To enable or disable the auto-commit mode, call the following method:

  1. public void setAutoCommit(boolean autoCommitEnabled)

It is recommended to leave the auto-commit mode disabled, as it ensures that the changes can be rolled back if any problems are noticed within the container items. Using the auto-commit mode will also lead to failure in item addition if the database table contains non-nullable columns.

Modified state

When used in the transaction mode it may be useful to determine whether the contents of the SQLContainer have been modified or not. For this purpose the container provides an isModified() method, which will tell the state of the container to the developer. This method will return true if any items have been added to or removed from the container, as well as if any value of an existing item has been modified.

Additionally, each RowItem and each ColumnProperty have isModified() methods to allow for a more detailed view over the modification status. Do note that the modification statuses of RowItem and ColumnProperty objects only depend on whether or not the actual Property values have been modified. That is, they do not reflect situations where the whole RowItem has been marked for removal or has just been added to the container.