Spring Data JDBC, PostgreSQL, CRUD Example

 Hello everyone, In this article, we will learn how to develop a  REST-style web service with Spring Boot, Spring Data JDBC, and PostgreSQL Database.GitHub repository link is provided at the end of this tutorial. You can download the source code.

What's new in this example? 
  • From Java 14 onwards, the record is a special type of class declaration aimed at reducing the boilerplate code. For more info click here
  • From Java 10 onwards, the var keyword allows local variable type inference, which means the type for the local variable will be inferred by the compiler, so we don't need to declare that. For more info click here

Technologies used : 

  • Spring Boot 2.6.3
  • Spring  Data JDBC 
  • Java 17 
  • PostgreSQL
  • Maven 3+ 

These are APIs that Spring backend App will export: 

  1. GET all User's        :     /api/v1/users             : get 
  2. GET User by ID     :     /api/v1/users/{id}       : get
  3. CREATE User        :     /api/v1/users             : post
  4. UPDATE User        :     /api/v1/users/{id}       : put
  5. DELETE User        :     /api/v1/users/{id}       : delete 

Final Project Directory:



Maven[pom.xml] 

A Project Object Model or POM is the fundamental unit of work in Maven. It is an XML file that contains information about the project and configuration details utilized by Maven to build the project. It contains default values for most projects. Some of the configurations that can be designated in the POM is the project dependencies, the plugins or goals that can be executed, the build profiles, and so on. Other information such as the project version, description, developers, mailing lists and such can withal be designated.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath />
</parent>
<groupId>com.knf.dev.demo</groupId>
<artifactId>spring-boot-data-jdbc-postgresql-crud</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-data-jdbc-postgresql-crud</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>
spring-boot-starter-data-jdbc
</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin
</artifactId>
</plugin>
</plugins>
</build>

</project>


shema.sql: Creating a table for 'users'

Spring Boot can automatically create the schema (DDL scripts) of your DataSource and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema. sql and data. sql , respectively.
--
-- Create table for `users`
--

CREATE TABLE users (
id serial PRIMARY KEY,
first_name VARCHAR ( 50 ) NOT NULL,
last_name VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) NOT NULL
);


application.properties

spring.datasource.url= jdbc:postgresql://localhost:5432/postgres
spring.datasource.username= postgres
spring.datasource.password= password

spring.datasource.initialization-mode=always


Creating the Model User

package com.knf.dev.demo.springbootdatajdbccrud.model;

public record User
(Long id,
String firstName,
String lastName,
String email) {
}


Creating the User Repository

package com.knf.dev.demo.springbootdatajdbccrud.repository;

import java.util.List;

import com.knf.dev.demo.springbootdatajdbccrud.model.User;

public interface UserRepository {

public User findOne(Long id);

public List<User> findAll();

public void save(User user);

public Long saveAndReturnId(User user);

public void update(User user);

public Boolean delete(Long id);

}


Creating the User Repository Implementation

package com.knf.dev.demo.springbootdatajdbccrud.repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import com.knf.dev.demo.
springbootdatajdbccrud.exception.InternalServerError;
import com.knf.dev.demo.
springbootdatajdbccrud.exception.UserNotFound;
import com.knf.dev.demo.
springbootdatajdbccrud.model.User;

@Repository
public class UserRepositoryImpl implements UserRepository {

private final JdbcTemplate jdbcTemplate;

@Autowired
public UserRepositoryImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

@Override
public User findOne(Long id) {
var sqlQuery = Query.FIND_ONE;

try {

return jdbcTemplate.
queryForObject(sqlQuery,
this::mapRowToUser, id);
} catch (EmptyResultDataAccessException ex) {
throw new UserNotFound("Invalid User Id");
} catch (Exception e) {
throw new InternalServerError
("Internal Server Error");
}

}

@Override
public List<User> findAll() {

var sqlQuery = Query.FIND_ALL;

return jdbcTemplate.
query(sqlQuery, this::mapRowToUser);
}

@Override
public void save(User user) {

var sqlQuery = Query.SAVE;

jdbcTemplate.update(sqlQuery,
user.firstName(),
user.lastName(),
user.email());
}

@Override
public Long saveAndReturnId(User user) {

var sqlQuery = Query.SAVE_AND_RETURN_ID;

var keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(connection -> {
PreparedStatement stmt = connection.
prepareStatement(sqlQuery,
new String[] { "id" });
stmt.setString(1, user.firstName());
stmt.setString(2, user.lastName());
stmt.setString(3, user.email());
return stmt;
}, keyHolder);

return keyHolder.getKey().longValue();
}

@Override
public void update(User user) {

var sqlQuery = Query.UPDATE;

jdbcTemplate.update(sqlQuery,
user.firstName(),
user.lastName(),
user.email(),
user.id());
}

@Override
public Boolean delete(Long id) {

var sqlQuery = Query.DELETE;

return jdbcTemplate.update(sqlQuery, id) > 0;
}

private User mapRowToUser(ResultSet resultSet, int rowNum)
throws SQLException {

var user = new User(resultSet.getLong("id"),
resultSet.getString("first_name"),
resultSet.getString("last_name"),
resultSet.getString("email"));

return user;
}

}


Query.java

package com.knf.dev.demo.springbootdatajdbccrud.repository;

public class Query {

public static final String FIND_ONE =
"select id, first_name, last_name,"
+ " email from users where id = ?";
public static final String FIND_ALL =
"select id, first_name,last_name,"
+ " email from users";
public static final String SAVE =
"insert into users(first_name, "
+ "last_name, email) "
+ "values (?, ?, ?)";
public static final String SAVE_AND_RETURN_ID =
"insert into users"
+ "(first_name, last_name, email) "
+ "values (?, ?, ?)";
public static final String UPDATE =
"update users set "
+ "first_name = ?, last_name = ?,"
+ " email = ? " + "where id = ?";
public static final String DELETE =
"delete from users where id = ?";
}


Exception Handler with Controller Advice in Spring

Spring supports exception handling by a global Exception Handler (@ExceptionHandler) with Controller Advice (@ControllerAdvice). This enables a mechanism that makes ResponseEntity work with the type safety and flexibility of @ExceptionHandler:
package com.knf.dev.demo.springbootdatajdbccrud.exception;

import java.util.Date;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.context.request.WebRequest;

@ControllerAdvice
public class GlobalExceptionHandler {

@ExceptionHandler(InternalServerError.class)
public ResponseEntity<ErrorMessage>
internalServerError(Exception ex, WebRequest request) {
var errors =
new ErrorMessage(500, new Date(),
ex.getMessage(), "Internal Server Error");

return new ResponseEntity<>
(errors, HttpStatus.INTERNAL_SERVER_ERROR);
}

@ExceptionHandler(UserNotFound.class)
public ResponseEntity<ErrorMessage>
userNotFound(Exception ex, WebRequest request) {
var errors =
new ErrorMessage(404, new Date(),
ex.getMessage(), "User Not Found");

return new ResponseEntity<>
(errors, HttpStatus.NOT_FOUND);
}
}


Custom Exception - Internal Server Error

package com.knf.dev.demo.springbootdatajdbccrud.exception;

public class InternalServerError extends RuntimeException {

private static final long serialVersionUID = 1L;

public InternalServerError(String msg) {
super(msg);
}
}


Custom Exception - UserNotFound

package com.knf.dev.demo.springbootdatajdbccrud.exception;

public class UserNotFound extends RuntimeException {
private static final long serialVersionUID = 1L;

public UserNotFound(String msg) {
super(msg);
}
}


Error Message

package com.knf.dev.demo.springbootdatajdbccrud.exception;

import java.util.Date;

public record ErrorMessage
(Integer statusCode,
Date timestamp,
String message,
String description) {
}



User Rest Controller

The @RestController annotation was introduced in Spring 4.0 to simplify the engendering of RESTful web services. It's a convenience annotation that combines @Controller and @ResponseBody. @RequestMapping annotation maps HTTP requests to handler methods of MVC and REST controllers.

package com.knf.dev.demo.springbootdatajdbccrud.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
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.knf.dev.demo.
springbootdatajdbccrud.exception.InternalServerError;
import com.knf.dev.demo.
springbootdatajdbccrud.model.User;
import com.knf.dev.demo.springbootdatajdbccrud.
repository.UserRepository;

@RestController
@RequestMapping("/api/v1/users")
public class UserController {

@Autowired
UserRepository userRepository;

//Create user
@PostMapping
public ResponseEntity<String> save(@RequestBody User usr) {

try {
var userId = userRepository.saveAndReturnId(usr);

return new ResponseEntity<String>
("User successfully created , Id ="
+ userId, HttpStatus.CREATED);
} catch (Exception e) {
throw new InternalServerError(e.getMessage());
}
}

//Get all users
@GetMapping
public ResponseEntity<List<User>> getAll() {

try {
return new ResponseEntity<List<User>>
(userRepository.findAll(), HttpStatus.OK);
} catch (Exception e) {
throw new InternalServerError(e.getMessage());
}
}

//Get user by id
@GetMapping("/{id}")
public ResponseEntity<User> getById
(@PathVariable("id") Long id) {

return new ResponseEntity<>
(userRepository.findOne(id), HttpStatus.OK);

}

// Delete user
@DeleteMapping("/{id}")
public ResponseEntity<String> deleteUser
(@PathVariable("id") Long id) {

userRepository.delete(userRepository.findOne(id).id());

return new ResponseEntity<>
("User removed successfully", HttpStatus.OK);

}

// Update user
@PutMapping("/{id}")
public ResponseEntity<String> updateUser
(@PathVariable("id") Long id, @RequestBody User user) {

var _user = userRepository.findOne(id);
var _upUSer = new User(_user.id(),
user.firstName(),user.lastName(),user.email());
userRepository.update(_upUSer);
return new ResponseEntity<>
("Updated successfully", HttpStatus.OK);

}
}



Spring Boot main driver

package com.knf.dev.demo.springbootdatajdbccrud;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootDataJdbcCrudApplication {

public static void main(String[] args) {
SpringApplication.
run(SpringBootDataJdbcCrudApplication.class, args);
}

}


Local setup 

Github repository download link is provided at the end of this tutorial 

Step 1: Download or clone the source code to a local machine. 
Step 2: mvn clean install
Step 3: Run the Spring Boot application mvn spring-boot:run

Testing API's using Postman

Create user:


Update user:


Get all users:


Get user by id:


Delete a user by id:


@Author Name: Innocent

Popular posts from this blog

Learn Java 8 streams with an example - print odd/even numbers from Array and List

Spring boot video streaming example-HTML5

Spring Boot + Mockito simple application with 100% code coverage

Spring Boot + OpenCSV Export Data to CSV Example

Custom Exception Handling in Quarkus REST API

Registration and Login with Spring Boot + Spring Security + Thymeleaf

DataTable-Pagination example with Spring boot, jQuery and ajax

Spring Webflux File Download - REST API Example

Node JS mini projects with source code - free download

ReactJS, Spring Boot JWT Authentication Example