java h2 in-memory database error: Table not found

Question

I tried googling around, but the solution to almost all this kind of questions was to add ;DB_CLOSE_DELAY=-1, however it does not solve anything for me.

Here is my test class

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {Main.class})
public class Testas {

    @Autowired
    @Qualifier("managerImplementation")
    private ClassifierManager manager;

    @Test
    public void testManager(){
        ClassifierGroupEntity cge = new ClassifierGroupEntity();
        manager.saveClassifierGroup(cge);
    }
}

Manager class

@Service("managerImplementation")
public class ClassifierManagerImpl implements ClassifierManager{

    @Autowired
    private ClassifierGroupEntityRepository groupEntityRepository;

    @Autowired
    private ClassifierEntityRepository entityRepository;

    @Autowired 
    private ClassifierValueEntityRepository valueEntityRepository;

    @Override
    public ClassifierGroupEntity getClassifierGroup(long id) {
        return groupEntityRepository.findOne(id);
    }

    @Override
    public ClassifierGroupEntity getClassifierGroup(String code) {
        return groupEntityRepository.findByCode(code);
    }

    @Override
    public ClassifierGroupEntity saveClassifierGroup(ClassifierGroupEntity entity) {
        return groupEntityRepository.save(entity);
    }

    @Override
    public void deleteClassifierGroup(long id) {
        groupEntityRepository.delete(id);
    }

    @Override
    public ClassifierEntity getClassifier(long id) {
        return entityRepository.findOne(id);
    }

    @Override
    public ClassifierEntity getClassifier(String code) {
        return entityRepository.findByCode(code);
    }

    @Override
    public ClassifierEntity saveClassifier(ClassifierEntity entity) {
        return entityRepository.save(entity);
    }

    @Override
    public void deleteClassifier(long id) {
        entityRepository.delete(id);
    }

    @Override
    public ClassifierValueEntity getClassifierValue(long id) {
        return valueEntityRepository.findOne(id);
    }

    @Override
    public ClassifierValue getClassifierValue(String classifiedCode, String valueCode) {
        return null;
    }

    @Override
    public ClassifierValueEntity saveClassifierValue(ClassifierValueEntity entity) {
        return valueEntityRepository.save(entity);
    }

    @Override
    public void deleteClassifierValue(long id) {
        valueEntityRepository.delete(id);
    }


}

And finally properties file

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.user=sa
spring.datasource.password=
spring.datasource.url=jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

Launching the test throws me

org.h2.jdbc.JdbcSQLException: Table "CLASSIFIER_GROUP_ENTITY" not found; SQL statement:
insert into classifier_group_entity (id, code, modified_details, modified_time, modified_user_id, order, revision, valid_details, valid_from, valid_till, parent_id) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [42102-191]

I don't know if I should provide anything else, please tell me if I do. I appreciate your help in advance.

Answer 1

What I found was default springboot is adding underscore to every table name and column name when executing this sqls.

Ex - actial table name -> testTable, springboot is converting this into -> test_table when executing sql.

You can disable this default behaviour by adding following property in your application.yml

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

Answer 2

In my case, I used liquibase to update the data. get the same error, so I used the doule quote " to wrap the table name. it worked.

Answer 3

add spring.jpa.defer-datasource-initialization=true

don't forget to add these values in application.properties/application.yaml file

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true

Answer 4

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.

Answer 5

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.

Answer 6

Please try this for this problem -

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

Answer 7

I always use the below configuration for H2 Database, Flyway, Spring Boot JPA before writing integration tests - https://gist.github.com/vslala/d412156e5840fafa1b9f61aae5b20951

Put below configuration in your src/test/resources/application.properties file.

# Datasource configuration for jdbc h2
# this is for file based persistent storage
# spring.datasource.url=jdbc:h2:file:/data/demo

# For in-memory storage
spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=vslala
spring.datasource.password=simplepass
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# This has to be over-ridden because
# it's not kept create rather kept none or validate after the first ddl creation.
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

# This is for FlyWay configuration
spring.flyway.url=jdbc:h2:mem:testdb
spring.flyway.schemas=testdb
spring.flyway.user=vslala
spring.flyway.password=simplepass

Answer 8

In my case, I use lower case syntax for columns when the query is defined but the columns in the H2 database are defined in the upper case. I was expecting to ignore the case sensitivity but it is not the case with H2 databases.

Answer 9

In my case I was dumb and forgot to actually put in a spring.datasource.url

For some reason JPA doesn't throw an error if it can't find an actual database url. Weird

Answer 10

I encountered the same problem while I was writing unit tests for my own application. After many tries, I was not able to get my application test cases running. I was running on version 1.4.191 and then I upgraded to 1.4.196 and it started working.

Answer 11

Have these properties in your application.properties file in the src/test/resources folder:

spring.jpa.generate-ddl=true

spring.jpa.hibernate.ddl-auto=create

This has to be over-ridden because it's not kept create rather kept none or validate after the first ddl creation.

Answer 12

I got this after adding a few columns to my table. The persistence generator created a few more fields in my entity.

I noticed this in my logs.

ERROR 13691 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table comment
ERROR 13691 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Scale($"0") must not be bigger than precision("-1")

It turns out that the when my IDE's persistence generator mapped my TEXT field it gave it a length of -1.

@Basic
@Column(name = "address", nullable = true, length = -1)
public String getAddress() {
  return address;
}

Removing the length attribute solved the problem for me.