H2 in-memory database Table not found

Question

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

User · Answer

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

User · Answer

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

User · Answer

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        lt dependency gt           lt groupId gt org springframework boot lt  groupId gt           lt artifactId gt spring-boot-starter-test lt  artifactId gt           lt scope gt test lt  scope gt       lt  dependency gt        lt  -- https   mvnrepository com artifact com h2database h2 -- gt       lt dependency gt           lt groupId gt com h2database lt  groupId gt           lt artifactId gt h2 lt  artifactId gt           lt version gt 1 4 198 lt  version gt           lt scope gt test lt  scope gt       lt  dependency gt    And the annotations used on test class    RunWith SpringRunner class   DataJpaTest  ActiveProfiles  test   public class CommentServicesIntegrationTests

User · Answer

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 lt Person gt  findAllPersons        return jdbcTemplate query  select   from person            new BeanPropertyRowMapper lt Person gt  Person class        main class   Thread sleep 1000   logger info  All users - gt       dao findAllPersons

User · Answer

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

User · Answer

lt bean id  benchmarkDataSource      class  org springframework jdbc datasource DriverManagerDataSource  gt       lt property name  driverClassName  value  org h2 Driver    gt       lt property name  url  value  jdbc h2 mem testdb DB CLOSE DELAY -1    gt       lt property name  username  value  sa    gt       lt property name  password  value      gt   lt  bean gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I have tried adding  DATABASE TO UPPER false parameter  which it did work a single test  but what did the trick for me was  CASE INSENSITIVE IDENTIFIERS TRUE  At the 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

User · Answer

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

User · Answer

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

User · Answer

I found it working after adding the dependency of Spring Data JPA -      lt dependency gt           lt groupId gt org springframework boot lt  groupId gt           lt artifactId gt spring-boot-starter-data-jpa lt  artifactId gt       lt  dependency gt        lt dependency gt           lt groupId gt com h2database lt  groupId gt           lt artifactId gt h2 lt  artifactId gt           lt scope gt runtime lt  scope gt       lt  dependency gt   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

[java] H2 in-memory database. Table not found

Examples related to java

Examples related to database

Examples related to h2