SpringBoot with H2 database - failing to execute SQL, while strating the application-CodePudding

I am learning spring security with JPA auth againest H2 table. It fails load seed data from data.sql I tried access the H2 console, but doesn't let me login. Any suggestions appreciated

applicaiton.properties

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true

User.java

@Entity
@Table(name = "USER_TABLE")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @Column(name="USER_NAME")
    private String userName;
  - - - - - - 
  - - - - - - 
}

data.sql

insert into USER_TABLE (id, USER_NAME, PASSWORD, ACTIVE, ROLES) values
(1, 'einstein', 'einstein', true, 'USER'),
(2, 'newton', 'newton', true, 'USER'),
(3, 'koushik', 'koushik', true, 'USER');

SecurityConfig.java

public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Autowired
    MyUserDetailsService userDetailsService;

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

MyUserService.java

@Service
public class MyUserDetailsService implements UserDetailsService {

    @Autowired
    UserRepository userRepository;

    @Override
    public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
        Optional<User> user = userRepository.findByUserName(userName);

        user.orElseThrow(() -> new UsernameNotFoundException("Not found: "   userName));

        return user.map(MyUserDetails::new).get();
    }
}

I get below error, while starting the spring-boot

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/resume-portal/target/classes/data.sql]: insert into USER_TABLE (id, USER_NAME, PASSWORD, ACTIVE, ROLES) values (1, 'einstein', 'einstein', true, 'USER'), (2, 'newton', 'newton', true, 'USER'), (3, 'koushik', 'koushik', true, 'USER'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "USER_TABLE" not found (this database is empty); SQL statement:
insert into USER_TABLE (id, USER_NAME, PASSWORD, ACTIVE, ROLES) values (1, 'einstein', 'einstein', true, 'USER'), (2, 'newton', 'newton', true, 'USER'), (3, 'koushik', 'koushik', true, 'USER') [42104-214]

Many thanks to the community.

CodePudding user response：

Since your database generates a unique id for you, you do not have to provide any id in the insert statement.

insert into USER_TABLE (USER_NAME, PASSWORD, ACTIVE, ROLES) values
('einstein', 'einstein', true, 'USER'),
('newton', 'newton', true, 'USER'),
('koushik', 'koushik', true, 'USER');

Change your DML to define no id to solve your problem.

Alternatively, you can create instances of your entity with id = null in a command line runner and add them to the repository.

@Component
public class Initializer implements CommandLineRunner {

  
    @Autowired
    UserRepository repository;

    @Override
    public void run(String... args) throws Exception {
           //Pseudocode
           User user = new User(id=null,name="Albert Einstein" ...)
           repository.save(user)
           
    }
}