How To Create CRUD For Database Views In Spring

Database views are a powerful feature for simplifying data access and organizing complex queries. However, implementing CRUD for database views can be a bit tricky since views are typically read-only by nature. So, how can you overcome this challenge in a Spring Boot application?

In this blog, we’ll show you how to create CRUD for database views in Spring Boot, using a combination of JPA, custom SQL queries, and database triggers to achieve full CRUD functionality. Whether you’re optimizing your application’s data layer or managing complex relationships, this guide will walk you through every step. Let’s get started!

What Are Database Views?

A database view is a virtual table that is created by querying one or more base tables in a database. It allows users to access and manipulate data stored in the underlying tables as if they were accessing a single table. Views are used to simplify complex queries, provide security by limiting access to certain columns or rows of data, and present data in a more user-friendly format. Views do not store any data themselves but instead, retrieve data dynamically from the base tables when they are queried.

Create CRUD For Database Views

First of all, we will create a Generic Repository for implementation in our views repository.

A Generic Repository is a design pattern used in software development that provides an abstraction layer between the data access code and the business logic code in an application. It helps to decouple the data access logic from the business logic, making the application more maintainable and testable.

@NoRepositoryBean
public interface ReadOnlyRepository<T, ID> extends Repository<T, ID> {

    List<T> findAll();
    List<T> findAll(Sort sort);
    Page<T> findAll(Pageable pageable);
    Optional<T> findById(ID id);
    long count();
}

@NoRepositoryBean

Annotation to exclude repository interfaces from being picked up and thus in consequence getting an instance being created.

Entity Class In Crud For Database Views

An entity class typically contains properties that correspond to the attributes or columns of the entity in the database. These properties encapsulate the data associated with the entity and provide a way to interact with and manipulate the entity’s data.

@Entity 
@Table(name="MY_VIEW", schema="MY_SCHEMA" ) // remove schema if not required
@Immutable
public class MyView{
    
    @Id
    @Column(name="VIEW_ID", nullable=false)
    private double viewed;
    @Column(name="FIELD_1")
    private BigDecimal field1;
    @Column(name="FIELD_2") 
    private String field2;
}

Repository In CRUD For Database View

A repository in an ORM typically provides methods for CRUD (Create, Read, Update, Delete) operations on entities. These methods interact with the underlying data store, such as a relational database, to perform data operations without exposing the details of the data access mechanism to the rest of the application.

@Repository
public interface MyViewRepository extends ReadOnlyRepository<MyView, BigDecimal>, JpaSpecificationExecutor<MyView>
{
    
}

Service In CRUD For Database View

@Service
@Slf4j
public class MyViewService {

     @Autowired
     private MyViewRepository myViewRepository;
     public List<MyView> findAll() {

    try {
       return myViewRepository.findAll();
    } catch (Exception e) {
            return Collections.empty list();
    }
 }
}

Controller For Database View

In the context of Spring Boot, a controller is a class that is responsible for handling incoming HTTP requests and returning an HTTP response to the client. Controllers in Spring Boot are typically annotated with @Controller or @RestController annotations to mark them as Spring MVC controllers.

@RestController
@CrossOrigin
@RequestMapping("/API/myView")
@Slf4j // Lombok Library class to implement log4j

public class MyViewController {

    @Autowired
    private MyViewService myViewService;
    @PostMapping
    public Page<MyView> findAll() {
        log.debug("Find All data info via view as name MyView");
        return myViewService.findAll();
    }
}

In Conclusion Of CRUD For Database Views

This is the simplest Implementation to create CRUD For Database Views Using Spring Boot. Happy Coding . If you like it leave a comment and If you think it can be modified Leave Comments also. You can connect with me at [email protected]

Small Suggestion