Master PostgreSQL with Spring Boot: Step‑by‑Step Guide & Code Samples

In modern relational databases, two open‑source products you must know are MySQL and PostgreSQL. This article introduces PostgreSQL and demonstrates how to use it with Spring Boot.

PostgreSQL Overview

PostgreSQL is a feature‑rich, free object‑relational database management system (ORDBMS) derived from the University of California's POSTGRES project. It supports most of the SQL standard and offers modern features such as complex queries, foreign keys, triggers, views, full transaction integrity, and multiversion concurrency control. Its extensibility allows adding new data types, functions, operators, aggregate functions, index methods, and procedural languages. The permissive license lets anyone use, modify, and distribute PostgreSQL for any purpose.

Advantages of PostgreSQL

Supports special data types like array, json, jsonb.

Better handling of geographic information, making it suitable as a spatial database.

Can quickly build REST APIs via PostgREST.

Native support for tree‑structured data.

External data source integration (MySQL, Oracle, CSV, Hadoop, etc.) as foreign tables.

Rich index support: B‑tree, hash, R‑tree, Gist, while MySQL index support depends on the storage engine.

Improved transaction isolation with built‑in optimistic lock versioning.

Higher time precision (sub‑second).

Better character support; no need for utf8mb4 to store emojis.

Can store larger data volumes; heap tables vs. MySQL's index‑organized tables.

Superior sequence handling; multiple tables can share a sequence.

Adding columns is faster; PostgreSQL updates the data dictionary without rebuilding the table.

For a more detailed comparison, refer to the linked articles.

Download and Installation

You can obtain PostgreSQL installers from the official site:

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

Use version 13.4 (instead of the newly released 14) to avoid compatibility issues with Spring Boot.

After installation, open pgAdmin. Its graphical interface makes it easy to start working with the database.

Using PostgreSQL in Spring Boot

Follow these steps to integrate PostgreSQL into a Spring Boot project.

Step 1: Create a basic Spring Boot project (refer to the quick‑start guide if needed).

Step 2: Add the required dependencies to 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>

The postgresql driver is mandatory; spring-boot-starter-data-jpa can be replaced with other data‑access frameworks if desired.

Step 3: Configure the data source in application.properties:

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

Step 4: Create an entity class mapping the user_info table:

@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: Define a repository interface for CRUD operations:

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

Step 6: Write a unit test to verify CRUD functionality:

@Slf4j
@SpringBootTest
public class ApplicationTests {

    @Autowired
    private UserInfoRepository userRepository;

    @Test
    public void test() 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));

        // Verify findAll
        Assertions.assertEquals(10, userRepository.findAll().size());

        // Verify findByName
        Assertions.assertEquals(60, userRepository.findByName("FFF").getAge().longValue());

        // Verify findUser
        Assertions.assertEquals(60, userRepository.findUser("FFF").getAge().longValue());

        // Verify findByNameAndAge
        Assertions.assertEquals("FFF", userRepository.findByNameAndAge("FFF", 60).getName());

        // Delete a record
        userRepository.delete(userRepository.findByName("AAA"));
        Assertions.assertEquals(9, userRepository.findAll().size());
    }
}

Run the test; the create strategy leaves the table in place. Open pgAdmin to see the automatically created user_info table and its data.

Reflection

If you have read the previous MySQL tutorials, the difference is minimal. The only real changes are the database driver dependency and the datasource configuration; higher‑level data operations remain the same thanks to Spring Data JPA’s abstraction.