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
GeneratedValueannotate, and the userIDENTITYstrategy - Because in hibernate, its do not support PGpoint data type, need to create a customer
PGpointTypeClass
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!
Top comments (0)