H2 in-memory database. Table not found

Question

I've got a H2 database with URL "jdbc:h2:test". I create a table using CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64));. I then select everything from this (empty) table using SELECT * FROM PERSON. So far, so good.

However, if I change the URL to "jdbc:h2:mem:test", the only difference being the database is now in memory only, this gives me an org.h2.jdbc.JdbcSQLException: Table "PERSON" not found; SQL statement: SELECT * FROM PERSON [42102-154]. I'm probably missing something simple here, but any help would be appreciated.

Answer 1

DB_CLOSE_DELAY=-1

hbm2ddl closes the connection after creating the table, so h2 discards it.

If you have your connection-url configured like this

jdbc:h2:mem:test

the content of the database is lost at the moment the last connection is closed.

If you want to keep your content you have to configure the url like this

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

If doing so, h2 will keep its content as long as the vm lives.

Notice the semicolon (;) rather than colon (:).

See the In-Memory Databases section of the Features page. To quote:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

Answer 2

I know this was not your case but I had the same problem because H2 was creating the tables with UPPERCASE names then behaving case-sensitive, even though in all scripts (including in the creation ones) i used lowercase.

Solved by adding ;DATABASE_TO_UPPER=false to the connection URL.

Answer 3

For Spring Boot 2.4+ use spring.jpa.defer-datasource-initialization=true in application.properties

Answer 4

Hard to tell. I created a program to test this:

package com.gigaspaces.compass;

import org.testng.annotations.Test;

import java.sql.*;

public class H2Test {
@Test
public void testDatabaseNoMem() throws SQLException {
    testDatabase("jdbc:h2:test");
}
@Test
public void testDatabaseMem() throws SQLException {
    testDatabase("jdbc:h2:mem:test");
}

private void testDatabase(String url) throws SQLException {
    Connection connection= DriverManager.getConnection(url);
    Statement s=connection.createStatement();
    try {
    s.execute("DROP TABLE PERSON");
    } catch(SQLException sqle) {
        System.out.println("Table not found, not dropping");
    }
    s.execute("CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64))");
    PreparedStatement ps=connection.prepareStatement("select * from PERSON");
    ResultSet r=ps.executeQuery();
    if(r.next()) {
        System.out.println("data?");
    }
    r.close();
    ps.close();
    s.close();
    connection.close();
}
}

The test ran to completion, with no failures and no unexpected output. Which version of h2 are you running?

Answer 5

When opening the h2-console, the JDBC URL must match the one specified in the properties:

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.h2.console.enabled=true

Which seems obvious, but I spent hours figuring this out..

Answer 6

One reason can be that jpa tries to insert data before creating table structure, in order to solve this problem , insert this line in application.properties :

spring.jpa.defer-datasource-initialization=true

Answer 7

The H2 in-memory database stores data in memory inside the JVM. When the JVM exits, this data is lost.

I suspect that what you are doing is similar to the two Java classes below. One of these classes creates a table and the other tries to insert into it:

import java.sql.*;

public class CreateTable {
    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new org.h2.Driver());
        Connection c = DriverManager.getConnection("jdbc:h2:mem:test");
        PreparedStatement stmt = c.prepareStatement("CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64))");
        stmt.execute();
        stmt.close();
        c.close();
    }
}

and

import java.sql.*;

public class InsertIntoTable {
    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new org.h2.Driver());
        Connection c = DriverManager.getConnection("jdbc:h2:mem:test");
        PreparedStatement stmt = c.prepareStatement("INSERT INTO PERSON (ID, FIRSTNAME, LASTNAME) VALUES (1, 'John', 'Doe')");
        stmt.execute();
        stmt.close();
        c.close();
    }
}

When I ran these classes one after the other, I got the following output:

C:\Users\Luke\stuff>java CreateTable

C:\Users\Luke\stuff>java InsertIntoTable
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "PERSON" not found; SQL statement:
INSERT INTO PERSON (ID, FIRSTNAME, LASTNAME) VALUES (1, 'John', 'Doe') [42102-154]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        ...

As soon as the first java process exits, the table created by CreateTable no longer exists. So, when the InsertIntoTable class comes along, there's no table for it to insert into.

When I changed the connection strings to jdbc:h2:test, I found that there was no such error. I also found that a file test.h2.db had appeared. This was where H2 had put the table, and since it had been stored on disk, the table was still there for the InsertIntoTable class to find.

Answer 8

I have tried to add

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

However, that didn't helped. On the H2 site, I have found following, which indeed could help in some cases.

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

However, my issue was that just the schema supposed to be different than default one. So insted of using

JDBC URL: jdbc:h2:mem:test

I had to use:

JDBC URL: jdbc:h2:mem:testdb

Then the tables were visible

Answer 9

Solved by creating a new src/test/resources folder + insert application.properties file, explicitly specifying to create a test dbase :

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

Answer 10

I had the same problem and changed my configuration in application-test.properties to this:

#Test Properties
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop

And my dependencies:

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

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

And the annotations used on test class:

@RunWith(SpringRunner.class)
@DataJpaTest
@ActiveProfiles("test")
public class CommentServicesIntegrationTests {
...
}

Answer 11

I was trying to fetch table meta data, but had the following error:

Using:

String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";

DatabaseMetaData metaData = connection.getMetaData();
...
metaData.getColumns(...);

returned an empty ResultSet.

But using the following URL instead it worked properly:

String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false";

There was a need to specify: DATABASE_TO_UPPER=false

Answer 12

I have tried adding ;DATABASE_TO_UPPER=false parameter, which it did work in a single test, but what did the trick for me was ;CASE_INSENSITIVE_IDENTIFIERS=TRUE.

At the end I had: jdbc:h2:mem:testdb;CASE_INSENSITIVE_IDENTIFIERS=TRUE

Moreover, the problem for me was when I upgraded to Spring Boot 2.4.1.

Answer 13

I might be a little late to the party, but I faced exactly the same error and I tried pretty much every solution mentioned here and on other websites such as *DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1; DB_CLOSE_ON_EXIT=FALSE; IGNORECASE=TRUE*. But nothing worked for me.

What worked for me was renaming data.sql to import.sql

I found it here - https://stackoverflow.com/a/53179547/8219358

Or

For Spring Boot 2.4+ use spring.jpa.defer-datasource-initialization=true in application.properties (mentioned here - https://stackoverflow.com/a/68086707/8219358)

I realize other solutions are more logical but none of them worked for me and this did.

Answer 14

Had similar problem Solution was to add the following to application.properties

spring.jpa.defer-datasource-initialization=true

Answer 15

I came to this post because I had the same error.

In my case the database evolutions weren't been executed, so the table wasn't there at all.

My problem was that the folder structure for the evolution scripts was wrong.

from: https://www.playframework.com/documentation/2.0/Evolutions

Play tracks your database evolutions using several evolutions script. These scripts are written in plain old SQL and should be located in the conf/evolutions/{database name} directory of your application. If the evolutions apply to your default database, this path is conf/evolutions/default.

I had a folder called conf/evolutions.default created by eclipse. The issue disappeared after I corrected the folder structure to conf/evolutions/default

Answer 16

Had the exact same issue, tried all the above, but without success. The rather funny cause of the error was that the JVM started too fast, before the DB table was created (using a data.sql file in src.main.resources). So I've put a Thread.sleep(1000) timer to wait for just a second before calling "select * from person". Working flawlessly now.

application.properties:

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

data.sql:

create table person
(
id integer not null,
name varchar(255) not null,
location varchar(255),
birth_date timestamp,
primary key(id)
);

insert into person values (
10001, 'Tofu', 'home', sysdate()
);

PersonJdbcDAO.java:

    public List<Person> findAllPersons(){
    return jdbcTemplate.query("select * from person", 
        new BeanPropertyRowMapper<Person>(Person.class));
}

main class:

Thread.sleep(1000);
logger.info("All users -> {}", dao.findAllPersons());

Answer 17

I found it working after adding the dependency of Spring Data JPA -

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

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

Add H2 DB configuration in application.yml -

spring:
  datasource:
    driverClassName: org.h2.Driver
    initialization-mode: always
    username: sa
    password: ''
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
  h2:
    console:
      enabled: true
      path: /h2
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: none

Answer 18

I have tried the above solution,but in my case as suggested in the console added the property DB_CLOSE_ON_EXIT=FALSE, it fixed the issue.

 spring.datasource.url=jdbc:h2:mem:testdb;DATABASE_TO_UPPER=false;DB_CLOSE_ON_EXIT=FALSE

Answer 19

<bean id="benchmarkDataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

Answer 20

The issue can also happen if there was error while generating the table.

If the entities use any features which are not supported by H2 (for example, specifying a non-standard columnDefinition), the schema generation will fail and test will continue without the database generated.

In this case, somewhere in the logs you will find this:

WARN ExceptionHandlerLoggedImpl: GenerationTarget encountered exception accepting command :
 Error executing DDL "create table ..." via JDBC Statement

Answer 21

In my case missing table error was happening during jpa test, table was created by schem.sql file, problem was fixed after puting @org.springframework.transaction.annotation.Transactional on test

Answer 22

In my case, I had used the special keywords for my column-names in the table, H2 Database. If you're using different databases avoid those special keywords across different databases. Spring & Hibernate isn't smart enough to tell you exactly which column names are prohibited or where the exact error is in the table-creation. Keywords such as;

desc, interval, metric

To resolve the issues I was experiencing, I renamed those fields to:

descr, time_interval, time_metric

http://www.h2database.com/html/advanced.html

Answer 23

   Use the same in applications.properties file
   
   spring.jpa.show-sql=true
   spring.datasource.url=jdbc:h2:mem:testdb;DATABASE_TO_UPPER=false
   DB_CLOSE_ON_EXIT=FALSE
   spring.data.jpa.repositories.bootstrap-mode=default
   spring.h2.console.enabled=true spring.jpa.generate-ddl=true
   spring.jpa.hibernate.ddl-auto=create
   spring.datasource.driverClassName=org.h2.Driver
   spring.jpa.defer-datasource-initialization=true

Answer 24

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "user" not found; SQL statement:

in my case, my table name was user but from H2 2.1.212 user is reserved so couldn't make the table

changed table name users by @Table(name="users") and

datasource:
  url: jdbc:h2:mem:testdb;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;

and it works now

Answer 25

This might be a beginners mistake but the error in my case was that, when I wanted to switch my DB to H2, I omitted a property that needed changing.

Namely: spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.H2Dialect

Other usual suspects could be:

• spring.datasource.driver-class-name: org.h2.Driver
• spring.datasource.url: jdbc:h2:file:./your/path
• spring.jpa.hibernate.ddl-auto: where you might have set a wrong value

I'm not sure whether those would result in different error messages. However, they are still worth a quick check.

Answer 26

If you are using @DataJpaTest you need to bear in mind that by default it ignores the datasource defined in your properties and creates a new one of its own.

Due to this, most of the solutions will not work, specially if you are trying to use an in-memory database in combination with Flyway/Liquibase. For instance, DB_CLOSE_DELAY=-1 connection property is ignored and setting spring.jpa.defer-datasource-initialization=true causes a circular dependency with Flyway.

Allowing Hibernate to create the schema with ddl-auto=create is not ideal either if you want to ensure your migration scripts actually work, so the best solution is to use the AutoConfigureTestDatabase annotation to tell @DataJpaTest not to create its own database and to use the one defined in your properties files.

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class YourDatabaseIntegrationTest {
    ...
}

Answer 27

I suspect the database you opened is a brand new db, not your application db. This is because:

• H2 in-memory database by default is private to the JVM and the classloader. It cannot be connected from another process/with TCP from same machine/from another machine; that's why all the guides suggest using H2 console, because that's within the same JVM of your application so it can access the database(however, my H2 console shipped with Spring 2.6 is not working, I need to find another way)
• H2 database can be launched in server mode, but ";AUTO_SERVER=true" does not work with in-memory db; it only can be added to the URL when you use a file based db; and to visit it you need to use absolute path to the db file, which is not portable and is ugly; additionally, auto-generation of tables are not done when you use a file so you need to create an init.sql to create tables. AND, when you connect H2 still tells you that you need server mode, because there is already one connection to the db(your app) and to allow 1+ connection, you need server mode

So in both cases you need server mode. How?

In Spring you need to create the DB as a bean(thanks to How to enable H2 Database Server Mode in Spring Boot); put this into a @Configuration and you are done:

@Bean(initMethod = "start", destroyMethod = "stop")
public Server h2Server() throws SQLException {
    return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "1234"); // or any other port
}

Your db url:

spring:
  datasource:
    url: jdbc:h2:mem:test
    driver-class-name: org.h2.Driver
    port: 1234
    username: sa
    password: sa

That's all. You can connect with H2 console, or DB Navigator, or other tools along with your app right now. The connection string is:

jdbc:h2:tcp://localhost:1234/mem:test