Adding a PostgreSQL database to a RESTful service on Spring Boot. Part 2

Adding a PostgreSQL database to a RESTful service on Spring Boot. Part 1 So, in the last part we learned how to install a PostgresSQL database on a computer, create a database in pgAdmin, and also create and delete tables in it manually and programmatically. In this part we will rewrite our program so that it learns to work with this database and tables. Why us? Because I myself am learning with you from this material. And then we will not only solve the task at hand, but also correct errors that arise on the go, with the help of advice from more experienced programmers. So to speak, we will learn to work in a team ;) First, let's create com.javarush.lectures.rest_examplea new package in a folder and call it repository. In this package we will create a new interface ClientRepository:

package com.javarush.lectures.rest_example.repository;

import com.javarush.lectures.rest_example.model.Client;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ClientRepository extends JpaRepository<Client, Integer>  {
}

This interface will “magically” interact with our databases and tables. Why magically? Because we won’t need to write its implementation, and the Spring framework will provide it to us. You just need to create such an interface, and you can already use this “magic”. The next step is to edit the class Clientlike this:

package com.javarush.lectures.rest_example.model;

import javax.persistence.*;

@Entity
@Table(name = "clients")
public class Client {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

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

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

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

    //… getters and setters
}

All we did in this class was just add some annotations. Let's go through them:

• @Entity - indicates that this bean (class) is an entity.
• @Table - indicates the name of the table that will be displayed in this entity.
• @Id - column id (primary key - the value that will be used to ensure the uniqueness of data in the current table. Note: Andrei )
• @Column - indicates the name of the column that is mapped to the entity property.
• @GeneratedValue - indicates that this property will be generated according to the specified strategy.

The names of the table fields do not have to match the names of the variables in the class. For example, if we have a variable firstName, then we will name the field in the table first_name. These annotations can be set both directly on fields and on their getters. But if you choose one of these methods, then try to maintain this style throughout your entire program. I used the first method just to shorten the listings. A more complete list of annotations for working with databases can be found here . Now let's go to the class ClientServiceImpland rewrite it as follows:

package com.javarush.lectures.rest_example.service;

import com.javarush.lectures.rest_example.model.Client;
import com.javarush.lectures.rest_example.repository.ClientRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ClientServiceImpl implements ClientService {

    @Autowired
    private ClientRepository clientRepository;

    @Override
    public void create(Client client) {
        clientRepository.save(client);
    }

    @Override
    public List<Client>  readAll() {
        return clientRepository.findAll();
    }

    @Override
    public Client read(int id) {
        return clientRepository.getOne(id);
    }

    @Override
    public boolean update(Client client, int id) {
        if (clientRepository.existsById(id)) {
            client.setId(id);
            clientRepository.save(client);
            return true;
        }

        return false;
    }

    @Override
    public boolean delete(int id) {
        if (clientRepository.existsById(id)) {
            clientRepository.deleteById(id);
            return true;
        }
        return false;
    }
}

As you can see from the listing, all we did was delete the lines we no longer needed:

// Хранorще клиентов
private static final Map<Integer, Client>  CLIENT_REPOSITORY_MAP = new HashMap<>();

// Переменная для генерации ID клиента
private static final AtomicInteger CLIENT_ID_HOLDER = new AtomicInteger();

We declared our interface instead ClientRepositoryand also placed the @Autowired annotation above it so that Spring would automatically add this dependency to our class. We also delegated all the work to this interface, or rather its implementation, which Spring will add. Let's move on to the final and most interesting stage - testing our application. Let's open the Postman program (see how to use it here ) and send a GET request to this address: http://localhost:8080/clients. We get this answer:

[
    {
        "id": 1,
        "name": "Vassily Petrov",
        "email": "vpetrov@jr.com",
        "phone": "+7 (191) 322-22-33)"
    },
    {
        "id": 2,
        "name": "Pjotr Vasechkin",
        "email": "pvasechkin@jr.com",
        "phone": "+7 (191) 223-33-22)"
    }
]

We send a POST request:

{
  "name" : "Amigo",
  "email" : "amigo@jr.com",
  "phone" : "+7 (191) 746-43-23"
}

And... we catch our first bug in the program:

{
    "timestamp": "2020-03-06T13:21:12.180+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
    "path": "/clients"
}

We look at the logs and find the following error:

org.postgresql.util.PSQLException: ОШИБКА: повторяющееся meaning ключа нарушает ограничение уникальности "clients_pkey"
  Detail: Ключ "(id)=(1)" уже существует.

We send the same POST request again, the result is the same, but with this difference: Ключ "(id)=(2)" уже существует. We send the same request for the third time, and we get Status: 201 Created. We send the GET request again and receive a response:

[
    {
        "id": 1,
        "name": "Vassily Petrov",
        "email": "vpetrov@jr.com",
        "phone": "+7 (191) 322-22-33)"
    },
    {
        "id": 2,
        "name": "Pjotr Vasechkin",
        "email": "pvasechkin@jr.com",
        "phone": "+7 (191) 223-33-22)"
    },
    {
        "id": 3,
        "name": "Amigo",
        "email": "amigo@jr.com",
        "phone": "+7 (191) 746-43-23"
    }
]

This suggests that our program is ignoring the fact that this table has already been pre-populated and is assigning an id again starting from one. Well, a bug is a working moment, don’t despair, this happens often. Therefore, I will turn to more experienced colleagues for help: “Dear colleagues, please advise in the comments how to fix this so that the program works normally.” Help didn’t take long to arrive, and Stas Pasinkov told me in the comments which direction I needed to look. Special thanks to him for this! But the thing was that in the class ClientI incorrectly specified the strategy for the annotation @GeneratedValue(strategy = GenerationType.IDENTITY)for the field id. This strategy is suitable for MySQL. If we work with Oracle or PostrgeSQL, then we need to set a different strategy. You can read more about strategies for primary keys here . I chose the GenerationType.SEQUENCE strategy. To implement it, we will need to slightly rewrite the initDB.sql file, and, of course, the annotations of the id field of the Client class. Rewrite initDB.sql:

CREATE TABLE IF NOT EXISTS clients
(
    id    INTEGER PRIMARY KEY ,
    name  VARCHAR(200) NOT NULL ,
    email VARCHAR(254) NOT NULL ,
    phone VARCHAR(50)  NOT NULL
);
CREATE SEQUENCE clients_id_seq START WITH 3 INCREMENT BY 1;

What has changed: the type of the id column of our table has changed, but more on that later. We added a line below in which we create a new sequence clients_id_seq, indicate that it should start with a three (because the last id in the populateDB.sql file is 2), and indicate that the increment should occur by one. Let's go back to the id column type. Here we specified INTEGER, because if we leave SERIAL, the sequence will be created automatically, with the same name clients_id_seq, but will start from one (which led to the program bug). However, now if you want to delete a table, you will need to additionally delete this sequence either manually through the pgAdmin interface or through a .sql file using the following commands:

DROP TABLE IF EXISTS clients;
DROP SEQUENCE IF EXISTS clients_id_seq

But if you do not use a file like populateDB.sql to initially populate the table, then you can use the SERIAL or BIGSERIAL types for the primary key, and you do not have to create the sequence manually, and therefore do not have to delete it separately. You can read more about the sequences on the website of. PostgreSQL Documentation . Let's move on to the idclass field annotations Clientand format them as follows:

@Id
@Column(name = "id")
@SequenceGenerator(name = "clientsIdSeq", sequenceName = "clients_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "clientsIdSeq")
private Integer id;

What we did: we installed a new annotation @SequenceGeneratorto create a sequence generator, assigned it a name clientsIdSeq, indicated that this is a generator for a sequence clients_id_seq, and added an attribute. allocationSize = 1 This is an optional attribute, but if we don’t do this, we will get the following error when we run the program:

org.hibernate.MappingException: The increment size of the [clients_id_seq] sequence is set to [50] in the entity mapping while the associated database sequence increment size is [1]

Here is what user Andrei writes about this in the comments: allocationSize is primarily intended to reduce hibernate’s trip to the database for a “new id”. If the value == 1, hibernate for each new entity, before saving it in the database, “runs” to the database for the id. If the value is > 1 (for example, 5), hibernate will contact the database for a “new” id less often (for example, 5 times), and when contacting, hibernate will ask the database to reserve this number (in our case, 5) values. The error that you described suggests that hibernate would like to receive 50 default ids, but in the database you indicated that you are ready to issue id for this entity only according to the 1st one . Another bug was caught by user Nikolya Kudryashov : If you run a request from the original article http://localhost:8080/clients/1, the error will be returned:

{
    "timestamp": "2020-04-02T19:20:16.073+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "Type definition error: [simple type, class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.javarush.lectures.rest_example.model.Client$HibernateProxy$ZA2m7agZ[\"hibernateLazyInitializer\"])",
    "path": "/clients/1"
}

This error is related to Hibernate's lazy initialization, and to get rid of it, we need to add an additional annotation to the Client class:

@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})

In this way:

@Entity
@Table(name = "clients")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Client {.....}

Now let’s run our program (after removing the clients table from the database if it remains there from last time), and comment out 3 lines from the application.properties file:

#spring.datasource.initialization-mode=ALWAYS
#spring.datasource.schema=classpath*:database/initDB.sql
#spring.datasource.data=classpath*:database/populateDB.sql

Last time we commented only the last line, but... Since we have already created and filled out the table, this seemed more logical to me at the moment. Let's move on to testing, perform GET, POST, PUT and DELETE requests via Postman, and we will see that the bugs have disappeared and everything is working fine. That's it, job done. Now we can summarize briefly and consider what we have learned:

• Install PostgreSQL on your computer
• Create databases in pgAdmin
• Create and delete tables manually and programmatically
• Populate tables via .sql files
• We learned a little about the “magic” JpaRepository interface of the Spring framework
• We learned about some bugs that may arise when creating such a program
• We realized that we shouldn’t be embarrassed to seek advice from colleagues
• We have confirmed that the JavaRush community is a force that will always come to the rescue ;)

We can finish here for now. Thanks to everyone who took the time to read this material. I will be glad to see your comments, observations, additions and constructive criticism. Perhaps you will offer more elegant solutions to this problem, which I promise to add to this article via the UPD “keyword”, with a mention of you as the author, of course. Well, in general, write if you liked this article and this style of presenting the material, and in general, whether I should continue writing articles on JR. Here are the additions: UPD1: user Justinian strongly recommended that I rename the package com.javarush.lectures.rest_exampleto com.javarush.lectures.rest.example, and the name of the project, so as not to violate the naming conventions in Java. UPD2 user Alexander Pyanov suggested that to initialize a field ClientRepositoryin a class ClientServiceImplit is better to use a constructor than an annotation @Autowired. This is explained by the fact that in rare cases you can get NullPointerException, and in general, this is best practice, and I agree with it. Logically, if a field is required for the initial functionality of an object, then it is better to initialize it in the constructor, because a class without a constructor will not be assembled into an object, therefore, this field will be initialized at the stage of object creation. I’ll add a code fragment with corrections (what needs to be replaced with what):

@Autowired
private ClientRepository clientRepository;

private final ClientRepository clientRepository;

public ClientServiceImpl(ClientRepository clientRepository) {
   this.clientRepository = clientRepository;
}

Link to the first part: Adding a PostgreSQL database to a RESTful service on Spring Boot. Part 1 PS If any of you want to continue developing this educational application, then I will be glad to add a link to your guides in this article. Perhaps someday this program will grow into something similar to a real business application that you can add work on to your portfolio. PPS Regarding this modest article, I decided to dedicate this test of the pen to our dear girls, women and ladies. Who knows, perhaps now there would be no Java, no JavaRush, no programming in nature, if not for this woman . Congratulations on your holiday, our dear smart people! Happy March 8th! Be happy and beautiful!