lpsandaruwan lpsandaruwan - 1 month ago 28
Java Question

Spring boot - MySQL settings are not working

I am trying to develop an application using Spring boot and MySQL. As the documentation said, First I created the project using Spring initializr using Intelij Idea, configured the

application.properties
file, and wrote
schema-mysql.sql
file and
data-mysql.sql
file. After I ran the project, I found there are no tables or data in the MySQL database. What is wrong with my configuration? Please help.


application.properties file,


spring.datasource.url=jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username=root
spring.datasource.password=password

spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql = true
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.datasource.schema=schema-mysql.sql
spring.datasource.data=data-mysql.sql



dependencies in pom.xml file,


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

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>



schema-mysql.sql file,


CREATE TABLE IF NOT EXISTS `SOL16_USERS` (
`USERNAME` VARCHAR(200) NOT NULL,
'PASSWORD' VARCHAR(200) NOT NULL,
PRIMARY KEY (`USERNAME`)
);

CREATE TABLE IF NOT EXISTS 'SOL16_PRIVILEGES' (
'PRIVILEGE_ID' INT(2) NOT NULL,
'PRIVILEGE' VARCHAR(15) NOT NULL,
PRIMARY KEY ('PRIVILEGE_ID')
);

CREATE TABLE IF NOT EXISTS 'SOL16_USER_PRIVILEGES' (
'USERNAME' VARCHAR(200) NOT NULL,
'PRIVILEGE_ID' VARCHAR(2) NOT NULL,
PRIMARY KEY ('USERNAME')
);



And the file/directory structure is,


src
|----main
| |----java
| |----resources
| | |----static
| | |----templates
| | |----application.properties
| | |----data-mysql.sql
| | |----schema-mysql.sql

Answer

As the Spring boot documentation mentioned, what have fixed my issue was adding spring.datasource.platform to the application.properties. That is what I have been missing to initialize the schema using schema-{platform}.sql and data-{platform}.sql.

{platform} = value of spring.datasource.platform

So my final application.properties file is,

spring.datasource.url = jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username = root
spring.datasource.password = password

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto = validate
spring.jpa.show-sql = true
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.datasource.platform=mysql
spring.datasource.schema=schema-mysql.sql
spring.datasource.data=data-mysql.sql
spring.datasource.initialize=true
spring.datasource.continue-on-error=true