# Hibernate, Panache, and Database Access

The Panache extensions to Hibernate and JPA used by Quarkus give us extensive flexibility in being able to quickly and efficiently create Data Access Object, Repositories, or ActiveRecord style database integrations. For this tutorial, we will leverage the Repository pattern (opens new window) capability within Quarkus to allow for simple integration with a PostgreSQL database.

  1. Add the H2 & PostgreSQL Database extensions to Quarkus using the Maven plugin
    $ ./mvnw quarkus:list-extensions | grep -i jdbc
    [INFO] Elytron Security JDBC                              quarkus-elytron-security-jdbc                     
    [INFO] JDBC Driver - DB2                                  quarkus-jdbc-db2                                  
    [INFO] JDBC Driver - Derby                                quarkus-jdbc-derby                                
    [INFO] JDBC Driver - H2                                   quarkus-jdbc-h2                                   
    [INFO] JDBC Driver - MariaDB                              quarkus-jdbc-mariadb                              
    [INFO] JDBC Driver - Microsoft SQL Server                 quarkus-jdbc-mssql                                
    [INFO] JDBC Driver - MySQL                                quarkus-jdbc-mysql                                
    [INFO] JDBC Driver - Oracle                               quarkus-jdbc-oracle                               
    [INFO] JDBC Driver - PostgreSQL                           quarkus-jdbc-postgresql                           
    [INFO] Camel JDBC                                         camel-quarkus-jdbc                             
    $ ./mvnw quarkus:add-extensions -Dextensions=quarkus-jdbc-postgresql,quarkus-test-h2
    // SNIP
    [INFO] --- quarkus-maven-plugin:2.0.0.Final:add-extensions (default-cli) @ quarkus-todo ---
    [INFO] [SUCCESS] ✅  Extension io.quarkus:quarkus-jdbc-postgresql has been installed
    [INFO] [SUCCESS] ✅  Extension io.quarkus:quarkus-jdbc-h2 has been installed
    [INFO] ------------------------------------------------------------------------
    // SNIP
    
  2. Add data source configurations for the PostgreSQL and H2 databases in src/main/resources/application.properties
    %test.quarkus.datasource.db-kind = h2
    %test.quarkus.datasource.jdbc.url = jdbc:h2:mem:todos;MODE=PostgreSQL;
    %test.quarkus.datasource.password=
    %test.quarkus.datasource.username=SA
    %test.quarkus.datasource.jdbc.driver=org.h2.Driver
    %test.quarkus.hibernate-orm.database.generation=drop-and-create
    
    quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/hibernate_orm_test
    quarkus.datasource.db-kind = postgresql
    quarkus.datasource.jdbc=true
    quarkus.datasource.jdbc.driver=org.postgresql.Driver
    quarkus.datasource.username=${TODODB_USER:tododb}
    quarkus.datasource.password=${TODODB_PASS:tododb}
    
  3. Create a new package under src/main/java called com.redhat.runtimes.data
  4. Create a new Java class in that package called TodosRepository.java
    package com.redhat.runtimes.data;
    
    import com.redhat.runtimes.models.Todo;
    import io.quarkus.hibernate.orm.panache.PanacheRepositoryBase;
    
    import javax.enterprise.context.ApplicationScoped;
    import java.util.UUID;
    
    @ApplicationScoped
    public class TodosRepository implements PanacheRepositoryBase<Todo, UUID> {
    }
    
    • This repository indicates that it handles Todo entities and uses a UUID as the primary key.

      TIP

      If you primary key is a Long you could instead just extend PanacheRepository<Entity>

    • By extending PanacheRepository or PanacheRepositoryBase we automatically get typical CRUD methods (opens new window) for our Entity type. Some examples are:
      • findById
      • listAll
      • deleteById
      • persist
      • update
  5. Create a test class under src/test/java and write a test for a method which returns paginated results
    package com.redhat.runtimes.data;
    
    import io.quarkus.panache.common.Sort;
    import io.quarkus.test.junit.QuarkusTest;
    import org.junit.jupiter.api.Test;
    
    import javax.inject.Inject;
    
    import static org.junit.jupiter.api.Assertions.*;
    
    @QuarkusTest
    class TodosRepositoryTest {
    
        @Inject
        TodosRepository underTest;
    
        @Test
        void getTodosPaginated() {
            var result = underTest.getTodosPaginated("id", Sort.Direction.Ascending, 25, 1);
            assertEquals(0, result.size(), "Without staged data, an empty list is expected");
        }
    }
    
  6. Add a new method to the repository for handling paginated data
    public List<Todo> getTodosPaginated(String sortKey, Sort.Direction sortOrder, int pageSize, int pageOffset) {
    	PanacheQuery<Todo> todos = this.findAll(Sort.by(sortKey).direction(sortOrder));
    	return todos.page(pageOffset, pageSize).stream().collect(Collectors.toList());
    }
    
  7. Run your tests to ensure that the implementation is correct
    ./mvnw test
    
Last Updated: 12/18/2021, 5:53:27 PM