CODEWITHSUNDEEP
mavenspring-boothsqldb
shortduck
shortduck

Reputation: 1525

I can`t see my created tables in HSQLDB, what could be the issue?

My environment is HSQLDB, Maven and spring-boot. I have created 2 entity POJOs. I do see the CREATE TABLE command under testedb.log file. But when I open Data Source Explorer in Eclipse, I can`t see my tables, albeit I do see all the system tables.

I have looked at this question too, but no vail: Where can I see the HSQL database and tables

Here is my partial pom.xml:

<dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>2.4.0</version>
        <scope>runtime</scope>
</dependency>

Here is my partial application.properties:

# DataSource
#spring.datasource.driverClassName=org.hsqldb.jdbc.JDBCDriver
spring.datasource.url=jdbc:hsqldb:file:resources/db/testedb;DATABASE_TO_UPPER=false
#spring.datasource.url=jdbc:hsqldb:mem:memTestdb
spring.datasource.username=sa
spring.datasource.password=

# Hibernate
spring.jpa.show-sql=true
#spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.ddl-auto=create

And below is my HSQLDB created on disk:

HSQLDB folder in my workspace Here is my partial testedb.script:

SET FILES LOG SIZE 50
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE SEQUENCE PUBLIC.HIBERNATE_SEQUENCE AS INTEGER START WITH 1
CREATE MEMORY TABLE PUBLIC.ENQUIRY_HISTORY(ID BIGINT NOT NULL PRIMARY  KEY,FROM_AMOUNT DOUBLE NOT NULL,FROM_CURRENCY VARCHAR(255) NOT NULL,QUERY_DATE TIMESTAMP NOT NULL,TO_AMOUNT DOUBLE NOT NULL,TO_CURRENCY VARCHAR(255) NOT NULL,USER_ID BIGINT NOT NULL,VERSION INTEGER NOT NULL)
CREATE MEMORY TABLE PUBLIC.USERS(ID BIGINT NOT NULL PRIMARY KEY,EMAIL VARCHAR(255) NOT NULL,LAST_LOGIN TIMESTAMP NOT NULL,PASSWORD VARCHAR(255) NOT NULL,VERSION VARCHAR(255) NOT NULL)
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
ALTER SEQUENCE PUBLIC.HIBERNATE_SEQUENCE RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC

Please see above that the CREATE TABLE contains the word MEMORY even though I have created file DB.

And by testsdb.log:

/*C12*/SET SCHEMA PUBLIC
drop table enquiry_history if exists
drop table users if exists
drop sequence hibernate_sequence if exists
create sequence hibernate_sequence start with 1 increment by 1
create table enquiry_history (id bigint not null, from_amount float not null, from_currency varchar(255) not null, query_date timestamp not null, to_amount float not null, to_currency varchar(255) not null, user_id bigint not null, version integer not null, primary key (id))
create table users (id bigint not null, email varchar(255) not null, last_login timestamp not null, password varchar(255) not null, version varchar(255) not null, primary key (id))
/*C14*/SET SCHEMA PUBLIC
DISCONNECT
/*C17*/SET SCHEMA PUBLIC

And Finally here is my screen shot of Database Data Source Explorer

Any pointer will be awesome, thanks for your time.

Upvotes: 5

Views: 2534

Answers (2)

shortduck
shortduck

Reputation: 1525

I was able to see the tables using in build HSQLDB interface, now a fancy one but it still works for me. I used the following [listed in this answer https://stackoverflow.com/a/35240141/8610216] 

java -cp /path/to/hsqldb.jar org.hsqldb.util.DatabaseManager

And then specify path your database:

jdbc:hsqldb:file:mydb

There was one more thing that I was doing incorrect; it was the path of the db. The correct path is spring.datasource.url=jdbc:hsqldb:file:src/main/resources/db/userx;DATABASE_TO_UPPER=falsein application.properties.

Upvotes: 1

Mohnish
Mohnish

Reputation: 2045

file and mem are in-process modes. For testing/debugging, if you need concurrent access to data from another process, start database in Server mode.

Check various available modes here.

Upvotes: 2

Related Questions