How to "allow remote database creation" when using h2 database?

Question

I'm trying to create a Spring Boot project with H2 database, which would be accessible by other programs.

application.properties

spring.datasource.url = jdbc:h2:tcp://localhost:8084/~/./db/tech
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.initialization-mode=always

SpringBootMyApplication.java

@SpringBootApplication
public class SpringBootMyApplication{

    public static void main(String[] args) {
        SpringApplication.run(SpringBootMyApplication.class, args);
    }

    @Bean(initMethod = "start", destroyMethod = "stop")
    public Server h2Server() throws SQLException {
        return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "8084");
    }

}

The exception is:

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database "C:/Users/onz03589/db/tech" not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149-200]

How to actually "allow remote database creation"?

Intranuclear

You should never do that when you use -tcpAllowOthers, it effectively creates a remote security hole in your system. Do you really have a reason to enable remote access to your server? – Alderson

@EvgenijRyazanov it's an internal server without any internet connection. – Intranuclear

Answer 1

You need to add "-ifNotExists" parameter to Server.createTcpServer(). But, again, you shouldn't use it together with "-tcpAllowOthers" unless your port is guarded somehow.

Alderson

Answer 2

You need to add spring boot starter jpa dependency to enable autoconfiguration for h2 database setup.

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

Inventory

I was working on creating a basic application and missed to add this dependency. Upon Adding this dependency, the database got created automatically – Pintail

Answer 3

The reason might be

1. After you run the server and hit the console the spring.datasource.url is not populated properly. Copy paste the url from application.properties file
2. You might have made mistake in properties uppercase and lowercase e.g. I have used

spring.datasource.driverclassname

instead of

spring.datasource.driverClassName

So check the casesensitive property names

Waxman

Answer 4

I added these two dependencies in the pom.xml

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


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

And added below in the Application.properties

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

And it worked for me.

If you have any tables or data to be inserted while creating database. You can do it by putting .sql files under resources directory

Now, you can open your database on http://localhost:port/h2-console

Avril

Answer 5

I created a file: "application.yml" and added the following: It works

spring:
  jpa:
    hibernate:
      ddl-auto: create
    show-sql: true
  datasource:
    driverClassName: org.h2.Driver
    password: ''
    username: sa
    url: jdbc:h2:file:~/h2/userApiValidationAndException 

(userApiValidationAndException creat your own file)

Lucier

Answer 6

Try adding these to application.properties it worked for me

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

Irkutsk

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Unclinch