Spring Boot app connecting to PostgreSQL using JPA

1. Context

We are going to create a spring boot app that connects to the PostgreSQL database using JPA. Let us build an imaginary TV program guide web app that lists and allows us to search TV channels, programs, etc. There would be two kinds of users in the app. Admin users would handle the management of channels and programs. End-users would view the program guide and perform various searches and filtration on channels and programs.

We are going to use:

• The popular spring boot framework for the core application
• JPA (Java Persistence API) as the database access layer
• PostgreSQL for data persistence
• Angular based UI

2. Maven Dependencies

Here is the pom.xml with spring-boot dependencies to spring-boot-starter-web, spring-boot-starter-tomcat and spring-boot-starter-data-jpa artifacts. Also, for PostgreSQL dependency to org.postgresql:postgresql.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.openapex.tvguide</groupId>
    <artifactId>server</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
</project>

3. Spring Boot Application

The main Spring application class is the starting point of the spring boot application. Note the annotations @EnableJpaRepositories and @EntityScan, are required besides @ComponentScan to include repositories and ORMs in the spring application context.

package org.openapex.tvguide;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@ComponentScan("org.openapex.tvguide")
@EnableJpaRepositories("org.openapex.tvguide")
@EntityScan("org.openapex.tvguide")
public class TvGuideApplication {
    public static void main(String[] args) {
        SpringApplication.run(TvGuideApplication.class, args);
    }
}

4. REST Controller and Service

This is the controller class, the handler of the REST operations, exposing list, search and create APIs:

package org.openapex.tvguide.controller;

import org.openapex.tvguide.model.Channel;
import org.openapex.tvguide.service.TvGuideService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;

@RestController(value = "tvGuideController")
@RequestMapping(path = "/guide")
public class TvGuideController {
    @Autowired
    private TvGuideService tvGuideService;

    @RequestMapping(method = RequestMethod.GET, path = "channels")
    public List<Channel> getChannels() {
        return tvGuideService.getChannels();
    }

    @RequestMapping(method = RequestMethod.GET, path = "search-channels")
    public List<Channel> searchChannels(@RequestParam(name = "channelGroup", required = false) String group) {
        return tvGuideService.searchChannels(group);
    }
    @RequestMapping(method = RequestMethod.POST, path="channel")
    public void createChannel(@RequestBody Channel channel){
        tvGuideService.createChannel(channel);
    }
}

Here are the REST endpoints this app is exposing:

• GET /guide/channels – lists all the channels
• GET /guide/search-channel?channelGroup=group – filters channels by channel group
• POST /guide/channel with Channel object in the request body – creates a channel.

The Service class that receives calls from the Controller and interacts with the repository to retrieve data from the database. This is the place where all business logic of the program guide app would be added.

package org.openapex.tvguide.service;

import org.openapex.tvguide.dbaccess.repository.ChannelRepository;
import org.openapex.tvguide.model.Channel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class TvGuideService {
    @Autowired
    private ChannelRepository channelRepository;

    public List<Channel> getChannels() {
        return channelRepository.findAll();
    }

    public List<Channel> searchChannels(String group) {
        return channelRepository.findByChannelGroup(group);
    }

    public void createChannel(Channel channel) {
        channelRepository.save(channel);
    }
}

5. JPA Repository and ORM

The Repository interface that extends from JpaRepository. We have a method to filter channels based on the group.

package org.openapex.tvguide.dbaccess.repository;

import org.openapex.tvguide.model.Channel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ChannelRepository extends JpaRepository<Channel, Integer>{
    List<Channel> findByChannelGroup(String group);
}

The ORM class Channel represents the channel table in the PostgreSQL database. We are using this ORM class also as a model. Alternatively, we can define a separate model class and then we would require to add two-way conversions between ORM and model objects. It uses Lombok annotations @Getter, @Setter, @AllArgsConstructor, @NoArgsConstructor to make the model class neat. The @Id annotation denotes that this field is the primary key of the database table.

package org.openapex.tvguide.model;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import javax.persistence.*;

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "channel")
public class Channel {
    @Id
    @SequenceGenerator(name="channel_number_seq", initialValue = 101)
    @GeneratedValue (strategy = GenerationType.SEQUENCE, generator = "channel_number_seq")
    private int channelNumber;
    private String name;
    private double price;
    private String channelGroup;
}

6. Database Configuration

The resource file application.properties contains database connection details such as data source URL, username, password, etc.:

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.username=tvguide
spring.datasource.password=password
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres

Here are the database table and the sequence creation SQL script. We need the sequence for the primary key generation. See @GeneratedValue and @SequenceGenerator in Channel ORM class.

create table channel(
  channel_number int primary key,
  name text,
  price double precision,
  channel_group text
);

create sequence channel_number_seq cycle
  increment 1
  start 101
  minvalue 1
  maxvalue 9999999999;

insert into channel (channel_number, name, price, channel_group)
values (100, 'Star Movies', 10.00, 'Star');

7. Conclusion

We can use this sample application as a template for developing any sort of 3-tier web application based on Spring Boot. We can plug in different relational databases by just changing the database configuration.

The full code of this program guide app is available at GitHub: tvguide