Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 832
shortduck Avatar asked Oct 16 '22 18:10

shortduck


2 Answers

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.

like image 89
Mohnish Avatar answered Oct 19 '22 23:10

Mohnish


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.

like image 35
shortduck Avatar answered Oct 19 '22 23:10

shortduck