Doesn't show H2 tables in browser

Question

Hello,

I have a problem with displaying the h2 tables in browser I've created on Java Web Application ( Spring 4, Hibernate 5, Thymeleaf 3, H2database 1.4.192 , etc.). It has Java-based configuration.

My DataSource:

@Bean(name = "dataSource")
public DataSource getDataSource() {
    logger.info("Setting dataSource properties.");
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    EmbeddedDatabase embeddedDatabase = builder
            .continueOnError(true)
            .setType(EmbeddedDatabaseType.H2)
            .addScript(CREATE_SCRIPT)
            .addScript(INIT_SCRIPT)
            .build();
    return embeddedDatabase;
}

My 'CREATE_SCRIPT':

CREATE TABLE CLIENTS
(
CLIENT_ID INT PRIMARY KEY AUTO_INCREMENT,
CLIENT_NAME VARCHAR(99) NOT NULL,
AGREEMENT BOOLEAN DEFAULT FALSE
);

CREATE TABLE ITEMS
(
  ITEM_ID INT PRIMARY KEY AUTO_INCREMENT,
  ITEM_NAME VARCHAR(99) NOT NULL,
  PRICE DECIMAL(10,2) NOT NULL
);

CREATE TABLE CLIENTS_ITEMS
(
  CLIENT_ID INT ,
  ITEM_ID INT NOT NULL,
  CONSTRAINT CLIENTS_CLIENT_ID_FK
  FOREIGN KEY (CLIENT_ID)
  REFERENCES CLIENTS(CLIENT_ID),
  CONSTRAINT ITEMS_ITEM_ID_FK
  FOREIGN KEY (ITEM_ID)
  REFERENCES ITEMS (ITEM_ID)
);

My 'INIT_SCRIPT':

INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES  ('Book', 5.50);
INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES ('Hook', 15.00);
INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES ('Nook', 199.9);
INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES ('Snook', 1.9);
INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES ('Stook', 0.99);
INSERT INTO ITEMS
(ITEM_NAME, PRICE) VALUES ('Mobile Phone', 10);

The tables created 100% cuz I can persist and fetch data.

ноя 10, 2016 11:15:59 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase

> INFO: Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'

ноя 10, 2016 11:15:59 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript

INFO: Executing SQL script from class path resource [create.sql]

ноя 10, 2016 11:15:59 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript

INFO: Executed SQL script from class path resource [create.sql] in 479 ms.

ноя 10, 2016 11:15:59 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript

INFO: Executing SQL script from class path resource [test.sql]

ноя 10, 2016 11:15:59 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript

INFO: Executed SQL script from class path resource [test.sql] in 11 ms.

ноя 10, 2016 11:16:09 AM org.springframework.orm.hibernate5.HibernateTransactionManager afterPropertiesSet

INFO: Using DataSource [org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory$EmbeddedDataSourceProxy@162b3d47] of Hibernate SessionFactory for HibernateTransactionManager

But when I open h2-console and connect to this database with current username and password I can find nothing.

It's very strange and I don't know what the problem is. Help me please.
Thank you much.

Answer 1

The problem is that you create an in-memory database

jdbc:h2:mem:testdb

which is not persistent and you cannot connect to it via the web console.

see: http://www.h2database.com/html/features.html#in_memory_databases

Depending what you want to do

• perist the data --> use a file based local H2 database
• keep in-memory --> maybe this SO answer could be a solution

edit To add it more in detail.

In some cases, only one connection to a in-memory database is required. This means the database to be opened is private. In this case, the database URL is jdbc:h2:mem: Opening two connections within the same virtual machine means opening two different (private) databases.

The URL jdbc:h2:mem: (an in memory database without a name) creates an database which can be used only with a single connection. A new connection within the same JVM to the URL jdbc:h2:mem: would create a new in memory database.

Sometimes multiple connections to the same in-memory database are required. In this case, the database URL must include a name. Example: jdbc:h2:mem:db1. Accessing the same database using this URL only works within the same virtual machine and class loader environment.

The URL jdbc:h2:mem:db1 (an in memory database with a name, in this case db1) creates a database which allows multiple connections. As long the connections are created within the same JVM and class loader.