4

updated with rieckpil suggestion

Hi,

right now I am facing an issue which appears only when I am using a CrudRepository to add/read entities from a H2 DB.

A very basic example which works:

My properties:

spring.datasource.url=jdbc:h2:file:~/flyawayTest;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE
spring.datasource.username=admin
spring.datasource.password=password
spring.datasource.driver-class-name=org.h2.Driver
spring.h2.console.enabled=true
spring.h2.console.path=/h2
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.flyway.baseline-on-migrate = true

and an example class

@Entity
@Data
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String username;

    public User() {
    }

    public User(String username) {
        this.username = username;
    }

    public User(String username, long lastLogin) {
        this.username = username;
        //this.lastLogin = lastLogin;
    }
}

Using this .sql file

CREATE TABLE USER (
  ID bigint(20) NOT NULL AUTO_INCREMENT,
  USERNAME varchar(100) NOT NULL,
  PRIMARY KEY (ID)
);

insert into USER (USERNAME) values ('User');

ALTER TABLE USER ADD COLUMN LASTLOGIN bigint(20) default 0

If I am going to work with a CrudRepository I got the exception:

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [last_login] in table [user]

Repo

@Repository
public interface UserRepo extends CrudRepository<User, Long> {

}

Helper-Class to test this procedure

@Component
public class UCML implements CommandLineRunner {


    private final UserRepo userRepo;

    public UCML(UserRepo userRepo) {
        this.userRepo = userRepo;
    }

    @Override
    public void run(String... args) throws Exception {

        userRepo.save(new User("TestUser"));
        userRepo.findAll().forEach(System.out::println);
    }
}

V1__baseline.sql (is empty since it is only used to get a baseline of the DB with consisting user TestUser

V2__migrate.sql

ALTER TABLE USER ADD COLUMN LASTLOGIN bigint(20) default 0

My steps are:

  1. Adding a new user userRepo.save(new User("A")); to my empty db
  2. Shutdown application
  3. Add the flywaydb dependency to my pom
  4. create an empty .sql file named V1__baseline.sql to make use of spring.flyway.baseline-on-migrate = true and start the app, success, shutdown
  5. create a new sql file named V2__migrate.sql with content ALTER TABLE USER ADD COLUMN LASTLOGIN bigint(20) default 0

then I add the field private long lastLogin; to the class User.

When I now start the application again I get the error I mentioned above

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [last_login] in table [user]

So what I do wrong here?

Improve this question

2 Answers 2

Reset to default
2

Your class field named as lastLogin and Hibernate translate it to last_login to find a column in the database. You must specify which column the field corresponds to manually like this:

@Data
@Entity
@Table(name = "USER", schema = "YOUR_SCHEMA_NAME")
public class User {

    @Id
    @Column(name = "ID", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Basic
    @Column(name = "USERNAME", nullable = false)
    private String username;

    @Basic
    @Column(name = "LASTLOGIN")
    private Long lastLogin = 0;
}

Also, you don't need @Repository anymore.

You must tag your SpringBootServletInitializer class with an @EnableJpaRepositories annotation indicating the package where the CrudRepository are located.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much. Also for the side information about the @Repository annotation which is useful for me to improve! A bit offtopic, but have you a good source to get informed about changes like this?
0

It looks like your Flyway scripts does not get populated. You have to follow the Flyway naming convention like V01__INIT.sql (you need the two _) and store them at src/main/resources/db/migration.

During the startup of your application, you should see that Flyway either populates your scripts or logs that you already have applied the latest schema to the database.

You can find a simple Flyway example here: https://flywaydb.org/getstarted/how

In addition, I would recommend using spring.jpa.hibernate.ddl-auto=validate to make sure that only Flyway creates your database schema.

Improve this answer

4 Comments

I updated my question with your suggestion. Indeed the error changed, but I don't even know if it is better or worse now.
Could you update your example class. Because right now there is now lastLogin object attribute. The error message says that JPA is missing a database column. Also please add all of your Flyway scripts to your question
Done. The lastLogin attribute is the column I want to add to the table user
I'd suggest to drop the current database (if possible) and then init the database schema only with Flyway and create one migration script V01__INIT_USER.sql where you init your whole User table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.