Spring Boot + Hibernate + PostgreSQL Example
Tags: springboot, postgressql, java, webdev
Published: 2024-06-16 14:26:06

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
```bash
$ sudo apt-get -y install PostgreSQL
```
The latter step requires that you first run the `psql` command.

* Create a new DB
```bash
$ createdb mydb
```

* Create a new user
```bash
$ CREATE USER newuser WITH PASSWORD 'xxxxxx';
```

* Create Table
```SQL
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:
```bash
$ mydb=> \i test.sql
```

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

```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
```yaml
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**

```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**
```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
```java
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**
```java
@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](https://github.com/GeorgeCh2/spring-boot-postgresql/blob/main/postgresql-rest-api.json) to Postman for API test.
![api-test](https://miro.medium.com/v2/resize:fit:1148/format:webp/1*lSjgN9uAKOOdfbR-0Fac9g.png)


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

The Source is open on [GitHub](https://github.com/GeorgeCh2/spring-boot-postgresql)!