In this post, we’ll explore using Spring Data JPA with PostgreSQL views in a Spring Boot application.
· Prerequisites
· Overview
∘ Understanding Database Views
∘ Key Benefits of Database Views
· Database Setup
· Let’s code
∘ Entity Class for the View
∘ Create a Spring Data repository
∘ Business service layer
∘ Create a REST API endpoint
· Test the REST APIs
· Conclusion
· References
Prerequisites
This is the list of all the prerequisites:
- Spring Boot 3+
- Maven 3.6.+
- Java 21 or later
- PostgreSQL
- Postman or Insomnia
- Your favorite IDE (IntelliJ IDEA, Eclipse, NetBeans, VS Code)
Overview
Understanding Database Views
Database views are a powerful feature in relational database management systems (RDBMS) that allow users to create virtual tables based on the result of a query. Views are widely used in relational databases like PostgreSQL, MySQL, Oracle, and SQL Server to simplify complex queries, enforce security, and provide a consistent interface to data.
Key Benefits of Database Views
The benefits of using Database views are listed below:
- A view is a virtual table that is defined by a SQL query. It does not store data but provides a way to access data from one or more tables.
- Views can simplify complex queries, making it easier for users to access data without needing to understand the underlying table structure.
- By restricting access to specific columns or rows, views can enhance data security.
- Views provide a level of abstraction, allowing changes to the underlying tables without affecting how users access the data.
- Applications can query views without being affected by changes to the underlying tables.
Database Setup
This story will use a Bookstore-Database-Design Github repository that already has several PostgreSQL views.
Consider the following books_rank view which shows the ranking of books with the average rate and number of copies sold.
CREATE OR REPLACE VIEW books_rank AS (
SELECT
isbn,
title,
rate,
sold,
array(SELECT DISTINCT name
FROM books_genres
JOIN genres ON books_genres.genre_id = genres.id
WHERE book_id LIKE isbn) AS genres
FROM (SELECT
books.isbn AS isbn,
title AS title,
avg(review) :: NUMERIC(4, 2) AS rate,
sum(s.sold) AS sold
FROM books
JOIN reviews ON books.isbn = reviews.book_id
JOIN (SELECT
isbn,
coalesce(sum(amount), 0) AS sold
FROM books
LEFT JOIN orders_details ON books.isbn = orders_details.book_id
GROUP BY isbn) AS s ON s.isbn LIKE books.isbn
GROUP BY books.isbn) AS o
ORDER BY sold DESC, rate DESC
);Result:
Let’s code
Let’s create a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Web, Spring Data JPA, PostgreSQL Driver, and Validation.
Entity Class for the View
First, let us start by creating a Java entity class, BooksRank, which represents the structure of the database view books_rank.
@Entity
@Immutable
@Table(name = "books_rank") // The name of the view
public class BooksRank {
@Id
@Column(name = "isbn")
private String isbn;
@Column(name = "title")
private String title;
@Column(name = "rate")
private BigDecimal rate;
@Column(name = "sold")
private Long sold;
@Column(name = "genres")
private List<String> genres;
// Getters and Setters
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public BigDecimal getRate() {
return rate;
}
public void setRate(BigDecimal rate) {
this.rate = rate;
}
public Long getSold() {
return sold;
}
public void setSold(Long sold) {
this.sold = sold;
}
public List<String> getGenres() {
return genres;
}
public void setGenres(List<String> genres) {
this.genres = genres;
}
}The model will be annotated with @Entity, @Tableand @Immutableannotations to make it immutable and read-only.
An immutable entity may not be updated by the application. Updates to an immutable entity will be ignored, but no exception is thrown. @Immutable must be used on root entities only.
The isbn field is chosen as @Id because it is a primary key in the books table. If in your DB view, you don’t have a unique key, you need to generate an @Id in your entity with a subselect.
@Entity
@Immutable
@Subselect("select uuid_in(md5(random()::text || random()::text)::cstring) as id, br.* from books_rank br")
@Table(name = "books_rank") // The name of the view
public class BooksRank {
@Id
private String id;
@Column(name = "isbn")
private String isbn;
.....
}Create a Spring Data repository
Now that we have an entity class, we can define a repository interface that uses it, as follows:
@Repository
public interface BooksRankRepository extends PagingAndSortingRepository<BooksRank, String> {
}The BooksRankRepository interface inherits all the read-only methods defined in thePagingAndSortingRepository
Business service layer
Create a service that uses BooksRankRepository to implement getAll Operations.
@Service
@Transactional
public class BooksRankServiceImpl implements BooksRankService {
private final BooksRankRepository repository;
public BooksRankServiceImpl(BooksRankRepository repository) {
this.repository = repository;
}
/**
* {@inheritDoc}
*/
@Override
public List<BooksRank> getAll() {
var books = repository.findAll(Sort.by(Sort.Direction.ASC, "title"));
return StreamSupport
.stream(books.spliterator(), false)
.toList();
}
/**
* {@inheritDoc}
*/
@Override
public Page<BooksRank> getPagination(Pageable pageable) {
return repository.findAll(pageable);
}
}Create a REST API endpoint
Finally, we create a controller that provides APIs for retrieving data from the database view.
@RestController
@RequestMapping("/api/books-rank")
public class BookController {
private final Logger log = LoggerFactory.getLogger(BookController.class);
private final BooksRankService entityService;
public BookController(BooksRankService entityService) {
this.entityService = entityService;
}
/**
* {@code GET /books-rank} : get all the books.
*
* @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of book in body.
*/
@GetMapping
public ResponseEntity<List<BooksRank>> getAllBook() {
log.debug("REST request to get all books rank");
List<BooksRank> lst = entityService.getAll();
return new ResponseEntity<>(lst, HttpStatus.OK);
}
/**
* @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the book, or with status {@code 404 (Not Found)}.
*/
@GetMapping(value = "/page")
public ResponseEntity<Page<BooksRank>> getOneBook(@RequestParam(defaultValue = "0") int page, @RequestParam(defaultValue = "20") int size) {
log.debug("REST request to get Books rank pagination");
Pageable paging = PageRequest.of(page, size);
var response = entityService.getPagination(paging);
return new ResponseEntity<>(response, HttpStatus.OK);
}
}Test the REST APIs
- Get all books rank
- Get books ranked by pagination
Conclusion
Well done !!. In this post, we explored using Spring Data JPA with PostgreSQL views in a Spring Boot application. By understanding and using database views effectively, you can simplify data access, improve security, and enhance the maintainability of your database applications.
The complete source code is available on GitHub.
Support me through GitHub Sponsors.
Thank you for reading!! See you in the next post.
