I have downloaded the H2 console from http://www.h2database.com/html/download.html
and I have configured the URL in my jdbc.properties file
to jdbc:h2:c:/data/Messaging.
I am using the same URL in the file to connect to the database but I cannot see the tables;
I can only see the Information Schema and when I try to select * from tables in it I cannot see the tables neither.
Does anybody have any idea what could be wrong?
One tricky thing is that the H2 console will not give you an error if you try to connect to a JDBC URL that doesn't exist. It will instead create a new database at that URL! To connect to the in memory DB, use this JDBC URL (http://localhost:8080/h2-console is the default console):
jdbc:h2:mem:testdb
If you were to enter something like jdbc:h2:~/test then a test.mv file would be created under your home directory. But your application would still be using the in memory database.
The console is available if you have the h2 dependency in your pom, and also the spring developer tools dependency. If you don't have the tools dependency, then you can also see it by having the h2 dependency and adding the following to your application.properties file:
spring.h2.console.enabled=true #not needed if you have spring-boot-devtools dependency
If you want the db as a file, and not in memory, add the following to applications.properties:
spring.datasource.url=jdbc:h2:~/test_db #You will see the file in your home directory.
H2 isn't meant for persisted data, but if you want to persist for testing purposes, then add:
spring.jpa.hibernate.ddl-auto = update
Then start up the app, and at the console, use this JDBC URL:
jdbc:h2:~/test_db
In case you were wondering, I only have 1 entry in application.properties (for the database file) and here are my dependencies:
<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-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
This is how you enable memory enable database using h2 module. You need to ensure the following things
spring.h2.console.enabled=truelocalhost:8080/h2-consoleJDBC URL: -> jdbc:h2:mem:testdbBased on your question, it doesn't look like you fell victim to this particular pitfall, but this thread ended up helping me nail down the issue, so I am recording the solution here for posterity since it may help others with the same problem.
I also found that when I tried to open my database with the H2 console that I got what appeared to be a blank H2 database (basically, just an INFORMATION_SCHEMA table). While double-checking that I got the name of the DB correct (mydb.mv.db), I discovered that the H2 console had created a second database file, mydb.mv.db.mv.db. Odd.
It turns out that the H2 Console expects you to omit the suffix .mv.db from the filename. Since I hadn't, it was looking for mydb.mv.db.mv.db. Changing the JDBC string to jdbc:h2:mydb solved the problem and I could then open the file from the H2 Console.
Had the same Problem.
This solved it for me: Why is my embedded h2 program writing to a .mv.db file
Just added ;MV_STORE=FALSE and ;MVCC=FALSE to the jdbc url and everything worked just fine.
I have used the below and I see my table get created.
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=FALSE
spring.h2.console.enabled=true
spring.h2.console.path=/h2console
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
I finally figured out how to view my default in-memory H2 database tables. I'm sharing this solution because I think the process must have changed recently, due to security concerns. It is no longer possible to use the default jdbc:h2:mem:testdb to gain access to the db, nor to set a name for entry via the application.properties line spring.datasource.name=yourdbnamehere.
As of this writing, when the application starts, there is a console message similar to the following:
2021-04-26 21:16:44.424 INFO 23142 --- [ restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:6da7bb82-4008-4396-b825-09e44c17388b'
I'm using Spring Tool Suite 4. Other IDEs may format this message differently.
The key part is the last section of this string, where the database address is specified. The identifier for the database now changes with each boot. (Maybe there is a way to suppress this, but I don't know it.)
I copied the address jdbc:h2:mem:whatever-long-random-name, and pasted it into the h2-console Login Form field labeled "JDBC URL". Once in, hitting or clicking Connect will bring up the database schema and console.
The tutorial I'm working through is Dan Vega's "Springit" application, from his Getting Started With Spring Boot 2.0 course. I'm including this info as a search breadcrumb for anyone following. This course may be a couple years old now, but Vega does a much better-than-average job of explaining, so it remains worthwhile. However, tech marches on, and I assume the need for enhanced security has grown to the point where it's no longer safe to allow an easily guessable database name.
