CODEWITHSUNDEEP
javadatabasespringspring-booth2
Abdul Wajeed
Abdul Wajeed

Reputation: 101

cannot insert data in h2 in-memory database

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

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

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]

Upvotes: 7

Views: 19310

Answers (5)

Nabeel Bhatti
Nabeel Bhatti

Reputation: 139

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

Upvotes: 8

WesternGun
WesternGun

Reputation: 12728

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

Upvotes: 1

sanket dumbre
sanket dumbre

Reputation: 71

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

Upvotes: 3

Coder
Coder

Reputation: 79

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

Upvotes: 0

Romil Patel
Romil Patel

Reputation: 13727

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

enter image description here 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

Upvotes: 9

Related Questions