Can't insert data into H2 database when starting Spring Boot app-CodePudding

My first time working with a h2 database and I'm stuck trying to insert data into the db when Spring starts my app. I have it set as a file db to be able to access it from both the console and IntelliJ. I can see the tables okay, and when I press the run both under the data.sql file the data in inserted, but not automatically when the application starts.

The tables were created automatically and without a sql file but inserting data through the data.sql file won't work. Even if we manually populate the db, later my repository.count() call will still return 0, which I think may be connected to the first issue as it's perhaps not finding the db. I've tried to fix this for two days now but I really can't figure out what is it that I'm missing.

spring.jpa.hibernate.ddl-auto=update
# Enabling H2 Console
spring.h2.console.enabled=true
#spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.url=jdbc:h2:file:./src/main/resources/data/resume-portal;MV_STORE=false;AUTO_SERVER=TRUE
spring.data.jpa.repositories.bootstrap-mode=default
spring.jpa.defer-datasource-initialization=true
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
server.port=8888
spring.sql.init.data-locations=classpath:data.sql

@Entity
@Table(name = "User")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int id;
    public String userName;
    public String password;
    public boolean active;
    public String roles;

... getters and setters as well

@EnableWebSecurity(debug = true)
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Autowired
    UserDetailsService userDetailsService;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService);
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
                .antMatchers("/edit").authenticated()
                .antMatchers("/console/**").permitAll()
                .antMatchers("/login").permitAll()
                .antMatchers("/*").permitAll()
                .and().formLogin()
                .failureHandler(new AuthenticationFailureHandler() {

                    @Override
                    public void onAuthenticationFailure(HttpServletRequest request, HttpServletResponse response, AuthenticationException exception) {
                        System.out.println("A user has failed to login. Error: "   exception.getMessage());
                    }
                })
                .permitAll();

        http.csrf().disable();
        http.headers().frameOptions().disable();
    }

    @Bean
    public PasswordEncoder getPasswordEncoder() {
        return NoOpPasswordEncoder.getInstance();
    }

}

https://github.com/francislainy/resume-portal

Thank you.

Update

Using an in memory DB instead of file type works. Not sure why.

spring.datasource.url=jdbc:h2:mem:testdb

CodePudding user response：

By default, Spring Boot's auto-configuration only executes the sql initialization scripts for embedded databases. When you use a file-based instead of an in-memory database, Spring Boot no longer considers the database to be embedded.

You can explicitly enable initialization by setting the property

spring.sql.init.mode=always

Please also have a look at the official documentation: https://docs.spring.io/spring-boot/docs/2.6.4/reference/html/howto.html#howto.data-initialization.using-basic-sql-scripts