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

Let me start by saying that I really enjoyed the series of articles by Elleanor Kerry called “REST Overview.” Here are links to these articles:

• Overview of REST. Part 1: What is REST
• Overview of REST. Part 2: Communication between client and server
• Overview of REST. Part 3: Creating a RESTful Service in Spring Boot

In general, I decided to take the liberty and add a little to them. Namely, tell how to create a database and connect it to this application. Where to begin? Probably from the database itself. I decided to use PostgreSQL because I liked its user-friendly admin interface. First, we need to download the PostgreSQL installer, for example from here: PostgreSQL Database Download I will not describe the complete installation process, because it may differ depending on your OS. However, I note that in the process you will need to come up with an administrator password for the default postgres role (account) and enter it 2 times. For simplicity, I set the password to 123 . Of course, in real projects it is necessary to come up with something more complicated. Next, the installation program prompts you to select a port; I left it at its default value. Locale was also left as default. Installed, then open the administrator interface pgAdmin . A browser will open with a pop-up window where we will be asked to enter the previously created password. Now we need to set the interface language. To do this, click Configure pgAdmin --> Miscellaneous --> User Language , select the desired language --> Save , and reload the browser page . In the upper left corner of the page, click Server , the “ Connect to server ” window appears . Enter our password again and check the box Save Password so as not to enter it every time. We could continue to use the administrator role for all databases, but it would be better to create a new role, because we may have many databases and many programs that use them. To do this, in the upper left corner, click on PostgreSQL 12 --> RMB on Login/Group Roles --> Create --> Login/Group Role In the pop-up window, on the “ General ” tab, enter the name of the role. I named the role root . On the “ Definition ” tab we create a new password, I left 123 just so as not to get confused. Go to the “ Rights ” tab and mark all the necessary items. I have set all items to " YES ". Click “ Save ” We proceed to creating a database. Right-click on “Databases” --> Create --> Database On the “ General ” tab, create the name of the database. Let it be, for example, customers . We assign the owner to root , whom we created in the previous step. On the "Definition » check that we have UTF8 encoding set . Click “ Save ”. That's it, our database has been created (empty for now). At this point we could end with pgAdmin, because... We will create tables programmatically, but, just in case, I will show you how to create a table manually. Expand the customers --> Schemas --> public tree . Right-click Tables --> Create --> Table . A pop-up window will open. On the “ General ” tab, we assign a name to our table, for example test_table , and assign root as the owner . Go to the “ Columns ” tab, click on “ + ” to create a new column. Enter the name “ id ” and the data type bigserial , which is equivalent to the Long type in Java, but with auto-increment (when adding a new record, id will automatically increase by one). We mark non-NULL as “ Yes ”, the primary key is also “ Yes ”. We create the columns “ name ”, “ email ” and “ phone ” in the same way . We select the data type character varying , this corresponds to the String type , but allows you to set the maximum length. We set the maximum name length to 200 characters to fill in the full name. in one column. We set the maximum email length to 254 characters. Why email has such a maximum length can be found here . For a phone number, select 20 characters, this should be enough. A little about telephone numbers: Misconceptions of programmers about telephone numbers (Habr) We mark non-NULL in all columns as “ Yes ” if we want this data to be mandatory. Click “ Save ”. That's it, the table has been created. To delete our test table, right-click on it (in the tree) and “ delete ”, because We no longer need it, because we will create tables from our program. Close pgAdmin and go to our program. Let's open IDEA with our project. Click Database in the right column of the interface, click on “ + ” to add our database. Next Data Source --> PostgreSQL . In the pop-up window, enter in the User field the root role that we created earlier, and our password123 in the password field . In the Database field we write the name of our customers database . We press the button Test Connection , and if we see a green checkmark under it, then everything is in order, and we press the button OK . That's it, we've connected to the database, now let's go to the pom.xml file and add dependencies. To work with ORM database :

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

To create a REST controller:

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

For Tomcat server:

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

For PostgreSQL:

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

Now that we've sorted out pom.xml, let's go to the resources folder and fill out the application.properties file as follows:

spring.datasource.url=jdbc:postgresql://localhost:5432/customers
spring.datasource.username=root
spring.datasource.password=123

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database=postgresql
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL10Dialect

Here we entered the URL of our database, provided the login and password for it, registered the driver for PostgreSQL, indicated that we would use the PostgreSQL data type, and specified the dialect for Hibernate. Next, let's create a new directory called database in the same resources folder . In this directory we will create 2 files: initDB.sql and populateDB.sql . The first will be responsible for creating tables, the second will be responsible for filling them out initially. Let's open initDB.sql and see a green bar at the top that says SQL dialect is not configured . This means that we have not chosen a SQL dialect for our project (and there are several of them). Click on the right side of the same strip on the inscription Change dialect to… . In the pop-up window, click Project SQL Dialect , and since our database is PostgreSQL , we select the dialect of the same name. Click OK Let's move on to filling out our .sql files . Let's first fill in the initDB.sql file :

CREATE TABLE IF NOT EXISTS clients
(
    id    BIGSERIAL PRIMARY KEY ,
    name  VARCHAR(200) NOT NULL ,
    email VARCHAR(254) NOT NULL ,
    phone VARCHAR(20)  NOT NULL
);

If, after filling out the file, some words other than clients are written in white font, then right-click inside the text and select Change Dialect --> PostgreSQL again . As you probably already understood, this is the same data that we filled in when creating the test table manually. Here they are formatted in the PostgreSQL dialect of the SQL language. Now let's fill the populateDB.sql file :

INSERT INTO clients VALUES
(1, 'Vassily Petrov', 'vpetrov@jr.com', '+7 (191) 322-22-33)'),
(2, 'Pjotr Vasechkin', 'pvasechkin@jr.com', '+7 (191) 223-33-22)');

If the name of your clients table is written in red letters, then it’s okay. The point is that we haven't created this table yet, and IDEA doesn't recognize it yet. In order to create and populate the table, we need to go back to the application.properties file and add the following three lines there:

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

In these lines we say that we want to initialize the database programmatically and indicate which files need to be used for this. Next, go to the main method of our application and launch it . After that, go to pgAdmin --> Servers --> PostgreSQL 12 --> Databases --> customers --> Schemas --> public , right-click on " Tables ", " Update ". If everything went well, then we see the clients table we created . After that, go back to the application.properties file and comment out the line.

spring.datasource.data=classpath*:database/populateDB.sql

as below:

spring.datasource.url=jdbc:postgresql://localhost:5432/customers
spring.datasource.username=root
spring.datasource.password=123

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database=postgresql
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL10Dialect

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

If we do not do this, then the next time we run the program we will receive the following error: org.postgresql.util.PSQLException: ERROR: A duplicate key value violates the unique constraint "clients_pkey" . This happens because we have already filled in the fields with id 1 and 2 in the table (even during the first launch). In the table we created, the id field is specified as bigserial , which corresponds to the Long type in Java. However, in our program the type for this field is Integer . I decided to show how to use Long (BIGSERIAL) because it has a larger range than Integer. The fact is that in tables, a field designated as Primary Key can be used not only to store user ids , but also to store indexes of a wide variety of data, and the number of such records can exceed the maximum Integer value. For example, if our program takes some measurements every second and writes the data to a table. In order to rewrite our classes to use the Long data type, we need to change the type from Integer to Long in all classes and methods that use the id field . We will not do this, because the program was originally written by the author for the id Integer type, which means this makes some sense. To continue, let's once again remove the clients table we created from our database, but now we'll try to do it programmatically rather than manually. To do this, comment out our code in the initDB.sql file and add one line:

-- CREATE TABLE IF NOT EXISTS clients
-- (
--     id    BIGSERIAL PRIMARY KEY ,
--     name  VARCHAR(200) NOT NULL ,
--     email VARCHAR(254) NOT NULL ,
--     phone VARCHAR(20)  NOT NULL
-- );
DROP TABLE IF EXISTS clients

Let's launch the program, go to pgAdmin , right-click on “ Tables ” (in our customers database ) - -> “ Update ”, and we will see that our table has disappeared. NB! Be careful when using this command, otherwise you risk losing all the data you had in your table! Let's go back to the initDB.sql file and rewrite it as follows:

CREATE TABLE IF NOT EXISTS clients
(
    id    SERIAL PRIMARY KEY ,
    name  VARCHAR(200) NOT NULL ,
    email VARCHAR(254) NOT NULL ,
    phone VARCHAR(50)  NOT NULL
);

Here we have changed the id type to SERIAL , which matches the Integer type we are using for the id field in our program. In addition, the maximum length of the phone field has been increased so that we can freely use spaces and special characters (parentheses, dashes, etc.) in its writing. The maximum number of digits in a phone number is currently 18 digits (if my memory serves me correctly). I set the size to 50 characters to be sure. Let's go to the application.properties file and uncomment the line:

spring.datasource.data=classpath*:database/populateDB.sql

Let's run our program, go to pgAdmin, check that our table has been created, and comment out this line back.

#spring.datasource.data=classpath*:database/populateDB.sql

This is where I’ll probably finish the first part of the article. I hope you like it, write comments (even if you didn’t like it). In part two, we'll rewrite our classes so they can work with real databases. Continuation: Adding a PostgreSQL database to a RESTful service on Spring Boot. Part 2 UPD Thanks to the moderators for correcting my pictures and text formatting!