Spring Hibernate MySQL initialization

Question

I'm trying to figure out Spring Hibernate - MySQL relationship. My purpose is, when I start the program, it execute the import.sql file within the classpath. I want to do that without destroy existing tables. I found some documentation about execute sql file on startup.

http://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

The official spring doc says;

In addition, a file named import.sql in the root of the classpath will be executed on startup.

But, Spring destroy my tables and than execute import.sql file. Is there a way to execute .sql file without lost existing data?

In the below question, he almost wanted the same thing like me.

How to import initial data to database with Hibernate?

But;

This will also work only if hbm2ddl.auto is set to create or create-drop.

I serached a lot but couldn't find a way. Long stroy short, how can I run .sql statement on startup without destroying database?

Read a little further... [Section about JDBC](http://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-intialize-a-database-using-spring-jdbc) don't use Hibernate or JPA to initialize the database just use JDBC. Add a `data.sql` instead. — M. Deinum, Sep 29 '15 at 07:30
You could put the population of data in a application-scoped, non-lazy initialized bean (and run native sql queries from there).. — Raphael Roth, Sep 29 '15 at 07:40
AFAIK, when configuring Hibernate, you can ask it to recreate the database schema on each and every start with the property `create-drop`. Be sure to set it at at most `validate`. — Serge Ballesta, Sep 29 '15 at 07:42
@M.Deinum, I read the section, and made the changes but it didn't execute the data.sql file. Here is the changes that I made; `application.properties` spring.datasource.initialize=true spring.datasource.data= classpath: data.sql hibernate.hbm2ddl.auto = and my `data.sql` file in the src/main/resources folder like `application.properties` file. — Black Glix, Sep 29 '15 at 08:01
You don't need to change anything, only add a `data.sql` everything else can remain as is. The file will be executed by default. Also please don't add code as comments improve your question with it. — M. Deinum, Sep 29 '15 at 08:23
Oh sorry about that. I undo the changes but still no execution. Based on this tutorial [link](http://websystique.com/spring-security/spring-security-4-remember-me-example-with-hibernate/) the place for data.sql is src/main/resources folder isn't it? — Black Glix, Sep 29 '15 at 08:40

score 4 · Accepted Answer · edited May 23 '17 at 12:17

I can finally find the answers for my own question. I used java-based configuration on my project. Most of the examples on the internet related to spring use xml-based configuration, and it is difficult to me find the answers for my questions.

Luckyly I saw this question;

How to execute SQL insert queries to populate database during application start/load?

and the answer is

DataSourceInitializer

It works like a charm!

Here is the code piece of running SQL statements on startup without destroy existing database.

@Bean
    public DataSourceInitializer dataSourceInitializer() {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));

    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDataSource(dataSource());
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    return dataSourceInitializer;
}

Spring Hibernate MySQL initialization

1 Answers1

Linked