cannot insert data in h2 in-memory database

Question

I am inserting the data in inMemory database,while inserting the data i am getting an issue,

Sample program to insert data in inMemory using boot,JPA,H2db

Created Pojo and annotated with JPA annotation
Created data.sql file for queries.
Running the application. please find issue details in screenshots.

I tried with a number of ways, but still the same exception

Configured in app.prop: String url = jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE
Added @Table with a given table in data.sql file
Added @Column name for conversion as mentioned in data.sql.

Where to configured; DB_CLOSE_ON_EXIT=FALSE in springboot?

POJO

@Entity
@Table(name = "exchange_value")
public class CurrencyExchange {
    @Id
    private Long id;
    @Column(name = "currency_from")
    private String from;
    @Column(name = "currency_to")
    private String to;
    @Column(name = "conversion_multiple")
    private BigDecimal conversion;
    private int port;

Controller

@Autowired
    private Environment env;
    @GetMapping("/currency-exchange/from/{from}/to/{to}")
    public CurrencyExchange retriveCurrencyExchange(@PathVariable String from,@PathVariable String to)
    {
        CurrencyExchange currencyExchange = new CurrencyExchange(1000L, from, to, BigDecimal.valueOf(65));
        currencyExchange.setPort(Integer.parseInt(env.getProperty("local.server.port")));
        return currencyExchange;

    }
}

app.prop

spring.application.name=currency-exchange-service
server.port=8000

spring.jpa.show-sql=true
spring.h2.console.enabled=true

data.sql file



 insert into exchange_value(id,currency_from,currency_to,conversion_multiple,port)
    values(1001,'USD','INR',65,0);
    insert into exchange_value(id,currency_from,currency_to,conversion_multiple,port)
    values(1002,'EUR','INR',75,0);

Output: The data should be inserted into in-memory database while hitting the service.

Error Caused by: Invocation of destroy method failed on bean with name 'inMemoryDatabaseShutdownExecutor': org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database is already closed (to disable automatic closing at VM shutdown, add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL) [90121-199] org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/naresh/Documents/workspace-sts-3.9.8.RELEASE/currency-exchange-service/target/classes/data.sql]: insert into exchange_value(id,currency_from,currency_to,conversion_multiple,port) values(1001,'USD','INR',65,0); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "EXCHANGE_VALUE" not found; SQL statement: insert into exchange_value(id,currency_from,currency_to,conversion_multiple,port) values(1001,'USD','INR',65,0) [42102-199] org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "EXCHANGE_VALUE" not found; SQL statement: insert into exchange_value(id,currency_from,currency_to,conversion_multiple,port) values(1001,'USD','INR',65,0) [42102-199]

Romil Patel Romil Patel · Accepted Answer · 2019-07-25T08:37:29

Change

String url = jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE

To

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

in application-properties

Also, make sure Table exchange_value exist (you have written SQL for creating table) before inserting the records.

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.

H2 Database

UPDATE

Create 2 sql files. One to create the Schema and another to insert the records

application.properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect


# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2

UPDATE 2

Yes, Spring Boot can auto-create Table for you make sure you have @Table(name = "TableName") and spring.jpa.hibernate.ddl-auto=create or spring.jpa.hibernate.ddl-auto=update

Entity

@Entity
@Table(name="exchange_value")
public class ExchangeValueEntity {
   //some fields
}

application.properties

spring.jpa.hibernate.ddl-auto=create

cannot insert data in h2 in-memory database

4 Answers