JPA — How To Fetch DTO via javax.persistence.Tuple and JPQL

Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely on javax.persistence.Tuple and JPQL.

Key points:

• use java.persistence.Tuple in a Spring repository
• for using Spring Data Projections check this item

Author.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private int age;
    private String name;
    private String genre;
}

AuthorRepostory.java

import java.util.List;
import com.bookstore.entity.Author;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.Tuple;
import org.springframework.data.jpa.repository.Query;

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

    @Transactional(readOnly = true)
    @Query(value = "SELECT a.id AS id, a.name AS name, a.age AS age FROM Author a")
    List<Tuple> fetchAuthors();
}

BookStoreService.java

import java.util.List;
import com.bookstore.repository.AuthorRepository;
import javax.persistence.Tuple;

import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final AuthorRepository authorRepository;

    public List<Tuple> fetchAuthors() {
        return authorRepository.fetchAuthors();
    }
}

MainApp.java

import java.util.List;
import com.bookstore.service.BookstoreService;
import javax.persistence.Tuple;

import lombok.RequiredArgsConstructor;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {

    private final BookstoreService bookstoreService;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {
            List<Tuple> authors = bookstoreService.fetchAuthors();
            System.out.println("Number of authors:" + authors.size());

            for (Tuple author : authors) {
                System.out.println("Author name: " + author.get("name") + " | Age: " + author.get("age")+" | Id: "+ author.get("id"));
            }
            System.out.println("\n------------------");
            System.out.println(authors.get(0).get("name") instanceof String);
            System.out.println(authors.get(0).get("age") instanceof Integer);
        };
    }

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true
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.MySQL8Dialect

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

spring.jpa.open-in-view=false

data-mysql.sql

insert into author (age, name, genre, id) values (23, "Mark Janel", "Anthology", 1);
insert into author (age, name, genre, id) values (43, "Olivia Goy", "Horror", 2);
insert into author (age, name, genre, id) values (51, "Quartis Young", "Anthology", 3);
insert into author (age, name, genre, id) values (34, "Joana Nimar", "History", 4);
insert into author (age, name, genre, id) values (38, "Alicia Tom", "Anthology", 5);
insert into author (age, name, genre, id) values (56, "Katy Loin", "Anthology", 6);

output.console

Number of authors:6
Author name: Mark Janel | Age: 23 | Id: 1
Author name: Olivia Goy | Age: 43 | Id: 2
Author name: Quartis Young | Age: 51 | Id: 3
Author name: Joana Nimar | Age: 34 | Id: 4
Author name: Alicia Tom | Age: 38 | Id: 5
Author name: Katy Loin | Age: 56 | Id: 6

— — — — — — — — — — — — — — — — — — — — —

How To Fetch DTO Via javax.persistence.Tuple And Native SQL

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely on javax.persistence.Tuple and native SQL.

Key points:

• use java.persistence.Tuple in a Spring repository and mark the query as nativeQuery = true
• for using Spring Data Projections check this item

AuthorRepository.java

import java.util.List;
import com.bookstore.entity.Author;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.Tuple;
import org.springframework.data.jpa.repository.Query;

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

    @Transactional(readOnly = true)
    @Query(value = "SELECT name, age FROM author", nativeQuery = true)
    List<Tuple> fetchAuthors();
}

— — — — — — — — — — — — — — — — — — — — — — —

How To Exploit Spring Projections(DTO) And Join Unrelated Entities In Hibernate 5.1+

Description: This application is a proof of concept for using Spring Projections (DTO) and join unrelated entities. Hibernate 5.1 introduced explicit joins on unrelated entities and the syntax and behaviour are similar to SQL JOIN statements.

Key points:

• define serveral entities (e.g., Author and Book unrelated entities)
• populate the database with some test data (e.g., check the file resources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., BookstoreDto)
• write joins queries using JPQL/SQL (e.g., queries all authors names and book titles of the given price)

Author.java

@AllArgsConstructor
@NoArgsConstructor
@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;
}

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String name;
    private String title;
    private String isbn;
    private int price;
}

AuthorRepository.java

import com.bookstore.dto.BookstoreDto;
import com.bookstore.entity.Author;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {
 
    // Query all author names and their titles with the given price
    
    @Transactional(readOnly = true)    
    @Query(value = "SELECT a.name AS name, b.title AS title "
            + "FROM Author a INNER JOIN Book b ON a.name = b.name "
            + "WHERE b.price = ?1")
    List<BookstoreDto> fetchAuthorNameBookTitleWithPrice(int price);
}

BookRepository.java

import com.bookstore.entity.Author;
import com.bookstore.entity.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

    @Transactional(readOnly = true)
    Book findByTitle(String title);
}

BookStoreService.java

import com.bookstore.dto.BookstoreDto;
import com.bookstore.repository.BookRepository;
import com.bookstore.repository.AuthorRepository;
import java.util.List;

import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final AuthorRepository authorRepository;
    private final BookRepository bookRepository;

    public List<BookstoreDto> fetchAuthorNameBookTitleWithPrice(int price) {
        return authorRepository.fetchAuthorNameBookTitleWithPrice(price);
    }
}

BookStoreDto.java

public interface BookstoreDto {
    String getName();
    String getTitle();    
}

MainApp.java

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {
    
    private final BookstoreService bookstoreService;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {            
            List<BookstoreDto> result = bookstoreService.fetchAuthorNameBookTitleWithPrice(42);
            result.forEach(r -> System.out.println("Author: " + r.getName() + "  Title: " + r.getTitle()));
        };
    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true
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.MySQL8Dialect

spring.jpa.open-in-view=false

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

data-mysql.sql

insert into author (age, name, genre, id) values (23, "Mark Janel", "Anthology", 1);
insert into author (age, name, genre, id) values (43, "Olivia Goy", "Horror", 2);
insert into author (age, name, genre, id) values (51, "Quartis Young", "Anthology", 3);
insert into author (age, name, genre, id) values (34, "Joana Nimar", "History", 4);
insert into author (age, name, genre, id) values (44, "Kym Less", "Anthology", 5);
insert into book (name, isbn, title, price, id) values ("Joana Nimar", "001-JN", "A History of Ancient Prague", 42, 1);
insert into book (name, isbn, title, price, id) values ("Joana Nimar", "002-JN", "A People's History", 40, 2);
insert into book (name, isbn, title, price, id) values ("Mark Janel", "001-MJ", "The Beatles Anthology", 42, 3);
insert into book (name, isbn, title, price, id) values ("Olivia Goy", "001-OG", "Carrie", 42, 4);
insert into book (name, isbn, title, price, id) values ("Quartis Young", "001-QY", "War Anthology", 42, 5);
insert into book (name, isbn, title, price, id) values ("Mark Janel", "002-MJ", "100 Days", 40, 6);
insert into book (name, isbn, title, price, id) values ("Kim Less", "001-KL", "Modern Anthology", 42, 7);

Output —

Hibernate: select author0_.name as col_0_0_, book1_.title as col_1_0_ from author author0_ inner join book book1_ on (author0_.name=book1_.name) where book1_.price=?
Author: Mark Janel Title: The Beatles Anthology
Author: Olivia Goy Title: Carrie
Author: Quartis Young Title: War Anthology
Author: Joana Nimar Title: A History of Ancient Prague

— — — — — — — — — — — — — — — — — — — — — — —

DTO Via Spring Data Class-Based Projections

Description: Fetch only the needed data from the database via Spring Data Projections (DTO). In this case, via class-based projections.

Key points:

• write an class (projection) containing a constructor, getters, setters, equals() and hashCode() only for the columns that should be fetched from the database
• write the proper query returning a List<projection>
• if it is applicable, limit the number of returned rows (e.g., via LIMIT)
• in this example, we can use query builder mechanism built into Spring Data repository infrastructure

Note: Using projections is not limited to use query builder mechanism built into Spring Data repository infrastructure. We can fetch projections via JPQL or native queries as well. For example, in this application we use a JPQL.

Author.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private int age;
    private String name;
    private String genre;
}

AuthorNameAge.java

import lombok.AllArgsConstructor;
import lombok.Data;

// Avoid Using NoArgs constrictor
@AllArgsConstructor
@Data
public class AuthorNameAge {
    private String name;
    private int age;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {      
    
    @Transactional(readOnly = true)
    List<AuthorNameAge> findFirst2ByGenre(String genre);
}

BookService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final AuthorRepository authorRepository;

    public List<AuthorNameAge> fetchFirst2ByBirthplace() {
        return authorRepository.findFirst2ByGenre("Anthology");
    }
}

MainApp.java

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {

    private final BookstoreService bookstoreService;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {
            List<AuthorNameAge> authors = bookstoreService.fetchFirst2ByBirthplace();
            System.out.println("Number of authors:" + authors.size());

            for (AuthorNameAge author : authors) {
                System.out.println("Author name: " + author.getName() + " | Age: " + author.getAge());
            }
        };
    }
}

application.properties

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.datasource.initialization-mode=always
spring.datasource.platform=mysql

spring.jpa.open-in-view=false

data-mysql.sql

insert into author (age, name, genre, id) values (23, "Mark Janel", "Anthology", 1);
insert into author (age, name, genre, id) values (43, "Olivia Goy", "Horror", 2);
insert into author (age, name, genre, id) values (51, "Quartis Young", "Anthology", 3);
insert into author (age, name, genre, id) values (34, "Joana Nimar", "History", 4);
insert into author (age, name, genre, id) values (38, "Alicia Tom", "Anthology", 5);
insert into author (age, name, genre, id) values (56, "Katy Loin", "Anthology", 6);

Hibernate: select author0_.name as col_0_0_, author0_.age as col_1_0_ from author author0_ where author0_.genre=? limit ?
Number of authors:2
Author name: Mark Janel | Age: 23
Author name: Quartis Young | Age: 51

— — — — — — — — — — — — — — — — — — — — — — — — — -

How To Use Spring Projections(DTO) And Cross Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and cross joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g., Book and Format
• populate the database with some test data (e.g., check the file resources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., check BookTitleAndFormatType.java)
• write cross joins queries using JPQL/SQL

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String title;
    private String isbn;
}

Format.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Format implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String formatType;
}

BookTitleAndFormatType.java

public interface BookTitleAndFormatType {
    String getTitle();
    String getFormatType();
}

BookRepository.java

@Repository
@Transactional(readOnly = true)
public interface BookRepository extends JpaRepository<Book, Long> {

    // Cross join books and formats (JPQL)    
    @Query(value = "SELECT b.title AS title, f.formatType AS formatType "
            + "FROM Book b, Format f")
    List<BookTitleAndFormatType> findBooksAndFormatsJpql();
    
    // Cross join books and formats (SQL)    
    @Query(value = "SELECT b.title AS title, f.format_type AS formatType "
            + "FROM book b CROSS JOIN format f",
            nativeQuery = true)
    List<BookTitleAndFormatType> findBooksAndFormatsSql();        
}

FormatRepository.java

@Repository
@Transactional(readOnly = true)
public interface FormatRepository extends JpaRepository<Format, Long> {

    // Cross join formats and books (JPQL)    
    @Query(value = "SELECT b.title AS title, f.formatType AS formatType "
            + "FROM Format f, Book b")
    List<BookTitleAndFormatType> findFormatsAndBooksJpql();

    // Cross join formats and books (SQL)    
    @Query(value = "SELECT b.title AS title, f.format_type AS formatType "
            + "FROM format f CROSS JOIN book b",
            nativeQuery = true)
    List<BookTitleAndFormatType> findFormatsAndBooksSql();
}

BookStoreService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final FormatRepository formatRepository;
    private final BookRepository bookRepository;

    // Cross join books and formats (JPQL)    
    public List<BookTitleAndFormatType> fetchBooksAndFormatsJpql() {
        return bookRepository.findBooksAndFormatsJpql();
    }

    // Cross join books and formats (SQL)    
    public List<BookTitleAndFormatType> fetchBooksAndFormatsSql() {
        return bookRepository.findBooksAndFormatsSql();
    }
    
    // Cross join formats and books (JPQL)    
    public List<BookTitleAndFormatType> fetchFormatsAndBooksJpql() {
        return formatRepository.findFormatsAndBooksJpql();
    }
    
    // Cross join formats and books (SQL)    
    public List<BookTitleAndFormatType> fetchFormatsAndBooksSql() {
        return formatRepository.findFormatsAndBooksSql();
    }
}

MainApp.java

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {

    private final BookstoreService bookstoreService;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {

            System.out.println("\nfetchBooksAndFormatsJpql: ");
            bookstoreService.fetchBooksAndFormatsJpql()
                    .forEach((e) -> System.out.println(e.getTitle() + " | " + e.getFormatType()));

            System.out.println("\nfetchBooksAndFormatsSql: ");
            bookstoreService.fetchBooksAndFormatsSql()
                    .forEach((e) -> System.out.println(e.getTitle() + " | " + e.getFormatType()));

            System.out.println("\nfetchFormatsAndBooksJpql: ");
            bookstoreService.fetchFormatsAndBooksJpql()
                    .forEach((e) -> System.out.println(e.getTitle() + " | " + e.getFormatType()));

            System.out.println("\nfetchFormatsAndBooksSql: ");
            bookstoreService.fetchFormatsAndBooksSql()
                    .forEach((e) -> System.out.println(e.getTitle() + " | " + e.getFormatType()));
        };
    }
}

application.property

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.MySQL8Dialect

spring.jpa.open-in-view=false

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

data-mysql.java

insert into book (isbn, title, id) values ("001-JN", "A History of Ancient Prague", 1);
insert into book (isbn, title, id) values ("002-JN", "A People's History", 2);
insert into book (isbn, title, id) values ("001-OG", "Carrie", 3);

insert into format (format_type, id) values ("paperback", 1);
insert into format (format_type, id) values ("kindle", 2);
insert into format (format_type, id) values ("pdf", 3);

fetchBooksAndFormatsJpql:
Hibernate: select book0_.title as col_0_0_, format1_.format_type as col_1_0_ from book book0_ cross join format format1_
A History of Ancient Prague | paperback
A People’s History | paperback
Carrie | paperback
A History of Ancient Prague | kindle
A People’s History | kindle
Carrie | kindle
A History of Ancient Prague | pdf
A People’s History | pdf
Carrie | pdf

fetchBooksAndFormatsSql:
Hibernate: SELECT b.title AS title, f.format_type AS formatType FROM book b CROSS JOIN format f
A History of Ancient Prague | paperback
A People’s History | paperback
Carrie | paperback
A History of Ancient Prague | kindle
A People’s History | kindle
Carrie | kindle
A History of Ancient Prague | pdf
A People’s History | pdf
Carrie | pdf

fetchFormatsAndBooksJpql:
Hibernate: select book1_.title as col_0_0_, format0_.format_type as col_1_0_ from format format0_ cross join book book1_
A History of Ancient Prague | paperback
A History of Ancient Prague | kindle
A History of Ancient Prague | pdf
A People’s History | paperback
A People’s History | kindle
A People’s History | pdf
Carrie | paperback
Carrie | kindle
Carrie | pdf

fetchFormatsAndBooksSql:
Hibernate: SELECT b.title AS title, f.format_type AS formatType FROM format f CROSS JOIN book b
A History of Ancient Prague | paperback
A History of Ancient Prague | kindle
A History of Ancient Prague | pdf
A People’s History | paperback
A People’s History | kindle
A People’s History | pdf
Carrie | paperback
Carrie | kindle
Carrie | pdf

— — — — — — — — — — — — — — — -

How To Use Spring Projections(DTO) And Inclusive Full Joins (PostgreSQL)

Description: This application is a proof of concept for using Spring Projections(DTO) and inclusive full joins written via JPQL and native SQL (for PostgreSQL).

Key points:

• define two entities (e.g., Author and Book in a (lazy) bidirectional @OneToMany association)
• populate the database with some test data (e.g., check the file resources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., check AuthorNameBookTitle.java)
• write inclusive full joins queries using JPQL/SQL

Author.java

@AllArgsConstructor
@NoArgsConstructor
@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;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "author", orphanRemoval = true)
    private List<Book> books = new ArrayList<>();

    public void addBook(Book book) {
        this.books.add(book);
        book.setAuthor(this);
    }

    public void removeBook(Book book) {
        book.setAuthor(null);
        this.books.remove(book);
    }
}

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String title;
    private String isbn;
    private int price;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;
}

AuthorBookNameTitle.java

public interface AuthorNameBookTitle {
    String getName();
    String getTitle();    
}

AuthorRepository.java

@Repository
@Transactional(readOnly = true)
public interface AuthorRepository extends JpaRepository<Author, Long> {

    // Fetch authors and books including authors that have no registered books and books with no registered authors (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Author a FULL JOIN a.books b")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql();

    // Fetch authors and books including authors that have no registered books and books with no registered authors (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM author a FULL JOIN book b ON a.id = b.author_id",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql();    
}

BookRepository.java

@Repository
@Transactional(readOnly = true)
public interface BookRepository extends JpaRepository<Book, Long> {

    // Fetch books and authors including authors that have no registered books and books with no registered authors (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Book b FULL JOIN b.author a")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql();

    // Fetch books and authors including authors that have no registered books and books with no registered authors (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM book b FULL JOIN author a ON a.id = b.author_id",
            nativeQuery = true)
    List<AuthorNameBookTitle> findBooksAndAuthorsSql();        
}

BookStoreService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final AuthorRepository authorRepository;
    private final BookRepository bookRepository;

    // Fetch books and authors including authors that have no registered books and books with no registered authors (JPQL)    
    public List<AuthorNameBookTitle> fetchBooksAndAuthorsJpql() {
        return bookRepository.findBooksAndAuthorsJpql();
    }

    // Fetch books and authors including authors that have no registered books and books with no registered authors (SQL)    
    public List<AuthorNameBookTitle> fetchBooksAndAuthorsSql() {
        return bookRepository.findBooksAndAuthorsSql();
    }

    // Fetch authors and books including authors that have no registered books and books with no registered authors (JPQL)    
    public List<AuthorNameBookTitle> fetchAuthorsAndBooksJpql() {
        return authorRepository.findAuthorsAndBooksJpql();
    }

    // Fetch authors and books including authors that have no registered books and books with no registered authors (SQL)    
    public List<AuthorNameBookTitle> fetchAuthorsAndBooksSql() {
        return authorRepository.findAuthorsAndBooksSql();
    }
}

MainApp.java

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {

    private final BookstoreService bookstoreService;
    
    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {

            System.out.println("\nfetchBooksAndAuthorsJpql: ");
            bookstoreService.fetchBooksAndAuthorsJpql()
                    .forEach((e) -> System.out.println(e.getName() + " | " + e.getTitle()));

            System.out.println("\nfetchBooksAndAuthorsSql: ");
            bookstoreService.fetchBooksAndAuthorsSql()
                    .forEach((e) -> System.out.println(e.getName() + " | " + e.getTitle()));

            System.out.println("\nfetchAuthorsAndBooksJpql: ");
            bookstoreService.fetchAuthorsAndBooksJpql()
                    .forEach((e) -> System.out.println(e.getName() + " | " + e.getTitle()));

            System.out.println("\nfetchAuthorsAndBooksSql: ");
            bookstoreService.fetchAuthorsAndBooksSql()
                    .forEach((e) -> System.out.println(e.getName() + " | " + e.getTitle()));
        };
    }
}

application.properties

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

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

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

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

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

spring.jpa.open-in-view=false

data-postgres.sql

insert into author (age, name, genre, id) values (23, 'Mark Janel', 'Anthology', 1);
insert into author (age, name, genre, id) values (43, 'Olivia Goy', 'Horror', 2);
insert into author (age, name, genre, id) values (51, 'Quartis Young', 'Anthology', 3);
insert into author (age, name, genre, id) values (34, 'Joana Nimar', 'History', 4);
insert into author (age, name, genre, id) values (37, 'Larisa Tomay', 'History', 5);
insert into book (isbn, title, price, author_id, id) values ('001-JN', 'A History of Ancient Prague', 34, 4, 1);
insert into book (isbn, title, price, author_id, id) values ('002-JN', 'Waste Of History', 44, 4, 2);
insert into book (isbn, title, price, author_id, id) values ('001-OG', 'Carrie', 33, 2, 4);
insert into book (isbn, title, price, author_id, id) values ('001-null', 'Lost book', 0, null, 5);

fetchBooksAndAuthorsJpql:
Hibernate: select book0_.title as col_0_0_, author1_.name as col_1_0_ from book book0_ full outer join author author1_ on book0_.author_id=author1_.id
Joana Nimar | A History of Ancient Prague
Joana Nimar | Waste Of History
Olivia Goy | Carrie
null | Lost book
Larisa Tomay | null
Mark Janel | null
Quartis Young | null

fetchBooksAndAuthorsSql:
Hibernate: SELECT b.title AS title, a.name AS name FROM book b FULL JOIN author a ON a.id = b.author_id
Joana Nimar | A History of Ancient Prague
Joana Nimar | Waste Of History
Olivia Goy | Carrie
null | Lost book
Larisa Tomay | null
Mark Janel | null
Quartis Young | null

fetchAuthorsAndBooksJpql:
Hibernate: select books1_.title as col_0_0_, author0_.name as col_1_0_ from author author0_ full outer join book books1_ on author0_.id=books1_.author_id
Mark Janel | null
Olivia Goy | Carrie
Quartis Young | null
Joana Nimar | Waste Of History
Joana Nimar | A History of Ancient Prague
Larisa Tomay | null
null | Lost book

fetchAuthorsAndBooksSql:
Hibernate: SELECT b.title AS title, a.name AS name FROM author a FULL JOIN book b ON a.id = b.author_id
Mark Janel | null
Olivia Goy | Carrie
Quartis Young | null
Joana Nimar | Waste Of History
Joana Nimar | A History of Ancient Prague
Larisa Tomay | null
null | Lost book

— — — — — — — — — — — — -

@Repository
@Transactional(readOnly = true)
public interface AuthorRepository extends JpaRepository<Author, Long> {

    // Fetch all authors and books (SQL)    
    @Query(value = "(SELECT b.title AS title, a.name AS name FROM author a "
            + "LEFT JOIN book b ON a.id = b.author_id) "
            + "UNION " //  will remove duplicates (use UNION ALL to keep duplicates)
            + "(SELECT b.title AS title, a.name AS name FROM author a "
            + "RIGHT JOIN book b ON a.id = b.author_id "
            + "WHERE a.id IS NULL)",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql();
}

New

@Repository
@Transactional(readOnly = true)
public interface AuthorRepository extends JpaRepository<Author, Long> {

    // Fetch authors and books excluding authors that have registered books and books with registered authors (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Author a FULL JOIN a.books b WHERE a.id IS NULL OR b.id IS NULL")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql();

    // Fetch authors and books excluding authors that have registered books and books with registered authors (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM author a FULL JOIN book b ON a.id = b.author_id WHERE a.id IS NULL OR b.id IS NULL",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql();

BookRepository.java

@Repository
@Transactional(readOnly = true)
public interface BookRepository extends JpaRepository<Book, Long> {

    // Fetch books and authors excluding authors that have registered books and books with registered authors (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Book b FULL JOIN b.author a WHERE b.id IS NULL OR a.id IS NULL")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql();

    // Fetch books and authors excluding authors that have registered books and books with registered authors (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM book b FULL JOIN author a ON a.id = b.author_id WHERE b.id IS NULL OR a.id IS NULL",
            nativeQuery = true)
    List<AuthorNameBookTitle> findBooksAndAuthorsSql();        
}

InnerJoins

@Repository
@Transactional(readOnly = true)
public interface AuthorRepository extends JpaRepository<Author, Long> {

    // Fetch authors and books (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Author a INNER JOIN a.books b")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql();

    // Fetch authors and books (SQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM author a INNER JOIN book b ON a.id = b.author_id",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql();

    // Fetch authors and books filtering by author's genre and book's price (JPQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Author a INNER JOIN a.books b WHERE a.genre = ?1 AND b.price < ?2")
    List<AuthorNameBookTitle> findAuthorsAndBooksByGenreAndPriceJpql(String genre, int price);

    // Fetch authors and books filtering by author's genre and book's price (SQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM author a INNER JOIN book b ON a.id = b.author_id WHERE a.genre = ?1 AND b.price < ?2",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksByGenreAndPriceSql(String genre, int price);
}

BookRepository.java

@Repository
@Transactional(readOnly = true)
public interface BookRepository extends JpaRepository<Book, Long> {

    // Fetch books and authors (JPQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Book b INNER JOIN b.author a")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql();

    // Fetch books and authors (SQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM book b INNER JOIN author a ON a.id = b.author_id",
            nativeQuery = true)
    List<AuthorNameBookTitle> findBooksAndAuthorsSql();
    
    // Fetch books and authors filtering by author's genre and book's price (JPQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Book b INNER JOIN b.author a WHERE a.genre = ?1 AND b.price < ?2")
    List<AuthorNameBookTitle> findBooksAndAuthorsByGenreAndPriceJpql(String genre, int price);

    // Fetch books and authors filtering by author's genre and book's price (SQL)
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM book b INNER JOIN author a ON a.id = b.author_id WHERE a.genre = ?1 AND b.price < ?2",
            nativeQuery = true)
    List<AuthorNameBookTitle> findBooksAndAuthorsByGenreAndPriceSql(String genre, int price);
}

— — — — — -

@Repository
@Transactional(readOnly = true)
public interface BookRepository extends JpaRepository<Book, Long> {

    // Fetch books and authors excluding books that have registered authors (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Book b LEFT JOIN b.author a WHERE a.id IS NULL")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql();

    // Fetch books and authors excluding books that have registered authors (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM book b LEFT JOIN author a ON a.id = b.author_id WHERE a.id IS NULL",
            nativeQuery = true)
    List<AuthorNameBookTitle> findBooksAndAuthorsSql();        
}

AuthorRepository.java

@Repository
@Transactional(readOnly = true)
public interface AuthorRepository extends JpaRepository<Author, Long> {

    // Fetch authors and books excluding authors that have registered books (JPQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM Author a LEFT JOIN a.books b WHERE b.id IS NULL")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql();

    // Fetch authors and books excluding authors that have registered books (SQL)    
    @Query(value = "SELECT b.title AS title, a.name AS name "
            + "FROM author a LEFT JOIN book b ON a.id = b.author_id WHERE b.id IS NULL",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql();    
}