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