JPA — Cross Joins, Full Join

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 )
• 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

@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

@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

@RequiredArgsConstructor
@Service
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();
    }
}

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

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);

MainApp.java

@RequiredArgsConstructor
@SpringBootApplication
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("\n ######### fetchBooksAndFormatsJpql: ");
            bookstoreService.fetchBooksAndFormatsJpql()
                    .forEach((e) -> System.out.println(e.getTitle() + " | " + e.getFormatType()));

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

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

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

— — — — — — — — — —

Full Joins

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();
}