How could I debug Spring Boot repository with H2 in memory database?

Question

I am working on Spring Boot and trying to use H2 database (in memory) for my unit tests. My Spring Data repository implementation is not working and I need help in debugging it.

I have reduced my project (not using the original project) to the following example to isolate and demonstrate the problem. The project can be found in GitHub.

Eclipse project structure:

pom.xml:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
        <groupId>org.glassfish.jaxb</groupId>
        <artifactId>jaxb-runtime</artifactId>
        <version>2.3.0.1</version>
    </dependency>
    <dependency>
        <groupId>javax.activation</groupId>
        <artifactId>activation</artifactId>
        <version>1.1.1</version>
    </dependency>
    <dependency>
        <groupId>javax.transaction</groupId>
        <artifactId>jta</artifactId>
        <version>1.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.maven</groupId>
        <artifactId>maven-model</artifactId>
        <version>3.5.4</version>
    </dependency>
    <dependency>
        <groupId>org.modelmapper</groupId>
        <artifactId>modelmapper</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <configuration>
                <executable>true</executable>
                <skipTests>true</skipTests>
            </configuration>
            <executions>
                <execution>
                    <goals>
                        <goal>repackage</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

<repositories>
    <repository>
        <id>spring-releases</id>
        <url>https://repo.spring.io/libs-release</url>
    </repository>
</repositories>
<pluginRepositories>
    <pluginRepository>
        <id>spring-releases</id>
        <url>https://repo.spring.io/libs-release</url>
    </pluginRepository>
</pluginRepositories>

application.properties:

debug=true

logging.level.root=WARN
logging.level.org.foo=WARN
logging.level.org.springframework=WARN

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:test_database;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

Main configuration class:

@SpringBootApplication
@Configuration
@PropertySource("classpath:application.properties")
@EnableJpaRepositories(basePackages = "org.foo.repositories")
@EntityScan("org.foo.entities")
@EnableTransactionManagement
public class Application {

    Logger logger = LoggerFactory.getLogger(Application.class);

    public static void main(String[] args) {
        Logger logger = LoggerFactory.getLogger(Application.class);
        SpringApplication.run(Application.class, args);
        logger.info("Application started");
    }

    @Bean
    public CommandLineRunner commandLineRunner(ApplicationContext ctx) {
        return args -> {

            logger.trace("Let's inspect the beans provided by Spring Boot:");
            String[] beanNames = ctx.getBeanDefinitionNames();
            Arrays.sort(beanNames);
            for (String beanName : beanNames) {
                logger.trace(beanName);
            }
        };
    }
}

Student entity class:

package org.foo.entities;

import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.Data;

@Entity
@Data
public class Student {

    @Id
    private long id;

    private String first_name;

    private String last_name;

}

StudentRepository class:

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
    @Override
    List<Student> findAll();
}

StudentRepositoryTester class:

@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
@ContextConfiguration(classes = { org.foo.Application.class }, loader = AnnotationConfigContextLoader.class)
public class StudentRepositoryTester {

    Logger logger = LoggerFactory.getLogger(StudentRepositoryTester.class);

    @Autowired
    private StudentRepository studentRepository;

    @Test
    public void findAllTester() {
        Iterable<Student> students = studentRepository.findAll();
        for(Student student: students) {
            logger.info(student.toString());
        }
        long size = StreamSupport.stream(students.spliterator(), false).count();
        assertTrue(students != null && size == 3);
    }

}

data.sql:

USE `test_database`;

INSERT INTO `students` (`id`,`first_name`,`last_name`) VALUES (1,'First1','Last1');
INSERT INTO `students` (`id`,`first_name`,`last_name`) VALUES (2,'First2','Last2');
INSERT INTO `students` (`id`,`first_name`,`last_name`) VALUES (3,'First3','Last3');

schema.sql:

DROP ALL OBJECTS; 

CREATE SCHEMA `test_database`;
USE `test_database`;

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);

I can see in the console log that H2 is populated as I can see the statements in data.sql executed. I see nothing else suspicious in the log.

Specific questions:

1. Running StudentRepositoryTester test class, I see (with breakpoint on the assert statement) that no entities are returned. I cannot understand why. Perhaps I miss something in the Spring Boot configuration annotations?

2. I tried to check the database content in the breakpoint, but I cannot find a way to explore the H2 database. I follow this tutorial to open a web explorer application of H2 but nothing is served in localhost:8080. Am I doing something wrong? Alternatively, is there another way to debug what is going on in the H2 database (in memory)?

Please note that the respective Eclipse project has been uploaded in GitHub to facilitate discussion.

Answer 1

Turn on logging of SQL statements executed by Hibernate (in application.properties):

logging.level.org.hibernate.SQL=DEBUG

This will output all the SQL statements to the console. But without variables injected. This should help you with troubleshooting.