Docs

You are viewing documentation for an older Vaadin version. View latest documentation

Docs

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.

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 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);

Creating the 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 TableQuery class. The 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 TableQuery is instantiated. We create the 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 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. If another user has changed the row and the version number in the database does not match the version number in memory, an 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");

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 SQLContainer 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.