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.
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 versionsDATABASE_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
Error While Running Allure Command Line Generate Command
Java Simpledateformatter With 10 Decimals After the Seconds, Cannot Convert to Date
Replacing Double Backslashes With Single Backslash
How to Access Variable Outside a Try Catch Block
How to Junit Test That Two List<E> Contain the Same Elements in the Same Order
Webdriver/Pageobject/Findby: How to Specify Xpath With Dynamic Value
How to Find Out My MySQL Url, Host, Port and Username
How to Mock JPA Repository'S Save Method in Unit Tests
How to Resolve Uri Encoding Problem in Spring-Boot
Removing a Column in Java from a 2D Array
Calculating the Angle Between Two Lines Without Having to Calculate the Slope (Java)
Java.Io.Filenotfoundexception: the System Cannot Find the File Specified
How to Return a List of Specific Type Instead of List<Object[]> in Hibernate
Determine the Number of Pages in a Pdf File
How to Find Out the Exact Requested Url in Servletrequest If That Url Contains Path Variables
How to Sort Arraylist of Objects by Timestamp and Get Last Five Elements