H2 in-memory database. Table not found

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.

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.

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.

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

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?

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..

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.

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

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

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 {
...
}

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

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.

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

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());

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

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

<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>

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

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

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.

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

   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