diff options
Diffstat (limited to 'documentation/sqlcontainer')
10 files changed, 796 insertions, 0 deletions
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 +<<dummy/../../../framework/datamodel/datamodel-container#datamodel.container.filtered,"Filterable +Containers">>. + +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<Filter> filters) +public void setFilters(List<Filter> filters, + FilteringMode filteringMode) +public void setOrderBy(List<OrderBy> 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. + + + |