Connecting to H2 database from H2 Console

Question

I have used the below settings in my Application properties file. But still cant see my tables in h2 console.

application.properties

logging.level.org.springframework.web=INFO
spring.datasource.url=jdbc:h2:mem:challenge;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MV_STORE=FALSE
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.schema=classpath:/schema.sql
spring.datasource.data=classpath:/data.sql
spring.h2.console.enabled=true
spring.h2.console.path=/h2
server.port = 8080

I used the string jdbc:h2:mem:challenge;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MV_STORE=FALSE in the JDBC URL on H2 console to login. Yet i am not seeing any tables

The below is my Graddle file

buildscript {
    ext {
        springBootVersion = '1.4.4.RELEASE'
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'

jar {
    baseName = 'challenge'
    version = '0.0.1-SNAPSHOT'
}

sourceCompatibility = 1.8

repositories {
    mavenCentral()
}


dependencies {
    compile('org.springframework.boot:spring-boot-starter-jdbc')
    compile('org.springframework.boot:spring-boot-starter-security')
    compile('org.springframework.boot:spring-boot-starter-web')
    runtime('org.springframework.boot:spring-boot-devtools')
    runtime('com.h2database:h2')
    testCompile('org.springframework.boot:spring-boot-starter-test')
}

The following is the class which implements Springsecurity for my spring boot app

@Configuration
@EnableWebSecurity
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Override
    protected void configure(HttpSecurity http) throws Exception {

     http.authorizeRequests().antMatchers("/h2/*").permitAll();


        http.csrf().disable();
        http.headers().frameOptions().disable();


    }
}

Thanks for your comments. But I did read all the other posts, none of them solve my problem. I have started my Spring-boot application which is basically a web service which connects to H2 database and fetches all the information. The webservice is displaying the info, but when i am logging into the H2 console, i dont see any tables or database — Vinayak, Jul 28 '17 at 22:12
@Vinayak See my answer below - it seems that when an H2 in-memory database is created, the database name you provide in your properties file is ignored and it's named `testdb` by default. The URL and username should be output to the console when the Spring app starts up. — jones-chris, Aug 12 '20 at 11:45

AchillesVan · Answer 1 · 2017-07-29T02:06:24.140

8

The H2 engine offers a console where you can see all the tables and its data. This console is a web app. So, what you need to get access to the H2 console is to include the spring-boot-starter-web pom dependency to your pom.xml .

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

You also need to add the following property to the src/main/resources/application.properties file.

spring.h2.console.enabled=true

The H2 web console can be accessed here (default link) : http://localhost:8080/h2-console

You should see the Driver class, the JDBC URL, and the credentials. If the JDBC URL is not the same, modify its value to jdbc:h2:mem:yourdbName .

Now if you have the spring-boot-starter-security dependency in your project a line needs to be added to the configure method of the SecurityConfig in your project , otherwise you will see an empty page after logging into the H2 console:

http.headers().frameOptions().disable();

Alternatively, the following line can be used :

http.headers().frameOptions().sameOrigin();

edited Jul 29 '17 at 02:06

answered Jul 28 '17 at 22:20

AchillesVan

4,156
3
35
47

1

I have already done the above as mentioned including the following: spring.h2.console.enabled=true (Application.properties file) Included the spring-boot-starter-web in gradle also. But still i am facing this issue – – Vinayak Jul 29 '17 at 02:02
First try removing spring security for testing purpose to see if it works – AchillesVan Jul 29 '17 at 02:08
I have disabled spring security by including "security.basic.enabled=false" in Application.properties file and also i have attached my spring security file for your reference – Vinayak Jul 29 '17 at 04:28
My problem is that the server doesn't answer while the code is waiting on a breakpoint .. – user1767316 Jan 15 '20 at 14:33
no time remains, to use the WEB UI if the UI dosn't answer when code is stoped on breakpoint. – user1767316 Jan 15 '20 at 15:50
Answer here: https://stackoverflow.com/questions/35179110/spring-boot-is-blocking-h2-console-in-debug-mode to the freezing. – user1767316 Jan 15 '20 at 16:00
1

@AchillesVan thanks for your reference to spring security – Timothy Vogel Mar 28 '23 at 01:21

score 1 · Answer 2 · answered May 19 '20 at 12:46

Use a pre-2019 version of the H2 database dependency that will auto-creates the database every time you run standalone application.

<dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.193</version>
</dependency>

jones-chris · Answer 3 · 2021-10-19T01:22:42.657

When the Spring Boot application starts, you should output similar to this:

2020-08-12 07:30:10.902  INFO 44404 --- [           main] net.querybuilder4j.Application           : Starting Application on sm7chrisjones with PID 44404 (/Users/chris.jones/repos/qb4j-api/target/classes started by chris.jones in /Users/chris.jones/repos/qb4j-api)
2020-08-12 07:30:10.905  INFO 44404 --- [           main] net.querybuilder4j.Application           : No active profile set, falling back to default profiles: default
2020-08-12 07:30:11.906  INFO 44404 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-08-12 07:30:11.913  INFO 44404 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-08-12 07:30:11.913  INFO 44404 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.30]
2020-08-12 07:30:11.985  INFO 44404 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-08-12 07:30:11.986  INFO 44404 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1032 ms
2020-08-12 07:30:11.999  INFO 44404 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
2020-08-12 07:30:12.085  INFO 44404 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'
2020-08-12 07:30:12.532  INFO 44404 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-08-12 07:30:12.681  WARN 44404 --- [           main] ion$DefaultTemplateResolverConfiguration : Cannot find template location: classpath:/templates/ (please add some templates or check your Thymeleaf configuration)
2020-08-12 07:30:12.801  INFO 44404 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-08-12 07:30:12.804  INFO 44404 --- [           main] net.querybuilder4j.Application           : Started Application in 2.228 seconds (JVM running for 2.816)

I noticed these lines:

2020-08-12 07:30:11.999  INFO 44404 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
2020-08-12 07:30:12.085  INFO 44404 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'

I placed the url and username in the H2 console and was able to connect successfully. Without this output, it would have taken a long time to figure out, because I called the in-memory database myDb in the application.properties, but apparently that's ignored and all H2 in-memory databases are called testdb.

I'm using these spring-boot-starter-web and h2 maven dependencies in case you find that another version of the dependencies does not provide this output on application start up:

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.2.3.RELEASE</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version>
    <scope>runtime</scope>
</dependency>

Connecting to H2 database from H2 Console

3 Answers3