JPA — Reuse Projection

This application is a sample of reusing an interface-based Spring projection. This is useful to avoid defining multiple interface-based Spring projections in order to cover a range of queries that fetches different subsets of fields.

Key points:

• define an interface-based Spring projection containing getters for the wider case.
• rely on class-level @JsonInclude(JsonInclude.Include.NON_DEFAULT) annotation to avoid serialization of default fields (e.g., fields that are not available in the current projection and are null - these fields haven't been fetched in the current query)
• this is useful to Jackson that will not serialize in the resulted JSON the missing fields (e.g., null fields)

Author.java

@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String genre;
    private int age;
    private String email;
    private String address;
    private String rating;
}

AuthorDto.java

@JsonInclude(JsonInclude.Include.NON_DEFAULT)
public interface AuthorDto {

    public Integer getAge();
    public String getName();
    public String getGenre();
    public String getEmail();
    public String getAddress();
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("SELECT a.age AS age, a.name AS name, a.genre AS genre, "
            + "a.email AS email, a.address AS address FROM Author a")
    List<AuthorDto> fetchAll();
    
    @Query("SELECT a.age AS age, a.name AS name, a.genre AS genre FROM Author a")
    List<AuthorDto> fetchAgeNameGenre();

    @Query("SELECT a.name AS name, a.email AS email FROM Author a")
    List<AuthorDto> fetchNameEmail();
}

BookStoreService.java

@RequiredArgsConstructor
@Service
public class BookstoreService {

    private final AuthorRepository authorRepository;

    @Transactional(readOnly = true)
    public List<AuthorDto> fetchAll() {
        return authorRepository.fetchAll();
    }
    
    @Transactional(readOnly = true)
    public List<AuthorDto> fetchAgeNameGenre() {
        return authorRepository.fetchAgeNameGenre();
    }

    @Transactional(readOnly = true)
    public List<AuthorDto> fetchNameEmail() {
        return authorRepository.fetchNameEmail();
    }
}

application.poperties

spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

spring.jpa.open-in-view=false

spring.datasource.initialization-mode=always
spring.datasource.platform=mysql

data-mysql

insert into author (age, name, genre, email, address, rating, id) values (23, "Mark Janel", "Anthology", "markj@gmail.com", "mark's address", 99, 1);
insert into author (age, name, genre, email, address, rating, id) values (43, "Olivia Goy", "Horror", "oliviag@gmail.com", "olivia's address", 89, 2);
insert into author (age, name, genre, email, address, rating, id) values (51, "Quartis Young", "Anthology", "young@gmail.com", "quartis's address", 84, 3);
insert into author (age, name, genre, email, address, rating, id) values (34, "Joana Nimar", "History", "jn@gmail.com", "joana's address", 95, 4);
insert into author (age, name, genre, email, address, rating, id) values (33, "Marin Kyrab", "History", "marin@gmail.com", "marin's address", 82, 5);

Result —

Fetch all:
---------------------------------
Hibernate: select author0_.age as col_0_0_, author0_.name as col_1_0_, author0_.genre as col_2_0_, author0_.email as col_3_0_, author0_.address as col_4_0_ from author author0_
Fetch ALL = 5

Fetch age, name and genre:
---------------------------------
Hibernate: select author0_.age as col_0_0_, author0_.name as col_1_0_, author0_.genre as col_2_0_ from author author0_
fetchAgeNameGenre = 5

Fetch name and email:
---------------------------------
Hibernate: select author0_.name as col_0_0_, author0_.email as col_1_0_ from author author0_
fetchNameEmail =5

Here is the some useful properties

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=root

spring.datasource.hikari.data-source-properties.reWriteBatchedInserts=true

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format-sql=true

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.properties.hibernate.jdbc.batch_size = 3
spring.jpa.properties.hibernate.generate_statistics=true

spring.jpa.open-in-view=false