Vaadin SLQContainer 0.8 User Manual Table of Contents
Contents
1. refresh void setAutoCommit autoCommitEnabled boolean isAutoCommit boolean setPageLength pageLength int void getPageLength void commit void rollback void addFilter filter Filter void addOrderBy orderBy OrderBy void setFilteringMode filteringMode FilteringMode void isModified boolean void lt gt lt lt interface gt gt QueryDelegate getCount int getResults offset int pagelength int ResultSet setFilters filters List lt Filter gt void setOrderBy orderBys List lt OrderBy gt void storeRow row RowItem int beginTransaction void void void getPrimaryKeyColumns List lt String gt implementationRespectsPagingLimits boolean setFilters filters List lt Filter gt filteringMode FilteringMode void removeRow row RowItem boolean containsRowWithKey keys Object boolean void Rowa lt lt interface gt gt Property i Item A ColumnProperty allowReai y owner RowItem V Rewtem lt gt getId RowId getContainer SQLContainer isModified boolean 9 OracleGenerator MSSQLGenerator DefaultSQLGenerator kx lt lt interface gt gt SQLGenerator generateSelectQuery tableName String filters List lt Filter gt orderBys List lt OrderBy gt offset int pageLength int toSelect String String generateSelectQuery tableName String filters List lt Filter gt fil2. 4 2 4 Modified state When used in the transaction mode it may be useful to determine whether the contents of the SOLContainer 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 RowItems and ColumnProperties only depend on whether or not the actual Property values have been modified That is they do not reflect situations where the whole RowlItem has been marked for removal or has just been added to the container 4 3 Caching paging and refreshing To decrease the amount of queries made to the database SOLContainer uses internal caching for database contents The caching is implemented with a size limited LinkedHashMap containing a mapping from Rowlds to RowlItems Typically developers do not need to modify caching options Vaadin SQL Container 0 8 User Manual although some fine tuning can be done if required 4 3 1 Container size The SQL Container 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
3. and Microsoft SQL Server MSSQLGenerator A new or modified implementation may be provided to gain compatibility with older versions or other database servers A class diagram of SQLContainer and its supporting classes as well as connections to Vaadin interfaces is provided in figure 1 Note that not every method is shown in the diagram due to space constraints For further detail refer to the SQLContainer API documentation Vaadin SQL Container 0 8 User M anual FilteringMode FILTERING MODE INCLUSIVE FILTERING MODE EXCLUSIVE lt lt interface gt gt Container ItemSetChangeNotifier Si lt lt interface gt gt Container Filterable lt lt interface gt gt 4 Container Ordered lt lt interface gt gt Container Sortable lt lt interface gt gt J Container Indexed Container q 2a 3 zs o o Y V N Implemented by the user for sorting filtering and write support lt lt interface gt gt FreeformQueryDelegate setFilters filters List lt Filter gt setOrderBy orderBys List lt OrderBy gt void int storeRow row RowItem 0 1 d FreeformQuery queryString String setDelegate delegate FreeFormQueryDelegate void J2EEConnectionPool column String value Object secondValue Object OrderBy comparisonType ComparisonType 1 isCaseSensitive boolean needsQuotes boolean olumn String sAscending boolean SQLContainer TemporaryRowld
4. 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 e g a primary key column of the table 4 2 3 Autocommit 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 public void setAutoCommit boolean autoCommitEnabled It is recommended to leave the auto commit mode disabled since 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
5. table row count is assumed to remain valid for 10 seconds This value can be altered from code class SQLContainer field sizeValidMilliSeconds If the size validity time has expired the row count will be automatically updated on e Acallto getItemIds method e Acallto size method e Some calls to indexOfId Object itemId method e Acallto firstItemId method e When the container is fetching a set of rows to the item cache lazy loading 4 3 2 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 SOL Container is calculated by multiplying the page lenght 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 Tables while ensuring that no huge amounts of memory will be used on the cache 4 3 3 Refreshing the container Normally the SQLContainer will handle refreshing automatically when required However there may be situations whe
6. which currently has to be written to the applications using the SOLContainer Provide support for all SQL data types Provide means for the developer to specify their own column lt gt Java type mapping 12
7. Vaadin SLQContainer 0 8 User Manual Teppo Kurki O 2010 Vaadin Ltd Table of Contents Ilic ec 2 Ze PRECIO CUI EG este ML 3 3 Getting started with SQULCOmtanmen cisscsscsscdsssccsstssessinussncetssssossssdunscesstcsasnisecneassvesocanasdeseasoavesaeses 5 3 1 Creating a connection POON cado ooo Foe Rer ule Saves vos Face dues ene iti ib 5 3 2 Creating the TableQuery query delegate ure itr ttp tasg Vene uote te Ma Dey ae Rv lu geste iubeas 5 3 3 Creating the COBTAIICE orat pa e ioc RO api rd eter s Gri Vv Abs VO ue I cedro a dte dedos 5 4 Using the SOLCOIUIDIEE ori des RoUbrp Db EOrkkeves pev fue on bee Reb e ososan sosio Rete ossessi osese oist 6 4l Filtering and VSO ERT sa tus seats tiu saeua fec ot eR UE cani ca usd rev unco d 6 BV cd EIFE S sse a dea ORE RBS CO A RR eI EOS d aote e gua cate pu b od c etx uds 6 4N X Filtering THO CF ERE TP 6 CN DS SOL ME ROR MM M M EE RON DEM EN P E E 4 2 2 EUIS eatsets vp ng one med e esate lees Se ov ee Maca E DM Ee UR TUE 7 4 21 Adding t ms RON ER M NR T 7 4 2 2 Fetching generated FOWAe Saudi aee t ee Beute pee ue d he e RR ER Dee A LERRA RET 7 4 2 3 Version column TOAWIVORIORI eco eus se emi Tett pe one rd genet to meia bcd drug rci qe in amp 4 23 A to commit Modere ine ORE EAT ede aH tases RAVEN Sega MANA Riedie Te amp X84 Modified state castas vd shea eu etd dos shaves dad abate iustae as hovel aede neis eh OP enda ere Ei amp 4 3 Caching paging and Tem
8. es Wim gic oet bi d i era ipte e Ho de pde sabe b UE aaaeei 8 Z3 15 Container SIZE ma a t ERR eet auae teme easi ug ORA cadi a eA 9 4 3 2 Page length and cache SIZE i o etes v me e Unete emen etu e ea Lec es eui qua un 9 4 3 3 Refreshing the container sse eene enne nennen nnne nnns 9 5 Using FreeformQuery and FreeformQueryDelegate cese eee eerte eene e eene nennen enu 10 SANE Getting icai RR Mr CH pm ET 10 2 2 MIRA lotis cot dessen eio ae eh a hl he Ld ede te LOL TUE 10 5 3 Creating your own FreeformQueryDelegafe isa es reet esee cod oes eed e Ms CO Pu ex Ren 10 6 Non implemented methods of Vaadin container interfaces eeeeeee eese eee reete nennen 11 6 1 About the getItemIds method ey e pee nete rait Duc ag m ped ped ui dw 11 T Vini cec n ET 12 A Supported Gata bases suc sets eise dede Des edis ta cuc S E mund esu esas wee au Depot nonae 12 B Known issues and limitations of SQL Container essen 12 C Planned features o Rn rdoisst dodrina tlaoreet eoru sut bd tinus nan tach ie 12 Vaadin SQL Container 0 8 User Manual 1 Introduction Vaadin SQLContainer is a Vaadin container implementation that allows easy and customizable access to data stored in various SQL speaking databases see appendix A for details SOL Container supports two types of database access Using TableQuery the pre made query generators will enable fetchin
9. even if auto commit mode is enabled 4 2 2 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 TableQuery commit has finished this method is called by SOL Container when necessary To receive updates on the row IDs you might use the following code assuming container is an instance of SQLContainer app getDbHelp getCityContainer addListener new QueryDelegate RowldChangeListener public void rowIdChange RowldChangeEvent event System err println Old ID is event getOldRowId System err println New ID is vent getNewRowId Vaadin SQL Container 0 8 User Manual 4 2 3 Version column requirement If you are using the TableQuery class as the query delegate to the SQL Container 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 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
10. g SOLContainer container new SQLContainer tq After this statement the SOL Container is connected to the table tablename and is ready to use for example as a data source for a Vaadin Table or a Vaadin Form Vaadin SQL Container 0 8 User Manual 4 Using the SQLContainer 4 1 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 4 1 1 Filtering Filtering can be performed either using the Vaadin provided means implemented from Container Filterable using the following method where propertyId means column name in the SOLContainer context More information on the standard filtering can be found in Book of Vaadin public void addContainerFilter Object propertyId String filterString boolean ignoreCase boolean onlyMatchPrefix Note Using the standard method only allows filtering on String typed properties Numeric types dates etc must be filtered using the Filter class see below In addition to the standard method it is also possible to directly add a Filter to the container via the addFilter Filter filter method This enables the developer to take advantage of a few more features including More comparison
11. g updating and inserting data directly from the container into a database table automatically whereas 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 container interface to implement a bit 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 manual is to briefly explain the architecture and some of the inner workings of SOL Container It will also give the readers some examples on how to use SQL Container in their own applications The requirements limitations and further development ideas are also discussed Vaadin SQL Container 0 8 User Manual 2 Architecture The architecture of SOLContainer is relatively simple and it is described in detail in this section SQLContainer is the class implementing the Vaadin container interfaces and providing access to most of the functionality of this add on The standard Vaadin Properties and Items have been extended by the ColumnProperty and RowItem classes I
12. listed below The getItemIds method is very inefficient avoid calling it unless absolutely required The addItem method will return a temporary row ID even if auto commit mode is enabled When using FreeformQuery without providing a FreeformQueryDelegate the row count query is very inefficient avoid using FreeformQuery without implementing at least the count query properly When using FreeformQuery without providing a FreeformQueryDelegate writing sorting and filtering will not be supported When using Oracle or MS SQL database the column name rownum can not be used as a column name in a table connected to SQLContainer This limitation exists because the databases in question do not support limit offset clauses required for paging Instead a generated column is used to implement paging support Some SQL data types do not have write support when using TableQuery e All binary types All custom types CLOB if not converted to a String by the JDBC driver When using Oracle database most or all of the numeric types are converted to java math BigDecimal by the Oracle JDBC Driver This seems to be a limitation or a feature of the Oracle Driver and therefore no remedy to it is provided by the SQLContainer or TableQuery C Planned features Create a method of easily joining two SQLContainers with TableQueries in cases where e g foreign key is used in one of the tables This would greatly reduce mapping code
13. methods via Filter ComparisonType EQUALS GREATER LESS GREATER OR EQUAL LESS OR EQUAL STARTS WITH ENDS WITH CONTAINS BETWEEN Filtering of numeric and other non String types Two valued filtering via Filter setSecondValue Object secondValue Implicit setting for need of quoting via setNeedsQuotes boolean needsQuotes Removing the filtering rules is also done via the standard Vaadin methods public void removeContainerFilters Object propertyId public void removeAllContainerFilters These methods will remove filters added with either addFilter or addContainerFilter method 4 1 2 Filtering mode Currentle the SOLContainer has limited support for two filtering modes The modes are defined in the FilteringMode enum which is located in the query package The two modes are called exclusive and inclusive Exlusive mode means that in the generated query all the filtering rules will be joined with an OR Inclusivu mode means that AND will be used The default filtering mode is inclusive AND filtering Vaadin SQL Container 0 8 User Manual 4 1 3 Sorting Sorting can be performed either using the Vaadin provided means implemented from Container Sortable using the following method where the propertyIds again refer to column names More information on the standard filtering can be found in Book of Vaadin public void sort Object propertyId boolean ascending In addition to the standard method it is also po
14. onnection 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 connectionPool new SimpleJDBCConnectionPool org hsqldb jdbc JDBCDriver jdbc hsqldb mem sqlcontainer SA 2 5 3 2 Creating the TableQuery query delegate After the connection pool is created we ll need a query delegate for the SOL Container The simplest way to create one is by using the built in TableQuery class The TableQuery delegate provides access to a defined database table and supports reading and writing data out of the box We create the TableQuery with the following statement TableQuery tq new TableQuery tablename connectionPool If we need to enable the write support we must set a version column to the 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 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 The following code will set the version column tq setVersionColumn OPTLOCK 3 3 Creating the container Finally we may create the container itself This is as simple as statin
15. orting 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 SOL Container package more specifically in the class com vaadin addon sqlcontainer demo DemoFreeformQueryDelegate 10 Vaadin SQL Container 0 8 User Manual 6 Non implemented methods of Vaadin container interfaces Due to the database connection inherent to the SOL Container 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 UnsupportedOperationException on invocation public boolean addContainerProperty Object propertyId Class lt gt 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 Item interface are not supported in the RowlItem class public boolean addItemProper
16. re an implicit refresh is needed e g 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 e Autocommit mode Page length Filters Sorting Vaadin SQL Container 0 8 User Manual 5 Using FreeformQuery and FreeformQueryDelegate In most cases the provided 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 e g joins is needed Or perhaps you need to redefine how the writing or filtering should be done The FreeformQuery query delegate is provided for this exact purpose Out of the box the FreeformQuery supports read only access to a database but it can be extended to allow writing also 5 1 Getting started Getting started with the FreeformQuery may be done as shown in the following The connection pool initialization is similar to the TableQuery example so it is omitted here Note that the name s of the primary key column s must be provided to the FreeformQuery manually This i
17. s 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 connectionPool ID SOLContainer container new SQLContainer query 5 2 Limitations While this looks just as easy as with the TableQuery do note that there are some important caveats here Using FreeformQuery like this without providing FreeformQueryDelegate 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 FreeformQueryDelegate interface 5 3 Creating your own FreeformQueryDelegate To create your own delegate for FreeformQuery you must implement some or all of the methods from the FreeformQueryDelegate 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 String getCountQuery public String getQueryString int offset int limit public String getContainsRowQueryString Object keys Filtering and s
18. ssible to directly add an OrderBy to the container via the addOrderBy OrderBy orderBy method This enables the developer to insert sorters one by one without providing the whole array of them at once Sorting rules can be cleared by calling the sort method with null or an empty array as the first argument 4 2 Editing Editing the items RowItems of SQLContainer can be done similarly to editing the items of any Vaadin container 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 autocommit mode 4 2 1 Adding items Adding items to the SOL Container 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 commited to the database through the querydelegate depending on whether the auto commit mode see 4 2 3 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 Rowld for the new item See 4 2 2 on how to fetch the actual key after the row insertion has succeeded Note Currently the addItem method will return a temporary row ID for the added item
19. tem IDs are represented by RowId and TemporaryRowId classes The Rowld class is built based on the primary key columns of the connected database table or query result In the connection package the JDBCConnectionPool interface defines the requirements for a connection pool implementation Two implementations of this interface are provided SimpleJDBCConnectionPool provides a simple yet very usable implementation to pool and access JDBC connections J2EEConnectionPool provides means to access J2EE DataSources The query package contains the QueryDelegat 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 TableQuery for automatic read write support for a database table and FreeformQuery for customizing the query sorting filtering and writing this is done by implementing relevant methods of the FreeformQueryDelegat interface The query package also contains Filter and 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 generator package contains a SQLGenerator interface which defines the kind of queries that are required by the TableQuery class The provided implementations include support for HSQLDB MySQL PostgreSQL DefaultSQLGenerator Oracle OracleGenerator
20. terMode FilteringMode orderBys List OrderBy offset int pagelength int toSelect int String generateUpdateQuery tableName String item RowItem String generateInsertQuery tableName String item RowItem String generateDeleteQuery tableName String item RowItem String lt lt interface gt gt T QueryDelegate RowldChangeEvent lt lt interface gt gt QueryDelegate RowldChangeListener lt lt interface gt gt QueryDelegate RowldChangeNotifier N 1 q releaseConnection conn Connection void destroy void tableName String sqlGenerator SQLGenerator SimpleJDBCConnectionPool initialConnections int maxConnections int connectionUri String userName String password String Figure 1 Detailed class diagram of SOLContainer and its supporting classes Vaadin SQL Container 0 8 User Manual 3 Getting started with SQLContainer Gettting development going with the SQLContainer is easy and quite straight forward The purpose of this chapter 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 3 1 Creating a connection pool First we need to create a connection pool to allow the SQL Container to connect to a database Here we will use the SimpleJDBCConnectionPool which is a basic implementation of connection pooling with JDBC data sources In the following code we create a c
21. ty Object id Property property public boolean removeItemProperty Object id 6 1 About the getItemIds method To properly implement the Vaadin Container interface a getItemIds method has been implented in the SQL Container By definition this method returns a collection of all the item IDs present in the container What this means in the SQL Container 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 SQLContainer and therefore the following warning is expressed here It is highly recommended not to call the getitemlds method unless it is known that in the use case in question the item ID set will always be of reasonable size 11 Vaadin SQL Container 0 8 User Manual 7 Appendices A Supported databases The following databases are supported by SQLContainer and TableQuery classes by default HSQLDB 1 8 or newer MySQL 5 1 or newer PostgreSQL 8 4 or newer Oracle Database 10g or newer Microsoft SQL Server 2005 or newer B Known issues and limitations of SQLContainer At this point there are still some known issues limiting the use of SQLContainer in certain situations The issues and brief explanations are
Download Pdf Manuals
Related Search