Spring Boot + Hibernate + PostgreSQL Example

PostgreSQL

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

1. Install PostgreSQL in Debian

sudo apt-get -y install postgresql

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

2. Create a new DB

createdb mydb

3. Create a new user

CREATE USER newuser WITH PASSWORD 'xxxxxx';

4. 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!