Spring Boot 3 CRUD Operations with PostgreSQL
Spring Boot 3 provides various features for CRUD, such as a JDBC template, entity manager, or Java Persistence API(JPA). The developer can choose which feature is suitable for the project or task.
The developer can read more about PostgreSQL.
Create a PostgreSQL database server with a docker link
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>Add configuration connection database PostgreSQL in 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.DriverCreate 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: “createUserProfile” method for insert row into 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 by using page number, row per page, sort and order. No path variable and request body are required.
SELECT: “getUserProfileById” method for selecting data in the user_profiles table by ID. This method receives the value from the path variable.
UPDATE: “updateUserProfile” method for updating a row of 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: “deleteUserProfile” method for deleting row of user_profiles table by ID. This method receives the value from the path variable.
The developer can read more RestControllerAdvice annotation.
Spring boot 3 exception handlers with rest controller advice annotation link
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);
}
}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 for declaring this variable is an ID.
GeneratedValue annotation for use generator name “UUID”.
GenericGenerator annotation for use “org.hibernate.id.UUIDGenerator” generate UUID.
Read more about UUID link
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> {
}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 organising test suites.
Read more about Postman link
Test case calls web service to create a user profile.
POST http://localhost:8080/api/user-profiles
{
"firstName": "Dan",
"lastName": "Marks",
"email": "dan.m@example.com",
"birthDate": "1985-06-05T17:00:00.000+00:00",
"sex": 1
}
{
"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 user profile that was created.
Test case calls web service to get all data in the user profile table.
GET http://localhost:8080/api/user-profiles
[
{
"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
}
]Test result web service returns HTTP status 200 OK and all data in the user profile table.
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 web service returns HTTP status 200 OK and data in the user profile table by ID.
The test case calls a web service to get a user profile by an ID that does not exist in the user profile table.
GET http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698User profile not foundTest result web service returns HTTP status 404 Not Found and “User profile not found” message.
Test case calls web service to update 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.
Test case calls web service to delete user profile by ID.
DELETE http://localhost:8080/api/user-profiles/5b81ae0a-fd55-4698-b7f4-de61469340c8
Test result web service returns HTTP status 204 No Content and data was deleted in the user profile table.
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-de61469340c8User profile not foundThe 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.
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);
}Modify the user profile service “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.
Read more about the row mapper link
@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 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.
Thanks for reading.