Cannot see H2 database tables and data via DBeaver or IntelliJ IDEA

Question

In a Spring Boot app, I am trying to read data via DBeaver as I generally did before in SERVER Mode:

Here is my app props:

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:${db_name};DATABASE_TO_UPPER=FALSE;DB_CLOSE_ON_EXIT=false
    username: ${db_username}
    password: # leave password field empty for H2 database Server Mode connection
  jpa:
    hibernate:
      ddl-auto: create
    show-sql: true
    open-in-view: false
    properties:
      hibernate.format_sql: true
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect

    h2:
      console:
        enabled: 'true'
        path: /h2-console

and pom.xml:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
    <!--<scope>runtime</scope>-->
</dependency>

Although the data is inserted to the database when running my app and I can query the data via Postman, I cannot see anything under PUBLIC > Tables when connecting to the database via DBeaver or IntelliJ IDEA.

So, what is the reason, do you have an idea? I tried some workarounds but not worked.

H2 is an in-memory database. It exists only locally in the JVM that created it. It is entirely unclear to me how you "connect" to the database via DBeaver or IntelliJ IDEA, but I suspect what is in fact happening in each case is that ***another*** in-memory database is being created, locally to either DBeaver or IntelliJ IDEA, and it is of course empty, because it is not the same as the one that your JVM created and populated. — Mike Nakis, Apr 05 '23 at 13:54
Yes, I know and it is possible to connect to in memory database and see tables as I have done before. — jonathan, Apr 05 '23 at 14:09

score 2 · Accepted Answer · answered Apr 05 '23 at 13:52

2

You are using a memory-based h2 instance, your connection from DBeaver or IntellijIDEA might not connected to the same instance.

If you want to make sure your DBeaver or IntellijIDEA connected to the same h2 instance, you'd better use file mode instead of mem mode for you h2 instance.

Using file mode for your h2 database, you can checkout H2 database - file mode for more information.

Just for simple, you can change spring.datasource.url from jdbc:h2:mem:${db_name};DATABASE_TO_UPPER=FALSE;DB_CLOSE_ON_EXIT=false to jdbc:h2:file:/path/to/${db_name};DATABASE_TO_UPPER=FALSE;DB_CLOSE_ON_EXIT=false. Now you can connect to your h2 instance in DBeaver or IntellijIDEA with new connection URL jdbc:h2:file:/path/to/${db_name};DATABASE_TO_UPPER=FALSE;DB_CLOSE_ON_EXIT=false.

answered Apr 05 '23 at 13:52

Horsing

1,070
7
22

Thanks for reply. I do not need to connect via both of them, Dbeaver would be enough. But the problem is table data is not seen. I want to use memory and connect in server mode as I used to before. So, any idea? – jonathan Apr 05 '23 at 14:10
@jonathan This might be useful: https://stackoverflow.com/questions/7309359/view-content-of-h2-or-hsqldb-in-memory-database . You can try to connect to your `mem` h2 instance by specifying url like `jdbc:h2:tcp://localhost:9092/~/default` – Horsing Apr 05 '23 at 14:13
Thanks for help, actually I could connect there is no problem for connection. But I do not see any data. On the other hand, I checked that page, but unfortunately the approaches too old and most of them cannot be applicable. – jonathan Apr 05 '23 at 14:18
Check this, http://www.h2database.com/html/tutorial.html#using_server . You should make sure the server started on specific port in Server mode. – Horsing Apr 05 '23 at 14:28
Thanks, voted up. But I tried everything and still not seem (but when I send request I fetch the data). – jonathan Apr 05 '23 at 14:40

Cannot see H2 database tables and data via DBeaver or IntelliJ IDEA

1 Answers1