JPA How To Check If A Transient Entity Exists In The Database Via Spring Query By Example

Description: This application is an example of using Spring Data Query By Example (QBE) to check if a transient entity exists in the database. Consider the Book entity and a Spring controller that exposes an endpoint as: public String checkBook(@Validated @ModelAttribute Book book, ...). Beside writting an explicit JPQL, we can rely on Spring Data Query Builder mechanism or, even better, on Query By Example (QBE) API. In this context, QBE API is quite useful if the entity has a significant number of attributes and:

• for all attributes, we need a head-to-head comparison of each attribute value to the corresponding column value
• for a subset of attributes, we need a head-to-head comparison of each attribute value to the corresponding column value
• for a subset of attributes, we return true at first match between an attribute value and the corresponding column value
• any other scenario

Key points:

• the repository, BookRepository extends QueryByExampleExecutor
• the application uses <S extends T> boolean exists(Example<S> exmpl) with the proper probe (an entity instance populated with the desired fields values)
• moreover, the probe relies on ExampleMatcher which defines the details on how to match particular fields

Note: Do not conclude that Query By Example (QBE) defines only the exists() method. Check out all methods here.

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 genre;
    private String isbn;
    private String author;
    private int price;
}

BookRepository.java

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

BookStoreService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final BookRepository bookRepository;

    public boolean existsBook1(Book book) {
        Example<Book> bookExample = Example.of(book);
        return bookRepository.exists(bookExample);
    }
    
    public boolean existsBook2(Book book) {
        Example<Book> bookExample = Example.of(book,
                ExampleMatcher.matchingAll().withIgnorePaths("genre", "price"));
        return bookRepository.exists(bookExample);
    }
    
    public boolean existsBook3(Book book) {
        Example<Book> bookExample = Example.of(book,
                ExampleMatcher.matchingAny().withIgnorePaths("genre", "price"));
        return bookRepository.exists(bookExample);
    }
}

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("Find a book:");

            // this can come via a controller endpoint
            Book book = new Book();
            book.setTitle("Carrie");
            book.setGenre("Horror");
            book.setIsbn("001-OG");
            book.setAuthor("Olivia Goy");
            book.setPrice(23);
            
            boolean foundAnd = bookstoreService.existsBook1(book);            
            System.out.println("Found (existsBook1): " + foundAnd + "\n");
            
            boolean foundOr = bookstoreService.existsBook2(book);            
            System.out.println("Found (existsBook2): " + foundOr + "\n");
            
            boolean foundIgnorePath = bookstoreService.existsBook3(book);            
            System.out.println("Found (existsBook3): " + foundIgnorePath + "\n");
        };
    }
}

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

insert into book (id, title, genre, isbn, author, price) values (1, "Carrie", "Horror", "001-OG", "Olivia Goy", 23);
insert into book (id, title, genre, isbn, author, price) values (2, "Old One", "History", "001-JN", "Joana Nimar", 40);
insert into book (id, title, genre, isbn, author, price) values (3, "Best Shot", "Anthology", "001-MJ", "Mark Janel", 30);
insert into book (id, title, genre, isbn, author, price) values (4, "Happy End", "Horror", "002-OG", "Olivia Goy", 36);
insert into book (id, title, genre, isbn, author, price) values (5, "Long Run", "Anthology", "002-MJ", "Mark Janel", 10);

Console Output

Find a book:
2021–06–18 16:07:23.172 INFO 36460 — — [ main] o.h.h.i.QueryTranslatorFactoryInitiator : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select book0_.id as id1_0_, book0_.author as author2_0_, book0_.genre as genre3_0_, book0_.isbn as isbn4_0_, book0_.price as price5_0_, book0_.title as title6_0_ from book book0_ where book0_.price=23 and book0_.title=? and book0_.author=? and book0_.isbn=? and book0_.genre=?
Found (existsBook1): true

Hibernate: select book0_.id as id1_0_, book0_.author as author2_0_, book0_.genre as genre3_0_, book0_.isbn as isbn4_0_, book0_.price as price5_0_, book0_.title as title6_0_ from book book0_ where book0_.title=? and book0_.author=? and book0_.isbn=?
Found (existsBook2): true

Hibernate: select book0_.id as id1_0_, book0_.author as author2_0_, book0_.genre as genre3_0_, book0_.isbn as isbn4_0_, book0_.price as price5_0_, book0_.title as title6_0_ from book book0_ where book0_.title=? or book0_.author=? or book0_.isbn=?
Found (existsBook3): true