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]

Answer 1

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