Spring Data ft. PostgreSQL - Simple integration

From controller to database in 5 minutes.

Brief

This will be a short and straightforward post to show how simple it is to integrate with a database using Spring Data and a PostgreSQL docker container.

We want to create a simple web application that connects to a database and retrieves a list of users.

Implementation

Spring Boot application

As we already know from the some previous posts, Spring Initializr is our friend. We only need to add the Spring Data dependency and we're ready to go.

To this basic application we need to add a few other dependencies:

• postgresql driver - to manage the connection to the database
• lombok ( I know that some are not fond of it, but at the moment I like it ) - to autogenerate code for us like getters/setters/builders/constructors/etc.

...
dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'

	implementation 'org.postgresql:postgresql' // driver

	annotationProcessor 'org.projectlombok:lombok:1.18.16'
	implementation 'org.projectlombok:lombok:1.18.16'

	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

First, we'll create the entity which will mirror the table. Let's assume that we have a simple users table with id, email and password as columns. The related Java entity will look like this:

@Entity
@Table(name = "user", schema = "public")
@Getter
@Setter
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "email")
    private String email;

    @Column(name = "password")
    private String password;
}

Second, the repository which will help us do the querying of the table:

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    List<User> findAll();
}

Third, the service which will be responsible for mapping the entity to the DTO (data transfer object) returned to the client:

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    public List<UserDto> getAllUsers() {
        return userRepository.findAll().stream()
                .map(user -> UserDto.builder()
                        .id(user.getId())
                        .email(user.getEmail())
                        .build())
                .collect(Collectors.toList());
    }
}

Note that we don't return the password on the response of this method.

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class UserDto {
    private Integer id;
    private String email;
}

Fourth, the controller which will expose an endpoint to our service.

@RestController
@RequestMapping("/users")
@RequiredArgsConstructor
public class Controller {
    private final UserService userService;

    @GetMapping
    public List<UserDto> getAllUsers() {
        return userService.getAllUsers();
    }
}

Database

For this, I assume you have Docker installed, if not, refer to the official documentation. And then, we only need to pull the image and run the container:

• docker pull postgres
• docker run -d --name dev-postgres -e POSTGRES_PASSWORD=password  -v ${HOME}/postgres-data/:/var/lib/postgresql/data -p 5432:5432 postgres

To create the table structure I made use of the Postgres' pgAdmin graphical interface which made it a lot easier to see data and define the table.

• docker pull dpage/pgadmin4
• docker run -p 80:80 -e 'PGADMIN_DEFAULT_EMAIL=user@test.local' -e 'PGADMIN_DEFAULT_PASSWORD=password' --name dev-pgadmin -d dpage/pgadmin4

• Go to localhost:80 in your browser and login with the credentials added in the run command above:
  • user: user@test.local
  • password: password

• Click on Add new server, pick a name for it and then connect to the dev-postgres container.

• Go to Connection tab and add the username and the host address
  • run docker inspect dev-postgres -f "{{json .NetworkSettings.Networks }}"
  • from the result you will take the value under the IpAddress field
  • Example: {"bridge":{"IPAMConfig":null,"Links":null,"Aliases":null,"NetworkID":"1231","EndpointID":"1321","Gateway":"172.17.0.1","IPAddress":"172.17.0.2","IPPrefixLen":16,"IPv6Gateway":"","GlobalIPv6Address":"","GlobalIPv6PrefixLen":0,"MacAddress":"1234"}}

• In the SQL tab you can run DDL and DML queries. For our case we will need to run:

CREATE TABLE IF NOT EXISTS public."user"
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    email text COLLATE pg_catalog."default" NOT NULL,
    password text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT user_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."user"
    OWNER to postgres;
   

INSERT INTO public."user"(email, "password")
VALUES ('email', 'password');

Integration

The actual connection between the application and database is done by adding the connection details to the configuration file.

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=password
spring.datasource.driverClassName = org.postgresql.Driver
spring.jpa.database-platform=postgres
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Done.

I'll do a follow up post in the future, where I'll go in depth to showcase the abilities of Spring Data, but for now, we've reached our goal: we have access in our application to the data in a table.

As always, find the full code here: https://github.com/andreiszu/spring-jpa-postgres