Spring Boot CRUD Operations Guide with PostgreSQL

Tutorial | By |

Spring Boot offers various features for CRUD, including a JDBC template, entity manager, and Java Persistence API (JPA). The developer can choose which feature is suitable for the project or task.

Getting Started | Accessing Data with JPA
Learn how to work with JPA data persistence using Spring Data JPA.

Step-by-step guide to Spring Boot CRUD

Create rest with spring.

1. Add dependencies in pom.xml

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>
<dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <optional>true</optional>
</dependency>

2. Add the PostgreSQL configuration connection to the application.properties file.

# ===============================
# = DATA SOURCE
# ===============================
spring.datasource.url=jdbc:postgresql://localhost:5432/demo
spring.datasource.username=admin
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver

3. Create RESTful web services.

import java.util.UUID;
import org.springframework.web.bind.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import com.example.demo.controller.handler.exception.ResourceNotFoundException;
import com.example.demo.entity.UserProfilesEntity;
import com.example.demo.services.UserProfileServiceInf;

@RestController
@RequestMapping("/api/user-profiles")
public class UserProfilesController {

    @Autowired
    UserProfileServiceInf userProfileServiceInf;

    @PostMapping
    public UserProfilesEntity createUserProfile(@RequestBody UserProfilesEntity userProfile) {
        userProfile.setCreateBy(UUID.randomUUID()); 
        return userProfileServiceInf.createUserProfile(userProfile);
    }

    @GetMapping
    public List<UserProfilesEntity> getAllUserProfiles() {
        return userProfileServiceInf.getAllUserProfiles();
    }

    @GetMapping("/{id}")
    public ResponseEntity<UserProfilesEntity> getUserProfileById(@PathVariable UUID id) {
        UserProfilesEntity userProfile = userProfileServiceInf.getUserProfileById(id)
                .orElseThrow(() -> new ResourceNotFoundException("User profile not found"));
        return ResponseEntity.ok(userProfile);
    }

    @PutMapping("/{id}")
    public ResponseEntity<UserProfilesEntity> updateUserProfile(@PathVariable UUID id, @RequestBody UserProfilesEntity userProfile) {
        userProfile.setUpdateBy(UUID.randomUUID());
        UserProfilesEntity updatedUserProfile = userProfileServiceInf.updateUserProfile(id, userProfile);
        return ResponseEntity.ok(updatedUserProfile);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUserProfile(@PathVariable UUID id) {
        userProfileServiceInf.deleteUserprofile(id);
        return ResponseEntity.noContent().build();
    }

}

INSERT: The “createUserProfile” method inserts a row into the user_profiles table.
The developer can modify “createBy” to get a value from JWT or any web token mechanism. This method receives the value from the request body.

SELECT: The “getAllUserProfiles” method selects all data in the user_profiles table. The developer can modify it for pagination using page number, row per page, sort, and order. No path variable or request body is required.

SELECT: The “getUserProfileById” method selects data in the user_profiles table by ID. This method receives the value from the path variable.

UPDATE: The “updateUserProfile” method updates a row of the user_profiles table by ID. This method receives the value from the path variable and request body. The developer can get “updateBy” from JWT or any web token mechanism.

DELETE: The “deleteUserProfile” method deletes rows of user_profiles tables by ID. It receives the value from the path variable.

Spring Boot 3 Exception Handlers with @RestControllerAdvice
Learn how @RestControllerAdvice in Spring Boot 3 simplifies exception handling. Reduce redundant code and boost maintainability.

Create a common exception handler with RestControllerAdvice annotation.

import org.springframework.web.bind.annotation.*;
import com.example.demo.controller.handler.exception.ResourceNotFoundException;
import org.springframework.web.context.request.WebRequest;

@RestControllerAdvice
public class CommonExceptionHandlers {

    @ExceptionHandler(ResourceNotFoundException.class)
    public ResponseEntity<?> resourceNotFoundException(ResourceNotFoundException ex, WebRequest request) {
        return new ResponseEntity<>(ex.getMessage(), HttpStatus.NOT_FOUND);
    }

    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> globalExceptionHandler(Exception ex, WebRequest request) {
        return new ResponseEntity<>(ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

}
public class ResourceNotFoundException extends RuntimeException {
    public ResourceNotFoundException(String message) {
        super(message);
    }
}

1. Create entity class.

An entity class is a mapping variable and column structure from the user_profiles table in the PostgreSQL database.

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.GenericGenerator;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.UUID;

@Setter
@Getter
@Entity
@Table(name = "user_profiles")
public class UserProfilesEntity {

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(
            name = "UUID",
            strategy = "org.hibernate.id.UUIDGenerator"
    )
    @Column(name = "id")
    UUID id;

    @Column(name = "first_name")
    String firstName;

    @Column(name = "last_name")
    String lastName;

    @Column(name = "email")
    String email;

    @Column(name = "birth_date")
    Date birthDate;

    @Column(name = "sex")
    int sex;

    @Column(name = "create_by")
    UUID createBy;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "create_date")
    Timestamp createDate;

    @Column(name = "update_by")
    UUID updateBy;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "update_date")
    Timestamp updateDate;

    @PrePersist
    protected void onCreate() {
        createDate = Timestamp.valueOf(LocalDateTime.now());
    }

    @PreUpdate
    protected void onUpdate() {
        updateDate = Timestamp.valueOf(LocalDateTime.now());
    }

}

ID annotation is used to declare this variable as an ID.
GeneratedValue annotation for use generator name “UUID”.
GenericGenerator annotation for use “org.hibernate.id.UUIDGenerator” generate UUID.

UUID Versions Explained | UUIDTools.com
There are 5 UUID version plus one common unofficial version. We will explain each of these versions as well as their purpose and when to use them. For more details, see the official UUID RFC 4122.

2. Create a user profile repository.

import java.util.UUID;
import org.springframework.stereotype.Repository;
import com.example.demo.entity.UserProfilesEntity;

@Repository
public interface UserProfileRepository extends JpaRepository<UserProfilesEntity, UUID> {

}

What is a DAO in Java?

  • A DAO (Data Access Object) is a design pattern that encapsulates the logic for interacting with a database or data source.
  • DAOs abstract persistence operations such as CRUD (Create, Read, Update, Delete) and provide a clean data-accessing interface.

What is @Repository?

  • @Repository It is a Spring annotation used to indicate that a class is responsible for data access operations.
  • It is a specialization of the @Component annotation, making the class eligible for component scanning and dependency injection.
  • It provides an additional benefit:
  • Automatically translates database-related exceptions (e.g., SQLException) into Spring’s DataAccessException.

While a @Repository-An annotated class often aligns with the DAO pattern. It is more of a Spring-specific annotation to mark a bean for data access responsibilities. If developers are following strict DAO principles, your @Repository a class can be your DAO implementation in a Spring application.

3. Create a service layer.

@Service
public class UserProfileServiceImpl implements UserProfileServiceInf {

    private final UserProfileRepository userProfileRepository;

    @Autowired
    public UserProfileServiceImpl(UserProfileRepository userProfileRepository) {
        this.userProfileRepository = userProfileRepository;
    }

    public UserProfilesEntity createUserProfile(UserProfilesEntity userProfilesEntity){
        return userProfileRepository.save(userProfilesEntity);
    }

    public List<UserProfilesEntity> getAllUserProfiles() {
        return userProfileRepository.findAll();
    }

    public Optional<UserProfilesEntity> getUserProfileById(UUID id) {
        return userProfileRepository.findById(id);
    }

    public UserProfilesEntity updateUserProfile(UUID id, UserProfilesEntity userProfilesEntity) {
        UserProfilesEntity userProfile = userProfileRepository.findById(id)
                .orElseThrow(() -> new ResourceNotFoundException("User profile not found"));

        userProfile.setFirstName(userProfilesEntity.getFirstName());
        userProfile.setLastName(userProfilesEntity.getLastName());
        userProfile.setEmail(userProfilesEntity.getEmail());

        return userProfileRepository.save(userProfile);
    }

    public void deleteUserprofile(UUID id) {
        UserProfilesEntity userProfile = userProfileRepository.findById(id)
                .orElseThrow(() -> new ResourceNotFoundException("User profile not found"));

        userProfileRepository.delete(userProfile);
    }
}
import com.example.demo.entity.UserProfilesEntity;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

public interface  UserProfileServiceInf {
    public UserProfilesEntity createUserProfile(UserProfilesEntity userProfilesEntity);
    public List<UserProfilesEntity> getAllUserProfiles();
    public Optional<UserProfilesEntity> getUserProfileById(UUID id);
    public UserProfilesEntity updateUserProfile(UUID id, UserProfilesEntity employeeDetails);
    public void deleteUserprofile(UUID id);
}

Test case scenario.

Test call web service by using the Postman app. Postman helps the developer test the web services. Postman provides a feature for organizing test suites.

Postman: The World’s Leading API Platform | Sign Up for Free
Accelerate API development with Postman’s all-in-one platform. Streamline collaboration and simplify the API lifecycle for faster, better results. Learn more.

1. Test case calls web service to create a user profile.

POST http://localhost:8080/api/user-profiles
Postman : request
{
    "firstName": "Dan",
    "lastName": "Marks",
    "email": "dan.m@example.com",
    "birthDate": "1985-06-05T17:00:00.000+00:00",
    "sex": 1
}
Postman : response
{
    "id": "5b81ae0a-fd55-4698-b7f4-de61469340c8",
    "firstName": "Dan",
    "lastName": "Marks",
    "email": "dan.m@example.com",
    "birthDate": "1985-06-05T17:00:00.000+00:00",
    "sex": 1,
    "createBy": "5bbaa849-db70-4141-bbd2-ffc5da426822",
    "createDate": "2024-06-05T07:54:06.682+00:00",
    "updateBy": null,
    "updateDate": null
}

Test result

Web service returns HTTP status 200 OK and information on the created user profile.

2. A test case calls for a web service to get all data in the user profile table.

GET http://localhost:8080/api/user-profiles
Postman : request
[
    {
        "id": "5b81ae0a-fd55-4698-b7f4-de61469340c8",
        "firstName": "Dan",
        "lastName": "Marks",
        "email": "dan.m@example.com",
        "birthDate": "1985-06-05T17:00:00.000+00:00",
        "sex": 1,
        "createBy": "5bbaa849-db70-4141-bbd2-ffc5da426822",
        "createDate": "2024-06-05T07:54:06.682+00:00",
        "updateBy": null,
        "updateDate": null
    },
    {
        "id": "43c1e74f-f08b-4477-b178-2ea5cdadf33b",
        "firstName": "Carroll",
        "lastName": "Sosa",
        "email": "carroll.s@example.com",
        "birthDate": "1985-06-05T17:00:00.000+00:00",
        "sex": 2,
        "createBy": "d316b5ad-16d7-4187-9b72-8d8cbdd93136",
        "createDate": "2024-06-04T09:22:17.043+00:00",
        "updateBy": null,
        "updateDate": null
    },
    {
        "id": "81f867c4-86e9-47e6-9f50-bbf22dfe13eb",
        "firstName": "Cathleen",
        "lastName": "Mccall",
        "email": "cathleen.m@example.com",
        "birthDate": "1985-06-05T17:00:00.000+00:00",
        "sex": 2,
        "createBy": "4d4f65ee-cfe3-4431-88e4-b4142eccb739",
        "createDate": "2024-06-04T09:26:39.325+00:00",
        "updateBy": null,
        "updateDate": null
    },
    {
        "id": "1e7a3085-8d0c-453f-b3d2-d1283557a34d",
        "firstName": "Marshall",
        "lastName": "Sweeney",
        "email": "marshall.s@example.com",
        "birthDate": "1985-06-05T17:00:00.000+00:00",
        "sex": 1,
        "createBy": "454c96c6-dde2-4a62-99f8-54d8837d4791",
        "createDate": "2024-06-04T09:25:09.507+00:00",
        "updateBy": null,
        "updateDate": null
    }
]

The test result

Web service returns HTTP status 200 OK; all data is in the user profile table.

3. Test case calls web service to get user profile by ID.

GET http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698-b7f4-de61469340c8
{
    "id": "5b81ae0a-fd55-4698-b7f4-de61469340c8",
    "firstName": "Dan",
    "lastName": "Marks",
    "email": "dan.m@example.com",
    "birthDate": "1985-06-05T17:00:00.000+00:00",
    "sex": 1,
    "createBy": "5bbaa849-db70-4141-bbd2-ffc5da426822",
    "createDate": "2024-06-05T07:54:06.682+00:00",
    "updateBy": null,
    "updateDate": null
}

Test result

The web service returns HTTP status 200 OK; the data is in the user profile table by ID.

4. The test case calls for a web service to get a user profile using an ID that does not exist in the user profile table.

GET http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698
User profile not found

Test result

Web service returns HTTP status 404 Not Found and “User profile not found” message.

5. The test case calls for a web service to update the user profile by ID.

PUT http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698-b7f4-de61469340c8
{
    "firstName": "Dan",
    "lastName": "Stanton",
    "email": "dan.s@example.com"
}

Change last name and email to last name “Stanton” and email “dan.s@example.com”.

{
    "id": "5b81ae0a-fd55-4698-b7f4-de61469340c8",
    "firstName": "Dan",
    "lastName": "Stanton",
    "email": "dan.s@example.com",
    "birthDate": "1985-06-05T17:00:00.000+00:00",
    "sex": 1,
    "createBy": "5bbaa849-db70-4141-bbd2-ffc5da426822",
    "createDate": "2024-06-05T07:54:06.682+00:00",
    "updateBy": null,
    "updateDate": "2024-06-05T08:23:53.663+00:00"
}

Test result

Web service returns HTTP status 200 OK and data that has changed in the user profile table.

6. The test case calls for a web service to delete a user profile by ID.

DELETE http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698-b7f4-de61469340c8
Postman : response

Test result

The web service’s test result returns HTTP status 204 with no content, and data was deleted from the user profile table.

7. Test case calls web service to get user profile by ID that was deleted.

GET http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698-b7f4-de61469340c8
User profile not found

Test result

Web service returns HTTP status 404 Not Found and a
“User profile not found” message. This test result shows that the record has been deleted from the user profile table.

Using Native Query in JPA

The JPA Repository cannot handle complex select queries. The developer can use a native query instead.

1. Modify the user profile repository.

import java.util.UUID;
import org.springframework.stereotype.Repository;
import com.example.demo.entity.UserProfilesEntity;

@Repository
public interface UserProfileRepository extends JpaRepository<UserProfilesEntity, UUID> {
    @Query(value = "SELECT * FROM user_profiles WHERE id= :id", nativeQuery = true)
    List<UserProfilesEntity> findByUserId(@Param("id") UUID id);
}

2. Modify the user profile service using the “getUserProfileById” method.

public Optional<UserProfilesEntity> getUserProfileById(UUID id) {
  //change from return userProfileRepository.findById(id); to  
  return userProfileRepository.findByUserId(id);
}

Alternatively, CRUD Operations with PostgreSQL use entity manager. When CRUD uses more than a couple of tables, it uses many complex table relations. The developer can use native queries by using entity manager instead of the JPA Repository.

For example, using entity manager.

The entity manager is helpful when queries are complex, such as joining multiple tables or selecting to update multiple columns.

@Transactional
public List<Object[]> getUserProfileById(UUID id){
    String sql = "SELECT * FROM user_profiles WHERE id = ?";
    List<Object[]> results = entityManager.createNativeQuery(sql)
            .setParameter(1, id)
            .getResultList();
    //The developer can use row mapper class to 
    //map List<Object[]> and List<UserProfilesEntity>
    return results;
}

@Transactional
public int createUserProfile(UserProfilesEntity userProfilesEntity){
    String sql = "INSERT INTO public.user_profiles(" +
            "id, first_name, last_name, email, birth_date, sex, create_by, create_date) " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
    entityManager.createNativeQuery(sql)
            .setParameter(1, userProfilesEntity.getId())
            .setParameter(2, userProfilesEntity.getFirstName())
            .setParameter(3, userProfilesEntity.getLastName())
            .setParameter(4, userProfilesEntity.getEmail())
            .setParameter(5, userProfilesEntity.getBirthDate())
            .setParameter(6, userProfilesEntity.getSex())
            .setParameter(7, userProfilesEntity.getCreateBy())
            .setParameter(8, userProfilesEntity.getCreateDate())
            .executeUpdate();
    return 1; //success
}

@Transactional
public int updateUserProfile(UserProfilesEntity userProfilesEntity){
    String sql = "UPDATE user_profiles " +
            "SET first_name=?, last_name=?, email=?, update_by=?, update_date=? " +
            "WHERE id=?";
    entityManager.createNativeQuery(sql)
            .setParameter(1, userProfilesEntity.getFirstName())
            .setParameter(2, userProfilesEntity.getLastName())
            .setParameter(3, userProfilesEntity.getEmail())
            .setParameter(4, userProfilesEntity.getUpdateBy())
            .setParameter(5, userProfilesEntity.getUpdateDate())
            .setParameter(6, userProfilesEntity.getId())
            .executeUpdate();
    return 1; //success
}

@Transactional
public int deleteUserProfileNa(UserProfilesEntity userProfilesEntity){
    String sql = "DELETE FROM public.user_profiles WHERE id=?";

    entityManager.createNativeQuery(sql)
            .setParameter(1, userProfilesEntity.getId())
            .executeUpdate();
    return 1; //success
}

Finally

This is a basic example of using JPA Repository CRUD from PostgreSQL. JPA Repository has various features to support tasks and business logic. The developer should learn features provided by the JPA Repository that help the developer find a suitable solution when facing a complex business logic problem.

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *