insert data from sql file H2 database spring boot-CodePudding

I'm trying to insert data from sql file using h2 database and spring boot . when i add the sql file into src/main/ressources, i succeeded to create the table and insert data into it. but when i create a model class named Employee which refer to the table, i can create the tables but there is no rows inserted .

sql file

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(250) NOT NULL, last_name VARCHAR(250) NOT NULL,
mail VARCHAR(250) NOT NULL, password VARCHAR(250) NOT NULL ); INSERT INTO employees (first_name, last_name, mail, password) VALUES
('Laurent', 'GINA', '[email protected]', 'laurent');

model :

import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table;

import lombok.Data;

@Data @Entity @Table(name = "employees")

public class Employee {
      @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long id;        @Column(name="first_name")  private String first_name;
@Column(name="last_name") private String last_name; private String mail; private String password;

}

application.properties

#Global configuration
spring.application.name=api

#Tomcat configuration
server.port=9000

#Log level configuration
logging.level.root=ERROR
logging.level.com.openclassrooms=INFO
logging.level.org.springframework.boot.autoconfigure.h2=INFO
logging.level.org.springframework.boot.web.embedded.tomcat=INFO

#H2 Configuration
spring.h2.console.enabled=true

CodePudding user response：

There are a few things that could be causing this issue.

First, make sure that your SQL file is located in the src/main/resources directory. If it is not, then the file will not be picked up by Spring Boot.

Next, check the permissions on the file. If the file is not readable by the application, then it will not be able to load the data.

Finally, make sure that the SQL file is formatted correctly. If there are any syntax errors in the file, then Spring Boot will not be able to load the data.

CodePudding user response：

There are many ways to initialize the database with spring boot:

Initialize a Database Using JPA
Initialize a Database Using Hibernate
Initialize a Database using basic SQL scripts

For your information: you should separate the schema from the data, so you should have 2 basic scripts:

schema.sql : In this file you can put your instruction to create table.

 CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(250) NOT NULL, last_name VARCHAR(250) NOT NULL,mail VARCHAR(250) NOT NULL, password VARCHAR(250) NOT NULL );

data.sql : In this file you can put your instruction to insert data

 INSERT INTO employees (first_name, last_name, mail, password) VALUES ('Laurent', 'GINA', '[email protected]', 'laurent');

if you choose to use the schema.sql : you shoud use this property spring.jpa.hibernate.ddl-auto=none.
otherwise (Hibernate) you shoud use this property spring.jpa.hibernate.ddl-auto=create-drop

For your information:

Note: spring boot version <= 2.4.x you can choose Hibernate to create the schema or use schema.sql, but you cannot do both.
Note: spring boot version >= 2.5.x if you want script-based DataSource initialization to be able to build upon the schema creation performed by Hibernate, set spring.jpa.defer-datasource-initialization to true.

Be careful it depends on which version you choose!