The Dev World – Sergio Lema

This article is part of a series where I will create a Fullstack application. When creating a Fullstack application I must take into account the behavior between a Frontend application and a Backend application. The data transmitted, where goes the logic, the security…

In this article, I will connect my Spring Boot Application to a PostgreSQL database.

I start from the project I’ve created in a previous article.

The content:

• Adding the dependencies
• Configuring the application
• Creating the entities
• Creating the Mapstruct mappers
• Creating the repositories

Adding the Dependencies

The application needs two dependencies:

• The JPA dependency to have available the annotations to connect my entities to tables and the repositories to make queries;
• And the PostgreSQL driver to be able to communicate with a PostgreSQL database.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>

Now, I will start a PostgreSQL database within Docker. It’s easier than installing a complete PostgreSQL server in my laptop. And I can delete it and regenerate it when I want.

To do so, I use the following command:

docker run -d \
    -e POSTGRES_HOST_AUTH_METHOD=trust \
    -e POSTGRES_USER=backend \
    -e POSTGRES_PASSWORD=backend \
    -e POSTGRES_DB=backenddb \
    -p 5432:5432 \
    postgres:13

The environment variable POSTGRES_HOST_AUTH_METHOD is only used for testing purpose. It allows me to connect to my database without any admin user.

The environment variables POSTGRES_USER and POSTGRES_PASSWORD are the username and password to connect to my database. Those are the one I must use in the configuration file of my Spring Boot application.

I indicate the name of the database with the environment variable POSTGRES_DB and the port where it will be available with 5432:5432. If I want to have it available in another port, I must change the first value (5433:5432 or 9000:5432).

Configuring the Spring Boot Application

I first create an application.yml file into my resources folder.

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/backenddb
    username: backend
    password: backend
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: create

The property driver-class-name indicates the driver of the database. Which can be loaded only because I’ve added the PostgreSQL Maven dependency in the previous step.

The property url is where is located my database. All the URLs of a database connection in a Java application start with jdbc. Then comes name of the database type, postgresql. And finally, the address of the database: localhost as it’s running in my laptop; 5432 is the port used; and backenddb is the name of the database I’ve created.

The values in username and password are those used in the Docker command.

Spring Boot automatically detects the dialect used by your database. This is done thanks to the driver loaded. But you can specify the dialect to use with the property database-platform to be explicit and bypass the check on startup.

Finally, the hibernate.ddl-auto property is the action Hibernate does at first when connecting the database. The available values are:

• none: I use this value when I manage the schema migration with an external tool like Liquibase or Flyway;
• validate: this make a validation of the database schema against the application entities. If the tables or columns don’t correspond, the application fails to start;
• update: this compares the database schema against the application entities and updates the schema if there is some differences. I recommend not using this value for production application, but Liquibase or Flyway instead;
• create: this creates the schema at the startup if it doesn’t exist. But the updates are not taken into account;
• create-drop: this drops your schema at the startup and creates a new one. This is useful for unit tests.

Creating the Entities

The entities are the link between Java objects and tables in the database. One entity corresponds to one table in the database. And one field in the entity corresponds to one column in the database.

I will create a simple entity, Vehicle, with some columns.

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class Vehicle {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column
    private String brand;

    @Column
    private String model;

    @Column(nullable = true)
    private String color;

    @Column(name = "construction_year")
    private int year;
}

The annotation @Entity is the one which connects the object Vehicle with a table in the database with the name vehicle. By default, the name used for the table is the name of the object in lowercase. If I want a different name, I can use the following @Entity(name = “my_table_name”).

The rest of the class annotations are Lombok annotations. With those annotations, I don’t need to create the constructors, getters and setters, Lombok will do it for me.

The first field is the id which is mapped to a column named id. Adding the annotation @Id, it identifies the column as the primary key. With the annotation @GeneratedValue(strategy = GenerationType.SEQUENCE), I link the column id with a sequence in the database, which gives me automatically a new value for each new insert.

Then come four more fields and columns: brand, model, color and year. All are annotated with @Column. I can add more properties, as nullable if I allow null values; or name if I want a different column name.

Creating the Mapstruct Mappers

Now that I have my entities, I must create DTOs (Data Transfer Objects) to be used in the endpoints. Because it’s a bad practice to return directly entities in the controllers. I may expose some confidential field in the endpoints.

With the DTOs, I can expose only selected fields from my entities. Let’s create a simple DTO in the package dtos.

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class VehicleDto {

    private Long id;
    private String brand;
    private String model;
    private String color;
    private int year;

}

The name of the object is the same as the entity but with the suffix Dto.

In this case, I’ve created the same fields as in the entity. But in some other cases, like in users entities, I should avoid using the password field in the Dto, to keep it secret.

Now that I have my Dto, I will create a Mapstruct mapper to convert a Dto into an Entity and viceversa. I will create the mappers in a package named mappers.

@Mapper(componentModel = "spring")
public interface VehicleMapper {

    Vehicle toVehicle(VehicleDto vehicleDto);

    VehicleDto toVehicleDto(Vehicle vehicle);

    List<VehicleDto> toVehicleDtos(List<Vehicle> vehicles);
}

The annotation @Mapper(componentModel = “spring”) allows me to inject the mapper in the services or controllers as any other Spring component.

The Mapstruct classes are interfaces, the implementation is generated by Mapstruct itself.

The first method, toVehicle(), reads a VehicleDto and returns a Vehicle. the mapping, field by field is done by Mapstruct.

I can even use list mapping as done with the method toVehicleDtos().

Creating the Repositories

The repositories are need to communicate with the database. With the repositories, I can query, update, delete or insert entities in my tables.

I will create a simple repository in a new package repositories:

public interface VehicleRepository extends JpaRepository<Vehicle, Long> {

    Optional<Vehicle> findByBrand(String brand);

    @Query(value = "select v from Vehicle u where construction_year < 1990")
    List<Vehicle> findOldVehicles();
}

A repository is an interface. The implementation is created by JPA.

Extending the JpaRepository already creates me a lot of useful methods as: save(), saveAll(), delete(), deleteAll(), findById(), findAll()… The types used in the JpaRepository definition indicates the type of the entity (Vehicle) and the type of the primary key (Long).

I can also add more methods which will be automatically linked to a query. In the example, I’ve used a query filtered by brand. JPA will translate this to a SQL query with no more questions. You can find all about the queries in this link.

And if I want a custom query, I can use the annotation @Query.

Finally, to use the repository, I can inject it at any service or controller, as it’s loaded by Spring automatically.

Conclusion

This application is now ready to communicate with a PostgreSQL database. It can be easily adapted, in the application.yml file, to any other RDBMS like Oracle or MySQL.

You can find the code of the project in this Github repository.

If you want to learn more about good quality code, make sure to follow me on Youtube.