When using a connection pool, the pool implementation takes care of closing the JDBC connections. Depending on the specific implementation, you might have to invoke this process. Usually, you want the pool to be active during the lifetime of your web application. Remember the ServletContextListener implementation we used to initialize the connection pool? Well, we can use it to shut the pool down. All we need to do is to implement the contextDestroyed method:

@WebListener
public static class JdbcExampleContextListener implements ServletContextListener {
    ...

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        DatabaseService.shutdown();
    }
}

Finally, the shutdown method is implemented as follows:

public class DatabaseService {
    ...
    public static void shutdown() {
        pool.shutdown();
    }
}

Now, it's a good time for you to play with the chapter-05 demo application again. Have a close look at the DatabaseService class and how it is used in the VaadinUI class. The findAllMessages method is pretty interesting in particular, as it acts as the main communication point between the Vaadin application and the UI:

package packt.vaadin.datacentric.chapter05.jdbc;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author Alejandro Duarte
 */
public class DatabaseService {

    private static final String SELECT_SQL = "SELECT content FROM messages";
    private static final String CONTENT_COLUMN = "content";
    ...

    public static List<String> findAllMessages() throws SQLException {
        try (Connection connection = pool.getConnection()) {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SELECT_SQL);

            List<String> messages = new ArrayList<>();
            while (resultSet.next()) {
                messages.add(resultSet.getString(CONTENT_COLUMN));
            }

            return messages;
        }
    }
    ...

}

See how the SQL queries are defined in String constants. Can you think of a better way of doing this? In a more complex application, you might end up having hundreds of SQL queries. A better practice in these scenarios is to externalize the SQL code. Properties files might help, but defining SQL queries in a single line could be a challenge, to say the least. A better approach is XML, a format that allows you to use multiple lines to define values. We'll see how MyBatis promotes this approach in Chapter 6, Connecting to SQL Databases Using ORM Frameworks.

Another interesting detail in the findAllMessages method is the return type. Why not return a ResultSet object? We do this to avoid coupling the presentation layer with persistence implementation details. This is what we are going to explore in the following section.