SpringBoot + PostgreSQL CRUD using Spring JPA tutorial

feature-image-how-to-integrate-springboot-with-postgresql-@site-loizenai.com

– Example Integrate SpringBoot 2.x + PostgreSQL CRUD using Spring JPA tutorial

How to integrate SpringBoot and PostgreSQL? It is the one of most common questions for developers. So in the post, I will help you to try it with Spring JPA. Here is a to do list for the tutorial:

  • Overview of Project Structure
  • Create SpringBoot with Spring Data JPA and PostgreSQL dependencies
  • Configuration SpringBoot to connect to PostgreSQL
  • Define a data model to map with table’s columns in PostgreSQL database
  • Define a JPA Repository to do CRUD operations
  • Use CommandLineRunner to excute above JPA Repository APIs: save, retrieve, update, detele entities

To do the tutorial, you need prepare Java >= 1.8, Spring Tool Suite in local computer for development.

Let’s go !

Video Guide

SpringBoot Project Overview

SpringBoot-PostgreSQL-CRUD-Example-Overview-How-to-Integrate-Springboot-2.x-with-PostgreSQL-using-Spring-JPA
Project Overview How to Integrate Springboot 2.x with PostgreSQL using Spring JPA

In the SpringBoot application, we use Spring JPA to connect and manipulate the data between Application and PostgreSQL database. We define a repository to do CRUD operations (save, retrieve, update, delete) with entities. And for mapping the data between Spring Application with database table’s columns, we define a model class.

Create SpringBoot Project

We use Eclipse that had integrated with SpringToolSuite to create a SpringBoot project. Remember we need add 2 dependencies Spring Data JPA and PostgreSQL driver.

Checking pom.xml file we see the needed dependencies as below:

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

Now we implement a SpringBoot Application to manipulate data with PostgreSQL database.

SpringBoot PostgreSQL CRUD Example - Springboot project structure
SpringBoot project structure

Setup SpringBoot datasource for connecting with PostgreSQL

SpringBoot helps us to create a datasource by simple configuration in application.properties file. We use the spring.datasource.* to setup the url, username and password for Spring datasource bean as above.


## PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/loizenaidb
spring.datasource.username=postgres
spring.datasource.password=123
#drop & create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create

How to create a Spring data model class?

Imagine that we have a customer table in PostgreSQL database with 4 columns:
{id, firstname, lastname, age}

How to map the data between SpringBoot application with these table’s columns for manipulating data? We need create a Customer model class having 4 properties {id, firstname, lastname, age} then use annotations to handle the mapping each columns of tables which corresponding properties in java model class.


package com.loizenai.postgresqlspringboot.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="customer")
public class Customer {
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;
	
	@Column(name="firstname")
	private String firstname;
	
	@Column(name="lastname")
	private String lastname;
	
	@Column(name="age")
	private int age;

	public void setFirstname(String firstname) {
		this.firstname = firstname;
	}
	
	protected Customer() {}
	
	public Customer(String firstname, String lastname, int age) {
		this.firstname = firstname;
		this.lastname = lastname;
		this.age = age;
	}
	
	public String toString() {
		return String.format("id=%d, firstname='%s', lastname'%s', age=%d", 
								id, firstname, lastname, age);	
	}
}

@Entity specifies that the class is an entity. This annotation is applied to the entity class.
@Table specifies the primary table for the annotated entity.
@Column specifies the mapped column for a persistent property or field.
@Id specifies the primary key of an entity.
@GeneratedValue provides for the specification of generation strategies for the values of primary keys. The GeneratedValue annotation may be applied to a primary key property or field of an entity or mapped superclass in conjunction with the Id annotation. The use of the GeneratedValue annotation is only required to be supported for simple primary keys. Use of the GeneratedValue annotation is not supported for derived primary keys.

In the model data class Customer, we defines 2 constructors:
– The default constructor protected Customer() {} is used only by Spring JPA and we will not directly to use it so we define the constructor with protected
– With the second constructor public Customer(String firstname, String lastname, int age), we use to create a Customer instance, so we define it with all properties of Customer class as parameters.

We also create a toString method that is used to print all property values of a customer instance on console for checking.

Create Spring JPA Repository to do CRUD operations

In the tutorial “SpringBoot PostgreSQL CRUD”, for easy to manipulate data with PostgreSQL, Spring JPA provides interface APIs to do the works. Here is the hierarchy structure of Spring JPA Repository:

SpringBoot PostgreSQL CRUD Example 
 - Repository Hierarchy Structure
Spring JPA CRUD Repository Hierarchy

Repository is central repository marker interface. Captures the domain type to manage as well as the domain type’s id type. General purpose is to hold type information as well as being able to discover interfaces that extend this one during classpath scanning for easy Spring bean creation.

Domain repositories extending this interface can selectively expose CRUD methods by simply declaring methods of the same signature as those declared in CrudRepository.

@Indexed
public interface Repository<T, ID> {
}

CrudRepository interface is used for generic CRUD operations on a repository for a specific type.

@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
...
}

List APIs of CrudRepository as below:

SpringBoot PostgreSQL CRUD Example - All APIs of Spring JPA CrudRepository Interface
All APIs of Spring Jpa CrudRepository Interface

Details some APIs methods of Spring JPA CrudRepository:

  • S save(S entity) is used to save a given entity. Use the returned instance for further operations as the save operation might have changed the entity instance completely.
  • saveAll(Iterableentities) is used to save all given entities.
  • Optional findById(ID id) is used to retrieve an entity by its id
  • Iterable findAll() is used to return all instances of the type.
  • long count() is used to return the number of entities available
  • void deleteById(ID id) is used to deletes the entity with the given id.
  • void delete(T entity) is used to delete a given entity.
  • void deleteAll(Iterable entities) is used to delete the given entities.
  • void deleteAll() is used to delete all entities managed by the repository.

PagingAndSortingRepository is extension of CrudRepository to provide additional methods to retrieve entities using the pagination and sorting abstraction.

@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {...}

Here is a list APIs of Spring JPA PagingAndSortingRepository:

SpringBoot PostgreSQL CRUD Example - All APIs of Spring JPA PagingAndSortingRepository
All APIs of Spring JPA PagingAndSortingRepository
  • Iterable findAll(Sort sort) is used to return all entities sorted by the given options.
  • Page findAll(Pageable pageable) is used to return a Page of entities meeting the paging restriction provided in the Pageable object.

JpaRepository is a JPA specific extension of org.springframework.data.repository.Repository

@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {...}

Here is the list APIs of Spring JpaRepository:

The List APIs of JpaRepository
The List APIs of JpaRepository

Details of API methods of Spring JpaRepository:

  • S S saveAndFlush(S entity) is used to save an entity and flushes changes instantly
  • void deleteInBatch(Iterable entities) is used to deletes the given entities in a batch which means it will create a single Query. Assume that we will clear the javax.persistence.EntityManager after the call.
  • void deleteAllInBatch() is used to deletes all entities in a batch call.
The List APIs of JpaRepository
The List APIs of JpaRepository

In the tutorial “SpringBoot PostgreSQL CRUD”, we just use CrudRepository to do the CRUD operations with PostgreSQL database:

package com.loizenai.postgresqlspringboot.repository;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

import com.loizenai.postgresqlspringboot.model.Customer;

public interface CustomerRepository extends CrudRepository<Customer, Long>{
	List<Customer> findByFirstname(String firstname);
}

List findByFirstname(String firstname) is an new API (be defined by developer) to find a customer entity in PostgreSQL table by the firstname field.

Implement CommandLineRunner to apply Spring Jpa CrudRepository to manipulate data in PostgreSQL

In the tutorial “SpringBoot PostgreSQL CRUD”, CommandLineRunner is used to indicate that a bean should run when it is contained within a SpringApplication. Multiple CommandLineRunner beans can be defined within the same application context and can be ordered using the Ordered interface or @Order annotation.

In the main class of SpringBoot application, we implement a CommandLineRunner and autowired the CustomerRepository to do CRUD operations: save, retrieve, update or delete entities in PostgreSQL database:


package com.loizenai.postgresqlspringboot;

import java.util.Arrays;
import java.util.List;
import java.util.Optional;

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

import com.loizenai.postgresqlspringboot.model.Customer;
import com.loizenai.postgresqlspringboot.repository.CustomerRepository;

@SpringBootApplication
public class SpringBootPostgreSqlApplication implements CommandLineRunner{
	
	@Autowired
	CustomerRepository repository;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringBootPostgreSqlApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		// just save a Customer
		repository.save(new Customer("Jack", "Smith", 29));
		
		// save a list of customers to PostgreSQL
		repository.saveAll(Arrays.asList(new Customer("Adam", "Johnson", 25), 
				new Customer("Kim", "Smith", 30),
				new Customer("David", "Williams", 23), 
				new Customer("Jack", "Davis", 27)));
		
		
		// find all customers;
		
		Iterable lstCst = repository.findAll();
		System.out.println(lstCst);
		
		// find all customers with firstname = Jack
		List jacks = repository.findByFirstname("Jack");
		System.out.println(jacks);
		
		
		// find a Customer by ID
		Optional cstOpt = repository.findById(2L);
		
		if(cstOpt.isPresent()) {
			Customer cst = cstOpt.get();
			
			System.out.println(cst);
			
			// update the customer's firstname;
			
			cst.setFirstname("UPDATED FIRSTNAME");
			repository.save(cst);
		}
		
		// delete a Customer by ID
		repository.deleteById(1L);
		
		System.out.println("Done");
	}
}

– In above segment code, we do following tasks:

  • Just save a customer entity: repository.save(new Customer("Jack", "Smith", 29))
  • Save a list of customer entities to PostgreSQL:
    repository.saveAll(Arrays.asList(new Customer("Adam", "Johnson", 25), ...
  • Retrieve all customer entities in PostgreSQL table:
    Iterable lstCst = repository.findAll()
  • Find all customer entities with firstname = Jack:
    List jacks = repository.findByFirstname("Jack")
  • Retrieve a customer entity by ID
    Optional cstOpt = repository.findById(2L)
  • Update the customer’s firstname:
    cst.setFirstname("UPDATED FIRSTNAME");
    repository.save(cst);
  • Delete a customer entity by ID
    repository.deleteById(1L)

Run SpringBoot Application and Check Results

Run above SpringBoot application, then check the PostgreSQL database, we see a created customer table:

results in postgresql customer table
results in postgresql customer table

Sourcecode

Thanks for reading! See you later!

Related posts with the tutorial: “SpringBoot PostgreSQL CRUD”

SpringBoot + Angular 10 + PostgreSQL CRUD Example – Architecture Diagram
SpringBoot + Angular 8 + PostgreSQL CRUD Example – Architecture Diagram
SpringBoot + Angular 9 + PostgreSQL CRUD Example – Architecture Diagram
Node.js PostgreSQL CRUD Example
Angular Node.js PostgreSQL Crud Example

More about Spring JPA, refer offical site of Spring Docs: Spring Boot JPA Docs

4 thoughts on “SpringBoot + PostgreSQL CRUD using Spring JPA tutorial”

  1. Your style is unique in comparison to other folks I’ve read stuff from.
    Thanks for posting when you have the opportunity, Guess I’ll
    just bookmark this page.

Leave a Reply

Your email address will not be published. Required fields are marked *