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, usejdbc: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.
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 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 versionsDATABASE_TO_LOWER=TRUE
should be specified with MySQL compatibility mode, otherwise all unquoted identifiers will be case-sensitive.
By
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).
Table is not Found while testing with H2Database
In H2 with its default settings identifiers are case-sensitive, but unquoted identifiers are converted to upper case. It means that table with real name task
can only be referenced with quoted identifier "task"
. Unquoted identifier task
is equal to "TASK"
and this is a different name.
Related Topics
How to Define a List Bean in Spring
Using Java to Find Substring of a Bigger String Using Regular Expression
How to Import Org.Openqa.Selenium.Webdriver Using Selenium and Java 11
Closing Jdbc Connections in Pool
How to Convert Java String to Date Object
Advanced Formatted Text Field Input Manipulation
Simple Way to Find If Two Different Lists Contain Exactly the Same Elements
Ignore Fields from Java Object Dynamically While Sending as JSON from Spring MVC
Ideal Method to Truncate a String with Ellipsis
Clone() VS Copy Constructor VS Factory Method
Java - How Would I Dynamically Add Swing Component to Gui on Click