CODEWITHSUNDEEP
javadatabaseh2
user8681
user8681

Reputation:

H2 in-memory database. Table not found

I've got a H2 database with URL "jdbc:h2:test". I created a table with the following code

CREATE TABLE PERSON (
   ID INT PRIMARY KEY, 
   FIRSTNAME VARCHAR(64), 
   LASTNAME VARCHAR(64)
);

I then select everything from this (empty) table 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 this error

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.

Upvotes: 260

Views: 315688

Answers (28)

Basil Bourque
Basil Bourque

Reputation: 340118

The accepted Answer by reini2901 is correct. In addition, here is an example of configuring by way of a DataSource object.

javax.sql.DataSource

Using a DataSource enables you to externalize the connection credentials (user name, password, server address, etc.). Rather than hard-coding these facts in your code, they can be configured outside the app. You can use a directory/name service such as an LDAP server or a Jakarta EE server to store the credentials info. Then your app uses Java Naming and Directory Interface (JNDI) to retrieve the info as a DataSource object at runtime.

As part of a transition to externalizing, here we hard-code the credentials info in a DataSource object in our code.

  • The mem in the URL means an in-memory database.
  • The DB_CLOSE_DELAY=-1 keeps the database open for further connections. 👉🏽 Closing an in-memory database instantly destroys it.

Regarding syntax… Notice the SEMICOLON ; as a delimiter separating the DB_CLOSE_DELAY=-1.

Using Java 21 with H2 Database Engine 2.2.224.

private javax.sql.DataSource dataSource ( )
{
    org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  // Implementation of `DataSource` bundled with H2.
    ds.setURL ( "jdbc:h2:mem://Users/basil_dot_work/DataSourceDemoDB;DB_CLOSE_DELAY=-1" );
    ds.setUser ( "scott" );
    ds.setPassword ( "tiger" );
    ds.setDescription ( "An example database demonstrating javax.sql.DataSource." );
    return ds;
}

Full example follows.

package work.basil.example.db;

import org.h2.jdbcx.JdbcDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.OffsetDateTime;

public class ThisMonth
{
    public static void main ( String[] args )
    {
        ThisMonth app = new ThisMonth ( );
        app.demo ( );
    }

    private void demo ( )
    {
        DataSource dataSource = this.dataSource ( );
        String sql = """
                SELECT CURRENT_TIMESTAMP ;
                """;
        try (
                Connection connection = dataSource.getConnection ( ) ;
                Statement statement = connection.createStatement ( ) ;
                ResultSet resultSet = statement.executeQuery ( sql ) ;
        )
        {
            while ( resultSet.next ( ) )
            {
                OffsetDateTime now = resultSet.getObject ( 1 , OffsetDateTime.class );
                System.out.println ( "now = " + now );
            }
        } catch ( SQLException e )
        {
            throw new RuntimeException ( e );
        }
    }

    private javax.sql.DataSource dataSource ( )
    {
        org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  // Implementation of `DataSource` bundled with H2.
        ds.setURL ( "jdbc:h2:mem://Users/basil_dot_work/DataSourceDemoDB;DB_CLOSE_DELAY=-1" );
        ds.setUser ( "scott" );
        ds.setPassword ( "tiger" );
        ds.setDescription ( "An example database demonstrating javax.sql.DataSource." );
        return ds;
    }
}

When run:

now = 2024-01-17T20:08:26.570651-08:00

Upvotes: 0

HARSHIT BAJPAI
HARSHIT BAJPAI

Reputation: 679

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.

Upvotes: 4

ibai
ibai

Reputation: 1318

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

Upvotes: 1

Benjamin Basmaci
Benjamin Basmaci

Reputation: 2567

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.

Upvotes: 0

WesternGun
WesternGun

Reputation: 12807

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

Upvotes: 0

Shane Park
Shane Park

Reputation: 141

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

Upvotes: 0

magdi amer
magdi amer

Reputation: 66

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

spring.jpa.defer-datasource-initialization=true

Upvotes: 3

Alexandru Severin
Alexandru Severin

Reputation: 6238

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

Upvotes: 1

khalid tounoussi
khalid tounoussi

Reputation: 577

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

Upvotes: 14

Waheed Khan
Waheed Khan

Reputation: 134

   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

Upvotes: 0

S34N
S34N

Reputation: 8371

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

Upvotes: 0

Shounak Bose
Shounak Bose

Reputation: 1140

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

Upvotes: 107

Guram Kankava
Guram Kankava

Reputation: 61

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

Upvotes: 0

stergipe
stergipe

Reputation: 145

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.

Upvotes: 4

Bagesh Sharma
Bagesh Sharma

Reputation: 889

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

Upvotes: 2

mathan26
mathan26

Reputation: 522

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

Upvotes: 2

reini2901
reini2901

Reputation: 4386

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.

Upvotes: 437

Tudor Gafiuc
Tudor Gafiuc

Reputation: 145

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

Upvotes: 2

nagy.zsolt.hun
nagy.zsolt.hun

Reputation: 6694

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

enter image description here

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

Upvotes: 13

Miguel Angel Vega Pabon
Miguel Angel Vega Pabon

Reputation: 61

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

Upvotes: 6

Georgi Peev
Georgi Peev

Reputation: 1109

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

Upvotes: 6

N.MATHIEU
N.MATHIEU

Reputation: 73

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

Upvotes: 8

DevDio
DevDio

Reputation: 1584

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

Upvotes: 7

Alex R
Alex R

Reputation: 11893

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

Upvotes: 1

Oscar Fraxedas
Oscar Fraxedas

Reputation: 4677

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

Upvotes: 2

Cristian Vrabie
Cristian Vrabie

Reputation: 4068

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.

Upvotes: 165

Luke Woodward
Luke Woodward

Reputation: 65054

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.

Upvotes: 9

Joseph Ottinger
Joseph Ottinger

Reputation: 4951

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?

Upvotes: 12

Related Questions