auto create tables mysql

[lastlink]

this is a duplicate of the closed issue [#74]

This is how to auto create the tables using https://flywaydb.org/. I figured it out using https://www.callicoder.com/spring-boot-flyway-database-migration-example/.

Basically

Add this dependency to the pom.xml

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

mkdir -p src/main/resources/db/migration
touch src/main/resources/db/migration/V1__init.sql

place this text in there

-- initialize tables
DROP TABLE IF EXISTS `user_authority`;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `authority`;


CREATE TABLE user
(
id integer NOT NULL,
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
enabled boolean,
lastpasswordresetdate timestamp NOT NULL,
CONSTRAINT user_pkey PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `authority`;

CREATE TABLE authority
(
id integer NOT NULL,
name VARCHAR(50) NOT NULL,
CONSTRAINT authority_pkey PRIMARY KEY (id)
);

CREATE TABLE user_authority
(
user_id integer NOT NULL,
authority_id integer NOT NULL,
CONSTRAINT fk_authority_id_user_authority FOREIGN KEY (authority_id)
REFERENCES authority (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_USER_user_authority FOREIGN KEY (user_id)
REFERENCES user (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

-- insert data
INSERT INTO user (ID, USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL, ENABLED, LASTPASSWORDRESETDATE) VALUES (1, 'admin', '$2a$08$lDnHPz7eUkSi6ao14Twuau08mzhWrL4kyZGGU5xfiGALO/Vxd5DOi', 'admin', 'admin', 'admin@admin.com', 1, STR_TO_DATE('01/01/2016', '%c/%e/%Y %r'));
INSERT INTO user (ID, USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL, ENABLED, LASTPASSWORDRESETDATE) VALUES (2, 'user', '$2a$08$UkVvwpULis18S19S5pZFn.YHPZt3oaqHZnDwqbCW9pft6uFtkXKDC', 'user', 'user', 'enabled@user.com', 1, STR_TO_DATE('01/01/2016','%c/%e/%Y %r'));
INSERT INTO user (ID, USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL, ENABLED, LASTPASSWORDRESETDATE) VALUES (3, 'disabled', '$2a$08$UkVvwpULis18S19S5pZFn.YHPZt3oaqHZnDwqbCW9pft6uFtkXKDC', 'user', 'user', 'disabled@user.com', 0, STR_TO_DATE('01/01/2016','%c/%e/%Y %r'));

INSERT INTO authority (ID, NAME) VALUES (1, 'ROLE_USER');
INSERT INTO authority (ID, NAME) VALUES (2, 'ROLE_ADMIN');

INSERT INTO user_authority (USER_ID, AUTHORITY_ID) VALUES (1, 1);
INSERT INTO user_authority (USER_ID, AUTHORITY_ID) VALUES (1, 2);
INSERT INTO user_authority (USER_ID, AUTHORITY_ID) VALUES (2, 1);
INSERT INTO user_authority (USER_ID, AUTHORITY_ID) VALUES (3, 1);

Don't forget to update application.properties with the mysql credentials

spring.jackson.serialization.INDENT_OUTPUT=true
spring.h2.console.enabled=true
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_db
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

jwt.header=Authorization
jwt.secret=mySecret
jwt.expiration=604800
jwt.route.authentication.path=/auth
jwt.route.authentication.refresh=/refresh

Then run
mvn spring-boot:run

I suppose we could create a separate branch for mysql support to not conflict w/ the h2 memory database.

[szerhusenBC]

@lastlink thanks for sharing your code!

[szerhusenBC]

Linked this in README.

[weibingtie]

thanks

[rs10615]

Description:

Field userRepository in org.zerhusen.security.service.JwtUserDetailsService required a bean named 'entityManagerFactory' that could not be found.

Action:

Consider defining a bean named 'entityManagerFactory' in your configuration.

[rs10615]

Can you help me out ,i am unable to run demo

[kambleaa007]

no comments replys

[enverefe12]

I did everything like you told. But i was got an error.
Why am i getting this error?

java.lang.IllegalStateException: Failed to introspect Class [org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration$FlywayConfiguration] from ClassLoader [jdk.internal.loader.ClassLoaders$AppClassLoader@6f94fa3e]