Using Spring Data JPA Repository for Database Views

When developing applications, it is common to work with database views alongside traditional tables to simplify complex data operations. Spring Data JPA in the Spring ecosystem, can seamlessly integrate with database views using its repository pattern. This article will guide you through setting up a Spring Data JPA repository for a database view.

1. Understanding Database Views

In relational database systems, a database view is a virtual table derived from one or more underlying database tables. It represents a subset of data or a transformed version of existing data in the database, presented in a structured format that can be queried like a regular table. Views provide a convenient way to abstract complex SQL queries and encapsulate data for specific use cases without altering the underlying schema.

1.1 Database Setup

Consider a practical example using the scenario of an e-commerce platform managing customer orders.

Suppose we have two tables in our database: customers and orders, defined as follows:

Tables: customers table and orders table

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

To simplify querying customer order data, we can create a database view that joins these tables to present a consolidated view of customer orders:

Database View: customer_orders_view (joining customers and orders)

CREATE VIEW customer_orders_view AS
SELECT 
    o.id AS order_id,
    o.customer_id,
    c.name AS customer_name,
    o.amount,
    o.order_date
FROM 
    orders o
JOIN 
    customers c ON o.customer_id = c.id;

In this view (customer_orders_view), we are combining fields from the orders table (id, customer_id, amount, order_date) with the name field from the customers table using a join operation. The view offers a clear and compact display of customer orders, where each row corresponds to an order with associated customer details.

1.2 Insert Records

Now, let’s insert sample records into the customers and orders tables:

-- Insert sample data into customers table
INSERT INTO customers (id, name, email)
VALUES
    (1, 'John Doe', 'john@example.com'),
    (2, 'Jane Smith', 'jane@example.com');

-- Insert sample data into orders table
INSERT INTO orders (id, customer_id, amount, order_date)
VALUES
    (1, 1, 100.00, '2024-04-01'),
    (2, 2, 150.50, '2024-04-03');

1.3 application.properties Configuration

Configure the application.properties file in your Spring Boot application to specify the database connection details and run SQL scripts.

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.hibernate.ddl-auto=update
spring.datasource.username=sa
spring.datasource.password=password
#spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.show-sql=true

spring.datasource.initialization-mode=always
spring.datasource.schema=classpath:schema.sql
spring.datasource.data=classpath:data.sql

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

1.4 Benefits of Database Views

Database views offer several advantages:

• Simplified Data Access: Views encapsulate complex SQL queries, making it easier to retrieve specific subsets of data.
• Data Abstraction: Views hide the underlying table structure, providing a layer of abstraction that can be useful for security and data integrity.
• Enhanced Query Performance: Optimized views can improve query performance by precomputing joins or aggregations.

2. Spring Data JPA Repository for Database View

This section will integrate the database view (customer_orders_view) with a Spring Boot application using Spring Data JPA. Spring Data JPA offers a convenient way to interact with this view using a repository.

Let’s start by creating a Spring Boot project with Spring Data JPA dependencies included in our pom.xml or build.gradle.

2.1 Entity Class for the View

Create a Java entity class, CustomerOrder, to represent the data structure of the database (customer_orders_view) View and add @Immutable annotation to mark the entity class as immutable, meaning instances of this entity cannot be modified or persisted.

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
@Immutable
public class CustomerOrder {

    @Id
    private Long orderId;
    private Long customerId;
    private String customerName;
    private BigDecimal amount;
    private LocalDate orderDate;

    public CustomerOrder(Long orderId, Long customerId, String customerName, BigDecimal amount, LocalDate orderDate) {
        this.orderId = orderId;
        this.customerId = customerId;
        this.customerName = customerName;
        this.amount = amount;
        this.orderDate = orderDate;
    }

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public Long getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public BigDecimal getAmount() {
        return amount;
    }

    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }

    public LocalDate getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(LocalDate orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "CustomerOrder{" + "orderId=" + orderId + ", customerId=" + customerId + ", customerName=" + customerName + ", amount=" + amount + ", orderDate=" + orderDate + '}';
    }

}

In this example:

• The CustomerOrder class is marked as @Entity to indicate that it’s an entity mapped to a database table.
• The @Immutable annotation ensures that instances of CustomerOrder are read-only and cannot be modified after retrieval from the database.

2.2 Create a ReadOnlyRepository Interface

Next, define a custom repository interface (ReadOnlyRepository) extending Spring Data JPA Repository for read-only operations on our CustomerOrder view.

import org.springframework.data.repository.NoRepositoryBean;
import java.util.List;
import java.util.Optional;
import org.springframework.boot.autoconfigure.data.web.SpringDataWebProperties.Pageable;
import org.springframework.boot.autoconfigure.data.web.SpringDataWebProperties.Sort;
import org.springframework.data.domain.Page;
import org.springframework.data.repository.Repository;

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

    List<T> findAll();
    
    Optional<T> findById(ID id);

    long count();
    
    List<T> findAllById(Iterable<ID> ids);
    
}

In this example, the ReadOnlyRepository interface extends Repository and provides a contract for read-only operations on the entity. It contains methods like findAll() and other read methods to fetch data from the database without supporting write (create, update, delete) operations.

2.3 Create CustomerOrderRepository Interface

Next, create a repository interface (CustomerOrderRepository) specifically for the CustomerOrder view by extending the ReadOnlyRepository interface.

@Repository
public interface CustomerOrderRepository extends ReadOnlyRepository<CustomerOrder, Long> {
    // Add custom read-only methods if necessary
    List<CustomerOrder> findByOrderId(Long orderId);
}

In this setup, the CustomerOrderRepository interface inherits all the read-only methods defined in the ReadOnlyRepository interface, and includes a custom findByOrderId() method.

3. Usage in Service or Controller

Finally, we can inject and use the CustomerOrderRepository in our service or controller to perform read operations on the CustomerOrder view.

@Service
public class OrderService {

    @Autowired
    private CustomerOrderRepository customerOrderRepository;

    public List<CustomerOrder> getAllCustomerOrders(Long id) {
        return customerOrderRepository.findByOrderId(id);
    }

}

And there you have it! We have effectively established a read-only repository for a database view within a Spring Boot application using Spring Data JPA.

Run Spring Boot Application: Start the Spring Boot application, and Spring Data JPA will automatically connect to the specified database using the configured properties. Now we can visit the h2 console located at http://localhost:8080/h2-console/ to see the database view (customer_orders_view).

4. Conclusion

In this article, we explored the concept of database views and demonstrated how to create a read-only repository interface (ReadOnlyRepository) using Spring Data JPA to interact with a database view (customer_orders_view). This approach enables efficient read operations on virtual tables within a Spring Boot application, enhancing data accessibility and encapsulating complex queries for improved maintainability and performance.

5. Download the Source Code

This article was about Using Spring Data JPA Repository for a Database View.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.