One of the most common pitfalls when developing web applications with Vaadin is to forget that what you are developing is actually a web application! Since the API resembles that of desktop-like UI frameworks, it's easy to forget that a Vaadin application is most likely going to be used by several users at the same time. You need to keep the multi user nature of a Vaadin application in mind when establishing connections to a database.

A desktop application you run locally on your machine might be able to work perfectly with a single connection to the database during its execution time (depending on the complexity of the application, of course). This is due to the single-user nature of the application; you know there's only one user per instance. On the other hand, a single instance of a web application is used by many users at the same time. It requires multiple connections to work properly. You don't want to have users A, B, C..., X waiting for the connection to be released by greedy user Z, right? However, establishing connections is expensive! Opening and closing a connection every time a new user requests the application is not an option, since your app could reach a big number of concurrent users, and hence connections.

This is where a connection pool comes in handy. A connection pool is a class that maintains several connections to the database, like a cache of connections, if you wish. The connection pool keeps all its connections open so that they can be reused by client classes when they need it. Without a connection pool, any time your app needs to perform a database operation, it has to create a new connection, execute the query, and close the connection. As mentioned previously, this is expensive and wastes resources. Instead, a connection pool creates a set of connections and "lends" them to client classes. Once the connection is used, it's not closed, but returned to the pool and used again.

As you can guess, connection pools are such a well-known pattern that many implementations exist. Let's see how to use one of them, BoneCP, a free open source JDBC connection pool implementation.

First of all, here are the dependencies you need to add:

<dependency>
    <groupId>com.jolbox</groupId>
    <artifactId>bonecp</artifactId>
    <version>0.8.0.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>1.7.25</version>
    <scope>test</scope>
</dependency>

BoneCP requires SLF4J, a logging library that offers a facade over several logging frameworks. This is needed in order to see logs by BoneCP in the console or web server log.

There should be one instance of the connection pool per instance of the web application. We used a static Java block in the previous chapter to initialize application-level resources. This works in small applications where these resources don't depend on others. In more complex applications, your initialization code might depend on other services (such as dependency injection) in order to work, so let's use a more realistic approach this time and use a ServletContextListener to init the connection pool. A ServletContextListener allows your application to react to events in the servlet context lifecycle; in particular, the initialization of a servlet context and its destruction.

As with previous examples, the chapter-05 Vaadin application includes a WebConfig class that defines everything web; that is, servlets and event listeners. Besides the VaadinServlet, we can include a ServletContextListener that initializes the database when the servlet context is created (that is when the web application starts... sort of):

@WebListener
public static class JdbcExampleContextListener implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        try {
            DatabaseService.init();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
    }
}

Thanks to the @WebListener annotation, this class is going to be discovered by the servlet container and registered with the servlet context automatically. Inside the contextInitialized method, we can add the code to initialize the connection pool; in this case, by delegating to the custom DatabaseService class we'll implement next.

The infrastructure code is ready; now it's time for the actual BoneCp connection pool. Let's start with the init method:

public class DatabaseService {

    private static BoneCP pool;

    public static void init() throws SQLException {
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl("jdbc:h2:tcp://localhost/~/h2-databases/demo");
        config.setUsername("sa");
        config.setPassword("");

        pool = new BoneCP(config);
    }
}

This implementation defines a static (only one instance per application) BoneCP field, pool, the actual connection pool. The pool field is initialized in the init method, which is called when the application starts (see the JdbcExampleContextListener class).

There are three things you need to specify when connecting to a database with JDBC:

• The connection URL: With JDBC, a database is represented by a connection URL. JDBC uses this URL to get the information about where and how to connect to the database. In the previous example, we can see that the string contains the name of the database (h2), a host (localhost), and a database name (~/h2-databases/demo).
• The username: Databases allow you to define a set of users, roles, and permissions. The username is an identifier that the database can check in order to grant permissions on the data. By default, the H2 database defines the username sa.
• The password: As you can guess, this is what allows the database engine to run the authentication check. By default, H2 uses an empty password for the default sa user.

What if you wanted to use MySQL or PostgreSQL now? you would have to change the String literals in this class, recompile, and redeploy. A much better approach is to externalize this String. One approach is to use the standard Java Properties class to load key/value pairs with the connection URL, username, and password. For example, the chapter-05 application includes a datasource.properties file in the /src/main/resources directory:

datasource.url=jdbc:h2:tcp://localhost/~/h2-databases/demo
datasource.username=sa
datasource.password=

The DatabaseService class can now use these properties (datasource.*) instead of the hard-coded literals:

public class DatabaseService {

    private static String url;
    private static String password;
    private static String username;
    private static BoneCP pool;

    public static void init() throws SQLException, IOException {
        loadProperties();
        createPool();
    }

    private static void loadProperties() throws IOException {
        try (InputStream inputStream = DatabaseService.class.getClassLoader().getResourceAsStream("datasource.properties")) {
            Properties properties = new Properties();
            properties.load(inputStream);

            url = properties.getProperty("datasource.url");
            username = properties.getProperty("datasource.username");
            password = properties.getProperty("datasource.password");
        }
    }

    private static void createPool() {
        ...
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        ...
    }
}

The connection properties (url, username, and password) are now static fields in the class populated from the datasource.properties file.

Connection connection = pool.getConnection();

A Connection represents a session with the database. This interface contains a number of methods to get information about the capabilities of the database and the state of the connection, but the most important part allows you to create Statements objects.