Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Boot 2.5.0, Spring Cloud 2020.0.2 and Hibernate 5.4.31 - H2 Database Multi Row Insert Failing

While Working on a Spring Boot Application with SB version 2.5.0, Spring Cloud (for Centralized Config 2020.0.2) The Hibernate version is 5.4.31 (I am not using a specific Hibernate version, it is as per Spring Boot compatibility). Using H2 database for in-memory data, as I need to create the sample application for demo.

In the Resources folder, I do have my SQL file. When I name it data.sql the application does not start at all. When I renamed this file as import.sql, my application started but still facing issues for multi-row insertion.

Data Insert SQL File

/* Data for Entity DataTable */
    INSERT INTO data_table (name) VALUES
    ('Data 1'),
    ('Data 2'),
    ('Data 3');

This was working perfectly fine when my Application is running with Spring Boot 2.3.10.RELEASE and Spring Cloud Hoxton.SR5.

I have also tried the properties of JPA and Hibernate to resolve this issue, but still no success. Find properties which I use are as below:

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true

Due to updating the Spring Boot and Spring Cloud version, I am not sure if there is anything I specifically need to set the H2 Database to work for Multi-Row insert.

Looks to be something relevant to Hibernate or H2 Database Issue.

WARN 7952 --- [           main] o.h.t.s.i.ExceptionHand lerLoggedImpl    : GenerationTarget encountered exception accepting command : Error executing DDL "('Data 2')" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "('Data 2')" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.31.Final.jar!/:5.4.31.Final]

...
...
...

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "('Data 2'[*]),"; expected "(, WITH, SELECT, TABLE, VALUES"; SQL statement: ('Data 2'), [42001-200]

UPDATE

I have reverted my SQL file name to data.sql, and then I observed the same H2 database exception is coming and the application does not startup.

Find below details for startup execution which I am getting:

WARN 4720 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException:

Failed to execute SQL script statement #1 of URL [jar:file:/D:/Projects/SpringCloudDemoApp/spring-cloud-demo/target/demo-data-service-0.1.jar!/BOOT-INF/classes!/data.sql]: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_TABLE" not found; SQL statement: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3') [42102-200]

...
...

ERROR 4720 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQLscript statement #1 of URL [jar:file:/D:/Projects/SpringCloudDemoApp/spring-cloud-demo/target/demo-data-service-0.1.jar!/BOOT-INF/classes!/data.sql]: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_TABLE" not found; SQL statement: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3') [42102-200]

Also please find my Entity class in case something wrong with this, but don't think so anything wrong with Entity class as it works well with older Spring Boot and Spring Cloud versions, but still sharing here.

DataTable Entity Class

@Entity
@Table(name = "data_table")
public class DataTable {   

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "dataid")
    private Long id;

    @Column(name = "name", nullable = false, unique = true, length = 200)
    private String name;

    // Getter Setters will follow here
}

Application YML file

server:
    port: 9090
spring:
    datasource:
        url: jdbc:h2:mem:demodb
        driverClassName: org.h2.Driver
        username: scdemo
        password: scdemopass
    jpa:
        properties:
            hibernate:
                dialect: org.hibernate.dialect.H2Dialect
                jdbc:
                    batch_size: 20
                order_inserts: true
    h2:
        console:
            enabled: true
            path: /h2-console
            settings:
                trace: false

IMPORTANT UPDATE

I also tried the same, only with Spring Boot 2.5.0 and JPA, Hibernate with H2 Database, created a sample application which has only 4 files, The Spring Boot Application file to start and the Entity class, data.sql as above, and the application.yml for configurations and made sure that it fails for the same condition. And I confirm that I am getting the same error to load data from the data.sql to insert, which works well with the older Spring Boot Release.

I just updated the SB version from 2.5.0 to 2.3.10.RELEASE, and the same code worked.

More Details:

  1. When the data.sql file is used with Spring Boot Version 2.3.10.RELEASE and the Insert Statements in the data.sql file are multi-row and are on multiple lines. It works perfectly fine.

  2. When data.sql renamed as an import.sql, with Spring Boot version 2.5.0 and the Insert Statements in the import.sql file are multi-row but it is expected to keep all values on the same line, this also works well.

  3. When data.sql renamed as an import.sql, with Spring Boot version 2.5.0 and the Insert Statements in the import.sql file are multi-row and are on multiple lines, this scenario fails. As it considers the statement on each new line as DDL. In this case Application Runs but the data insert does not take place.

    GenerationTarget encountered exception accepting command : Error executing DDL "('Data 3')" via JDBC Statement
    
  4. And the 4th Case is when data.sql with Spring Boot version 2.5.0 and the Insert Statements in the data.sql file are multi-row and are on multiple lines, this scenario fails too At the time of Data Source Initialization and Application do not run at all.

Sharing the Sample Code for the same: The source code can be tried with the 4 conditions mentioned above: demo-data-service

Please suggest.

like image 421
Mandar Pandit Avatar asked May 22 '21 14:05

Mandar Pandit


People also ask

How to check H2 database tables in Spring Boot?

Accessing the H2 Console H2 database has an embedded GUI console for browsing the contents of a database and running SQL queries. By default, the H2 console is not enabled in Spring. Then, after starting the application, we can navigate to http://localhost:8080/h2-console, which will present us with a login page.

Is Spring Cloud included in spring boot?

Spring Cloud Stream Applications are out of the box Spring Boot applications providing integration with external middleware systems such as Apache Kafka, RabbitMQ etc. using the binder abstraction in Spring Cloud Stream.

What is spring Cloud in spring boot?

Spring Cloud is a framework for building robust cloud applications. The framework facilitates the development of applications by providing solutions to many of the common problems faced when moving to a distributed environment.

How to check H2 database console?

Access the H2 Console You can access the console at the following URL: http://localhost:8080/h2-console/.

How to integrate Spring Boot with Hibernate?

We’ll build a simple Spring Boot application and see how easy it is to integrate it with Hibernate. 2. Bootstrapping the Application We’ll use Spring Initializr to bootstrap our Spring Boot application. For this example, we’ll use only the needed configurations and dependencies to integrate Hibernate, adding Web, JPA, and H2 dependencies.

What is the latest version of Spring Boot 2021?

Update 2021-05-25: The release contains fixes for Spring Framework CVE-2021-22118. On behalf of the Spring Boot team and everyone that has contributed, I am pleased to announce that Spring Boot 2.5.0 has been released and is available from Maven Central. This release adds a significant number of new features and improvements.

Where can I find Service Release 2 of the Spring Cloud?

On behalf of the community, I am pleased to announce that Service Release 2 of the Spring Cloud 2020.0 Release Train (2020.0.2) is available today. The release can be found in Maven Central. You can check out the 2020.0 release notes for more information. This release was primarliy for bug fixes and dependency upgrades.

How to check if H2 is working in Spring Boot?

We can check the configuration was successful on the logs when we start up the Spring Boot application: We can now access the H2 console on localhost: http://localhost:8080/h2-console/. 4. Creating the Entity To check that our H2 is working properly, we’ll first create a JPA entity in a new models folder:


1 Answers

You need to add this to the app config:

spring.jpa.defer-datasource-initialization=true

Since Spring Boot 2.5.0, the data.sql is executed before the schema initialization by default.

See also:

Spring Boot Data JPA with H2 and data.sql - Table not Found

like image 97
Dan Avatar answered Oct 21 '22 17:10

Dan