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

--

--

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
PA

PA

Java Developer and enthusiast