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 : http://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-java-connection or this : http://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-java-connection — akuma8, Apr 19 '17 at 15:20
Install MySQL Workbench, it'll be easier for you to manage your database. Beside that, add the database name with each insertion. Something like : `INSERT INTO asndb.USER(....)` — akuma8, Apr 19 '17 at 16:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142101/discussion-between-steveohio-and-akuma8). — SteveOhio, Apr 19 '17 at 16:20

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

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 '17 at 12:18

Community

1
1

answered Apr 19 '17 at 15:21

coderpc

4,119
6
51
93

thanks that helps.. but now there are no data from my data.sql in the DB anymore – SteveOhio Apr 19 '17 at 15:27
try to add some data into your DB using mySQL commands from your console and check it. – coderpc Apr 19 '17 at 15:30
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 – SteveOhio Apr 19 '17 at 15:42
1

`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 - https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words @SteveOhio – coderpc Apr 19 '17 at 16:39

akuma8 · Answer 2 · 2017-04-19T16:43:13.843

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 '17 at 16:43

answered Apr 19 '17 at 16:21

akuma8

4,160
5
46
82

Spring: Setup MySQL Database in Project

2 Answers2