SpringBoot JPA data.sql failed

Question

i wanna sql script to load my data. but my SQL script dosen't work. and i don't know why error is caused.

my data.sql is

insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A');

and my test code is

 @Test
void curd() {
    Person person = new Person();
    person.setName("john");
    person.setAge(10);
    person.setBloodType("A");

    personRepository.save(person);

    List<Person> result = personRepository.findByName("john");

    assertThat(result.size()).isEqualTo(1);
    assertThat(result.get(0).getName()).isEqualTo("john");
    assertThat(result.get(0).getAge()).isEqualTo(10);
    assertThat(result.get(0).getBloodType()).isEqualTo("A");
}

and my domain Person class is

@Entity
@NoArgsConstructor
@AllArgsConstructor
@RequiredArgsConstructor
@Data
public class Person {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NonNull
private String name;

@NonNull
private int age;

private String hobby;

@NonNull
private String bloodType;

@Valid
@Embedded
private Birthday birthday;

private String job;

@ToString.Exclude
private String phoneNumber;

@OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
@ToString.Exclude
private Block block;

}

and my error is

Caused by: 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:/D:/CCC/intellij_ex/mycontact/build/resources/test/data.sql]: insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO PERSON('id'[*], 'name', 'age', 'blood_type') VALUES (1, 'martin', 10, 'A')"; expected "identifier"; SQL statement:
insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A') 

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/D:/CCC/intellij_ex/mycontact/build/resources/test/data.sql]: insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO PERSON('id'[*], 'name', 'age', 'blood_type') VALUES (1, 'martin', 10, 'A')"; expected "identifier"; SQL statement:
insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A') 

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO PERSON('id'[*], 'name', 'age', 'blood_type') VALUES (1, 'martin', 10, 'A')"; expected "identifier"; SQL statement:
insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A')

I digging this error issues so long time. but i didn't find out. plz help me

review this question's answer: https://stackoverflow.com/questions/44267245/h2-schema-initailization-syntax-error-in-sql-statement — 0xh3xa, Mar 22 '20 at 10:23
@GeneratedValue (strategy = GenerationType.IDENTITY) <-- I tried this already but it doesn't run. — Jongwon, Mar 22 '20 at 11:16
omg...I found the problem. I was using " ' "instead of " ` " . thanks for ur help... — Jongwon, Mar 22 '20 at 11:38

score 1 · Accepted Answer · answered Mar 22 '20 at 11:57

1

data.sql code

insert into person('id', 'name', 'age', 'blood_type') values (1, 'martin', 10, 'A');

have to change

insert into person(`id`, `name`, `age`, `blood_type`) values (1, 'martin', 10);

answered Mar 22 '20 at 11:57

Jongwon

31
2
7

or even drop the backticks – Andronicus Mar 22 '20 at 12:17

score 0 · Answer 2 · answered Mar 22 '20 at 12:12

0

Your Id already generated as below.

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

so you can try again the edited SQL query.

insert into person('name', 'age', 'blood_type') values ('martin', 10, 'A');

answered Mar 22 '20 at 12:12

Rohan C Bandara

23
5

SpringBoot JPA data.sql failed

2 Answers2