Thursday, February 23, 2012

Sample App Tour

In my last blog, I performed a very short DB migration from a local PostgreSQL installation to one running in the cloud. In this blog, I want to share the small "wine database" application that I used. All of the code is available in this zip file, including a pom.xml file for building through maven (if you want to build without maven, the only dependencies are the Java EE APIs and Vaadin -- see below). My target audience is developers who know how to set up JDBC connection pools and generate DB tables, but if there's interest I can follow up with a step-by-step guide to deploying this application.

Before getting into the application, here is the SQL used for creating the database tables and initial information (the file is also included in the zip above in the 'sql' directory). There are two simple tables, one for wines and one for wine classifications. The wine table holds a foreign key to entries in the classifications table.

The application is a small Java EE app using Vaadin for the web framework. Because the last post was more about the database than the application, it's not a full CRUD app: it's more CR-oriented in that you can save and view data only. Very little code is required to create this application: one POJO for each DB entity/table, a simple EJB service object between the UI and back end, three files for the whole UI, and a very short servlet class used to load the UI. Please see the source code for full comments, but here is the relevant part of the servlet class:
@WebServlet(urlPatterns = {"/app/*", "/VAADIN/*"})
public class UIServlet extends AbstractApplicationServlet {
    WineService service;
    protected Application getNewApplication(HttpServletRequest request)
        throws ServletException {
        return new MainUI(service);
    protected Class<? extends Application> getApplicationClass()
        throws ClassNotFoundException {
        return MainUI.class;

That's all that's needed to create the Vaadin application object and pass a reference to the EJB service to it. From there, the class creates a small user interface with a table and single button for adding more entries to the table. The button has a listener that pops up a child window containing the form for adding another entry:

To access the back-end data, I'm using two separate strategies. That may sound complicated, but it's actually due to laziness (a good trait in engineers when used properly!). The table is using Vaadin's SQL Container, which is very easy to set up. It connects to the database through SQL directly, and handles details like paging, optimistic locking, etc, automatically. The form that adds new wines is using JPA, which also means very little code to write since the EJB can persist an entity while handling transactions, locking, etc, with one line of code:

    public void store(Wine wine) {

The data source for the table, the SQLContainer, can use a database connection pool to perform its queries. This is as simple as using new J2EEConnectionPool("jdbc/WineDB") in the table class. Because this jndi name is used here and in persistence.xml, both database access methods use a single definition of the datasource, as shown here from the GlassFish administration console. Here is the resource definition:

...and here are the relevant details of the winedb-pool connection pool:

Thus, I can change the location of the database in only one place and the application will find it. I'm letting the database assign primary keys rather than letting JPA do it. Here is the SQL, for example, for creating the classification table and inserting some data:
create table classification (
  id               serial primary key,
  class_name       varchar(255) not null
insert into classification (class_name) values ('Cabernet');
insert into classification (class_name) values ('Merlot');

In my JPA entity, I use the following annotations to work with the primary key generation strategy:
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

This makes it easier to access the database through tools outside of the application itself, without worrying about details like a key generation table.

One last note about the application: every class in the app is serializable, which is required by Vaadin and which is a good idea in general for a web app (so the container can persist your session to some storage or to enable replication/failover). I didn't bother with generating serialVersionUIDs for the UI classes since they will only be serialized/deserialized by the container and not transferred around. I could have done the same with the entities, but for these I try to remember to generate the IDs just in case a future version of an application uses them as DTOs. In this case, it's just a matter of preference.

For a future blog, I hope to rewrite the app to run in Tomcat and deploy it to Amazon's Elastic Beanstalk using the JDBC URL property to connect it to the cloud database.


  1. This comment has been removed by the author.

  2. Hi Bobby,

    I know this post is nearly three years old but I'm having trouble deploying the WineDB project as is. Is it possible you could go through step-by-step the process? Thanks so much!