Unsuccessful importing import.sql in hibernate

Question

I want to automatically drop the table and create a new one every time the app is running and also automatically inserting pre-defined data. I've already preparing the data in import.sql. I've already set spring.jpa.hibernate.ddl-auto=create-drop in application.properties. But, why do I get the following error? I can insert it just fine manually.

2015-11-20 20:53:57.242 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: INSERT INTO gender
2015-11-20 20:53:57.242 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
2015-11-20 20:53:57.242 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: (gender_id, gender_name)
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gender_id, gender_name)' at line 1
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: VALUES
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES' at line 1
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: (1, 'Male'),
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, 'Male'),' at line 1
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: (2, 'Female')
2015-11-20 20:53:57.257 ERROR 7092 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2, 'Female')' at line 1

This is my entity:

@Entity
public class Gender {
    @Id
    @Column(name = "gender_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "gender_name")
    private String name; 
}

This is my query in import.sql:

INSERT INTO gender 
    (gender_id, gender_name) 
VALUES 
    (1, 'Male'), 
    (2, 'Female');

The error shows there is some problem in your query. Try printing your query at runtime and share. — jcool, Nov 20 '15 at 13:15
excuse me, what do you mean by printing query at runtime? I've already provided my query and I can insert it manually. — Disp Hay, Nov 20 '15 at 13:17
If you are providing query as String then remove single quotes from Male and Female — jcool, Nov 20 '15 at 13:19
it's still the same. Yes, I am providing it in a file named `import.sql`. — Disp Hay, Nov 20 '15 at 13:26
It seems the problem comes from how the code is interprated. Have you tried changing your file encoding? Or even set the query on one line? — RPresle, Nov 20 '15 at 13:28
Duplicate of [H2 SQL Grammar Exception](http://stackoverflow.com/questions/17926093/h2-sql-grammar-exception) - allthough it is about H2, the cause of the problem is the same — Tobias Liefke, Nov 20 '15 at 17:05

score 6 · Accepted Answer · answered Nov 20 '15 at 13:37

6

By the pattern of the errors, it seems that your line ending contains characters that cannot be processed (hidden characters like LF or something like that).

I'm saying this because all your errors relate to the end of the line. Try to put your import.sql in one line like this:

INSERT INTO gender (gender_id, gender_name) VALUES (1, 'Male'), (2, 'Female');

By taking care to have only space between keywords and removing all unprintable characters. You can use your favorite text editor and use the option to 'show all charaters'.

answered Nov 20 '15 at 13:37

JFPicard

5,029
3
19
43

8

That's true. I need to make it all in one line. As the link mentioned by @Tobias Liefke, Hibernate use `SingleLineSqlCommandExtractor` by default which consider every lines as separate query. To make it possible to read multiple lines query in `import.sql`, change it to `MultipleLinesSqlCommandExtractor`. Add `spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor=org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor` to `application.properties` if you are using `Spring Boot` – Disp Hay Nov 21 '15 at 05:12

Unsuccessful importing import.sql in hibernate

1 Answers1