Here's what I want Hibernate to do:
First, to check the existence of the required table. If it's absent, create it according to my
schema.sqlfile. If it's present, update it according to the code in the@Entityclass.Second, to fill the
userstables with sample rows if it's empty, and ensure therolestable has at least one row withrole = 'USER', and one row withrole = 'ADMIN'.Also, fill the
@JoinTableso that every user is aUSERexcept the first user, who should be anADMIN.
To do that, I added these properties in my application.properties
file (as per this Baeldung article):
# url, username, password
spring.jpa.defer-datasource-initialization=true
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always
and put schema.sql and data.sql files in my resources/sql directory. These are the trimmed versions of my tables:
-- schema.sql
CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(15) NOT NULL UNIQUE,
password VARCHAR(200) NOT NULL,
enabled TINYINT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE roles
(
id INT NOT NULL AUTO_INCREMENT,
role VARCHAR(15) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
CREATE TABLE user_role
(
user_id INT NOT NULL,
role_id INT NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (role_id) REFERENCES roles (id)
);
-- data.sql
-- inserting sample rows if empty
INSERT INTO users(username, password, enabled)
SELECT *
FROM (VALUES ROW ('mickey_m',
-- ↓ it's "mickey" in a bcrypted form
'$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
1),
-- and so on
) AS sample_rows
WHERE NOT EXISTS(SELECT NULL FROM users);
-- ensuring the required roles exist
INSERT IGNORE INTO roles(role)
VALUES ('USER'),
('ADMIN');
-- filling the @JoinTable: all users must be USERs
INSERT IGNORE INTO user_role
SELECT users.id, roles.id
FROM users
JOIN roles ON roles.role = 'USER';
-- filling the @JoinTable: the first user must also be an ADMIN
INSERT IGNORE INTO user_role
SELECT users.id, roles.id
FROM users
JOIN roles ON roles.role = 'ADMIN' ORDER BY users.id LIMIT 1;
It doesn't work – data.sql doesn't have any effect as the first user
in the users table is still not an ADMIN after the application's
startup.
I skimmed the console for any INSERT-related logs – no luck.
The SQL works as expected on dbfiddle.
The only apparent difference is that on dbfiddle the tables are
first explicitly created. (I already have them in my database.)
However, I expected data.sql to be executed regardless of whether
the tables exist.
Did I miss some pieces of configuration?
