0

I had trouble getting Hibernate to find my data.sql file and populate my table. I asked ChatGPT for help and it suggested specifying my scripts location because

According to the Spring Boot documentation, by default, it looks for data.sql in the classpath root

–GPT-4

I followed GPT's advice and added this property to my application.properties file (the scripts are in the resources/sql directory)

spring.sql.init.data-locations=classpath:/sql/data.sql

It worked! But then I decided to shorten it a bit. I don't like that it points to a specific file rather than to a directory. Result:

Caused by: java.sql.SQLSyntaxErrorException: 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 'data.sql' at line 1

The SQL was and is valid (you can run it here)

INSERT INTO users(username, password, name, last_name, department, salary, age, email, enabled)
SELECT *
FROM (VALUES ROW ('mickey_m', '$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
              'Mickey', 'Mouse', 'sales', 180000, 95, 'mickey_m@gmail.com', 1),
              
              -- more rows
             
              )

         AS sample_rows

WHERE NOT EXISTS(SELECT NULL FROM users);

so the issue has to do with how Hibernates finds and handles my scripts, I guess

How do I correctly specify the directory of my SQL scripts (rather than point to a specific file)?

Importantly, the resources/sql directory also has a schema.sql file that should also be visible to Hibernate if the tables do not exist for initial table creation

No, you don't need an MRE to answer this!

UPD: I tried some things. So far, this property set works best for me

spring.jpa.hibernate.ddl-auto=none
spring.sql.init.mode=always
spring.sql.init.continue-on-error=true
spring.sql.init.data-locations=classpath:/sql/data.sql
spring.sql.init.schema-locations=classpath:/sql/schema.sql

-- it would be nice if I could just go with
-- spring.sql.init-locations=classpath:/sql
-- or better still
-- sql-locations=/sql

But notice it takes up two lines of code to specify locations instead of just one

Sergey Zolotarev
  • 379
  • 11
  • Better safe than sorry. I'll put it in the comments after all. You can see the screenshots in the edit history. _I noticed that after my script is run, my `roles` get upside down. Most likely, it's because of some additional indices that weren't there before the script was executed. The indices underlined with red were presumably added by Hibernate (what a silly thing to do!). It probably would make sense for `role` since it's `UNIQUE`, but I have no idea what `UK_98` is and what it is for_ – Sergey Zolotarev Apr 20 '23 at 02:05
  • By the way, [this](https://www.baeldung.com/spring-boot-data-sql-and-schema-sql) Baeldung statement appears BS: _[spring.jpa.defer-datasource-initialization=true] will ensure that after Hibernate schema creation is performed, then additionally schema.sql is read for any additional schema changes, and further data.sql is executed to populate the database_. The `schema.sql` is not "read for additional changes", it gets executed no matter what so you'll get SQL exceptions when tables created with `@Entity` mappings are created again with `schema.sql` – Sergey Zolotarev Apr 20 '23 at 02:51
  • (unless you have the property `spring.sql.init.continue-on-error=true`) – Sergey Zolotarev Apr 20 '23 at 02:53
  • Which begs the question: why on earth may I need `spring.jpa.defer-datasource-initialization=true` then? – Sergey Zolotarev Apr 20 '23 at 02:56

0 Answers0