Spring Boot and JDBC for PostgreSQL on Heroku

Author       : Rajdeep dua
Last Updated : March 17, 2016

This article shows how to run a Spring Boot app with PostgreSQL JDBC driver which connects to the PostgreSQL database. Runtime environment will be local as well as Heroku.

../../_images/spring-boot-psql.png

Figure 1 : Heroku Spring Boot Application Data flow

Figure 1 show the how Spring Boot App interacts with Heroku PostgreSQL the Http Client. App runs inside a Dyno and talkes to the PostgreSQL Add-On.

Note

Make sure you have Java 1.8, Maven 3.3.3 installed, PostgreSQL 9.4.4 or above and Heroku Toolbelt.

We will be using a Spring Boot Sample app available at heroku spring boot psql.

This app has two rest endpoints

  • @app.route('/') shows the home page of the App
  • @app.route('/users') which fetches list of users from Postgres table cuser
  • @app.route('/createuser') creates a new user in the database, called from Html Form using Http POST
  • @app.route('/createuserform') endpoint which servers the createuser Thymeleaf teamplate

Clone the Source Code

Clone the source code using git clone and change into the cloned project directory heroku-spring-boot-jdbc-psql

$ git clone https://github.com/rajdeepd/heroku-spring-boot-jdbc-psql
$ cd heroku-spring-boot-jdbc-psql

Code

This is a basic Spring-boot app which uses PostgrSQL JDBC driver to connect to the PostgreSQL database and return the list of Users. It uses Thymeleaf to render the HTML template

Structure of the App is Listed below

|-- pom.xml
|-- Procfile
|-- README.md
|-- src
|   `-- main
|       |-- java
|       |   `-- com
|       |       `-- example
|       |           |-- Application.java
|       |           `-- User.java
|       |-- resources
|       |   |-- application.properties
|       |   `-- templates
|       |       |-- createuser.html
|       |       |-- home.html
|       |       |-- result.html
|       |       `-- user.html
|       `-- webapp
|           `-- assets
|               |-- css
|               |   |-- bootstrap.css
|               |   `-- style.css
|               `-- js
|                   `-- bootstrap.js

About the Application

Application is based on Spring MVC Architecture.

The Spring Web model-view-controller (MVC) framework uses DispatcherServlet that dispatches requests to handlers. It has configurable handler mappings, view resolution, locale and theme resolution. The default handler is based on the @Controller and @RequestMapping annotations, offering a wide range of handling methods.

Controllers provide access to the application behavior defined through a service interface. Controllers interpret user input and transform it into a model that is represented to the user by the view (Thymeleaf in our case). Spring implements a controller in a abstract way, which enables developers to create a wide variety of controllers.

Since Spring 2.5 annotation-based programming model for MVC controllers that uses annotations @RequestMapping, @RequestParam, @ModelAttribute has been introduced.
Controllers implemented using this style do not have to extend specific base classes or implement specific interfaces

In our example we are using @Controller annotation to define the Controller and @RequestMapping to map rest endpoints to implementation methods.

Spring Boot developers always annotate the main class with @Configuration, @EnableAutoConfiguration and @ComponentScan. These annotations are frequently used together Spring Boot provides a convenient @SpringBootApplication alternative.

The @SpringBootApplication annotation is equivalent to using @Configuration, @EnableAutoConfiguration and @ComponentScan with their default attributes. This is the annotation we are using in our controller listed below.

Controller

Main Controller of the App is Application class which provides implementation of rest Endpoints.

@Controller
@SpringBootApplication
public class Application {

}

Following two @RequestMappings have been implemented in the Controller Application

@RequestMapping("/")
public String home(Model model) {
    ...
}

@RequestMapping("/createuserform")
public String createUserForm(Model model) {

}

@RequestMapping("/users")
public String users(Model model) {

}

@RequestMapping(value="/createuser", method=RequestMethod.POST)
public String createUser(@ModelAttribute User user, Model model) {

}

Before we look at the implementation of users() method, following code listing shows how the JDBC connection is obtained based on DATABASE_URL environment variable. This can be setup for a local PostgreSQL, or a PostgreSQL instance running in Heroku as shown in sections later in this tutorial.

In Heroku dynos running on Heroku this environment variable is setup autmatically

private static Connection getConnection() throws URISyntaxException, SQLException {
        URI dbUri = new URI(System.getenv("DATABASE_URL"));

        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
        + dbUri.getPort() + dbUri.getPath()
        + "?sslmode=require";

        return DriverManager.getConnection(dbUrl, username, password);
}

Get Users Implementation

In the implementation of users(Model model) method following steps are followed:

  1. Get the database connection using getConnection() method in the controller

  2. Create a Statement object stmt

    Statement stmt = connection.createStatement();
    
  3. Create the sql String to be executed which in our case is

    sql = "SELECT id, first, last, email, company, city FROM cuser";
    
  4. Execute the statement stmt

    ResultSet rs = stmt.executeQuery(sql);
    
  5. Populate List of com.example.User Object for each User and add to Model model. Code listing for User. class User

  6. return the user.html Thymeleaf template

    model.addAttribute("users", users);
    return "user";
    
    @RequestMapping("/users")
    public String users(Model model) {
        try {
            Connection connection = getConnection();
            Statement stmt = connection.createStatement();
            String sql;
            sql = "SELECT id, first, last, email, company, city FROM cuser";
            ResultSet rs = stmt.executeQuery(sql);
            StringBuffer sb = new StringBuffer();
            List users = new ArrayList<>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String first = rs.getString("first");
                String last = rs.getString("last");
                String email = rs.getString("email");
                String company = rs.getString("company");
                String city = rs.getString("city");
                users.add(new User(id,first, last, email, company, city));
            }
            model.addAttribute("users", users);
            return "user";
        } catch (Exception e) {
            return e.toString();
        }
    }
    

Complete code Listing for Application class

View implementation with Thymeleaf Templates

Thymeleaf is a template engine capable of processing and generating HTML, XML etc. It can work both in web and non-web environments. It is better suited for serving the view layer of web applications. It provides an optional integration with Spring MVC, so that it can be used as a complete substitute of JSP.

We are using Thymeleaf templates for rendering the Users sent by the controller.

Thymeleaf template user.html below uses variable set in Model to retrieve and display list of Users.

<!DOCTYPE HTML>
    <html xmlns:th="http://www.thymeleaf.org">
    <head>
       ...
    </head>
    <body>
    <div class="container">
    <h3> Users : </h3>
    <table>
    <th><td>Id</td><td>First</td><td>Last</td><td>Email</td><td>Company</td><td>City</td></th>
    <tr th:each="contact : ${users}">
        <td></td>
        <td th:text="${contact.id}" ></td>
        <td th:text="${contact.first}" ></td>
        <td th:text="${contact.last}" ></td>
        <td th:text="${contact.email}" ></td>
        <td th:text="${contact.company}" ></td>
        <td th:text="${contact.city}" ></td>
    </tr>
    </table>
    </div>
    </body>
    </html>

Create Users

To Create users we have implemented a rest endpoint /createuser which is called from createuserform.html and does a Http POST with the form parameters.

Create User Form

Form is served from the endpoint /createuserform and returns the following Thymeleaf teamplate

Thymelead Template for create user form

<html xmlns:th="http://www.thymeleaf.org">
<head>
    ..
</head>
<body>
<div class="container">
<h3> Users : </h3>
    <form action="#" th:action="@{/createuser}" th:object="${user}" method="post">>
        <table border="0">
            <tr>
                <td colspan="2" align="center"><h2>Spring
                MVC Form Demo - Registration</h2></td>
            </tr>
            <tr>
                <td>First Name:</td>
                <td><input id="first" type="text" th:field="*{first}"/></td>
            </tr>
            <tr>
                <td>Last Name:</td>
                <td><input id="last" type="text" th:field="*{last}"/></td>
            </tr>
            <tr>
                <td>Email:</td>
                <td><input id="email" type="text" th:field="*{email}"/></td>
            </tr>
            <tr>
                <td>Company:</td>
                <td><input id="company" type="text" th:field="*{company}"/></td>
            </tr>
            <tr>
                <td>City:</td>
                <td><input id="city" type="text" th:field="*{city}"/></td>
            </tr>
            <tr><td><input type="submit" name="Submit"/></td></tr>

        </table>
    </form>
</div>
</body>
</html>

In the implementation we use object of type User and extract the form parameters.

Construct a sql insert statement based on these parameters and execute the SQL.

Complete code listing for createUser method implementation.

Compile the App Locally

  1. Execure the following commands to compile and build the jar file

    $ mvn compile
    $ mvn package
    
  2. Configure Local Database : Make sure you have PostgreSQL installed locally.

    • Create a database userdb and connect to it

      $ psql -h myhost -d mydb -U myuser
      ubuntu=# create database userdb;
      ubuntu=# \connect userdb;
      
    • Create a table cuser

      create table cuser
      (
           id SERIAL PRIMARY KEY not null,
           first varchar(100),
           last varchar (100),
           email varchar (100),
           company varchar (150),
           city varchar(100)
      );
      
    • Export DATABASE_URL with the format

      DATABASE_URL=postgres://<username>:<password>@<host>:<port>/<database>

      Example for our local environment

      export DATABASE_URL=postgres://ubuntu:ubuntu@localhost:5432/userdb
      
  1. Run the app using the following command locally

    $ mvn spring-boot:run
    

    Your app should now be running on localhost:8080

Screenshots below show how the runtime looks like

Home

../../_images/spring-boot-psql-home.png

List Users

../../_images/users.png

Create User

../../_images/create-user.png

Procfile

There is already a Procfile which tells the Heroku what kind of Dyno is required and the source for the application.

web: java -Dserver.port=$PORT -jar target/heroku-spring-boot-psql-0.0.1-SNAPSHOT.jar

Deploying to Heroku

$ heroku create
$ git push heroku master

The App is deployed but table needs to be created in PostgreSQL.

$ heroku pg:psql

---> Connecting to DATABASE_URL

psql (9.4.4, server 9.4.6)
SSL connection (protocol: TLSv1.2,... compression: off)
Type "help" for help.

Execute the following command on psql prompt

$ rd-heroku-spring-boot-psql::DATABASE=> create table cuser
rd-heroku-spring-boot-psql::DATABASE-> (
rd-heroku-spring-boot-psql::DATABASE(>      id SERIAL PRIMARY KEY not null,
rd-heroku-spring-boot-psql::DATABASE(>      first varchar(100),
rd-heroku-spring-boot-psql::DATABASE(>      last varchar (100),
rd-heroku-spring-boot-psql::DATABASE(>      email varchar (100),
rd-heroku-spring-boot-psql::DATABASE(>      company varchar (150),
rd-heroku-spring-boot-psql::DATABASE(>      city varchar(100)
rd-heroku-spring-boot-psql::DATABASE(> );

Open the Remote App in Heroku

$ heroku open

The App opens up in the default route / with the following view

../../_images/spring-boot-psql-remote-home.png

Show Users

Browse to URL http://{your-app-name}.herokuapp.com/users to see the list of user names.

../../_images/spring-boot-psql-remote-users.png

Create a User

Browse to URL http://{your-app-name}.herokuapp.com/createuserform to see the list of user names.

../../_images/spring-boot-psql-remote-create-user.png

Summary

In this tutorial we learnt how to configure a Pre-existing Spring Boot Application to work with Heroku. We used PostgreSQL JDBC driver for talking to the PostgreSQL database deployed on Heroku.