From a1b265c318dbda4a213cec930785b81e4c0f7d2b Mon Sep 17 00:00:00 2001 From: elmot Date: Fri, 25 Sep 2015 16:40:44 +0300 Subject: Framework documentation IN Change-Id: I767477c1fc3745f9e1f58075fe30c9ac8da63581 --- .../sqlcontainer/chapter-sqlcontainer.asciidoc | 47 +++++++ .../sqlcontainer-architecture.asciidoc | 50 ++++++++ .../sqlcontainer/sqlcontainer-caching.asciidoc | 119 ++++++++++++++++++ .../sqlcontainer/sqlcontainer-editing.asciidoc | 139 +++++++++++++++++++++ .../sqlcontainer-filteringsorting.asciidoc | 78 ++++++++++++ .../sqlcontainer/sqlcontainer-freeform.asciidoc | 94 ++++++++++++++ .../sqlcontainer-getting-started.asciidoc | 85 +++++++++++++ .../sqlcontainer/sqlcontainer-limitations.asciidoc | 44 +++++++ .../sqlcontainer-nonimplemented.asciidoc | 66 ++++++++++ .../sqlcontainer/sqlcontainer-referencing.asciidoc | 74 +++++++++++ 10 files changed, 796 insertions(+) create mode 100644 documentation/sqlcontainer/chapter-sqlcontainer.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-architecture.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-caching.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-editing.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-filteringsorting.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-freeform.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-getting-started.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-limitations.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-nonimplemented.asciidoc create mode 100644 documentation/sqlcontainer/sqlcontainer-referencing.asciidoc (limited to 'documentation/sqlcontainer') diff --git a/documentation/sqlcontainer/chapter-sqlcontainer.asciidoc b/documentation/sqlcontainer/chapter-sqlcontainer.asciidoc new file mode 100644 index 0000000000..9b5ff51573 --- /dev/null +++ b/documentation/sqlcontainer/chapter-sqlcontainer.asciidoc @@ -0,0 +1,47 @@ +[[sqlcontainer]] +== Vaadin SQLContainer + +Vaadin SQLContainer is a container implementation that allows easy and +customizable access to data stored in various SQL-speaking databases. + +SQLContainer supports two types of database access. Using +[classname]#TableQuery#, the pre-made query generators will enable fetching, +updating, and inserting data directly from the container into a database table - +automatically, whereas [classname]#FreeformQuery# allows the developer to use +their own, probably more complex query for fetching data and their own optional +implementations for writing, filtering and sorting support - item and property +handling as well as lazy loading will still be handled automatically. + +In addition to the customizable database connection options, SQLContainer also +extends the Vaadin [classname]#Container# interface to implement more advanced +and more database-oriented filtering rules. Finally, the add-on also offers +connection pool implementations for JDBC connection pooling and JEE connection +pooling, as well as integrated transaction support; auto-commit mode is also +provided. + +The purpose of this section is to briefly explain the architecture and some of +the inner workings of SQLContainer. It will also give the readers some examples +on how to use SQLContainer in their own applications. The requirements, +limitations and further development ideas are also discussed. + +SQLContainer is available from the Vaadin Directory under the same unrestrictive +Apache License 2.0 as the Vaadin Framework itself. + + +include::sqlcontainer-architecture.asciidoc[leveloffset=+2] + +include::sqlcontainer-getting-started.asciidoc[leveloffset=+2] + +include::sqlcontainer-filteringsorting.asciidoc[leveloffset=+2] + +include::sqlcontainer-editing.asciidoc[leveloffset=+2] + +include::sqlcontainer-caching.asciidoc[leveloffset=+2] + +include::sqlcontainer-referencing.asciidoc[leveloffset=+2] + +include::sqlcontainer-freeform.asciidoc[leveloffset=+2] + +include::sqlcontainer-nonimplemented.asciidoc[leveloffset=+2] + +include::sqlcontainer-limitations.asciidoc[leveloffset=+2] diff --git a/documentation/sqlcontainer/sqlcontainer-architecture.asciidoc b/documentation/sqlcontainer/sqlcontainer-architecture.asciidoc new file mode 100644 index 0000000000..ff0dd6fdd3 --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-architecture.asciidoc @@ -0,0 +1,50 @@ +--- +title: Architecture +order: 1 +layout: page +--- + +[[sqlcontainer.architecture]] += Architecture + +The architecture of SQLContainer is relatively simple. [classname]#SQLContainer# +is the class implementing the Vaadin [classname]#Container# interfaces and +providing access to most of the functionality of this add-on. The standard +Vaadin [classname]#Property# and [classname]#Item# interfaces have been +implementd as the [classname]#ColumnProperty# and [classname]#RowItem# classes. +Item IDs are represented by [classname]#RowId# and [classname]#TemporaryRowId# +classes. The [classname]#RowId# class is built based on the primary key columns +of the connected database table or query result. + +In the [package]#connection# package, the [classname]#JDBCConnectionPool# +interface defines the requirements for a connection pool implementation. Two +implementations of this interface are provided: +[classname]#SimpleJDBCConnectionPool# provides a simple yet very usable +implementation to pool and access JDBC connections. +[classname]#J2EEConnectionPool# provides means to access J2EE DataSources. + +The [package]#query# package contains the [classname]#QueryDelegate# interface, +which defines everything the SQLContainer needs to enable reading and writing +data to and from a database. As discussed earlier, two implementations of this +interface are provided: [classname]#TableQuery# for automatic read-write support +for a database table, and [classname]#FreeformQuery# for customizing the query, +sorting, filtering and writing; this is done by implementing relevant methods of +the [classname]#FreeformStatementDelegate# interface. + +The [package]#query# package also contains [classname]#Filter# and +[classname]#OrderBy# classes which have been written to provide an alternative +to the standard Vaadin container filtering and make sorting non-String +properties a bit more user friendly. + +Finally, the [package]#generator# package contains a [classname]#SQLGenerator# +interface, which defines the kind of queries that are required by the +[classname]#TableQuery# class. The provided implementations include support for +HSQLDB, MySQL, PostgreSQL ( [classname]#DefaultSQLGenerator#), Oracle ( +[classname]#OracleGenerator#) and Microsoft SQL Server ( +[classname]#MSSQLGenerator#). A new or modified implementations may be provided +to gain compatibility with older versions or other database servers. + +For further details, please refer to the SQLContainer API documentation. + + + diff --git a/documentation/sqlcontainer/sqlcontainer-caching.asciidoc b/documentation/sqlcontainer/sqlcontainer-caching.asciidoc new file mode 100644 index 0000000000..c93a012fa5 --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-caching.asciidoc @@ -0,0 +1,119 @@ +--- +title: Caching, Paging and Refreshing +order: 5 +layout: page +--- + +[[sqlcontainer.caching]] += 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 [classname]#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. + +[[sqlcontainer.caching.container-size]] +== Container Size + +The [classname]#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 +[methodname]#setSizeValidMilliSeconds()# in [classname]#SQLContainer#. + +If the size validity time has expired, the row count will be automatically +updated on: + +* A call to [methodname]#getItemIds()# method + +* A call to [methodname]#size()# method + +* Some calls to [methodname]#indexOfId(Object itemId)# method + +* A call to [methodname]#firstItemId()# method + +* When the container is fetching a set of rows to the item cache (lazy loading) + + + +[[sqlcontainer.caching.page-length]] +== Page Length and Cache Size + +The page length of the [classname]#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 [methodname]#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 [classname]#Table#; obviously, this is also +dependent on the cache ratio set for the [classname]#Table# component. + +The size of the internal item cache of the [classname]#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. + + +[[sqlcontainer.caching.refreshing]] +== Refreshing the Container + +Normally, the [classname]#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 +[methodname]#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 [classname]#QueryDelegate# of the container +* Auto-commit mode +* Page length +* Filters +* Sorting + + +ifdef::web[] +[[sqlcontainer.caching.flush-notification]] +== 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 [methodname]#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 +[classname]#SQLContainer# is added to the notification reference list or a +container calls the notification method. + +When a [classname]#SQLContainer# has its cache notifications set enabled, it +will call the static [methodname]#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 [classname]#QueryDelegate# (either +[classname]#TableQuery# or [classname]#FreeformQuery#) and the table name or +query string must match with the container that fired the notification. If a +match is found the [methodname]#refresh()# method of the matching container is +called, resulting in cache flushing in the target container. + +__Note: Standard Vaadin issues apply; even if the [classname]#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.__ + +endif::web[] + + + diff --git a/documentation/sqlcontainer/sqlcontainer-editing.asciidoc b/documentation/sqlcontainer/sqlcontainer-editing.asciidoc new file mode 100644 index 0000000000..9fe8d7ba7a --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-editing.asciidoc @@ -0,0 +1,139 @@ +--- +title: Editing +order: 4 +layout: page +--- + +[[sqlcontainer.editing]] += Editing + +Editing the items ( [classname]#RowItem#s) of SQLContainer can be done similarly +to editing the items of any Vaadin container. [classname]#ColumnProperties# of a +[classname]#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. + +[[sqlcontainer.editing.adding]] +== Adding items + +Adding items to an [classname]#SQLContainer# object can only be done via the +[methodname]#addItem()# method. This method will create a new [classname]#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 +[classname]#TemporaryRowId# as a [classname]#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 [classname]#SQLContainer#, the +[methodname]#addItem()# method will return the final [classname]#RowId# of the +new item. + + +[[sqlcontainer.editing.fetching]] +== 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 +[classname]#QueryDelegate# interface. Currently only the [classname]#TableQuery# +class implements the [classname]#RowIdChangeNotifier# interface, and thus can +notify interested objects of changed row IDs. The events fill be fired after +[methodname]#commit()# in [classname]#TableQuery# has finished; this method is +called by [classname]#SQLContainer# when necessary. + +To receive updates on the row IDs, you might use the following code (assuming +container is an instance of [classname]#SQLContainer#). Note that these events +are not fired if auto commit mode is enabled. + + +---- +app.getDbHelp().getCityContainer().addListener( + new QueryDelegate.RowIdChangeListener() { + public void rowIdChange(RowIdChangeEvent event) { + System.err.println("Old ID: " + event.getOldRowId()); + System.err.println("New ID: " + event.getNewRowId()); + } + }); +---- + + +[[sqlcontainer.editing.version-column]] +== Version column requirement + +If you are using the [classname]#TableQuery# class as the query delegate to the +[classname]#SQLContainer# and need to enable write support, there is an enforced +requirement of specifying a version column name to the [classname]#TableQuery# +instance. The column name can be set to the [classname]#TableQuery# using the +following statement: + + +---- +tq.setVersionColumn("OPTLOCK"); +---- + +The version column is preferrably an integer or timestamp typed column in the +table that is attached to the [classname]#TableQuery#. This column will be used +for optimistic locking; before a row modification the [classname]#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! [classname]#TableQuery# assumes that the database will take care of +updating the version column by either using an actual [literal]#++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. + + +[[sqlcontainer.editing.autocommit]] +== Auto-commit mode + +[classname]#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 +[methodname]#commit()# or discarded by calling [methodname]#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: + + +---- +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. + + +[[sqlcontainer.editing.modified-state]] +== Modified state + +When used in the transaction mode it may be useful to determine whether the +contents of the [classname]#SQLContainer# have been modified or not. For this +purpose the container provides an [methodname]#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 [classname]#RowItem# and each [classname]#ColumnProperty# +have [methodname]#isModified()# methods to allow for a more detailed view over +the modification status. Do note that the modification statuses of +[classname]#RowItem# and [classname]#ColumnProperty# objects only depend on +whether or not the actual [classname]#Property# values have been modified. That +is, they do not reflect situations where the whole [classname]#RowItem# has been +marked for removal or has just been added to the container. + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-filteringsorting.asciidoc b/documentation/sqlcontainer/sqlcontainer-filteringsorting.asciidoc new file mode 100644 index 0000000000..73d3c28334 --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-filteringsorting.asciidoc @@ -0,0 +1,78 @@ +--- +title: Filtering and Sorting +order: 3 +layout: page +--- + +[[sqlcontainer.filteringsorting]] += Filtering and Sorting + +Filtering and sorting the items contained in an SQLContainer is, by design, +always performed in the database. In practice this means that whenever the +filtering or sorting rules are modified, at least some amount of database +communication will take place (the minimum is to fetch the updated row count +using the new filtering/sorting rules). + +[[sqlcontainer.filteringsorting.filtering]] +== Filtering + +Filtering is performed using the filtering API in Vaadin, which allows for very +complex filtering to be easily applied. More information about the filtering API +can be found in +<>. + +In addition to the filters provided by Vaadin, SQLContainer also implements the +[classname]#Like# filter as well as the [classname]#Between# filter. Both of +these map to the equally named WHERE-operators in SQL. The filters can also be +applied on items that reside in memory, for example, new items that have not yet +been stored in the database or rows that have been loaded and updated, but not +yet stored. + +The following is an example of the types of complex filtering that are possible +with the new filtering API. We want to find all people named Paul Johnson that +are either younger than 18 years or older than 65 years and all Johnsons whose +first name starts with the letter "A": + + +---- +mySQLContainer.addContainerFilter( + new Or(new And(new Equal("NAME", "Paul"), + new Or(new Less("AGE", 18), + new Greater("AGE", 65))), + new Like("NAME", "A%"))); +mySQLContainer.addContainerFilter( + new Equal("LASTNAME", "Johnson")); +---- + +This will produce the following WHERE clause: + + +---- +WHERE (("NAME" = "Paul" AND ("AGE" < 18 OR "AGE" > 65)) OR "NAME" LIKE "A%") AND "LASTNAME" = "Johnson" +---- + + +[[sqlcontainer.filteringsorting.sorting]] +== Sorting + +Sorting can be performed using standard Vaadin, that is, using the sort method +from the [classname]#Container.Sortable# interface. The [parameter]#propertyId# +parameter refers to column names. + + +---- +public void sort(Object[] propertyId, boolean[] ascending) +---- + +In addition to the standard method, it is also possible to directly add an +[classname]#OrderBy# to the container via the [methodname]#addOrderBy()# method. +This enables the developer to insert sorters one by one without providing the +whole array of them at once. + +All sorting rules can be cleared by calling the sort method with null or an +empty array as the first argument. + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-freeform.asciidoc b/documentation/sqlcontainer/sqlcontainer-freeform.asciidoc new file mode 100644 index 0000000000..c5e6d1094c --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-freeform.asciidoc @@ -0,0 +1,94 @@ +--- +title: Making Freeform Queries +order: 7 +layout: page +--- + +[[sqlcontainer.freeform]] += Making Freeform Queries + +In most cases, the provided [classname]#TableQuery# will be enough to allow a +developer to gain effortless access to an SQL data source. However there may +arise situations when a more complex query with, for example, join expressions +is needed. Or perhaps you need to redefine how the writing or filtering should +be done. The [classname]#FreeformQuery# query delegate is provided for this +exact purpose. Out of the box the [classname]#FreeformQuery# supports read-only +access to a database, but it can be extended to allow writing also. + +[[sqlcontainer.freeform.getting-started]] +== Getting started + +Getting started with the [classname]#FreeformQuery# may be done as shown in the +following. The connection pool initialization is similar to the +[classname]#TableQuery# example so it is omitted here. Note that the name(s) of +the primary key column(s) must be provided to the [classname]#FreeformQuery# +manually. This is required because depending on the query the result set may or +may not contain data about primary key columns. In this example, there is one +primary key column with a name 'ID'. + + +---- +FreeformQuery query = new FreeformQuery( + "SELECT * FROM SAMPLE", pool, "ID"); +SQLContainer container = new SQLContainer(query); +---- + + +[[sqlcontainer.freeform.limitations]] +== Limitations + +While this looks just as easy as with the [classname]#TableQuery#, do note that +there are some important caveats here. Using [classname]#FreeformQuery# like +this (without providing [classname]#FreeformQueryDelegate# or +[classname]#FreeformStatementDelegate# implementation) it can only be used as a +read-only window to the resultset of the query. Additionally filtering, sorting +and lazy loading features will not be supported, and the row count will be +fetched in quite an inefficient manner. Bearing these limitations in mind, it +becomes quite obvious that the developer is in reality meant to implement the +[classname]#FreeformQueryDelegate# or [classname]#FreeformStatementDelegate# +interface. + +The [classname]#FreeformStatementDelegate# interface is an extension of the +[classname]#FreeformQueryDelegate# interface, which returns +[classname]#StatementHelper# objects instead of pure query [classname]#String#s. +This enables the developer to use prepared statetemens instead of regular +statements. It is highly recommended to use the +[classname]#FreeformStatementDelegate# in all implementations. From this chapter +onwards, we will only refer to the [classname]#FreeformStatementDelegate# in +cases where [classname]#FreeformQueryDelegate# could also be applied. + + +[[sqlcontainer.freeform.custom-freeformstatementdelegate]] +== Creating your own [classname]#FreeformStatementDelegate# + +To create your own delegate for [classname]#FreeformQuery# you must implement +some or all of the methods from the [classname]#FreeformStatementDelegate# +interface, depending on which ones your use case requires. The interface +contains eight methods which are shown below. For more detailed requirements, +see the JavaDoc documentation of the interface. + + +---- +// Read-only queries +public StatementHelper getCountStatement() +public StatementHelper getQueryStatement(int offset, int limit) +public StatementHelper getContainsRowQueryStatement(Object... keys) + +// Filtering and sorting +public void setFilters(List filters) +public void setFilters(List filters, + FilteringMode filteringMode) +public void setOrderBy(List orderBys) + +// Write support +public int storeRow(Connection conn, RowItem row) +public boolean removeRow(Connection conn, RowItem row) +---- + +A simple demo implementation of this interface can be found in the SQLContainer +package, more specifically in the class +[classname]#com.vaadin.addon.sqlcontainer.demo.DemoFreeformQueryDelegate#. + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-getting-started.asciidoc b/documentation/sqlcontainer/sqlcontainer-getting-started.asciidoc new file mode 100644 index 0000000000..acecb4dc5e --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-getting-started.asciidoc @@ -0,0 +1,85 @@ +--- +title: Getting Started with SQLContainer +order: 2 +layout: page +--- + +[[sqlcontainer.getting-started]] += Getting Started with SQLContainer + +Getting development going with the SQLContainer is easy and quite +straight-forward. The purpose of this section is to describe how to create the +required resources and how to fetch data from and write data to a database table +attached to the container. + +[[sqlcontainer.getting-started.connection-pool]] +== Creating a connection pool + +First, we need to create a connection pool to allow the SQLContainer to connect +to a database. Here we will use the [classname]#SimpleJDBCConnectionPool#, which +is a basic implementation of connection pooling with JDBC data sources. In the +following code, we create a connection pool that uses the HSQLDB driver together +with an in-memory database. The initial amount of connections is 2 and the +maximum amount is set at 5. Note that the database driver, connection url, +username, and password parameters will vary depending on the database you are +using. + + +---- +JDBCConnectionPool pool = new SimpleJDBCConnectionPool( + "org.hsqldb.jdbc.JDBCDriver", + "jdbc:hsqldb:mem:sqlcontainer", "SA", "", 2, 5); +---- + + +[[sqlcontainer.getting-started.query-delegate]] +== Creating the [classname]#TableQuery# Query Delegate + +After the connection pool is created, we'll need a query delegate for the +SQLContainer. The simplest way to create one is by using the built-in +[classname]#TableQuery# class. The [classname]#TableQuery# delegate provides +access to a defined database table and supports reading and writing data +out-of-the-box. The primary key(s) of the table may be anything that the +database engine supports, and are found automatically by querying the database +when a new [classname]#TableQuery# is instantiated. We create the +[classname]#TableQuery# with the following statement: + + +---- +TableQuery tq = new TableQuery("tablename", connectionPool); +---- + +In order to allow writes from several user sessions concurrently, we must set a +version column to the [classname]#TableQuery# as well. The version column is an +integer- or timestamp-typed column which will either be incremented or set to +the current time on each modification of the row. [classname]#TableQuery# +assumes that the database will take care of updating the version column; it just +makes sure the column value is correct before updating a row. If another user +has changed the row and the version number in the database does not match the +version number in memory, an [classname]#OptimisticLockException# is thrown and +you can recover by refreshing the container and allow the user to merge the +data. The following code will set the version column: + + +---- +tq.setVersionColumn("OPTLOCK"); +---- + + +[[sqlcontainer.getting-started.container-creation]] +== Creating the Container + +Finally, we may create the container itself. This is as simple as stating: + + +---- +SQLContainer container = new SQLContainer(tq); +---- + +After this statement, the [classname]#SQLContainer# is connected to the table +tablename and is ready to use for example as a data source for a Vaadin +[classname]#Table# or a Vaadin [classname]#Form#. + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-limitations.asciidoc b/documentation/sqlcontainer/sqlcontainer-limitations.asciidoc new file mode 100644 index 0000000000..3e245a1ddf --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-limitations.asciidoc @@ -0,0 +1,44 @@ +--- +title: Known Issues and Limitations +order: 9 +layout: page +--- + +[[sqlcontainer.limitations]] += Known Issues and Limitations + +At this point, there are still some known issues and limitations affecting the +use of SQLContainer in certain situations. The known issues and brief +explanations are listed below: + +* Some SQL data types do not have write support when using TableQuery: ** All binary types +** All custom types +** CLOB (if not converted automatically to a [classname]#String# by the JDBC driver in use) +** See [classname]#com.vaadin.addon.sqlcontainer.query.generator.StatementHelper# for details. + + +* When using Oracle or MS SQL database, the column name " [parameter]#rownum#" can +not be used as a column name in a table connected to [classname]#SQLContainer#. + ++ +This limitation exists because the databases in question do not support +limit/offset clauses required for paging. Instead, a generated column named +'rownum' is used to implement paging support. + + +The permanent limitations are listed below. These can not or most probably will +not be fixed in future versions of SQLContainer. + +* The [methodname]#getItemIds()# method is very inefficient - avoid calling it unless absolutely required! +* When using [classname]#FreeformQuery# without providing a [classname]#FreeformStatementDelegate#, the row count query is very inefficient - avoid using [classname]#FreeformQuery# without implementing at least the count query properly. +* When using [classname]#FreeformQuery# without providing a [classname]#FreeformStatementDelegate#, writing, sorting and filtering will not be supported. +* When using Oracle database most or all of the numeric types are converted to +[classname]#java.math.BigDecimal# by the Oracle JDBC Driver. + ++ +This is a feature of how Oracle DB and the Oracle JDBC Driver handles data +types. + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-nonimplemented.asciidoc b/documentation/sqlcontainer/sqlcontainer-nonimplemented.asciidoc new file mode 100644 index 0000000000..595085822f --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-nonimplemented.asciidoc @@ -0,0 +1,66 @@ +--- +title: Non-Implemented Methods +order: 8 +layout: page +--- + +[[sqlcontainer.nonimplemented]] += Non-Implemented Methods + +Due to the database connection inherent to the SQLContainer, some of the methods +from the container interfaces of Vaadin can not (or would not make sense to) be +implemented. These methods are listed below, and they will throw an +[classname]#UnsupportedOperationException# on invocation. + + +---- +public boolean addContainerProperty(Object propertyId, + Class type, + Object defaultValue) +public boolean removeContainerProperty(Object propertyId) +public Item addItem(Object itemId) +public Object addItemAt(int index) +public Item addItemAt(int index, Object newItemId) +public Object addItemAfter(Object previousItemId) +public Item addItemAfter(Object previousItemId, Object newItemId) +---- + +Additionally, the following methods of the [classname]#Item# interface are not +supported in the [classname]#RowItem# class: + + +---- +public boolean addItemProperty(Object id, Property property) +public boolean removeItemProperty(Object id) +---- + +[[sqlcontainer.nonimplemented.getitemids]] +== About the getItemIds() method + +To properly implement the Vaadin [classname]#Container# interface, a +[methodname]#getItemIds()# method has been implented in the +[classname]#SQLContainer#. By definition, this method returns a collection of +all the item IDs present in the container. What this means in the +[classname]#SQLContainer# case is that the container has to query the database +for the primary key columns of all the rows present in the connected database +table. + +It is obvious that this could potentially lead to fetching tens or even hundreds +of thousands of rows in an effort to satisfy the method caller. This will +effectively kill the lazy loading properties of [classname]#SQLContainer# and +therefore the following warning is expressed here: + + +[WARNING] +==== +It is highly recommended not to call the [methodname]#getitemIds()# method, +unless it is known that in the use case in question the item ID set will always +be of reasonable size. + +==== + + + + + + diff --git a/documentation/sqlcontainer/sqlcontainer-referencing.asciidoc b/documentation/sqlcontainer/sqlcontainer-referencing.asciidoc new file mode 100644 index 0000000000..b31b10d39d --- /dev/null +++ b/documentation/sqlcontainer/sqlcontainer-referencing.asciidoc @@ -0,0 +1,74 @@ +--- +title: Referencing Another SQLContainer +order: 6 +layout: page +--- + +[[sqlcontainer.referencing]] += Referencing Another SQLContainer + +When developing a database-connected application, there is usually a need to +retrieve data related to one table from one or more other tables. In most cases, +this relation is achieved with a foreign key reference, where a column of the +first table contains a primary key or candidate key of a row in another table. + +SQLContainer offers limited support for this kind of referencing relation, +although all referencing is currently done on the Java side so no constraints +need to be made in the database. A new reference can be created by calling the +following method: + + +---- +public void addReference(SQLContainer refdCont, + String refingCol, String refdCol); +---- + +This method should be called on the source container of the reference. The +target container should be given as the first parameter. The +[parameter]#refingCol# is the name of the 'foreign key' column in the source +container, and the [parameter]#refdCol# is the name of the referenced key column +in the target container. + +__Note: For any [classname]#SQLContainer#, all the referenced target containers +must be different. You can not reference the same container from the same source +twice.__ + +Handling the referenced item can be done through the three provided set/get +methods, and the reference can be completely removed with the +[methodname]#removeReference()# method. Signatures of these methods are listed +below: + + +---- +public boolean setReferencedItem(Object itemId, + Object refdItemId, SQLContainer refdCont) +public Object getReferencedItemId(Object itemId, + SQLContainer refdCont) +public Item getReferencedItem(Object itemId, + SQLContainer refdCont) +public boolean removeReference(SQLContainer refdCont) +---- + +The setter method should be given three parameters: [parameter]#itemId# is the +ID of the referencing item (from the source container), [parameter]#refdItemId# +is the referenced [parameter]#itemID# (from the target container) and +[parameter]#refdCont# is a reference to the target container that identifies the +reference. This method returns true if the setting of the referenced item was +successful. After setting the referenced item you must normally call +[methodname]#commit()# on the source container to persist the changes to the +database. + +The [methodname]#getReferencedItemId()# method will return the item ID of the +referenced item. As parameters this method needs the item ID of the referencing +item and a reference to the target container as an identifier. +[classname]#SQLContainer# also provides a convenience method +[methodname]#getReferencedItem()#, which directly returns the referenced item +from the target container. + +Finally, the referencing can be removed from the source container by calling the +[methodname]#removeReference()# method with the target container as parameter. +Note that this does not actually change anything in the database; it merely +removes the logical relation that exists only on the Java-side. + + + -- cgit v1.2.3 950'>950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226