REST CRUD API : Spring Boot JDBI 3 with Postgres Database

{getToc} $title={Table of Contents}

Introduction

Why JDBI ?

It is open source
Offers full control of the queries
It can be combined with connection pools and high-availability features in the database drivers.
It can invoke a database stored procedure.

In this blog, I’ll implement a sample REST API that uses Spring Boot with Jbdi 3 and PostgreSQL.

Prerequisites

This is the list of all the prerequisites to build this project:

java 11
spring boot 2.7.3
apache maven 3.6.3
jdbi3-postgres 3.32.0
jdbi3-core 3.32.0
jdbi3-sqlobject 3.32.0
lombox 1.18.24
postgresql 42.3.6

1. Getting Started

We will start by creating a simple Spring Boot project from start.spring.io, with the following dependencies:

Spring Web,
Spring Data JDBC,
Lombok,
PostgreSQL Driver, and
Validation.

generate spring boot project

Spring Maven Project Structure

spring maven project structure

2. Create Entity

package com.yuthads.springdatajdbi3crud.entities;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {

	private Long id;
	
	@NotBlank
	@Size(max = 100)
	private String name;
	
	@Size(max = 255)
	private String description;
	
	private double price;
	
	private int qty;
	
}

I have create an entity class product in package: com.yuthads.springdatajdbi3crud.entities.

And I will create a table product with these same column as in the entity class.

3. Create Table Script

create table if not exists jdbi3_crud.product (
	
	id bigserial primary key,
	name varchar(100) NOT NULL,
	description varchar(255),
	price numeric(10,2),
	qty integer

);

I have create a script file in order to genreate table product with schema jdbi3_crud.

4. Set Datasource Connection Properties

spring.profiles.active=dev
spring.datasource.username=local_dev_user
spring.datasource.password=123456
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/spring-jdbi3-crud
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.schema=jdbi3_crud
spring.sql.init.mode=always

spring.sql.init.mode=always : to generate table whenever start running project.
postgres database name: spring-jdbi3-crud running in local on port 5432
postgres database user: local_dev_user
postgres database schema : jdbi3_crud same as I have created in sql script in step 3

I have created local database using pgAdmin 4

local postgres database

5. Datasource Configuration

5.1. Add JDBI 3 Dependencies

<properties>
	<java.version>11</java.version>
	<jdbi.version>3.32.0</jdbi.version>
</properties>

...

<dependency>
	<groupId>org.jdbi</groupId>
	<artifactId>jdbi3-postgres</artifactId>
	<version>${jdbi.version}</version>
</dependency>
<dependency>
	<groupId>org.jdbi</groupId>
	<artifactId>jdbi3-core</artifactId>
	<version>${jdbi.version}</version>
</dependency>
<dependency>
	<groupId>org.jdbi</groupId>
	<artifactId>jdbi3-sqlobject</artifactId>
	<version>${jdbi.version}</version>
</dependency>

Configuration

package com.yuthads.springdatajdbi3crud.configuration;

import javax.sql.DataSource;

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.postgres.PostgresPlugin;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class DatasourceConfiguration {

	@Bean
	@ConfigurationProperties("spring.datasource")
	DataSource driverManagerDataSource() {
		return new DriverManagerDataSource();
	}
	
	@Bean
	DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource) {
		DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
		dataSourceTransactionManager.setDataSource(dataSource);
		return dataSourceTransactionManager;
	}
	
	@Bean
	Jdbi jdbi(DataSource dataSource) {
		return Jdbi.create(dataSource)
				.installPlugin(new SqlObjectPlugin())
				.installPlugin(new PostgresPlugin());
	}
	
}

The Jdbi class is the main entry point into the library. For that, we need to declare a JDBI bean, where we install plugins to work with the SQL Object API and Postgres.

We have also defined beans for the datasource and the transaction manager.

6. Create Product Service

package com.yuthads.springdatajdbi3crud.service;

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

import javax.validation.Valid;

import com.yuthads.springdatajdbi3crud.entities.Product;

public interface ProductCrudService {

	Product create(Product product);

	List<Product> gdtAllProducts();

	Optional<Product> getProductById(long id);

	int updateProductById(@Valid Product product);

	int deleteProductById(long id);
	
}

I hvae create a product service interface in package com.yuthads.springdatajdbi3crud.service with crud service methods in order to create REST API for Product data CRUD operation.

Product create(Product product): to create product data.
List<Product> gdtAllProducts(): to list all created product.
Optional<Product> getProductById(long id): to retrieve product data by ID
int updateProductById(@Valid Product product): to update product data
int deleteProductById(long id): to delete data

7. Create Repository

package com.yuthads.springdatajdbi3crud.repository;

import java.util.List;

import javax.validation.Valid;

import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.customizer.BindBean;
import org.jdbi.v3.sqlobject.statement.GetGeneratedKeys;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.transaction.Transaction;
import org.springframework.stereotype.Repository;

import com.yuthads.springdatajdbi3crud.entities.Product;

@Repository
@RegisterBeanMapper(Product.class)
public interface ProductCrudRepository {

	@Transaction
	@SqlUpdate("insert into jdbi3_crud.product (name, description, price, qty) values(:name, :description, :price, :qty);")
	@GetGeneratedKeys
	int insert(@BindBean Product product);

	@SqlQuery("select * from jdbi3_crud.product where id = :id;")
	Product getById(@Bind("id") long id);

	@SqlQuery("select * from jdbi3_crud.product;")
	List<Product> getAllProducts();

	
	@Transaction
	@SqlUpdate("update jdbi3_crud.product set name = :name, description = :description, price = :price, qty = :qty where id = :id;")
	int updateProductById(@BindBean @Valid Product product);

	@Transaction
	@SqlUpdate("delete from jdbi3_crud.product where id = :id;")
	int deleteProductById(long id);

}

We have created a repository anterface in package com.yuthads.springdatajdbi3crud.repository.

The product interface repository uses the SQL Objects API from JDBI, which allows you to use annotations to declare the SQL that executes in the database. Jdbi will provide an implementation of the repository.

In this repository, I have working with sql script in order to do crud operation with Postgres Database. I have created sql query with each method same as in service above.

@SqlUpdate annotation for operations that modify data (i.e. inserts, updates, deletes).
@SqlQuery annotation for select operations.
@GetGeneratedKeys annotation may be used on a @SqlUpdate or @SqlBatch method to return the keys generated from the SQL statement.
@RegisterBeanMapper(Product.class) annotation to tell Jdbi to convert the returned row into a Product object.
@Transaction annotation tells Jdbi to wrap a specific method call in a transaction.

8. Service Implementation

package com.yuthads.springdatajdbi3crud.service.impl;

import java.text.MessageFormat;
import java.util.List;
import java.util.Optional;

import javax.validation.Valid;

import org.jdbi.v3.core.Jdbi;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import com.yuthads.springdatajdbi3crud.entities.Product;
import com.yuthads.springdatajdbi3crud.repository.ProductCrudRepository;
import com.yuthads.springdatajdbi3crud.service.ProductCrudService;
import com.yuthads.springdatajdbi3crud.service.exception.DataNotFoundException;

@Service
public class ProductCrudServiceImpl implements ProductCrudService {

	private final ProductCrudRepository productRepository;
	
	public ProductCrudServiceImpl(Jdbi jdbi) {
		this.productRepository = jdbi.onDemand(ProductCrudRepository.class);
	}
	
	@Override
	public Product create(Product product) {
		return getById(productRepository.insert(product));
	}
	
	private Product getById(long id) {
		
		Product product = productRepository.getById(id);
		if(ObjectUtils.isEmpty(product)) {
			throw new DataNotFoundException(MessageFormat.format("Product id {0} not found.", String.valueOf(id)));
		}
		return product;
	}

	@Override
	public List<Product> gdtAllProducts() {
		return productRepository.getAllProducts();
	}

	@Override
	public Optional<Product> getProductById(long id) {
		return Optional.ofNullable(productRepository.getById(id));
	}

	@Override
	public int updateProductById(@Valid Product product) {
		return productRepository.updateProductById(product);
	}

	@Override
	public int deleteProductById(long id) {
		return productRepository.deleteProductById(id);
	}


}

The service layer implementation class (ProductCrudServiceImpl) will be injected by the ProductCrudRepository through the JDBI bean. And implements ProductCrudService.

The onDemand instances have an open-ended lifecycle, as they obtain and release a connection for each method call. They are thread-safe and may be reused across an application.

9. Create Controller Class

This class will be handle to create all the CRUD REST API Endpoints in this whole project.

package com.yuthads.springdatajdbi3crud.controller;

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

import javax.validation.Valid;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.yuthads.springdatajdbi3crud.entities.Product;
import com.yuthads.springdatajdbi3crud.service.ProductCrudService;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@RestController
@RequestMapping("/api/product")
@RequiredArgsConstructor
@Slf4j
public class ProductCrudController {

	
	private final ProductCrudService productService;
	
	@PostMapping()
	public ResponseEntity<Product> createProduct (@RequestBody @Valid Product product) {
		
		log.info("Rest request to save product: {}", product);
		
		return new ResponseEntity<>(productService.create(product), HttpStatus.CREATED);
		
	}
	
	@GetMapping()
	public ResponseEntity<List<Product>> getAllProducts() {
		
		log.info("Rest request to get all products");
		
		List<Product> list = productService.gdtAllProducts();
		
		return new ResponseEntity<>(list, HttpStatus.OK);
		
	}
	
	@GetMapping("/{id}")
	public ResponseEntity<Product> getProductById(@PathVariable("id") long id) {
		
		log.info("Rest request to get product by id : {}", id);
		
		Optional<Product> optProduct = this.productService.getProductById(id);
		
		return optProduct.map(T -> 
					new ResponseEntity<>(T, HttpStatus.OK))
					.orElse(new ResponseEntity<>(null, HttpStatus.NOT_FOUND));
		
	}
	
	@PutMapping()
	public ResponseEntity<String> updateProductById(@RequestBody @Valid Product product) {
		
		log.info("Rest request to update product by id : {}", product.getId());
		
		Optional<Product> opt = this.productService.getProductById(product.getId());
		
		opt.ifPresent(T -> this.productService.updateProductById(product));
		
		return opt.map(T -> new ResponseEntity<>("Product with id "+T.getId()+" was updated.", HttpStatus.OK))
				.orElse(new ResponseEntity<>(HttpStatus.NOT_FOUND.getReasonPhrase(), HttpStatus.NOT_FOUND));
		
	}
	
	@DeleteMapping("/{id}")
	public ResponseEntity<String> deleteProductById(@PathVariable("id") long id) {
		
		log.info("Rest request to delete product from id : {}", id);
		
		Optional<Product> opt = this.productService.getProductById(id);
		
		opt.ifPresent(T -> this.productService.deleteProductById(id));
		
		return opt.map(T -> new ResponseEntity<>("Product with id "+T.getId()+" was deleted.", HttpStatus.OK))
				.orElse(new ResponseEntity<>(HttpStatus.NOT_FOUND.getReasonPhrase(), HttpStatus.NOT_FOUND));
	}
}

We have created 5 RES API endpoints in order to do CRUD operation with JDBI 3 in this REST CRUD project.

createProduct: to save product data in to Postgres Database.
getAllProducts: to retrieve all product data which have created.
getProductById: to retrieve product data by product ID.
updateProductById: to update product data by ID.
deleteProductById: to delete the created product data by ID.

10. Test REST APIs

Create product data

rest api to create data

Retriever all data

rest api to retrieve all data

Retrieve data by ID

rest api to retrieve data by id

Update data by ID

rest api to update data by id

Delete data by ID

rest api to delete data by id

Conclusion

Hope this article was helpful.

GitHub: https://github.com/Yuth-Set/REST-CRUD-API-Spring-Data-JDBI-3

Watch Full Video:

REST CRUD API : Spring Boot JDBI 3 with Postgres Database

Introduction

Why JDBI ?

In this blog, I’ll implement a sample REST API that uses Spring Boot with Jbdi 3 and PostgreSQL.

Prerequisites

1. Getting Started

Spring Maven Project Structure

2. Create Entity

3. Create Table Script

4. Set Datasource Connection Properties

I have created local database using pgAdmin 4

5. Datasource Configuration

5.1. Add JDBI 3 Dependencies

Configuration

6. Create Product Service

7. Create Repository

8. Service Implementation

9. Create Controller Class

10. Test REST APIs

Create product data

Retriever all data

Retrieve data by ID

Update data by ID

Delete data by ID

Conclusion

Reactor RabbitMQ in your Reactive Spring Boot Application — Part 1

Reactor RabbitMQ in your Reactive Spring Boot Application — Part 2

What is Kubernetes and Containers and how does it work?

ERROR [Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: DevDataSource- Connection is not available, request timed out after 30000ms.]

REST CRUD API : Spring Boot JDBI 3 with Postgres Database

Introduction

Why JDBI ?

In this blog, I’ll implement a sample REST API that uses Spring Boot with Jbdi 3 and PostgreSQL.

Prerequisites

1. Getting Started

Spring Maven Project Structure

2. Create Entity

3. Create Table Script

4. Set Datasource Connection Properties

I have created local database using pgAdmin 4

5. Datasource Configuration

5.1. Add JDBI 3 Dependencies

Configuration

6. Create Product Service

7. Create Repository

8. Service Implementation

9. Create Controller Class

10. Test REST APIs

Create product data

Retriever all data

Retrieve data by ID

Update data by ID

Delete data by ID

Conclusion

You might like