Vaadin SLQContainer 0.8 User Manual Table of Contents
Contents
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 connection pool that uses the HSQLDB driver an in memory database The initial amount of connections is 2 and the maximum amount is set at 5 JDBCConnectionPool connectionPool new SimpleJDBCConnectionPool org hsqldb jdbc JDBCDriver jdbc hsqldb mem sqlcontainer SA 2 5 Creating the TableQuery query delegate After the connection pool is created we ll need a query delegate for the SQL Container The simplest way to create one is by using the provided 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 column2. 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 Vaadin SQL Container 0 8 User Manual Appendices A Supported databases The following databases are supported by SQL Container 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 listed below The getItemIds method is very inefficient avoid calling it unnecessarily 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
3. the 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 When the container is fetching a set of rows to the item cache Page length and cache size The page length of the SQL Container 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 rate 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 cache size becomes 200 items Refreshing the container Normally the SQLContainer will handle refreshing automatically when required However there may be situations where an implicit refresh is needed For this purpose a refresh method is provided This method simply clears all caches resets the current item
4. tq setVersionColumn OPTLOCK Creating the container Finally we may create the container itself This is as simple as stating SOLContainer container new SQLContainer tq After this statement the SQL 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 Using the SQL Container 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 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 Filt
5. 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 Architecture The architecture of SOL Container 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 extend by ColumnProperty and RowItem and RowId and TemporaryRowId are used as item IDs In the connection package JDBCConnectionPool interface defines requirements for a connection pool implementation and SimpleJDBCConnectionPool provides a simple yet very usable implementation of the interface The query package contains the QueryDeleg
6. 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 All binary types e All custom types e CLOB if not converted to a String by the JDBC driver When using Oracle database most of the numeric types including INTEGER 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 Replace Oracle numeric type mapping with something more reasonable Design means to allow direct access to database generated keys after adding rows e Possibly a TemporaryRowld lt gt RowId mapping 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 which currently has to be written to the applications using the SQLContainer 10
7. E Arrays asList ID connectionPool SOLContainer container new SQLContainer query 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 Creating your own FreeformQueryDelegate To create your own delegate for FreeformQuery you must implement some or all of the methods from the FreeformQueryDelegate interface 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 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 boolea
8. Vaadin SLQContainer 0 8 User Manual Teppo Kurki O 2010 Vaadin Ltd Table of Contents PTE OC UW CUOM REM CM e M 2 PRP CIC CUUR G PN seese resien a a esee iaae aios 3 Getting started with SQLContainer ssessseessoecssesssocesoessoosssoesssesssosssoessoossssesssosessosoesssssssoessssssssoe 4 Creating a connection POO loaner im a dele e aa e pani ete a ae arita 4 Creating the TableQuery query delegate berti teta ese ute tee seatiags teettayvanedyaqausetameaaess 4 Creating the COBLAIDOE nassir scree cous pe rv api er eter Qs Sr Pv Vo rua I cred rod a ani 4 Using th SOLCOBUDIGE cei derite dee reto Eieieves bev fue iex sosto ots TES pP V Rete Ye ep odes sses oossoo 5 Filtering and SELINA core dut oe t sauber cl cya oN ssh Ll snb cc tob dn Dessen teet cedes 5 TOTO La nteger ort esp MR ga a e ERR nc afe eot agio E deut 5 Filtering MORE eet na ee ae we de tee su EU he vols a Fon a a A ebat etd 5 VA Jaca T A Tore E E E I A DM aD Ram v as 5 JE I MR M RD A DR E MER RUE aos ERI RUN NOE E 6 Autocommit mode e loei siete ostio peo Ms eL eter Ea as RR T Ru LA Waa cadere ee fud tuat 6 NEBULA SEALE oat e tete petas donde ise ob osi utbs cn E ose dte die D odd 6 Caching paging and refreshimg coc ecd Ls Lo eeu Are Ee D A ape E Lea uud 6 CONG SITE ose eon tete dicata OSCURA Rc Usa A tst 7 Pagelenethand cute ISO otia sme EEE tuni ee vut vadel o aos dua ein usn ut eto ooi uir A Refreshing the CORGIBEF did ee oti ein ore oe ape bg ae dne o i IMAM AR acs R
9. at 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 make the standard Vaadin container filtering and sorting a bit more database 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 and Microsoft SOL Server MSSQLGenerator A new or modified implementation may be provided to gain compatibility with additional database servers A detailed class diagram of SQLContainer and its supporting classes as well as connections to Vaadin interfaces is provided in figure 1 column String value Object secondValue Object Property FilteringMode OrderBy lt lt interface gt gt A Item N ColumnProperty ERES boolean owner Rowltem lt lt interface gt gt Td Container Filterable i Q T Rowld c T SQLContainer Container Ordered S refresh void setAutoComm
10. er filter method This enables the developer to take advantage of a few more features including More comparison 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 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 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 pub
11. 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 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 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 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 SAMPL
12. it autoCommitEnabled boolean void V Rewitem interfacex isAutoCommit boolean Rowld 1 lt gt ftgetId RowId Container Sortable lt setPageLength pageLength int void getContainer SQLContainer getPageLength void isModified boolean E i B 0 Zeintertaces gt commit void Container Indexed S Prot back ye More s addFilter filter Filter void addOrderBy orderBy OrderBy void MSSQLGenerator OracleGenerator 1 setFilteringMode filteringMode FilteringMode void Container UN isModified boolean gt DefaultSQLGenerator lt lt lt interface gt gt V QueryDelegate lt lt interface gt gt getCount int SQLGenerator getIdList ResultSet FILTERING MODE_INCLUSIVE FILTERING MODE EXCLUSIVE lt lt interface gt gt needsQuotes boolean comparisonType ComparisonType isCaseSensitive boolean column String isAscending boolean Container ItemSetChangeNotifier Si generateSelectQuery tableName String filters List lt Filter gt getResults offset int pagelength int ResultSet orderBys List OrderBy setFilters filters List lt Filter gt void setOrderBy orderBys List OrderBy void storeRow row RowItem int offset int pageLength int toSelect String String generateUpdateQuery tableName String item RowItem String lt lt interface gt gt beginTransaction void generateInsertQuery tableName String item RowItem String F
13. lic void sort Object propertyId boolean ascending In addition to the standard method it is also possible 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 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 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 autocommit mode When this mode is enabled all changes will be committed to the database immediately To enable or disable the autocommit mode call the following method public void setAutoCommit boolean autoCommitEnabled It is recommended to leave the autocommit mode disabled since it ensures that the changes can be rolled back if any problems are noticed within the containe
14. n 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 Vaadin SQL Container 0 8 User Manual Non implemented methods of Vaadin container interfaces Due to the database connection inherent to the SQL Container some of the methods from the container interfaces of Vaadin can not be or would not make sense to 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 addItemProperty Object id Property property public boolean removeItemProperty Object id 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
15. ota et E 7 Using FreeformQuery and FreeformQueryDelegate eerie eee e eee e ee ette nenne eee tnn annue 8 CIeL ng Started soos tuam RTA OE RBS SM AM ot Dep M E 8 EAI ATI ONS T 8 Creating your own EreeformQueryDelesate i cavenstsaencdaivcesnets ver ee bete an art ceca aee n vue vs 8 Non implemented methods of Vaadin container interfaces eee eee eee eee eee ettet 9 About the getItemIds t ethod ont ee pcr ei edu anoo ppm UM ot telat 9 AP PpPEndiCES oL O G 10 AX supported databases ue id Goto bed cup ese aee E d e tase edere teta bo Reto E p So td 10 B Known issues and limitations of SQL Container essen nnne 10 COUP Toti d EC aEUEE 28 a2 ortu Ss utet tuu cue cates ela e Lu M M DM EU EE 10 Vaadin SQL Container 0 8 User Manual 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 fetching 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
16. r items Using the autocommit mode will also lead to failure if the database table contains non nullable columns Modified state When used in the transaction mode it may be useful to determine whether the contents of the 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 RowlItem 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 do not reflect situations where the whole RowItem has been marked for removal or has just been added to the container 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 Vaadin SQL Container 0 8 User Manual mapping from Rowlds to RowItems Typically developers do not need to modify caching options although some fine tuning can be done if required 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
17. reeformQueryDelegate rollback void setSearchStringEscape searchStringEscape String void commit void generateDeleteQuery tableName String item RowItem String t setFilters filters List lt Filter gt void getPrimaryKeyColumns List lt String gt 1 setOrderBy orderBys List OrderBy void implementationRespectsPagingLimits boolean storeRow row RowItem int setFilters filters List lt Filter gt filteringMode FilteringMode void 9 1 removeRow row RowItem boolean 0 containsRowWithKey keys Object boolean TableQuery A tableName String sqlGenerator SQLGenerator FreeformQuery V queryString String D setDelegate delegate FreeFormQueryDelegate void 1 SimpleJDBCConnectionPool N s lt intertace gt gt initialConnections int Implemented by the user JDBCConnectionPool maxConnections for sorting filtering and write support reserveConnection Connection lt connectionUri String releaseConnection conn Connection void userName String destroy void password String Figure 1 Detailed class diagram of SQLContainer and its supporting classes Vaadin SQL Container 0 8 User Manual 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
Download Pdf Manuals
Related Search