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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store