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:
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.
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.
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
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.
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.
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.
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.
Access the H2 Console You can access the console at the following URL: http://localhost:8080/h2-console/.
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.
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.
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.
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With