Getting Started With Dropwizard: Connecting to a Database Using Hibernate

In the previous installments of this series we discussed how to create a Dropwizard application using Maven archetype and how to use a YAML file to set configuration parameters for the application. In this tutorial we'll create a simple application that exposes an Employee Directory via REST API. For simplicity reasons our representations will not contain hyperlinks, that is we'll talk about Level 2 of Richardson Maturity Model. The sources for the tutorial can be found here.

We'll begin with adding parameters of database connection including credentials, connection URI and database driver name to the application's configuration file. The snippet below shows how to configure the database connection.

# Database settings.
database:
    # the name of the JDBC driver, mysql in our case
    driverClass: com.mysql.jdbc.Driver
    # the username
    user: javaeeeee
    # the password
    password: 1
    # the JDBC URL; the database is called DWGettingStarted
    url: jdbc:mysql://localhost:3306/DWGettingStarted

MySQL RDBMS was used for our example, but some other DBMS can be used. As we'll use Hibernate to talk to our database, switching databases is a snap, it's only necessary to change the name of the driver in the snippet above. The benefit of using Hibernate is that it knows how to work with different database management systems, and there is no necessity to change application's code after switching to a different RDBMS.

Our next step would be changing the pom.xml file of the application. Dropwizard has JDBI and Hibernate modules which can be used to connect to a database, and both include the database module which is also part of Dropwizard, therefore by adding Hibernate module, we accomplish the bigger part of changes necessary to use database in our application. In addition, we should add a dependency on a database driver, MySQL one in our case. So, if you prefer some other RDBMS, don't forget to replace the driver dependency. A code snippet with all necessary dependencies is shown below.

        <dependency>
            <groupId>io.dropwizard</groupId>
            <artifactId>dropwizard-hibernate</artifactId>
            <version>${dropwizard.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.36</version>
        </dependency>

Then, after the required dependencies were added, we can change the Configuration class, called DWGettingStartedConfiguration in the example application. The sets of related parameters are grouped into so-called factories. We should create a new field of DataSourceFactory type and add a getter for it and Dropwizard will deserialize all database connections settings from the config.yml file to the factory. Here is the code to be added to the Configuration class.

public class DWGettingStartedConfiguration extends Configuration {

  ...

    /**
     * A factory used to connect to a relational database management system.
     * Factories are used by Dropwizard to group together related configuration
     * parameters such as database connection driver, URI, password etc.
     */
    @NotNull
    @Valid
    private DataSourceFactory dataSourceFactory
            = new DataSourceFactory();

    ...

    /**
     * A getter for the database factory.
     *
     * @return An instance of database factory deserialized from the
     * configuration file passed as a command-line argument to the application.
     */
    @JsonProperty("database")
    public DataSourceFactory getDataSourceFactory() {
        return dataSourceFactory;
    }

}

If you are new to Hibernate, it suffices for now to know that Hibernate is able to map Java classes to database tables, that is for our application we'll need an Employee class with all the fields, describing an employee and an employees table with columns to store the same data and Hibernate knows how to transmit data from fields to columns and vice versa. Enough talk, let’s create a table and a class. SQL script to create a table is shown below.

-- A script to create employee table
create table employees(
    -- auto-generated primary key
    id bigint primary key not null auto_increment,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    -- employee position
    e_position  varchar(255) not null,
    phone  varchar(255) not null,
    e_mail varchar(255) not null
);

Now let's turn to the class.

@Entity
@Table(name = "employees")
@NamedQueries({
    @NamedQuery(name = "com.javaeeeee.dwstart.core.Employee.findAll",
            query = "select e from Employee e"),
    @NamedQuery(name = "com.javaeeeee.dwstart.core.Employee.findByName",
            query = "select e from Employee e "
            + "where e.firstName like :name "
            + "or e.lastName like :name")
})
public class Employee {

    /**
     * Entity's unique identifier.
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    /**
     * employee first name.
     */
    @Column(name = "first_name")
    private String firstName;
    /**
     * employee last name.
     */
    @Column(name = "last_name")
    private String lastName;
    /**
     * employee position.
     */
    @Column(name = "e_position")
    private String position;
    /**
     * employee phone.
     */
    private String phone;
    /**
     * employee e-mail.
     */
    private String e_mail;

    /**
     * A no-argument constructor.
     */
    public Employee() {
    }

    /**
     * A constructor to create employees. Id is not passed, cause it's
     * auto-generated by RDBMS.
     *
     */
    public Employee(String firstName, String lastName, String position, String phone, String e_mail) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.position = position;
        this.phone = phone;
        this.e_mail = e_mail;
    }

    // Auto-generated equald, hashCode, getters and setters.
    ...
  }

It's a Plain Old Java Object in addition to fields containing a couple of constructors, the no-argument is required, equals() and hashCode() methods inherited from Object and getters and setters for all fields, which can be autogenerated by an IDE and are not shown here. Also, there are some annotations. The first, @Entity shows that this POJO is mapped to a table. The second, @Table, is used to supply the name of the table to which the POJO is mapped and is required only if the names of the class and the table differ as in our case: Employee/employees. The third, @Id, is used to mark the field that uniquely identifies the instance of the class and in our case will store the auto-generated by RDBMS primary key. And, finally, the @GeneratedValue annotation shows that we use auto-increment as the generation strategy and instructs Hibernate to read the generated key value from the database to id field after saving a new entity. Also, there is a @Column which is used only if the name of a field and related column name differ.

In addition, we'll need to decorate our entity with one more annotation - @NamedQuery, which contains a comma – separated list of named queries, each used to extract data from the database. A special Java Persistence Query Language (JPQL) is used to create queries, which resembles SQL, but works not with tables and rows, but with classes and entities. The couple of queries to extract the full list of employees and to search employees by name are shown above.

We used fully qualified  class names in the names of the queries to prevent creating queries with the same names but for different entities. The second query contains the so-called named parameter and we'll see in a moment how to pass parameters to queries. Also it is possible to split returned by queries lists of objects into parts, or in other words to paginate the list.

To hide from the developers most of the intricacies of Using Hibernate, Dropwizard offers us an AbsractDAO class. We should create DAO classes which implement all the CRUD logic by extending this class. An example is shown here.

public class EmployeeDAO extends AbstractDAO<Employee> {

    /**
     * Constructor.
     *
     * @param sessionFactory Hibernate session factory.
     */
    public EmployeeDAO(SessionFactory sessionFactory) {
        super(sessionFactory);
    }

    /**
     * Method returns all employees stored in the database.
     *
     * @return list of all employees stored in the database
     */
    public List<Employee> findAll() {
        return list(namedQuery("com.javaeeeee.dwstart.core.Employee.findAll"));
    }

    /**
     * Looks for employees whose first or last name contains the passed
     * parameter as a substring.
     *
     * @param name query parameter
     * @return list of employees whose first or last name contains the passed
     * parameter as a substring.
     */
    public List<Employee> findByName(String name) {
        StringBuilder builder = new StringBuilder("%");
        builder.append(name).append("%");
        return list(
                namedQuery("com.javaeeeee.dwstart.core.Employee.findByName")
                .setParameter("name", builder.toString())
        );
    }

    /**
     * Method looks for an employee by her id.
     *
     * @param id the id of an employee we are looking for.
     * @return Optional containing the found employee or an empty Optional
     * otherwise.
     */
    public Optional<Employee> findById(long id) {
        return Optional.fromNullable(get(id));
    }
}

We relied on the inherited method namedQuery(), which returns objects of a class implementing Query interface, which, in turn, can be used to pass parameters to queries. Also we used the inherited list() method, which knows how to retrieve lists from database using JPQL queries and inherited get() method which is used to fetch entities by id.

The next leg of our journey will be to create a resource class which relies on the newly created DAO and returns JSON representations of separate employees and lists of employees. The code is shown below. Most parts should be familiar to you, if you’ve read the previous two articles in this series. The difference now is that our resource class contains a DAO as a field and a constructor to initialize it. The striking difference is the @UnitOfWork annotation that decorates the methods of the class. It is part of Dropwizard and is used to save developers from writing a lot of Hibernate-related boilerplate code.

@Path("/employees")
@Produces(MediaType.APPLICATION_JSON)
public class EmployeesResource {

    /**
     * The DAO object to manipulate employees.
     */
    private EmployeeDAO employeeDAO;

    /**
     * Constructor.
     *
     * @param employeeDAO DAO object to manipulate employees.
     */
    public EmployeesResource(EmployeeDAO employeeDAO) {
        this.employeeDAO = employeeDAO;
    }

    /**
     * Looks for employees whose first or last name contains the passed
     * parameter as a substring. If name argument was not passed, returns all
     * employees stored in the database.
     *
     * @param name query parameter
     * @return list of employees whose first or last name contains the passed
     * parameter as a substring or list of all employees stored in the database.
     */
    @GET
    @UnitOfWork
    public List<Employee> findByName(
            @QueryParam("name") Optional<String> name
    ) {
        if (name.isPresent()) {
            return employeeDAO.findByName(name.get());
        } else {
            return employeeDAO.findAll();
        }
    }

    /**
     * Method looks for an employee by her id.
     *
     * @param id the id of an employee we are looking for.
     * @return Optional containing the found employee or an empty Optional
     * otherwise.
     */
    @GET
    @Path("/{id}")
    @UnitOfWork
    public Optional<Employee> findById(@PathParam("id") LongParam id) {
        return employeeDAO.findById(id.get());
    }
}

Also a Dropwizard LongParam class should be mentioned, which is helpful in checking that the argument of the correct type was passed from the client. If the client tries to provide a string instead of long, 400 Bad Request response will be returned. Please note, that Dropwizard knows how to deal with Optionals, and 404 Not Found HTTP response code code will be returned to the client, if a method in the resource class returns an empty Optional.

And now we have to create a so-called Hibernate bundle – a new field in the Application class, called DWGettingStartedApplicationin the example application. Please note, that a comma-separated list of all entity classes should be passed to the Hibernate bundle.

public class DWGettingStartedApplication
        extends Application<DWGettingStartedConfiguration> {

    /**
     * Hibernate bundle.
     */
    private final HibernateBundle<DWGettingStartedConfiguration> hibernateBundle
            = new HibernateBundle<DWGettingStartedConfiguration>(
                    Employee.class
            ) {

                @Override
                public DataSourceFactory getDataSourceFactory(
                        DWGettingStartedConfiguration configuration
                ) {
                    return configuration.getDataSourceFactory();
                }

            };

    ...

    @Override
    public void initialize(
            final Bootstrap<DWGettingStartedConfiguration> bootstrap) {
        bootstrap.addBundle(hibernateBundle);
    }

    ...

}

We have to add the bundle in the body of the Application's initialize() method and after that, it can be used later to produce instances of Hibernate ConnectionFactory objects. We won't go into the details of how the aforementioned factory works, due to the fact that Dropwizard uses it behind the scenes to perform CRUD operation and there is no necessity to dig deeper to start using Hibernate with Dropwizard.

Finally, we should register our resource class in the run() method of the Application class, but before that we have to create an instance of DAO and pass an instance of Hibernate session factory, obtained from Hibernate bundle, to it. The code snippet below shows how this could be accomplished.

public class DWGettingStartedApplication
        extends Application<DWGettingStartedConfiguration> {

    ...

    @Override
    public void run(final DWGettingStartedConfiguration configuration,
            final Environment environment) {
        final EmployeeDAO employeeDAO
                = new EmployeeDAO(hibernateBundle.getSessionFactory());

        ...

        environment.jersey().register(new EmployeesResource(employeeDAO));
    }

}

We have created a simple resource class that can extract data from a RDBMS, using Hibernate and helpful classes offered by Dropwizard, and return JSON representations to the client. If you followed the series, you should be able to test the code using either cURL or a browser add-on. It can be seen that Dropwizard Hibernate module makes it extremely easy to create a RESTlike API that uses a database to store its data.

Resources

1. Dropwizard Hibernate

2. Hibernate Documentation

3. Ideas for Employee Directory application