Spring Boot Way to Replace Views in SQL

Question

Context

I've inherited a small Spring-based project --it tracks servers, software installations, etc for or teams internal use. It uses Angular front end and Spring/java/mysql back end for a restful back end.

I've converted it to Spring boot over the last few months. I'm new to Spring and Spring boot. So I'm learning as I go along.

I started replacing the 'hand coded' sql with JPA ("spring-data-rest"). JPA has been easy for some tables, but not for others. For these "others": The hand-coded SQL uses "views" when retrieving data .

Approach

Specifically, it uses this approach:

• When it creates or updates a table (e.g. "sites" table, as in "web site"), it passes in the "ID's:" Server_id, product_id, customer_id etc.

• The software retrieves from a "sites view" that joins on the server , product, and customer tables, so that the java can retrieve server name, product name, customer name, etc. without a separate database call.

Questions

• What is the cleanest, most-Spring-boot-ish way to convert these "read-only views" to JPA?

• What generally is the cleanest way to handle 'star joins' in Spring/JPA? (where one central table joins with several other tables to give the user the data he/she needs)

thanks

Answer 1

I assume you can use the usual projection with native query to get data from views. Something like this:

public interface ViewResult {
    String getField1();
    Integer getField2();
    Double getField3();
    //...
}

public interface SomeRepository extends JpaRepository<SomeEntity, Long> {
    @Query(value = "select v.field1 as field1, v.field2 as field2, v.field3 as field3 from some_view v", nativeQuery = true)
    List<ViewResult> getViewResult();
}

To work with complex query you can also use projections:

@Query("select a as abc, b as bcd, c as cde from Abc a join a.bcd b join a.cde c")
List<AbcBcdCde> getComplexData();

where AbcBcdCde is a projection:

public interface AbcBcdCde {
    Abc getAbc();
    Bcd getBcd();
    Cde getCde();
}