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: stackoverflow.com/questions/44267245/… — 0xh3xa
– 0xh3xa, Commented Mar 22, 2020 at 10:23
@GeneratedValue (strategy = GenerationType.IDENTITY) <-- I tried this already but it doesn't run. — Jongwon
– Jongwon, Commented Mar 22, 2020 at 11:16
omg...I found the problem. I was using " ' "instead of " ` " . thanks for ur help... — Jongwon
– Jongwon, Commented Mar 22, 2020 at 11:38

Jongwon · Accepted Answer · 2020-03-22 11:57:59Z

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, 2020 at 11:57

Jongwon

312 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andronicus Over a year ago

or even drop the backticks

Rohan C Bandara · Accepted Answer · 2020-03-22 12:12:15Z

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, 2020 at 12:12

Rohan C Bandara

236 bronze badges

Collectives™ on Stack Overflow

SpringBoot JPA data.sql failed

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related