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.sql
file. If it's present, update it according to the code in the @Entity
class
Second, fill the users
tables with sample rows if it's empty and ensure the roles
table has at least a row with role = 'USER'
and a row with role = 'ADMIN'
. Also, fill the @JoinTable
in case users
was filled with my sample rows
To do that, I added these properties in my application.properties
file
# url, username, password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always
and put schema.sql
and data.sql
files in my resources/sql
directory
# schema.sql
CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(15) NOT NULL UNIQUE,
password VARCHAR(200) NOT NULL,
name VARCHAR(15) NOT NULL,
last_name VARCHAR(25) NOT NULL,
department VARCHAR(50) NOT NULL,
salary INT,
age TINYINT NOT NULL,
email VARCHAR(50),
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,
username VARCHAR(15) NOT NULL,
role_id INT NOT NULL,
role VARCHAR(15) NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (role_id) REFERENCES roles (id)
);
INSERT INTO users(username, password, name, last_name, department, salary, age, email, enabled)
SELECT *
FROM (VALUES ('mickey_m', '$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
'Mickey', 'Mouse', 'sales', 180000, 95, 'mickey_m@gmail.com', 1),
# and so on
) sample_rows
WHERE NOT EXISTS(SELECT NULL FROM users);
INSERT IGNORE INTO roles(role)
VALUES ('USER'),
('ADMIN');
INSERT IGNORE INTO user_role (user_id, username, role_id, role)
SELECT users.id, users.username, roles.id, roles.role
FROM (VALUES
ROW ('mickey_m', 'USER'),
ROW ('scrooge_m', 'USER'),
ROW('scrooge_m', 'ADMIN')
# and so on
)
AS user_to_role (username, role)
JOIN users ON users.username = user_to_role.username
JOIN roles ON roles.role = user_to_role.role;
Now, it doesn't seem to work. At least, Hibernate is fine with the fact that my columns are mostly nullable. It doesn't force them to become NOT NULL
. The @Entity
classes match schema.sql
in terms of nullability
@Entity
@Table(name = "users")
public class User implements UserDetails {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(nullable = false, unique = true)
private String username;
@Column(nullable = false)
private String password;
// and so on
I don't get any error messages
How do I make Hibernate alter tables according to my @Entity
mappings?