Java H2 In-Memory Database Error: Table Not Found

H2 in-memory database. Table not found

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.

java h2 in-memory database error: Table not found

Table was not found, because there was an error at the start when trying to create it. And the error was due to the fact, that one of the ClassifierGroupEntity fields was named 'order', which is one of reserved words in SQL, and thus the generated SQL statement by Spring was syntactically incorrect.

Java h2 in memory database JdbcSQLSyntaxErrorException: Table table_name not found

You are missing a \n in your comment for the SQL. Now the whole SQL statement is interpreted as a comment and thus nothing will be executed.

String sql = "-- template\n"+
"CREATE TABLE my_model_template ("+
" uuid UUID NOT NULL,"+
... +
");";

something along those lines, or if you are on a newer version of Java you might even be able to use text blocks.

String sql = """-- template
CREATE TABLE my_model_template (
uuid UUID NOT NULL,"+
...
);""";

This would work from Java 15 (with experimental features enabled) or Java 17 or up as well.

Table myTable not found error in while accessing the H2 DB

Replace mem to file

   def driver = Class.forName('org.h2.Driver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", username)
props.setProperty("password", password)
return driver.connect("jdbc:h2:file:${absolutePath};DB_CLOSE_DELAY=-1;IFEXISTS=true", props)
  • Also make sure use the same version of H2 jar, which is using by h2-server.

H2 in-memory database even if I use DB_CLOSE_DELAY=-1 reports Table not found

The embbeded in-memory instance of H2 that you are starting from IntelliJ runs in a different process and is unrelated to your application's embedded H2 instance. You are dealing with two separate databases, your application can't see the tables that you have created with IntelliJ.

If you need to use an embedded in-memory database then you will also need to create the database schema from the application itself. For example, by executing a SQL script on connection or by using a schema evolution tool such as Flyway or Liquibase. Frameworks like Spring Boot can also be configured to perform Database Initialisation

If you actually need multiple processes to have access to the same database instance then you'll need to run H2 in Server Mode (it works with in-memory databases if you start the server programmatically from your application) or Automatic Mixed Mode (doesn't work with in-memory databases).

Spring Boot H2 db for testing throws Table not found and SQL syntax error

Based on our conversation on the comments, using the parameter MODE worked for OP.
So the solution was to change this configuration from:

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

To

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL

Note the MODE=MySQL added to the end. This will make H2 database implementation to ignore or replace certain MySQL reserved words with its (H2) equivalent ones.

Op also had to change another configuration for it to solve all problems and the other one as setting the ddl-auto setting
from:

spring.jpa.hibernate.ddl-auto=create

To

spring.jpa.hibernate.ddl-auto=update

Please also note comment from Evgenij Ryazanov so check the version you are in and correct it accordingly:

DATABASE_TO_UPPER=FALSE should only be used with H2 1.4.197 and older versions, for H2 1.4.198 Beta and newer versions DATABASE_TO_LOWER=TRUE should be specified with MySQL compatibility mode, otherwise all unquoted identifiers will be case-sensitive.
By

Spring Boot Data JPA with H2 and data.sql - Table not Found

If you're using hibernate as a JPA implementation, the best way I think is by using the file import.sql instead of data.sql for Database Initialization.

for more information on database initialization see the official Spring Boot documentation Database Initialization

Spring JPA H2 database get org.h2.jdbc.JdbcSQLSyntaxErrorException Table not found

Hibernate and Spring by default having naming strategies, which decide how the entity class must be compiled and the table and column names be generated. This can be customized as per use through application properties or hibernate configuration file.

eg

spring:
jpa:
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl


Related Topics



Leave a reply



Submit