There are two open source products you need to know about in today’s relational databases. One of them is MySQL, I believe that my friends must be familiar, because the previous Spring Boot on relational databases are all examples of MySQL to introduce. Today we’ll cover another open source relational database, PostgreSQL, and how to use it in Spring Boot.

PostgreSQL profile

When studying PostgreSQL, we always compare it to MySQL: MySQL claims to be the most popular open source database, while PostgreSQL claims to be the most advanced open source database. How advanced is that? Let’s get to know it!

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

The advantage of PostgreSQL

Since MySQL is a relational database, we need to know when to use MySQL and when to use PostgreSQL. If you have these requirements, you are better off with PostgreSQL than with MySQL. Otherwise, you are better off with MySQL.

  • Supports storage of special data types, such as array, JSON, and JSONB
  • There is better support for the storage and processing of geographic information, so it can become a spatial database, better management of data measurement and geometric topology analysis
  • You can quickly build REST apis and use PostgREST to provide RESTful API services for any PostgreSQL database
  • Supports tree structure, facilitating processing of data stores with this feature
  • External data source support, MySQL, Oracle, CSV, Hadoop and other as their own database tables to query
  • Index support is even 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. Repeatable Read, the transaction isolation level of MySQL, cannot prevent common concurrent updates, only locks can be added. However, pessimistic locks will affect performance, and it is complicated to implement optimistic locks manually. The PostgreSQL column has a hidden optimistic lock version field. The default REPEATable read level ensures that concurrent updates are correct and has optimistic lock performance.
  • Time accuracy is higher, can be accurate to less than seconds
  • Character support is better. MySQL requires UTF8MB4 to display emojis, PostgreSQL doesn’t
  • The PostgreSQL main table is stored in a heap, while MySQL uses indexed tables to organize tables, which can support a larger amount of data than MySQL.
  • Sequence support is better. MySQL does not support multiple tables fetching ids from the same sequence, whereas PostgreSQL does
  • Adding columns is easier, adding columns to MySQL tables, basically rebuilding tables and indexes, can take a long time. Adding columns to a PostgreSQL table simply adds the table definition to the data dictionary and does not rebuild the table.

Here are just a few of the advantages that a developer’s perspective focuses on, but there are a few others that you should check out this article for a more detailed explanation.

Download and Install

The PostgreSQL installation program is available at the link below. The installation process is not described here, but you can follow the installation instructions (go next, set access password and port).

Download address: www.enterprisedb.com/downloads/p…

Note: since version 14 is just released today, to avoid compatibility problems with Spring Boot, the previous version 13.4 is used to complete the following experiments.

After the installation is complete, open pgAdmin. Because of the built-in interface management tools, so if you have used mysql or any relational database, it is basically not much to learn, you can start to use.

How to use Spring Boot

With PostgreSQL installed, let’s try using the PostgreSQL database in Spring Boot.

Step 1: Create a basic Spring Boot project (see this article: Quick Start if you don’t already)

Step 2: Introduce two important dependencies needed to access PostgreSQL in 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>
Copy the code

Postgresql is a must, and spring-boot-starter-data-jPA can be replaced with other data access packages, such as MyBatis, depending on your usage habits. Because it is already a higher level of encapsulation, the basic use of MySQL is similar to before, so you can also refer to the previous MySQL article for configuration, but the data source part needs to be configured according to the following section.

Step 3: Configure the data source and necessary JPA configurations for the PostgreSQL database 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
Copy the code

Step 4: Create user information entity and map user_Info table (the 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; }}Copy the code

Step 5: Create add, delete, change and query of user information entity

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);

}
Copy the code

Step 6: Create unit tests and try add, delete, change, and check.

@Slf4j
@SpringBootTest
public class ApplicationTests {

    @Autowired
    private UserInfoRepository userRepository;

    @Test
    public void test(a) throws Exception {
        // Create 10 records
        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));

        // Test findAll to query all records
        Assertions.assertEquals(10, userRepository.findAll().size());

        // Test findByName to find User with name FFF
        Assertions.assertEquals(60, userRepository.findByName("FFF").getAge().longValue());

        // test findUser to findUser with name FFF
        Assertions.assertEquals(60, userRepository.findUser("FFF").getAge().longValue());

        // Test findByNameAndAge to find User with name FFF and age 60
        Assertions.assertEquals("FFF", userRepository.findByNameAndAge("FFF".60).getName());

        // Test deleting User with name AAA
        userRepository.delete(userRepository.findByName("AAA"));

        // test findAll to findAll records and verify that the above deletion was successful
        Assertions.assertEquals(9, userRepository.findAll().size()); }}Copy the code

Run the unit test:

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

Think about it

If you’ve read the 10 or so MySQL use cases in this tutorial series, are you looking at the PostgreSQL use case and seeing the difference? The real change is in two main places:

  1. Database-driven dependencies
  2. Configuration information about the data source

For higher-level Data operations, not much has changed, especially when using Spring Data JPA, which is the beauty of abstraction! Do you feel it?

Well, that’s all for today’s learning! If you have difficulty in learning? You can join our super high quality Spring technology exchange group, participate in the exchange and discussion, better learning and progress! More Spring Boot tutorials can be clicked direct! , welcome to collect and forward support!

Code sample

For the complete project of this article, see the chapter6-4 project in the 2.x directory of the warehouse below:

  • Github:github.com/dyc87112/Sp…
  • Gitee:gitee.com/didispace/S…

If you found this article good, welcomeStarSupport, your attention is my motivation!

References:

  • Baike.baidu.com/item/Postgr…
  • www.biaodianfu.com/mysql-vs-po…

Welcome to pay attention to my public account: program ape DD, share the outside can not see the dry goods and thinking!