Spring Boot default H2 jdbc connection (and H2 console)

Question

I am simply trying to see the H2 database content for an embedded H2 database which spring-boot creates when I don't specify anything in my application.properties and start with mvn spring:run. I can see hibernate JPA creating the tables but if I try to access the h2 console at the URL below the database has no tables.

http://localhost:8080/console/

I see suggestions like this one: View content of embedded H2 database started by Spring

But I don't know where to put the suggested XML in spring-boot and even if I did, I don't want the h2console to be available anymore when an external database is configured so it is more likely that I need to handle this with some kind of conditional code (or maybe just allow spring to automatically handle it in the most ideal case where I only include H2 when a maven profile is activated).

Does anyone have some sample code showing how to get the H2 console working in boot (and also the way to find out what the jdbc connection string that spring is using is)?

Aaron Zeckoski · Accepted Answer · 2014-07-13 22:57:31Z

137

This is how I got the H2 console working in spring-boot with H2. I am not sure if this is right but since no one else has offered a solution then I am going to suggest this is the best way to do it.

In my case, I chose a specific name for the database so that I would have something to enter when starting the H2 console (in this case, "AZ"). I think all of these are required though it seems like leaving out the spring.jpa.database-platform does not hurt anything.

In application.properties:

spring.datasource.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

In Application.java (or some configuration):

@Bean
public ServletRegistrationBean h2servletRegistration() {
    ServletRegistrationBean registration = new ServletRegistrationBean(new WebServlet());
    registration.addUrlMappings("/console/*");
    return registration;
}

Then you can access the H2 console at {server}/console/. Enter this as the JDBC URL: jdbc:h2:mem:AZ

answered Jul 13, 2014 at 22:57

Aaron Zeckoski

5,1868 gold badges29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Splaktar Over a year ago

Using new WebServlet() give me issues. Which class is this importing for you? It's pulling in javax.servlet.annotation.WebServlet for me as the only option and it is just an interface.

Splaktar Over a year ago

Oh I got it, org.h2.server.web.WebServlet is the one that is needed. My project wasn't in sync with Gradle to pull in the new h2 libraries.

Splaktar Over a year ago

The application.properties pieces aren't needed. You can just use the Application.java part that you put, then connect to jdbc:h2:mem:testdb with a blank username and blank password. localhost:8082 works with this setup.

nerdherd Over a year ago

@Splaktar Thank you! I was going nuts trying to figure out how to connect and the missing piece of the puzzle was to use "testdb" for the database name.

karthik m Over a year ago

@Splaktar - you should make your comment the answer. Like you said the source code for EmbeddedDatabaseConnection shows all the default Embedded DB Connection URIs

|

Sinisha Mihajlovski · Accepted Answer · 2018-01-28 11:21:30Z

62

As of Spring Boot 1.3.0.M3, the H2 console can be auto-configured.

The prerequisites are:

You are developing a web app
Spring Boot Dev Tools are enabled
H2 is on the classpath

Even if you don't use Spring Boot Dev Tools, you can still auto-configure the console by setting spring.h2.console.enabled to true

Check out this part of the documentation for all the details.

Note that when configuring in this way the console is accessible at: http://localhost:8080/h2-console/

edited Jan 28, 2018 at 11:21

Sinisha Mihajlovski

1,8511 gold badge21 silver badges35 bronze badges

answered Sep 4, 2015 at 10:06

geoand

64.9k24 gold badges185 silver badges201 bronze badges

7 Comments

keiki Over a year ago

Or as explained in the doc you enable it with spring.h2.console.enabled=true. With the prerequisites it's enabled automatically.

garci560 Over a year ago

What does You are developing a web app exactly mean?

geoand Over a year ago

In the context of Spring Boot, it means that you have added spring-boot-starter-web as a dependency

anujprashar Over a year ago

Also I think when you open localhost:8080/h2-console you need to write jdbc:h2:mem:testdb in jdbc url to see your tables. And in url localhost:8080/h2-console after localhost you need to specify port of application.

geoand Over a year ago

@anujprashar I am pretty sure jdbc:h2:mem:testdb is for the connection URL, not for the URL were the console is accessible

|

Krzysztof Kaczor · Accepted Answer · 2015-08-09 14:22:08Z

45

I have found a nice tutorial about this topic:

https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/

Basically the correct JDBC URL for me was: jdbc:h2:mem:testdb

answered Aug 9, 2015 at 14:22

Krzysztof Kaczor

5,6167 gold badges43 silver badges47 bronze badges

3 Comments

Dan Vega Over a year ago

dumb question but this seems to be the default JDBC URL because the setting spring.datasource.name defaults to testdb. Why if thats the case if I change the db name to something like foodb is the jdbc still the same and not jdbc:h2:mem:foodb

Kamal Joshi Over a year ago

This jdbc url jdbc:h2:mem:testdb is correct. It helped me finally connect to the default database created by spring data jpa.

Aleksei Maide Over a year ago

You can set the db name as anything such as: spring.datasource.url=jdbc:h2:mem:test; in application.properties

mancini0 · Accepted Answer · 2019-11-15 15:47:32Z

27

From http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html

H2 Web Console (H2ConsoleProperties):

spring.h2.console.enabled=true //Enable the console.
spring.h2.console.path=/h2-console //Path at which the console will be available.

Adding the above two lines to my application.properties file was enough to access the H2 database web console, using the default username (sa) and password (empty, as in don't enter a password when the ui prompts you).

edited Nov 15, 2019 at 15:47

answered Dec 12, 2015 at 1:34

mancini0

4,8232 gold badges32 silver badges32 bronze badges

2 Comments

georger Over a year ago

Shouldn't be spring.h2.console.enabled=true? False will disable it. And spring.h2.console.path=/h2-console is redundant because /h2-console is the default path from Spring Boot. As per documentation "By default the console will be available at /h2-console. You can customize the console’s path using the spring.h2.console.path property." More details here docs.spring.io/spring-boot/docs/current/reference/html/…

mancini0 Over a year ago

I included the latter property to show OP where he can access the console. Obviously spring.h2.console.enabled.enabled=false will disable the console, the point is that this is configurable as a property. I'll set that property to true for clarity.

biniam · Accepted Answer · 2016-10-12 04:53:18Z

21

A similar answer with Step by Step guide.

Add Developer tools dependency to your pom.xml or build.gradle

Maven

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

Gradle

dependencies {
    compile("org.springframework.boot:spring-boot-devtools")
}

Access the db from http://localhost:8080/h2-console/
Specify jdbc:h2:mem:testdb as JDBC URL
You should see the entity you specified in your project as a table.

answered Oct 12, 2016 at 4:53

biniam

8,2479 gold badges51 silver badges58 bronze badges

1 Comment

Raja Nagendra Kumar Over a year ago

adding this in dependency section worked for me 'runtime("com.h2database:h2")'

Witold Kaczurba · Accepted Answer · 2019-01-05 22:12:38Z

21

For Spring Boot 2.1.1 straight from Spring Initialzr:

Default with devtools is http://127.0.0.1:8080/h2-console/
- POM: spring-boot-starter, h2, spring-boot-starter-web, spring-boot-devtools
Without devtools - you need to set it in properties: spring.h2.console.enabled=true spring.h2.console.path=/h2-console
- POM: spring-boot-starter, h2, spring-boot-starter-web

Once you get there - set JDBC URL: jdbc:h2:mem:testdb (The default one will not work)

answered Jan 5, 2019 at 22:12

Witold Kaczurba

10.6k3 gold badges66 silver badges70 bronze badges

Comments

rak22 · Accepted Answer · 2016-05-01 19:04:51Z

17

I had only below properties in /resources/application.properties. After running spring boot, using this URL(http://localhost:8080/h2-console/), the table in H2 console was visible and read to view the table data, also you can run simple SQL commands. One thing, in your java code, while fetching data, the column names are upper-case, even though schema.sql is using lower-case names :)

spring.datasource.initialize=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=- 1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

answered May 1, 2016 at 19:04

rak22

3384 silver badges13 bronze badges

Comments

vaquar khan · Accepted Answer · 2018-12-20 02:23:09Z

17

Check spring application.properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

here testdb is database defined Make sure h2 console have same value while connecting other wise it will connect to default db

answered Dec 20, 2018 at 2:23

vaquar khan

11.5k7 gold badges81 silver badges103 bronze badges

1 Comment

mazend Over a year ago

Thanks. You saved my day. I missed to change the JDBC URL and thought that 'Why password changing not working???'

RahulDeep Attri · Accepted Answer · 2020-08-18 15:56:34Z

16

For Spring Boot 2.3.3.RELEASE straight from Spring Initialzr:

POM: data jpa, h2, web

application properties: spring.h2.console.enabled=true

When you run the application look for line like below in the run console:

2020-08-18 21:12:32.664  INFO 63256 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:eaa9d6da-aa2e-4ad3-9e5b-2b60eb2fcbc5'

Now use the above JDBC URL for h2-console and click on Connect.

answered Aug 18, 2020 at 15:56

RahulDeep Attri

4184 silver badges13 bronze badges

Comments

georger · Accepted Answer · 2017-04-05 20:47:43Z

10

If you use Spring Boot's developer tools, it comes with H2 Console enabled by default. It can be accessed from /h2-console/. On the login interface, for input JDBC URL use value jdbc:h2:mem:testdb. Pay attention to mem string.

If you don't use Spring Boot's developer tools, you can enable the console in application.properties using spring.h2.console.enabled=true. This will enable console under /h2-console. If you want to change the URL then you can add another entry with spring.h2.console.path=my_console_path.

The default schema name is testdb.

More details in Spring Boot Documentation.

answered Apr 5, 2017 at 20:47

georger

1,68822 silver badges24 bronze badges

1 Comment

Sudip Bhandari Over a year ago

Why isn't jdbc:h2:mem:testdb set as default jdbc url? I spent a lot of time pondering where my jpa entities went wrong

vaquar khan · Accepted Answer · 2020-07-09 22:10:54Z

9

In order to get the tables all you need to do is create 2 sql files schema.sql(for table creation) and data.sql(data for the created tables). These files to be put in src/main/resources folder. Spring boot auto detects them and takes care of the rest during runtime.

If your using more than 2 DB in your project ensure to use specific files like (schema-h2.sql -- for h2 DB , schema-oracle.sql -- for oracle DB). The same to be followed for data.sql too.

Also ensure that you drop tables by adding drop table statement in your schema.sql as first statement. To avoid appending of duplicate records.

The link for spring boot is here.

My application.properties is as follows.

spring.datasource.url=jdbc:h2:~/file/Shiva;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.platform=h2
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.datasource.initialize=true 
spring.error.whitelabel.enabled=true
spring.h2.console.path=/console
spring.datasource.continue-on-error=true
spring.jpa.hibernate.ddl-auto=create
spring.hibernate.hbm2ddl.auto=update
spring.hibernate.show_sql=true

You can follow the steps in the below link.

https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/

edited Jul 9, 2020 at 22:10

vaquar khan

11.5k7 gold badges81 silver badges103 bronze badges

answered Nov 5, 2016 at 23:54

kemparaj565

3854 silver badges6 bronze badges

1 Comment

Yura Over a year ago

"spring.jpa.hibernate.ddl-auto" twice with different values...

Pragnesh Rana · Accepted Answer · 2018-05-15 20:19:42Z

3

I found that with spring boot 2.0.2.RELEASE, configuring spring-boot-starter-data-jpa and com.h2database in the POM file is not just enough to have H2 console working. You must configure spring-boot-devtools as below. Optionally you could follow the instruction from Aaron Zeckoski in this post

  <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
 </dependency>

answered May 15, 2018 at 20:19

Pragnesh Rana

312 bronze badges

Comments

user9023317 · Accepted Answer · 2018-08-31 20:09:50Z

3

Use jdbc:h2:mem:testdb as your path when logging into the H2 console.

Obviously if you have altered Spring Boot properties your datasource may be different, but it seems like you're struggling with how to find the default. That's all there is to it! You'll see your schema after logging in to H2.

answered Aug 31, 2018 at 20:09

user9023317

Comments

AbhishekB · Accepted Answer · 2019-03-27 06:02:39Z

1

I had made a very stupid mistake when I had this same problem. I had added H2 DB for running unit test cases and hence I had set the scope to test in pom.xml. While running the application using mvn spring:run I removed the scope and it works fine now.

answered Mar 27, 2019 at 6:02

AbhishekB

2,0853 gold badges18 silver badges24 bronze badges

Comments

AAAA · Accepted Answer · 2024-11-01 11:16:53Z

0

None of the answers helped me, in fact it logged

H2 console available at '/h2'. Database available at 'jdbc:h2:mem:test'

but this wasn't the case, because Spring didn't started the web server.

What helped was writing in the JUnit Test class

@SpringBootTest(webEnvironment= SpringBootTest.WebEnvironment.DEFINED_PORT)

Then the web server started and the h2 console is accessible.

answered Nov 1, 2024 at 11:16

AAAA

354 bronze badges

Comments

Subham Kr Gupta · Accepted Answer · 2024-12-23 09:53:24Z

0

h2 screen db url and this application.properties url should be same

# H2 Connection
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=root
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

answered Dec 23, 2024 at 9:53

Subham Kr Gupta

417 bronze badges

Collectives™ on Stack Overflow

Spring Boot default H2 jdbc connection (and H2 console)

16 Answers 16

6 Comments

7 Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

16 Answers 16

6 Comments

7 Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Related