Spring Boot Connect to PostgreSQL Database Examples

Details: Written by Nam Ha Minh; Last Updated on 31 December 2020 | Print Email

Through this Spring Boot tutorial, you will learn how to configure and write code for connecting to a PostgreSQL database server in a Spring Boot application. I’ll share with you the two common ways:

Use Spring JDBC with JdbcTemplate to connect to a PostgreSQL database
Use Spring Data JPA to connect to a PostgreSQL database

To connect a Spring Boot application to a PostgreSQL database, you need follow these steps below:

Add a dependency for PostgreSQL JDBC driver, which is required to allow Java applications to be able to talk with a PostgreSQL database server.
Configure data source properties for the database connection information
Add a dependency for Spring JDBC or Spring Data JPA, depending on your need:
- Use Spring JDBC for executing plain SQL statements
- Use Spring Data JPA for more advanced use, e.g. mapping Java classes to tables and Java objects to rows, and take advantages of the Spring Data JPA API.

Below are the details of configuration and code examples.

1. Add dependency for PostgreSQL JDBC Driver

Declare the following dependency in your project’s pom.xml file:

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

This will use the default version specified by Spring Boot. If you want to explicitly specify a PostgreSQL JDBC version, refer to this page.

2. Configure Data Source Properties

Next, you need to specify some database connection information in the Spring Boot application configuration file (application.properties) as follows:

spring.datasource.url=jdbc:postgresql://localhost:5432/shopme
spring.datasource.username=postgres
spring.datasource.password=password

Here, the JDBC URL points to a PostgreSQL database server running on localhost. Update the JDBC URL, username and password according to your environment.

3. Connect to PostgreSQL Database with Spring JDBC

In the simplest case, you can use Spring JDBC with JdbcTemplate to work with a relational database. So add the following dependency to your Maven project file:

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

And the following code example is of a Spring Boot console program uses JdbcTemplate to execute a SQL Insert statement:

package net.codejava;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class SpringJdbcTemplate2PostgreSqlApplication implements CommandLineRunner {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringJdbcTemplate2PostgreSqlApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		String sql = "INSERT INTO students (name, email) VALUES ("
				+ "'Nam Ha Minh', 'nam@codejava.net')";
		
		int rows = jdbcTemplate.update(sql);
		if (rows > 0) {
			System.out.println("A new row has been inserted.");
		}
	}

}

This program will insert a new row into the students table in a PostgreSQL database, using Spring JDBC which is a thin API built on top of JDBC.

For details about using Spring JdbcTemplate, I recommend you to read this tutorial.

4. Connect to PostgreSQL Database with Spring Data JPA

If you want to map Java classes to tables and Java objects to rows and take advantages of an Object-Relational Mapping (ORM) framework like Hibernate, you can use Spring Data JPA. So declare the following dependency to your project:

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

Besides the JDBC URL, username and password, you can also specify some additional properties as follows:

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL81Dialect

And you need to code an entity class (a POJO Java class) to map with the corresponding table in the database, as follows:

package net.codejava;

import javax.persistence.*;

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

	private String name;
	private String email;

	// getters and setters...
}

Then you need to declare a repository interface as follows:

package net.codejava;

import org.springframework.data.jpa.repository.JpaRepository;

public interface StudentRepository extends JpaRepository<Student, Integer> {

}

And then you can use this repository in a Spring MVC controller or business class as follows:

@Controller
public class StudentController {
    @Autowired
    private StudentRepository studentRepo;
      
    @GetMapping("/students")
    public String listAll(Model model) {
        List<Studnet> listStudents = studentRepo.findAll();
        model.addAttribute("listStudents", listStudents);
          
        return "students";
    }
      
}

I recommend you to follow this article: Understand Spring Data JPA with Simple Example to learn more about Spring Data JPA.

Those are some code examples for connecting to PostgreSQL database in Spring Boot. As you have seen, Spring Boot greatly simplifies the programming, and you can choose to use Spring JDBC or Spring Data JPA.

Watch the following video to see the coding in action:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.