Spring Boot + IntelliJ + Embedded Database = Headache

Question

Either I'm missing some core concept buried deep within some documentation (Spring, Spring Boot, H2, HSQLDB, Derby, IntelliJ) or I've been staring at this for too long.

I have a Spring Boot project going. Tried using and initializing an H2 DB at first, tried connecting to it in IntelliJ only to realize that I might not be able to easily browse the db without giving up my firstborn child (Connect to H2 database using IntelliJ database client).

So I moved to DerbyDB. Same thing - db root folder is created in my app, I connect to it in IntelliJ but my tables, that were just created from starting the app are not available to browse.

I even tried SQLite, but support for SQLite is not as good and certain update functions were not available, but I could atleast find my tables in IntelliJ browser!

I just want a simple single file embedded DB that I can use, browse, and play with easily. Any suggestions?!

When I run the application, I see that the schema is exported:

2015-07-19 09:37:45.836  INFO 98608 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
Hibernate: drop table user_roles if exists
Hibernate: drop table users if exists
Hibernate: create table user_roles (id bigint generated by default as identity, role_name varchar(255), version bigint, user_id bigint, primary key (id))
Hibernate: create table users (id bigint generated by default as identity, email varchar(255), password varchar(255), username varchar(255), version bigint, primary key (id))
Hibernate: alter table user_roles add constraint FK_g1uebn6mqk9qiaw45vnacmyo2 foreign key (user_id) references users
2015-07-19 09:37:45.849  INFO 98608 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete

In IntelliJ, nothing (using a remote source of jdbc:h2:./test;AUTO_SERVER=TRUE as per heenenee suggestion):

I see some votes to close because its unclear what I'm asking:

How do I develop applications using H2, HSQLDB, or Derby databases and connect to them with IntelliJ?

Kalina

Using ./test as the path will make 2 dbs: one in your spring boot project's working directory, and one in IntelliJ's working directory (or thereabouts). Use ~/test in both places so both URLs point at the same location on the filesystem. – Kuomintang

Answer 1

H2 Automatic Mixed Mode should be fine for you. Use jdbc:h2:~/mydbInHomeDir;AUTO_SERVER=TRUE as your spring.datasource.url. In IntelliJ, create a remote H2 data source and use the exact same JDBC URL. You may have to explicitly press the Synchronize button in the IntelliJ Database window to get the database tables to show up.

Kuomintang

this worked for me. Thank you. Further details on AUTO_SERVER mode can be found on [link] h2database.com/html/features.html#auto_mixed_mode – Oligosaccharide

Could you suggest anything if db was in-memory? I have h2 in-memory db and it has public.roles table, but when I run query from intellij it shows : "Table "ROLES" not found; SQL statement:". I deem IDEA is creating its own in-memory db instead of trying to connect the one which is in my project – Deventer

And to access the console : localhost:8080/h2-console. then in the "JDBC URL" field, put also jdbc:h2:~/mydbInHomeDir;AUTO_SERVER=TRUE – Seko

To anyone else finding this later: The REMOTE part is very important. IntelliJ keeps setting it back to embedded. – Lambda

Answer 2

To add to what heenenee mentioned above. If you dont specify AUTO_SERVER only one connection will be permitted to your H2 instance.

I am using spring-boot with spring-data-jpa. Make sure you have @Entity declared for your entities that represent each table(s).

Following is my application.yml / application.properties

spring.datasource.url: 
jdbc:h2:file:/Users/blah[![enter image description here][1]][1]/db/vlad4;AUTO_SERVER=TRUE
spring.datasource.username: sa
spring.datasource.password:

spring:
  jpa:
    hibernate:
      ddl-auto: create #will create schema based on entities
    show-sql: true

Start your application and import some data into it. Spring boot will automatically import your data if you have import.sql in the classpath ex: /src/main/resources/import.sql

Configure you IntelliJ like so

If you are not using IntelliJ download the server/client combo @ http://www.h2database.com/html/download.html extract it and start the browser-based client using:

h2/bin: java -cp h2*.jar org.h2.tools.Server

Connect to your imbedded database by specifying the connection string:

Oligosaccharide

Yay, it worked, thanks! Could you please help me connect to this DB with a DriverManager.getConnection? I've added the same AUTO_SERVER=TRUE to the URL for it, but it still doesn't see the table :( – Advice

UPD: Resolved. The DB had been re-created for some reason at that time. Simply created a table once more, and the application finallybfound it. – Advice

Answer 3

I had similar problem. It was due to the default create-drop ddl strategy of the hibernate. With this strategy after the application shutdown hibernate destroys the schema at the end of the session, that's why IntelliJ don't show anything. Change ddl strategy to the create and hibernate will create the schema and destroy previous data on the next application startup.

Here is an example of my configuration:

application.yml

spring:
  datasource.url: jdbc:h2:./db/testDb
  jpa.hibernate.ddl-auto: create

IntelliJ database properties

Result

Anesthetic

Thank you for this, maybe it will help somebody. I've moved on to other frameworks. – Kalina

Answer 4

If you follow the steps in this article: https://techdev.io/en/developer-blog/querying-the-embedded-h2-database-of-a-spring-boot-application

I think it will provide the help in getting a Spring Boot application with H2 in-memory database exposed via a tcp server such that you can use the IntelliJ database client to connect to it.

Ludwig

Can you provide another link> Because the one you mentioned above is expired. It is redirecting to somewhere else – Deventer

This situation shows why it's better to write the solution in the Stackoverflow answer rather than link to an external site that may disappear. – Somerville

Answer 5

Using the example here to expose an in-memory DB via console and TCP I was able to connect using H2 Console and IntelliJ client as per screenshots.

https://mcmap.net/q/244780/-h2-db-not-accessible-at-localhost-8080-h2-console-when-using-webflux

Connect using IntelliJ - jdbc:h2:tcp://localhost:9092/mem:default

Connect using H2 Console: jdbc:h2:mem:default

Example application.yml

spring:
  application:
    name: example-service
  r2dbc:
    url: r2dbc:pool:h2:mem:///default?options=DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: testuser
    password: testpass
    pool:
      initial-size: 100
      max-size: 500
      max-idle-time: 30m
      validation-query: SELECT 1

Unfrock