vaadin-framework/documentation/sqlcontainer/sqlcontainer-getting-started.asciidoc

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