Spring Boot + Hibernate + PostgreSQL Example

This tutorial will build a Spring Boot CRUD Rest API example with Maven that uses Spring Data JPA/Hibernate to interact with the PostgreSQL database.

You’ll know:

• How to configure Spring Data, JPA, and Hibernate to work with PostgreSQL Database
• Way to use Spring Data JPA to interact with PostgreSQL Database

Technology

• Java 11
• Spring Boot 2.x
• PostgreSQL
• Maven

PostgreSQL Set up

• Install PostgreSQL in Debian

$ sudo apt-get -y install PostgreSQL

The latter step requires that you first run the psql command.

• Create a new DB

$ createdb mydb

• Create a new user

$ CREATE USER newuser WITH PASSWORD 'xxxxxx';

• Create Table

CREATE TABLE weather (
 id              serial primary key,
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date,
    is_del          int default 0
);
ALTER TABLE weather OWNER TO newuser;

CREATE TABLE cities (
    id              serial primary key,
    name            varchar(80),
    location        point
);
ALTER TABLE cities OWNER TO newuser;

Or, use my test.sql initial Table:

$ mydb=> \i test.sql

Create Spring Boot Project

Use Spring Initializr to create a Maven Spring Boot Project
Add some dependencies to pom.xml:

<dependency>  
    <groupId>org.projectlombok</groupId>  
    <artifactId>lombok</artifactId>  
    <scope>provided</scope>  
</dependency>  

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

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

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

<dependency>  
    <groupId>javax.persistence</groupId>  
    <artifactId>javax.persistence-api</artifactId>  
</dependency>

PostgreSQL Configuration

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb  
spring.datasource.username=newuser  
spring.datasource.password=xxxxxx 

# connection timeout  
spring.datasource.hikari.connection-timeout=20000  
# min idle connections  
spring.datasource.hikari.minimum-idle=5  
# max pool size  
spring.datasource.hikari.maximum-pool-size=12  
spring.datasource.hikari.idle-timeout=300000  
spring.datasource.hikari.max-lifetime=1200000  
spring.datasource.hikari.auto-commit=true

For production environments, a single database connection is not enough to solve the real demand, so we need to configure the connection pool here.
By default, jpa-data uses hikari connection pooling, so it only needs to be configured in the application.properties file, no other dependencies are needed.

Define Model Class

Weather.java

package com.example.demo.model;  


import lombok.Data;  
import javax.persistence.*;  
import java.io.Serializable;  
import java.time.LocalDate;  

@Data  
@Entity  
@Table(name = "weather")  
public class Weather implements Serializable {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String city;  

    private Integer temp_hi;  

    private Integer temp_lo;  

    private Float prcp;  

    private Integer is_del;  

    private LocalDate date;  
}

City.java

package com.example.demo.model;  

import com.example.demo.PGpointType;  
import org.hibernate.annotations.Type;  
import org.hibernate.annotations.TypeDef;  
import org.postgresql.geometric.PGpoint;  
import lombok.Data;  

import javax.persistence.*;  
import java.io.Serializable;  

@Data  
@Entity  
@TypeDef(name = "point", typeClass = PGpointType.class)  
@Table(name = "cities")  
public class City implements Serializable {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String name;  

    @Type(type = "point")  
    private PGpoint location;  
}

• For auto increment id, you need to use GeneratedValue annotate, and the user IDENTITY strategy
• Because in hibernate, its do not support PGpoint data type, need to create a customer PGpointType Class

Create Repository Interface

package com.example.demo.repository;  

import com.example.demo.model.Weather;  
import org.springframework.data.domain.Pageable;  
import org.springframework.data.jpa.repository.JpaRepository;  
import org.springframework.data.jpa.repository.Query;  
import org.springframework.data.repository.query.Param;  

import java.util.List;  

public interface WeatherRepository extends JpaRepository<Weather, Long> {  

    @Query("SELECT w FROM Weather w " +  
            " WHERE (:city is NULL OR :city = '' OR w.city = :city)" +  
            " AND w.is_del = 0")  
    List<Weather> listWeather(@Param("city") String city, Pageable pageable);  
}

If use SELECT id, city, temp_hi, temp_lo, prcp, is_del, date FROM weather, the result will be Object[], can’t convert to Weather.class, So I use SELECT w FROM Weather w

Create Controller & Service

Controller

@RestController  
@RequestMapping("/api")  
public class DemoController {
 @Autowired  
 private DemoService demoService;
 //...
}
@Service  
public class DemoService {  

    @Autowired  
    private WeatherRepository weatherRepository;  

    @Autowired  
    private CityRepository cityRepository;

 //...
}

Api Test

Import api-test.json to Postman for API test.

Conclusion

Above are the steps for building a Spring Boot + Hibernate + PostgreSQL example with REST API.

The Source is open on GitHub!