0

I use H2 database (file) in my Java app and due to some data appearance problem in IntelliJ, I use file option instead of memory.

Here is my url setting in application.properties:

spring.datasource.url=jdbc:h2:file:~/test-db;
    DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;AUTO_SERVER=TRUE

I can connect and see table data via IntelliJ and H2 console by using the url parameter as shown below:

jdbc:h2:file:~/test-db

However, even I connect to the database successfully, the table data is not seen in DBeaver as shown below:

enter image description here

I think I should use tcp option to connect H2 daabase, but I cannot by using the following settings:

spring.datasource.url=jdbc:h2:tcp://localhost/~/test-db

or

jdbc:h2:tcp://localhost/C:/test-db

and get "Unable to open JDBC Connection for DDL execution" error (I use Windows). Any idea?

  • Does anybody else have never used **tcp** connection type for H2 database? –  Jun 27 '22 at 12:33

1 Answers1

1

You're using a file-associated connection to that in-memory DB that can only be used by 1 tool at once. So when IntelliJ is connected it generates a lock-file to protect it against multi-access.

I would suggest a TCP connection that allows multiple connections - see redundant question here: H2 database error: Database may be already in use: "Locked by another process"

LenglBoy
  • 297
  • 2
  • 8
  • Thanks a lot, you right and I was trying to use tcp like this url: `spring.datasource.url=jdbc:h2:tcp://localhost/~/test-db` but it is not working. So, I already look at the connections strings, but not sure which one is the most proper for my situation and how can I convert my connection url for that? –  Jun 27 '22 at 11:32
  • I also tried with `jdbc:h2:tcp://localhost/C:/test-db` (I use Windows) and get "*Unable to open JDBC Connection for DDL execution*" error. –  Jun 27 '22 at 11:35
  • Amigo? Are you there? –  Jun 27 '22 at 12:45
  • @Jonathan sry for not responding: I would use the server mode for the H2 DB which allows then a real connection management. By default, just 1 connection can be applied. Just add `;AUTO_SERVER=TRUE` to your default connection string and use TCP. – LenglBoy Jun 28 '22 at 07:21
  • I solved the problem by following the steps on [this](https://stackoverflow.com/questions/43256295/how-to-access-in-memory-h2-database-of-one-spring-boot-application-from-another/43276769#43276769) page. Anyone who need to connect to H2 db in server mode can follow those steps. Thanks for help, voted up. –  Jun 29 '22 at 06:18