40

I use JDBC and created h2 database called usaDB from sql script. Then I filled all tables with jdbc.

The problem is that after I connect to usaDB at localhost:8082 I cannot see on the left tree my tables. There is only INFORMATION_SCHEMA database and rootUser which I specified creating usaDB.

How to view the content of tables in my h2 database?

I tried query SELECT * FROM INFORMATION_SCHEMA.TABLES.

But it returned many table names except those I created. My snapshot:

enter image description here

Paul Vargas
  • 41,222
  • 15
  • 102
  • 148
Volodymyr Levytskyi
  • 3,364
  • 9
  • 46
  • 83
  • 2
    Check your connection string in JDBC. You might be connecting to someother database. But after creating new database, your forgot to include that new db name with your new tables. Like: `create newDb.table_name` was missing, I believe. And hence, tables might be created in default database to which you connected using JDBC. – Ravinder Reddy May 01 '14 at 07:45
  • Thanks but creating usaDB schema explicitly didn't help. From jdbc I can retrieve my table names and columns but not at localhost:8082 . – Volodymyr Levytskyi May 01 '14 at 08:10
  • what is the difference in `jdbc` and `localhost:8082` settings? – Ravinder Reddy May 01 '14 at 08:13
  • It seems to me no difference except relative path in jdbc and absolute in localhost! – Volodymyr Levytskyi May 01 '14 at 08:48
  • No no ... Is your jdbc connecting to db on `locathost` or some remote server? What are the configuration differences was my question. – Ravinder Reddy May 01 '14 at 08:50
  • I installed h2 database with windows installer on my computer. I use only local h2 server. – Volodymyr Levytskyi May 01 '14 at 09:09
  • Did you change something related to MVCC setting? – voho Jul 15 '15 at 08:50
  • you can use broke point to find the right jdbc url. In my case is site in targetSource->target->jdbc->dataSource – 王良海 Jul 13 '18 at 05:15

10 Answers10

53

I had the same issue and the answer seems to be really stupid: when you type your database name you shouldn't add ".h2.db" suffix, for example, if you have db file "D:\somebase.h2.db" your connection string should be like "jdbc:h2:file:/D:/somebase". In other way jdbc creates new empty database file named "somebase.h2.db.h2.db" and you see what you see: only system tables.

Le_Enot
  • 799
  • 6
  • 16
34

You can use the SHOW command:

grammar

Using this command, you can lists the schemas, tables, or the columns of a table. e.g.:

SHOW TABLES
Community
  • 1
  • 1
Paul Vargas
  • 41,222
  • 15
  • 102
  • 148
15

This problem drove me around the twist and besides this page I read many (many!) others until I solved it.
My Use Case was to see how a SpringBatch project created in STS using :: Spring Boot :: (v1.3.1.RELEASE) was going to behave with the H2 database; to do the latter, I needed to be able to get the H2 console running as well to query the DB results of the batch run.

This is what I did and found out:

  1. Created an Web project in STS using Spring Boot: enter image description here

    • Added the following to the pom.xml of the latter: enter image description here
    • Added a Spring configuration file as follows to the project: enter image description here This solves the Web project deficiencies in STS. If you run the project now, you can access the H2 console as follows: http://localhost:8080/console enter image description here

  2. Now create a SpringBatch project in STS as follows (the alternative method creates a different template missing most of the classes for persisting data. This method creates 2 projects: one Complete, and the other an initial. Use the Complete in the following.): enter image description here

    • The SpringBatch project created with STS uses an in memory H2 database that it CLOSES once the application run ends; once you run it, you can see this in the logging output.
    • So what we need is to create a new DataSource that overrides the default that ships with the project (if you are interested, just have a look at the log messages and you will see that it uses a default datasource...this is created from: o.s.j.d.e.EmbeddedDatabaseFactory with the following parameters:
      Starting embedded database: url='jdbc:hsqldb:mem:testdb', username='sa')
    • So, it starts an in memory, and then closes it. You have no chance of seeing the data with the H2 console; it has come and gone.
    • So, create a DataSource as follows: enter image description here
    • You can of course use a properties file to map the parameters, and profiles for different DataSource instances...but I digress.
    • Now, make sure you set the bit that the red arrow in the picture is pointing to, to a location on your computer where a file can be persisted.
    • Running the SpringBatch (Complete project) you should now have a db file in that location after it runs (persisting Person data)
    • Run the Web project you configured previously in these steps, and you WILL :=) see your data, and all the Batch job and step run data (et voila!): enter image description here Painful but rewarding. Hope it helps you to really BOOTSTRAP :=)
Beezer
  • 1,084
  • 13
  • 18
5

I have met exactly this problem.

From what you describe, I suppose that you connect your jdbc with the "real" h2 server, but you are connecting on web application to database by the wrong mode (embedded-in-memory mode, aka h2mem). It means that h2 will create a new database in-memory, instead of using your true database stored elsewhere.

Please make sure that when you connect to this database, you use the mode Generic H2 (Server), NOTGeneric H2 (Embedded). You can refer to the picture below.

enter image description here

Hoàng Long
  • 10,746
  • 20
  • 75
  • 124
1

Version of jar file and installed h2 database should be same.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
rajeesh
  • 616
  • 10
  • 20
1

It is an old question, but I came across the same problem. Eventually I found out that the default JDBC URL is pointing a test server rather than my application. After correcting it, I could access the right DB.

I tried with both Generic H2 (Embedded) and the Generic H2 (Server) options, both worked as long as the JDBC URL: is provided correctly.

In grails 4.0.1 the jdbc URL for development is jdbc:h2:mem:devDb. Check your application.yml file for the exact URL. enter image description here

Memin
  • 3,788
  • 30
  • 31
1

If in case you have created and populated H2 database table using maven dependency in spring boot, then please do change the JDBC URL as jdbc:h2:mem:testdb while connecting to H2 using web console.

Gandhi
  • 11,875
  • 4
  • 39
  • 63
1

For the people who are using H2 in embedded(persistent mode) and want to "connect" to it from IntelliJ(other IDEs probably apply too).

  1. Using for example jdbc url as follows: jdbc:h2:./database.h2
  2. Note, that H2 does not allow implicit relative paths, and requires adding explicit ./
  3. Relative paths are relative to current workdir
  4. When you run your application, your workdir is most likely set to your project's root dir
  5. On the other hand, IDE's workdir is most likely not your project's root
  6. Hence, in IDE when "connecting" to your database you need to use absolute path like: jdbc:h2:/Users/me/projects/MyAwesomeProject/database.h2
  7. For some reason IntelliJ by default also adds ;MV_STORE=false. It disables MVStore engine which in fact is currently used by default in H2.
  8. So make sure that both your application and your IDE use the same store engine, as MVStore and PageStore have different file layouts.
  9. Note that you cannot "connect" to your database if your application is using it because of locking. The other way around applies too.
Aleksander Stelmaczonek
  • 1,350
  • 1
  • 22
  • 29
0

In my case the issue was caused by the fact that I didn't set the h2 username, password in java. Unfortunatelly, Spring didn't display any errors to me, so it was not easy to figure out. Adding this lines to dataSource method helped me fix the issue:

dataSource.setUsername("sa");
dataSource.setPassword("");

Also, I should have specified the schema when creating tables in schema.sql

Zhenya
  • 6,020
  • 6
  • 34
  • 42
0

Selecting Generic H2 (Server) solved for me. We tempted to use default Generic H2 (Embedded) which is wrong.

Well Smith
  • 733
  • 7
  • 10