1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
---
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#.
|