Spring Boot - Initialize mysql with .sql Script is failed-CodePudding

I need an advice for running sql script when i startup with spring project.

Here's where i am :

I don't want to use JPA(Hibernate) -> No JPA dependency in pom.xml
Spring Project, mysql are running very well, But No Data in my DB.

looks very small issue, but can't figure out what is the problem ;(

Thanks in advance!

pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>
</dependencies>

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/MyDB
spring.datasource.username=root
spring.datasource.password=root-password
spring.sql.init.mode=always
spring.sql.init.platform=mysql

schema-mysql.sql

DROP TABLE IF EXISTS test_db;

CREATE TABLE test_db
(
    id    int NOT NULL AUTO_INCREMENT,
    value VARCHAR(25),
    PRIMARY KEY(id)
);

data-mysql.sql

INSERT INTO test_db(value) VALUES(100);

CodePudding user response：

Flyway example. Add to resources folder, for example 'scripts' and put your scripts. Then add property application.properties

spring.flyway.user=root
spring.flyway.password=root-password
spring.flyway.schema=public
spring.flyway.url=jdbc:mysql://localhost:3306/MyDB
spring.flyway.locations=classpath:/scripts

CodePudding user response：

I failed to use the concept 'JDBC initialize'. But thanks to the opinion @andrew17 and @K.Nikita, I decided to use Flyway. (I'd never known flyway before, thanks again!)

So Here is the result of the configuration files.

pom.xml

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <configuration>
        <url>jdbc:mysql://localhost:3306/Restagram</url>
        <user>root</user>
        <password>qwER12#$</password>
        <baselineOnMigrate>true</baselineOnMigrate>
    </configuration>
</plugin>

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/MyDB
spring.datasource.username=root
spring.datasource.password=root-password
spring.sql.init.mode=always
spring.sql.init.platform=mysql

data file(.sql)

location : resources/db/migration
DDL file Name : V1__create_testDB.sql (create table query is in here)
DML file Name : V1.1__insert_testDB.sql (insert query is in here)