0

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?

  • Please check your question. Looks like the constraints in entity and in schema.sql are exactly the same? nullable=false vs. NOT NULL – Mar-Z Apr 16 '23 at 08:04
  • Another point: you have activated both sql and hibernate schema generation. The generation from entities will run PRIOR to the generation from schema.sql – Mar-Z Apr 16 '23 at 08:09
  • @Mar-Z why shouldn't they be the same? – Sergey Zolotarev Apr 16 '23 at 13:39
  • @Mar-Z I know it. What do you mean to say? – Sergey Zolotarev Apr 16 '23 at 13:40
  • OK. I got your point now. You mean that before you start application the table users already exists and have nullable columns. And you want to alter the table to not nullable. Using either update from entity or from schema.sql. Right? – Mar-Z Apr 16 '23 at 14:39
  • I have tested it with H2 file database and have the same issue. It looks like auto-generation from entities has limited update capabilities. You can add a new column to a table but not delete it anymore. And not update the constraints like not null. – Mar-Z Apr 16 '23 at 15:29
  • @Mar-Z basically, yes. I want Hibernate to either create tables, if they do not exist (according to `schema.sql`), or, if they do exist, alter them if necessary (according to `@Entity` mappings) – Sergey Zolotarev Apr 16 '23 at 16:20
  • 1
    Hibernate will only add colums to table it will not alter constraints, not-null or default values if those have changed. Also it is a bad idea to let Hibernate manage your schema, only do this for tests and don't try to use 2 different mechanisms to manage your schema. It will also be impossible for Spring Boot to cater for all needs (you want `schema.sql`, Hibernate, `data.sql` others might want another combo). – M. Deinum Apr 19 '23 at 07:39
  • 1
    See also https://stackoverflow.com/questions/75153080/hibernate-column-changes-are-not-reflected-in-database/75160276#75160276 what will and will not be updated. – M. Deinum Apr 19 '23 at 09:51
  • @M.Deinum what's the point of `nullable=false` then? – Sergey Zolotarev Apr 19 '23 at 22:30
  • 2
    That is taken inconsideration when the field is added not when it is changed as explained in the answer/question I linked to. So it is used for newly added columns but not when you change the nullability from true to false (or vice-versa). – M. Deinum Apr 20 '23 at 05:50
  • @Henke Of course, not. It has almost nothing to do with my question – Sergey Zolotarev Apr 20 '23 at 10:14

0 Answers0