Showing Console of hsqldb as H2, Spring Boot 3

Question

I'm trying to use hsqldb like in-memory database.

In my build.gradle file, you can see the version:

implementation group: 'org.hsqldb', name: 'hsqldb', version: '2.7.2'

In my application.yml file, I have:

spring:
  application:
    name: ms-people

  datasource:
    driver-class-name: org.hsqldb.jdbc.JDBCDriver
    url: jdbc:hsqldb:mem:testdb;DB_CLOSE_DELAY=-1
    username: sa
    password:
  jpa:
    show-sql: true
    generate-ddl: true
    #database-platform: org.hibernate.dialect.HSQLDialect
    hibernate:
      ddl-auto: create

In the Log, I obtained:

Hibernate: drop table if exists phones cascade 
Hibernate: drop table if exists users cascade 
Hibernate: create table phones (city_code integer, country_code integer, id bigint generated by default as identity (start with 1), number bigint, user_id bigint not null, primary key (id))
Hibernate: create table users (isactive boolean not null, created timestamp(6), id bigint generated by default as identity (start with 1), last_login timestamp(6), modified timestamp(6), email varchar(255) unique, name varchar(255), password varchar(255), token varchar(255), primary key (id))
Hibernate: alter table phones add constraint FKmg6d77tgqfen7n1g763nvsqe3 foreign key (user_id) references users

But, when I tryied to connect and see my database and table. I can't see nothing.

How I would see the executed query/command?

What are my mistakes?

EDIT

I was reading this post https://www.baeldung.com/java-in-memory-databases

• H2 Database
• HSQLDB (HyperSQL Database)
• Apache Derby Database
• SQLite Database

And I was testing with H2: In my build.gradle file, you can see the version:

runtimeOnly group: 'com.h2database', name: 'h2', version: '2.2.224'

In my application.yml file, I have:

spring:
  application:
    name: ms-people
  h2:
    console:
      enabled: true
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:testdb
    username: sa
    password:
  jpa:
    show-sql: true
    generate-ddl: true
    hibernate:
      ddl-auto: create

Alternatively to H2, what other could be shown as H2 allow us?

Answer 1

For HSQLDB (HyperSQL Database) I found this article https://mkyong.com/spring/spring-view-content-of-hsqldb-embedded-database/

package org.bz.app.mspeople.configurations;

import jakarta.annotation.PostConstruct;
import org.hsqldb.util.DatabaseManagerSwing;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class SpringRootConfig {

    @Autowired
    DataSource dataSource;

    @Bean
    public JdbcTemplate getJdbcTemplate() {
        return new JdbcTemplate(dataSource);
    }

    //default username : sa, password : ''
    @PostConstruct
    public void getDbManager() {
        DatabaseManagerSwing.main(
                new String[]{"--url", "jdbc:hsqldb:mem:testdb", "--user", "sa", "--password", ""});

    }

}

and this class:

package org.bz.app.mspeople.configurations;


import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource(){
        //jdbc:hsqldb:mem:testdb
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.HSQL)
                //.addScript("db/hsqldb/db.sql")
                .build();
        return db;
    }

}

and this link: https://gist.github.com/fredjoseph/0d9eabb60860b12ba7b7adbfd4d0c558

Then, we need to add the following VM arguments -Djava.awt.headless=false when the application is started

I'm using IntelliJ:

I was testing: