Spring: Setup MySQL Database in Project

Question

I try to include a MySQL-DB in my project.

I've installed MySQL program, created a DB (schema) and set password and username for the access-authentification.

Included pom-file dependencies and so on.

Now I'm stuck with my application.properties:

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/asndb
spring.datasource.username=root
spring.datasource.password=asnTeam1
spring.jpa.hibernate.ddl-auto=create

In the MySQL Manager I can see all properties from my models, so the connection should work, right?

What do I wrong? Do I need the MySQL client?

UPDATE changed application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/asndb?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=asnTeam1
spring.jpa.hibernate.ddl-auto=create
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.jpa.show-sql = true
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

Now I want to open with MySQL client my data.sql file. But there are erros in my sql syntax. Following an insert statement:

INSERT INTO USER (FIRST_NAME, LAST_NAME, USERNAME, PASSWORD, USER_ROLE) VALUES('Admin', 'Istrator', 'admin', '$2a$10$W9xQIwa/FstPUvcbzJXnQ.XjVdTyIcCEp.g6VCq1gYuSsQNjJjbJG', 'ADMIN')

INSERT INTO USER (FIRST_NAME, LAST_NAME, USERNAME, PASSWORD, USER_ROLE) VALUES('Bernd', 'Menia', 'bernd', '$2a$10$6dQcayeT/JAFvgFvzIjlcew5z9cmdCrGlv57.BGnIKvPXTDsQm7hG', 'PARENT')
INSERT INTO PARENT (FAMILY_STATUS, LOCATION, POSTCODE, STREET_NAME, IMG_NAME, STATUS, ID) VALUES ('MARRIED', 'Innsbruck', '6020', 'Bahnhofstraße', 'john.jpg',TRUE, SELECT ID FROM USER WHERE USERNAME = 'bernd')

What do I wrong?

SOLUTION:

insert the larger application.properties from above and for a persistent DB comment out the ddl-auto=true and initialize=true after the first spring-boot.

User is a reserved keyword in MySQL, so it is not allowed to use.

to fill a mySQL DB with data.sql file from spring-app is not possible, so I open the file via MySQL-Workbench and execute the statements.

it seems like the issue comes from timezone configuration. This can help you : stackoverflow.com/questions/7605953/… or this : stackoverflow.com/questions/7605953/… — akuma8
– akuma8, Commented Apr 19, 2017 at 15:20

Community · Accepted Answer · 2017-05-23 12:18:22Z

3

Came across to a similar problem from this post. Hope this helps.

Instead of

spring.datasource.url=jdbc:mysql://localhost:3306/asndb

Try this:

spring.datasource.url=jdbc:mysql://localhost:3306/asndb?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Apr 19, 2017 at 15:21

krsna

4,3437 gold badges61 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SteveOhio Over a year ago

thanks that helps.. but now there are no data from my data.sql in the DB anymore

krsna Over a year ago

try to add some data into your DB using mySQL commands from your console and check it.

SteveOhio Over a year ago

it doesn't work. when i re-execute the SFW-statement the table is empty. I tried to open my data.sql file but it says there are errors in my sql syntax ... details above in the question

krsna Over a year ago

user is a reserved keyword in SQL. So, you can't use that as a table name. Create a table with another name like user_table. Check this Link - drupal.org/docs/develop/coding-standards/… @SteveOhio

akuma8 · Accepted Answer · 2017-04-19 16:43:13Z

1

To insert data in your database tables, be sure that the database is opened, so in your MySQL client, begin by :

mysql>use asndb;

And check that your tables : user and parent realy exist :

mysql>show tables;

After that you can insert your data :

INSERT INTO asndb.USER(.....);

And USER you can't use it

edited Apr 19, 2017 at 16:43

answered Apr 19, 2017 at 16:21

akuma8

4,7139 gold badges58 silver badges98 bronze badges

Collectives™ on Stack Overflow

Spring: Setup MySQL Database in Project

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related