The Dev World – Sergio Lema

In this article, I show the Spring JPA configuration for the database connection with Spring Boot, and the entities and Spring Repositories creation to read the data.

Content:

• The Spring JPA configuration;
• The database initialization;
• The entities and relationships (OneToMany and ManyToOne, ManyToMany with the same table);
• The usage of AuditingEntityListener;
• The Spring Repositories.

For more details, check this explanatory video.

All the code of the article is available in the following repository.

The Spring JPA configuration

In a previous article, I’ve configured my application with an authentication system. But I haven’t any database to store the user password, so I had a hardcoded password. Now, I will connect this application to a database where I will be able to store the user information and some other content. The database i will use is PostgreSQL due to the simplicity to install everywhere. I’ve already started PostgreSQL with Docker, and it’s available on localhost at port 5432. Now to connect my Spring Boot application with the database, I only need two additional dependencies and some configuration.

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

<dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
</dependency>

spring:
  datasource:
    platform: postgres
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/socialnetworkdb
    username: socialnetwork
    password: socialnetwork
    initialization-mode: always
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    show-sql: false
    hibernate:
      ddl-auto: validate

The initialization mode means that the database doesn’t exist. So Spring Boot needs to create it. And the JPA, the Java Persistence Api, where I said I want the entities to be validated against the database before starting. Otherwise, don’t start application.

The initialization of the database

With the initialization mode, I will need an additional file, the schema.sql. This file will be executed at every startup of the Spring application. And due to this, I must take care to not create some entities twice (with the reserved words if not exists).

create table if not exists social_network_user (
    id bigserial primary key ,
    first_name varchar(100),
    last_name varchar(100),
    login varchar(100),
    password varchar(100),
    created_date timestamp
);
create sequence if not exists social_network_user_sequence start 1000 increment 1;

create table if not exists message (
    id bigserial,
    content text,
    user_id bigint references social_network_user(id),
    created_date timestamp
);

create sequence if not exists message_sequence start 1000 increment 1;

create table if not exists friends (
    user_id bigint not null references social_network_user(id),
    friend_id bigint not null references social_network_user(id)
)

The entities and relationships

Let’s start implementing the entities.

@Entity
@Table(name = "social_network_user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceUserGenerator")
    @GenericGenerator(
            name = "sequenceUserGenerator",
            strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
                    @Parameter(name = "sequence_name", value = "social_network_user_sequence"),
                    @Parameter(name = "initial_value", value = "1000"),
                    @Parameter(name = "increment_size", value = "1")
            }
    )
    private Long id;

    @Column(name = "first_name", nullable = false)
    @Size(max = 100)
    private String firstName;

    @Column(name = "last_name", nullable = false)
    @Size(max = 100)
    private String lastName;

    @Column(nullable = false)
    @Size(max = 100)
    private String login;

    @Column(nullable = false)
    @Size(max = 100)
    private String password;

In the @Table annotation, I specify the name of the table in the database, because the name user is already a reserved table from PostgreSQL.

Then, I configure the sequence Id to use a generator which starts at 1000 (the first thousand are reserved for my purpose, if I want to inject some specific values).

The other entities will look similar. Let’s go now with the first relationship.

OneToMany and ManyToOne

The OneToMany relationship will refer to one User which is connected to multiple Messages. Let’s see how the list of Messages is configured into the User object.

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "user_id")
private List<Message> messages;

And now, how it looks in the Message object.

@ManyToOne(fetch = FetchType.LAZY)
private User user;

This was the easy one. Let’s go with a more complicated relationship.

ManyToMany

The ManyToMany relationship will be used to connect the Users between them. The particularity here, is that a User will be connected to multiple Users. There will be a connection of many-to-many within the same table User.

@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(
        name = "friends",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "friend_id", referencedColumnName = "id")
)
private List<User> friends;

For the ManyToMany relationships, I need an intermediary table. In this case, I have the table friends.

One last field for the entities: the created date.

The usage of AuditingEntityListener

I need to add the annotation @AuditingEntityListener to an entity if I want to use some other annotation as the @CreatedDate which will update some fields automatically.

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "social_network_user")
public class User {

   ...

   @CreatedDate
   @Column(name = "created_date", nullable = false)
   private LocalDateTime createdDate;

This way, every time I insert a new user into the database, the field createdDate will be filled automatically.

And now, let’s request all these data with the Spring repositories.

The Spring Repositories

The Spring repositories allow me to perform SQL commands without the need to write the SQL commands. Only with the method names. This has two advantages: if a column is renamed, at the compilation time, Spring JPA will check correct mapping from the repository to the entities; and I don’t need to know the SQL dialect to use, I can use Postgres, Oracle or other databases.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByLogin(String login);

But of course, I can always have some custom SQL queries to run with the @Query annotation.

@Query(value = "select u from User u where first_name like :term or last_name like :term or login like :term")
List<User> search(@Param("term") String term);

Conclusion

To connect my Spring Boot application to a PostgreSQL database, I needed:

• The Maven dependencies of Spring Data JPA and Postgres;
• Create a schema.sql file to initialize the database. This can be skipped if I have some alternative for the database management;
• Adapt the objects to be entities associated with the database tables;
• Adapt the ID sequence of the entities;
• Create the fields which will reflect the relationships between the tables;
• Create the Spring repositories to query to data.

References

• https://spring.io/guides/gs/accessing-data-jpa/