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]

Duumvirate

The error says EXCHANGE_VALUE table does not exists – Controvert

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

Elute

I tried adding spring.datasource.url: 'jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1', Still the same error am facing. Question: where i need to run the script in Spring boot? – Duumvirate

Hello @AbdulWajeed Make sure that you have created table EXCHANGE_VALUE before inserting the record and let me know – Elute

Please let me know, in boot where i need to create a script? As i preach, am using inMem DB that is H2. – Duumvirate

we can put sql files into resources folder (/src/main/resources/) – Elute

@AbdulWajeed please see the updated answer and if it doesn't work share your whole application.properties and project structure – Elute

I added data.sql and in that file i wrote insert query directly, witout creating any table. I thought boot auto create table in H2 DB – Duumvirate

@AbdulWajeed please see the update 2 and let me know – Elute

spring.jpa.hibernate.ddl-auto=update enables updating of table schema thus allowing for one to insert data. – Slime

Answer 2

incase if anyone encounters with this problem with set this property in the .properties file

By default, the data.sql script executes before Hibernate initialization. This aligns the script-based initialization with other database migration tools such as Flyway and Liquibase. As we're recreating the schema generated by Hibernate each time, we need to set an additional property:

spring.jpa.defer-datasource-initialization=true

Please read this tutorial

Enzyme

Answer 3

this works

spring.application.name=currency-exchange-service
server.port= 8000
spring.datasource.url=jdbc:h2:mem:testdb
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=update

Chinkapin

Welcome to Stack Overflow. Code without any explanation are rarely helpful. Stack Overflow is about learning, not providing snippets to blindly copy and paste. Please edit your question and explain how it answers the specific question being asked. See How to Answer. – Crumbly

Ah! Change ddl-auto from create-drop to update works; now my data got inserted into table; before, I only get table created. I don't know why, but thanks. vladmihalcea.com/hibernate-hbm2ddl-auto-schema according to here, maybe the insert sql is not executed when I use create-drop? My sql includes drop all objects; create schema; create table ... and insert into ... – Imalda

Answer 4

Make sure that:

• ddl-auto is set to update; somehow only this allows data insertion
• and in the scripts to run, remember to drop all objects; create schema...; set schema ... as the first step; this initialize the database every time
• and include the sql to create table and insert data

Imalda

Answer 5

Make sure you have provided the data.sql inside the resources folder.

Cogen