Use PostgreSQL database in Spring Boot

In today's relational database, there are two open source products that you must know. One of them is MySQL. I believe those who follow me must be familiar with it, because all the previous Spring Boot examples of relational databases are introduced to MySQL. Today we will introduce another open source relational database: PostgreSQL and how to use it in Spring Boot.

Introduction to PostgreSQL

When learning PostgreSQL, we always compare it with MySQL: MySQL claims to be the most popular open source database, while PostgreSQL claims to be the most advanced open source database, so how advanced is it? Let's get to know it together!

PostgreSQL is a free software object-relational database management system (ORDBMS) with very complete features. It is an object-relational database management system based on POSTGRES, version 4.2 developed by the Department of Computer Science at the University of California. Many of the leading concepts of POSTGRES only appeared in the commercial website database at a relatively late time. PostgreSQL supports most of the SQL standards and provides many other modern features, such as complex queries, foreign keys, triggers, views, transaction integrity, and multi-version concurrency control. Similarly, PostgreSQL can also be extended in many ways, such as by adding new data types, functions, operators, aggregate functions, indexing methods, procedural languages, etc. In addition, because of the flexible license, anyone can use, modify, and distribute PostgreSQL for any purpose for free.

Advantages of PostgreSQL

Since it is the same as MySQL and a relational database, we naturally need to understand when to use MySQL and when to use PostgreSQL. So the following briefly introduces what advantages PostgreSQL has compared to MySQL. If you have these needs, then choosing PostgreSQL is better than MySQL, otherwise, it is better to choose MySQL:

• Support to store some special data types, such as: array, json, jsonb
• It has better support for the storage and processing of geographic information, so it can become a spatial database to better manage data measurement and geometric topology analysis
• REST API can be built quickly, and RESTful API services can be easily provided for any PostgreSQL database through PostgREST
• Support tree structure, which can handle data storage with such characteristics more conveniently
• External data source support, you can query MySQL, Oracle, CSV, Hadoop, etc. as tables in your own database
• Support for indexes is stronger. PostgreSQL supports B-tree, Hash, R-tree and Gist indexes. MySQL depends on the storage engine. MyISAM: BTREE, InnoDB: BTREE.
• Transaction isolation is better. MySQL's transaction isolation level repeatable read does not prevent common concurrent updates. It requires locking, but pessimistic locks will affect performance, and it is complicated to manually implement optimistic locks. The PostgreSQL column has a hidden version field for optimistic locking, and the default repeatable read level can ensure the correctness of concurrent updates and has the performance of optimistic locking.
• Time accuracy is higher, can be accurate to less than a second
• Character support is better, utf8mb4 is needed in MySQL to display emoji, PostgreSQL does not have this pit
• The storage method supports a larger amount of data. The PostgreSQL main table uses a heap table to store, and MySQL uses an index-organized table, which can support a larger amount of data than MySQL.
• Sequence support is better. MySQL does not support multiple tables to take IDs from the same sequence, while PostgreSQL can
• Adding columns is simpler. Adding columns to a MySQL table is basically rebuilding tables and indexes, which will take a long time. Adding a column to a PostgreSQL table only adds a table definition to the data dictionary, but does not rebuild the table.

Here are only some of the advantages that the developer’s perspective focuses on, and there are some other advantages. Readers can check out the article for a more detailed interpretation.

Download and install

Readers can obtain the installation programs of each version of PostgreSQL through the links below. The installation process is not described in detail here. According to the instructions of the installation program, I believe everyone can complete the installation (all the way next, just set the access password and port).

Download : 16160f7af306b3 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Note : Because 14 is the version just released today, in order to avoid compatibility problems with Spring Boot, we still choose the previous 13.4 version to complete the following experiment.

After the installation is complete, open pgAdmin. Because it comes with interface management tools, if you have used any relational database such as mysql, you don't need to learn much, you can use it.

How to use in Spring Boot

After installing PostgreSQL, let's try to use PostgreSQL database in Spring Boot.

first step : Create a basic Spring Boot project (if you don’t it yet, you can refer to this article: 16160f7af30789 Quick Start )

second step : Introduce two important dependencies required to access PostgreSQL pom.xml

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

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

Here postgresql is necessary, spring-boot-starter-data-jpa can also be replaced with other data access encapsulation frameworks, such as: MyBatis, etc., you can replace the dependencies according to your usage habits. Because it is a higher-level package, the basic use is similar to the previous MySQL. So you can also refer to the article before 16160f7af30923 for configuration, but the data source part needs to be configured according to the following part.

third step : Configure the data source for the PostgreSQL database and the necessary configuration of JPA in the configuration file.

spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=create

Fourth step : Create user information entity, map user_info table (final completion can be viewed in pgAdmin)

@Entity
@Data
@NoArgsConstructor
public class UserInfo {

    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private Integer age;

    public UserInfo(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

Step 5 : Add, delete, modify and check the creation of user information entities

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {

    UserInfo findByName(String name);

    UserInfo findByNameAndAge(String name, Integer age);

    @Query("from UserInfo u where u.name=:name")
    UserInfo findUser(@Param("name") String name);

}

sixth step : Create a unit test, try to add, delete, modify and check operations.

@Slf4j
@SpringBootTest
public class ApplicationTests {

    @Autowired
    private UserInfoRepository userRepository;

    @Test
    public void test() throws Exception {
        // 创建10条记录
        userRepository.save(new UserInfo("AAA", 10));
        userRepository.save(new UserInfo("BBB", 20));
        userRepository.save(new UserInfo("CCC", 30));
        userRepository.save(new UserInfo("DDD", 40));
        userRepository.save(new UserInfo("EEE", 50));
        userRepository.save(new UserInfo("FFF", 60));
        userRepository.save(new UserInfo("GGG", 70));
        userRepository.save(new UserInfo("HHH", 80));
        userRepository.save(new UserInfo("III", 90));
        userRepository.save(new UserInfo("JJJ", 100));

        // 测试findAll, 查询所有记录
        Assertions.assertEquals(10, userRepository.findAll().size());

        // 测试findByName, 查询姓名为FFF的User
        Assertions.assertEquals(60, userRepository.findByName("FFF").getAge().longValue());

        // 测试findUser, 查询姓名为FFF的User
        Assertions.assertEquals(60, userRepository.findUser("FFF").getAge().longValue());

        // 测试findByNameAndAge, 查询姓名为FFF并且年龄为60的User
        Assertions.assertEquals("FFF", userRepository.findByNameAndAge("FFF", 60).getName());

        // 测试删除姓名为AAA的User
        userRepository.delete(userRepository.findByName("AAA"));

        // 测试findAll, 查询所有记录, 验证上面的删除是否成功
        Assertions.assertEquals(9, userRepository.findAll().size());

    }

}

Run the unit test:

If everything goes well, because the create strategy is used here, the table is still there, open pgAdmin, you can see that the user_info table is automatically created, and the data inside can also be checked to see if it matches the logic of the unit test.

think for a while

If you have read more than 10 use cases of MySQL in this series of tutorials before, look at this case of using PostgreSQL, do you feel that the difference is very small? In fact, the real changes are mainly in two places:

1. Database-driven dependency
2. Data source configuration information

For the higher-level data operations, there is actually not much change, especially when using Spring Data JPA, this is the charm of abstraction! Did you experience it?

Well, that's all for today's study! If you encounter difficulties in the learning process? You can join our ultra-high-quality Spring technical exchange group , participate in exchanges and discussions, and learn and progress better! More Spring Boot tutorials can be clicked directly! , welcome to collect and forward support!

Code example

The complete works of this paper can be viewed below warehouse 2.x directory chapter6-4 project:

• Github：https://github.com/dyc87112/SpringBoot-Learning/
• Gitee：https://gitee.com/didispace/SpringBoot-Learning/

If you think this article is good, welcome Star support, your attention is my motivation for persistence!

Reference materials:

• https://baike.baidu.com/item/PostgreSQL
• https://www.biaodianfu.com/mysql-vs-postgresql.html

Welcome to pay attention to my public account: Program Ape DD, share knowledge and thoughts that can’t be seen elsewhere