Reputation: 661

springboot 2.3.0 while connecting to h2 database

In Springboot 2.3.0.RELEASE I am getting the the following error while connecting to h2 database in the console

Database "mem:testdb" not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149-200] 90149/90149

Upvotes: 56

Answers (30)

Bhargab Nath

Reputation: 11

To resolve this issue, we need to make sure the following 2 points:

1 Ensure application.properties includes:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

2.1 For Gradle, include the following in your build.gradle:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    runtimeOnly 'com.h2database:h2'
}

2.2: For Maven, add the following to your pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

These configurations will allow your Spring Boot application to connect to the H2 database successfully.

Upvotes: 0

Archana Hassan

Reputation: 41

Step 1: add the dependencies in pom.xml file.

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

Step 2:

add below code in the application.properties file:

spring.h2.console.enabled=true

spring.datasource.username=sergey

spring.datasource.password=sergey

spring.datasource.url=jdbc:h2:mem:testdb

spring.datasource.driverClassName=org.h2.Driver

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Step 3 :

run : http://localhost:8099/h2-console/

and entered the below details:

jdbc url : jdbc:h2:mem:testdb
user name : sergey
password : sergey

it worked for me .

Upvotes: 2

ayman.mostafa

Reputation: 619

I am using spring boot 2.5. I tried many things mentioned above to solve the issue such as adding spring.datasource.generate-unique-name=false and add "INIT=CREATE SCHEMA" in the spring.datasource.url to be something like spring.datasource.url=jdbc:h2:mem:apiDB;INIT=CREATE SCHEMA IF NOT EXISTS user_scheme\;SET SCHEMA user_scheme;

But Nothing solved the issue

But when I added the below depedency to my pom.xml

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

The issue was gone

I think the main reason is that in case of in-memory DB, spring needs a DB connection to execute creating the DB (apiDB), and this can not be done without spring data-jpa library otherwise it will complains that the DB does not exist.

Do not forget to add the below line to your application.properties

spring.h2.console.enabled=true

Upvotes: 1

Prasad Matha

Reputation: 11

copy the jdbc url --> jdbc:h2:mem:test like below. then your problem will be solved.

h2-console

Upvotes: 1

Dhruv Bhavsar

Reputation: 11

Remove the spring-boot-devtools dependency from pom.xml and restart the application. for me, this worked.

Upvotes: 1

kanik

Reputation: 451

You are not able to connect to database because you are using old JDBC URL. Every time you start a spring project, JDBC URL changes as well.

Solution: Copy the JDBC URL from console every time you want to connect to a database or just use below property in application.properties file spring.datasource.url=jdbc:h2:mem:testdb

Upvotes: 28

supernova

Reputation: 3261

Looks like everybody trying their own ways but solution to problem is NOT configuration but PHYSICAL. See @Subramanian answer.

Somehow , If you want to connect via console, Spring is unable to create a PHYSICAL file testdb.mv.db in root folder, i.e C:/USERS/testdb.mv.db. I went to C:/Users/SHEKHAR , and created a New > text file ( don't save it untill you rename it to testdb.mv.db or .mv.db. Restart Springboot, "Test Connection" is Green and Connect , takes me to Console GUI.

Upvotes: 0

Iftekhar Alam

Reputation: 1

I am using Spring 2.7.3. I had the same issue with mem:testdb not found. After trying out all the different solutions I could find in Stackoverflow, the only solution that worked for me was adding both "spring.datasource.url=jdbc:h2:mem:testdb" AND "spring.datasource.generate-unique-name=false" in my application properties file.

I thought that by adding just the "spring.datasource.url=jdbc:h2:mem:testdb" should stop spring from generating unique name everytime I restart the application but apparently not. Manually creating the test.mv file did not work for me either. Hope this helps.

Upvotes: 0

Suprem Vanam

Reputation: 132

Adding JPA Dependency to pom.xml fixed this error for me.

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

Here is my application.properties file, just for your reference.

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testapp
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Upvotes: 0

Shiru99

Reputation: 459

It's error of H2-Database:

Solution -

Let's suppose ~/Test-H-2/Project-X/DB-files path of your project (Database files)

create myTest.mv.db file in ~/Test-H-2/Project-X/DB-files folder
Add jdbc:h2:~/Test-H-2/Project-X/DB-files/myTest to JDBC URL in H2-Database console

simply you can create ~\test.mv.db file & add jdbc:h2:~/test in to JDBC URL in H2-Database console

Upvotes: 1

Sharad Rakesh Maddheshiya

Reputation: 49

NOTE:->spring.datasource.url=jdbc:h2:mem:nilesh-->Should be same in console(JDBC URL)

application.properties

spring.h2.console.enabled=true
spring.datasource.platform=h2
spring.datasource.url=jdbc:h2:mem:nilesh

Upvotes: 2

oldium

Reputation: 171

Adding to answers, which mention auto-generated database name in Spring Boot 2.3+ – this is the way how to get the generated name into H2 Console programatically in Spring Boot, so that you can keep the generated database name. It basically gets the first H2 database source and updates/generates the H2 Console configuration file ~/.h2.server.properties, which is then loaded by H2 Console when it is first accessed.

Configure pom.xml to use H2 types directly:

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>compile</scope>
        </dependency>

Enable H2 Console in application.properties (true is a default value):

spring.h2.console.enabled=true

Code to use auto-generated database name:

import java.io.OutputStream;
import java.sql.Connection;
import java.util.List;
import java.util.Objects;
import java.util.Properties;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.h2.engine.Constants;
import org.h2.store.fs.FileUtils;
import org.h2.util.SortedProperties;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Configuration;

@Configuration
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class H2ConsoleDatabaseAutoConfigurator {

  @Autowired
  public void configure(ObjectProvider<DataSource> dataSource) throws Exception
  {
    Properties properties = SortedProperties.loadProperties(Constants.SERVER_PROPERTIES_DIR + "/" + Constants.SERVER_PROPERTIES_NAME);

    List<String> urls = dataSource.orderedStream().map((available) -> {
      try (Connection connection = available.getConnection()) {
        if (connection.getMetaData().getURL().startsWith("jdbc:h2:mem:")) {
          return connection.getMetaData().getURL() + "|" + connection.getMetaData().getUserName();
        } else {
          return null;
        }
      }
      catch (Exception ex) {
        return null;
      }
    }).filter(Objects::nonNull).collect(Collectors.toList());

    if (urls.size() > 0)
    {
      for (int i = 0;; i++)
      {
        String value = properties.getProperty(String.valueOf(i), null);
        if (value == null || value.startsWith("Local H2|")) {
          properties.setProperty(String.valueOf(i), "Local H2|org.h2.Driver|" + urls.get(0));
          break;
        }
      }

      OutputStream out = FileUtils.newOutputStream(
          Constants.SERVER_PROPERTIES_DIR + "/" + Constants.SERVER_PROPERTIES_NAME, false);
      properties.store(out, "H2 Server Properties");
      out.close();
    }
  }
}

The console will contain the current H2 name as Local H2 menu entry:

The code is a composite of sources from H2 Console and Spring Boot H2 Console Autoconfiguration.

Upvotes: 0

Sajjad Serajzadeh

Reputation: 361

my solution for this problem is:

in case you you didn't made a database folder in home directory (in windows under the: C:\Users*USERNAME\test* || in Linux: under the: ~/test) make it and add below lines to application.properties:

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:h2:~/test
spring.jpa.hibernate.ddl-auto=create-drop

it should help you to login.

((use blank username and password for H2))

Upvotes: 0

Tharindu Krishan

Reputation: 177

Add property to application.properties:

spring.datasource.generate-unique-name=false

I had also missed adding the following dependencies to pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
    
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Upvotes: 2

Cagdas Karabulut

Reputation: 31

I added "test.mv.db" file into "C:\Users\YourUserName" then restart the server and it's worked.

Upvotes: 3

NAVED HASHMY

Reputation: 31

Use h2 dependency with the old version (1.4.192).

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.192</version>
    <scope>runtime</scope>
</dependency>

Upvotes: 3

Imane Erguiti

Reputation: 117

I had the same problem and I got it solved this way

Go to application.properties
Add the following :

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

now you can use sa for username and password for password

PS : that's usually the by default configuration that spring boot uses

Upvotes: 3

Ankush Thakur

Reputation: 141

Please use this below solution it's working.

If you are dealing with the Spring Boot project, please change the JDBC URL jdbc:h2:~/test to jdbc:h2:mem:testdb in the login page, which is the default URL configured by Spring Boot.

Upvotes: -3

qqqqDonl

Reputation: 43

I connect h2 by jdbc:h2:mem:testdb but not jdbc:h2:~/testdb

Upvotes: 2

Chamod Dissanayake

Reputation: 716

Adding Spring-boot started JDBC solved my issue.

https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc/2.5.2

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.5.2</version>
</dependency>

Upvotes: 1

rrlinus

Reputation: 39

Add these two properties in application.property file

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb

type url: localhost:<portnumber>/h2-console in browser You will get a page regrading login database Remove existing JDBC url in the page and put this jdbc:h2:mem:testdb

Upvotes: 0

Subramanian

Reputation: 561

Actually, your h2 databse is looking for a file called test.mv.db. But that file was not present in your user directory. So, that it just slapping you and asking you to pre-create it in that path.

Note: That is the root file where our H2 DB store all our information.

Add below line in your application.properties file
spring.datasource.url = jdbc:h2:mem:testdb
Go to your user directory, in my case, it is (C:\Users\subra)
Create a new file called test.mv.db and saved it under all file option like below.
Now restart your app.
Done

Reference Screenshot:

My Problem:

My Problem

The Result

Upvotes: 56

Pushkar Mehta

Reputation: 29

Replace the default JDBC url of h2-console from application.properties to use JDBC Url=jdbc:h2:mem:testdb Below is the application.properties

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=user
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true

Upvotes: 0

egemen

Reputation: 839

I had same issue in windows 10. Try to replace

spring.datasource.url=jdbc:h2:~/testdb

with

spring.datasource.url=jdbc:h2:mem:testdb

Upvotes: 0

Aldo aldo

Reputation: 412

In case this helps anybody. My issue was because I have this in my application.properties

spring.autoconfigure.exclude=org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration

Not sure why I added this config before. But once this is taken out, it solves my issue

Upvotes: 1

Mahalaxmi Chinchani

Reputation: 31

Even I came across the same set of errors when started learning data persistence with h2 database. The solution is in the console log itself. The database name is auto-generated by and can be found in the spring logs. The reason behind the error is after from 2.3.0 version release onwards if the URL is not mentioned its auto-generated.

Here is the log message which includes database URL: INFO 16096 --- [ restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:f1dfeade-60dd-4809-8978-42547353b2f3'

Upvotes: 3

Krangerich

Reputation: 343

Another reason for the error could be a the missing JDBC dependency:

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

I got the exact same error and this was the issue in my case.

Upvotes: 20

Yoshita Mahajan

Reputation: 453

Step 1. In application.properties:

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb

Step 2. Start your Spring Boot App and open:

http://localhost:8080/h2-console/

If you still face issue try pasting the URL value which you mentioned in application.properties jdbc:h2:mem:testdb in

JDBC URL of h2-console

Then you wont face below mentioned issue Database h2 not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149-200] 90149/90149 (Help)

Upvotes: 36

thekirmizi

Reputation: 89

Create a file with a .mv.db extension in your project's folder. Make sure that the file's name ends with .mv.db otherwise H2 console does not recognize the file (I know it doesn't make sense and I have no explanation why :D). After creating the file, enter jdbc:h2:./devdb to h2 console's JDBC URL like this:

Check this image out

Then you should be OK!

Upvotes: 6

DEWA Kazuyuki - 出羽和之

Reputation: 2643

Since 2.3.0, default value of generate-unique-name is true.

You can revert to the previous behavior by following setting:

spring.datasource.generate-unique-name=false

Upvotes: 2

springboot 2.3.0 while connecting to h2 database

Answers (30)

To resolve this issue, we need to make sure the following 2 points:

1 Ensure application.properties includes:

2.1 For Gradle, include the following in your build.gradle:

2.2: For Maven, add the following to your pom.xml:

My Problem:

The Result

Related Questions