Best Design Approach to fetch Huge Data from multiple table joins from Database in Spring Boot Application

In an Enterprise Spring Boot application, mapping database table to entity object is very easy using JPA / CRUD repository. If tables are dependent, still JPA repository provided easy solution. However, sometimes our SQL query is so complex involving multiple independent tables to fetch data that it is very difficult to express them using JPA Query Language. In such cases we adopt following approach.

1. Create a database view and map it to an entity.
2. Execute a native query and map the result to a entity/DTO.

The database view approach is better is if you want to implement write operations. However, if you just want to implement read operation, I suggest native query approach.?

?So after analysing system design and requirement, I found there are 4 ways to support our design requirement.

1. Pure Native query in the repository (no view - no immutable entity)

In repository just have a method with the native query.

2. With a database view and native query in the repository

Creating a database view.

1. With a database view and an immutable entity that select the view and JPQL query in repository

Using an entity (immutable) to encapsulate the view.

4. No view but immutable entity with the query in the @Subselect and JPQL query in repository

No view on the database but the immutable entity has the query in the @Subselect annotation with the view query' and the @Synchronize annotation.

Performance Analysis

Database view is just a relation that is expanded into a query plan, usually during parse time, so there should be very little to no notable difference when using a view vs writing out the whole query directly. Using a view allows you to reuse the query more easily but when you want to change something, you will have to change the view and maybe also all applications that use the view, so the reusability aspect can bite you back.

I usually don't recommend views because in general cases people put in a lot of unnecessary joins into the views for the sole purpose of "making it easier" for others. The problem with joins that aren't used is, that databases usually can't eliminate them. TLDR, I recommend writing out the queries directly in the code because you can omit joins that you don't need and thus get better performance.

Specific Requirement Case

Should you have full control of the database rather than just an application specific requirement creating an actual view is much better than embedded SQL.?

The database view approach is better is if you want to implement write operations. However, if you just want to implement read operation, i suggest native query approach.

As our focus in optimising read operation hence now we are left with option 1 & 4(Native SQL queries). Lets explore these 2 more in details. There are two ways to execute Native SQL query.

1. Using @Subselect in entity class.
2. Using @Query?in repository class.

@Subselect

In general, the difference between?@Subselect?and entity-to-view mapping is very small. The former uses the exact SQL statement that we provide in annotation, while the latter uses an existing view. Both approaches support managed associations, so choosing one of these options entirely depends on our requirements.

Why & Why not use @Subselect

1. Use the Hibernate-specific @Subselect annotation, in case you don’t want to map the query to a database view.
2. Use @Subselect to select entities not from a particular table but from a sub-select query. This is very convenient if we do not want to duplicate the same parts of SQL statements to fetch our entities. But this implies that our entities that use @Subselect are de-facto immutable, and we should not try to persist them from the application code.

Before you use @Subselect mapping, you need to be aware of two side effects:

1. You can’t use this entity to perform any write operations. Hibernate would try to execute the operation on the SQL statement provided by the @Subselect annotation. You should, therefore, annotate the entity with @Immutable, use the field-based access strategy and omit all setter methods.
2. Hibernate doesn’t know which database tables are used by the SQL statement configured in the?@Subselect annotation. You can provide this information by annotating the entity with?@Synchronize. That enables Hibernate to flush pending state transitions on tables table1, table2,table3 and table4 etc. entities before selecting a Table Status entity.

@Query

With @Query Spring Data JPA provides the required JPA code to execute the statement as a JPQL or native SQL query with Your preferred JPA implementation,? such as Hibernate and will then execute the query and map the result.

Another advantage of using the Spring Data JPA @Query annotation is related to code manageability.

With @Query, you are binding the queries to the Java method that executes them. By using this approach over annotating the domain class, you free the domain class from storing any persistence specific information. Also, it is convenient to co-locate the queries in the repository interface.

Native queries(@Query) are the most powerful and flexible way to implement your read operations. They enable you to use all features supported by your database, and Spring Data JPA handles almost all of the required boilerplate code

@Query annotation supports both JPQL and native SQL queries.

Also add Pagination and Sorting feature to support Native queries. Why Pagination and Sorting are Essential ?

Scalability:?As the amount of data grows, efficiently retrieving a manageable subset becomes vital to ensure application performance.

User Experience:?From an end-user perspective, presenting data in a paginated and sorted manner enhances readability and navigation.

Conclusion

Spring Data JPA’s?@Query?annotation offers developers a powerful tool for intricate data access needs. Through our exploration, we've seen its versatility from basic operations to advanced technique. However, with this power comes the responsibility to use it judiciously. By adhering to best practices, developers can ensure efficient, scalable, and secure applications. In essence, the?@Query?annotation, combined with knowledge and best practices, enables the creation of robust data-driven applications with Spring.

?