CODEWITHSUNDEEP
javaspring-bootspring-boot-test
Al Phaba
Al Phaba

Reputation: 6755

Spring boot test throws JdbcException "table not found"

I have application-test.properties where I defined two datasources

    app.datasource.server.url=jdbc:h2:mem:fooserver
    app.datasource.server.username=sa
    app.datasource.server.password=
    app.datasource.server.driverClassName=org.h2.Driver
    app.datasource.server.hikari.minimumIdle=5
    app.datasource.server.hikari.maximumPoolSize=50
    app.datasource.server.hikari.idleTimeout=50000
    app.datasource.server.hikari.maxLifetime=55000


    app.datasource.manager.url=jdbc:h2:mem:barmanager
    app.datasource.manager.username=sa
    app.datasource.manager.password=
    app.datasource.manager.driverClassName=org.h2.Driver
    app.datasource.manager.hikari.minimumIdle=5
    app.datasource.manager.hikari.maximumPoolSize=50
    app.datasource.manager.hikari.idleTimeout=50000
    app.datasource.manager.hikari.maxLifetime=55000


    # Hibernate ddl auto (create, create-drop, validate, update)
    spring.jpa.hibernate.ddl-auto=create-drop


    #logging
    logging.level.root=info
    logging.file=foobar-rest-test.log

    #required for SpringBootTest does not know why
    spring.main.allow-bean-definition-overriding=true
    spring.h2.console.enabled=true
    spring.h2.console.path=/h2-console

Each of the datasource requires schema to be available named "foo" this will be created by a schema-fooserver.sql and schema-barmanager.sql in each of these sql scripts the foo schema will be created. Therefore I defined a dataSourceIntializer Bean where I can define which schema-sql file will be loaded.

@Bean(name = "managerDataSourceInitializer")
public DataSourceInitializer dataSourceInitializer1(@Qualifier("managerDataSource") DataSource datasource) {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("schema-barmanager.sql"));        

    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDataSource(datasource);
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    return dataSourceInitializer;
}



@Bean(name = "serverDataSourceInitializer")
public DataSourceInitializer dataSourceInitializer1(@Qualifier("serverDataSource") DataSource datasource) {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("schema-fooserver.sql"));

    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDataSource(datasource);
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    return dataSourceInitializer;
}

During the start of my test the logs show that these schema files have been called and executed.

    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [schema-fooserver.sql]
    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE SCHEMA IF NOT EXISTS FOO
    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [schema-fooserver.sql] in 0 ms.


    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [schema-barserver.sql]
    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE SCHEMA IF NOT EXISTS FOO
    2019-03-28 15:04:34.252 DEBUG 3124 --- [main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [schema-barserver.sql] in 0 ms.

Now when I try to execute my test case it fails because of the following error: 

    28 15:04:36.035 DEBUG 3124 --- [           main] org.hibernate.SQL                        : insert into foo.Account (uid, password_hash, login) values (null, ?, ?)
    Hibernate: insert into foo.Account (uid, password_hash, login) values (null, ?, ?)
    2019-03-28 15:04:36.036 DEBUG 3124 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : could not prepare statement [insert into foo.Account (uid, password_hash, login) values (null, ?, ?)]

    org.h2.jdbc.JdbcSQLException: Tabelle "ACCOUNT" nicht gefunden
    Table "ACCOUNT" not found; SQL statement:
    insert into foo.Account (uid, password_hash, login) values (null, ?, ?) [42102-197]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)

At this point I am trying to create a UserAccount in my testcase

This is the defined UerEntity

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    @Entity
    @Table(name = "foo.Account")
    public class UserEntity {

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "uid")
        private Long id;
        @Column(name = "login")
        private String username;
        @Column(name = "password_hash")
        private String password;
 ....

Here is the Testcase. The error occurs when the before mehod is called during the createUser Method. 

    @RunWith(SpringRunner.class)
    @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
    @ActiveProfiles("test")
    @AutoConfigureMockMvc
    public class UserControllerTest {
        private static final Logger LOG = LoggerFactory.getLogger(UserControllerTest.class);

        @LocalServerPort
        private int port;
        TestRestTemplate restTemplate = new TestRestTemplate();
        HttpHeaders headers = new HttpHeaders();
        @Autowired
        private WfcSpringRestApplication controller;

        @Autowired
        private UserRepository repository;

        @Autowired
        private MockMvc mvc;

        private AuthenticationToken authToken;

        @Before
        public void before() throws Exception {
            headers = new HttpHeaders();
            UserEntity user = createTestUser(TEST_ADMIN_USER, TEST_ADMIN_MD5_PW, UserRight.ADMIN);
            UserEntity userService = createTestUser(TEST_SERVICE_USER, TEST_ADMIN_MD5_PW, UserRight.SERVICE);
            getAuthenticationTokenForTestUser(user);

        }


        private UserEntity createTestUser(String username, String md5_password, UserRight right) {

            UserEntity ue = new UserEntity();
            ue.setUsername(username);
            ue.setPassword(md5_password);

            UserRole roleAdmin = new UserRole();
            roleAdmin.setRight(right);

            ue.getRoles().put(roleAdmin.getRight(), roleAdmin);

            repository.save(ue);

            return ue;
        }


        @Test
        public void contextLoads() {
            assertThat(controller).isNotNull();
        }

In the error message there is the correct table name "could not prepare statement [insert into foo.Account" why it throws the exception that the table account is not found?

Upvotes: 1

Views: 3145

Answers (1)

HARSHIT BAJPAI
HARSHIT BAJPAI

Reputation: 650

I faced similar error and I tried pretty much every solution mentioned on other websites such as DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1; DB_CLOSE_ON_EXIT=FALSE; IGNORECASE=TRUE

But nothing worked for me.

For Spring Boot 2.4+ use spring.jpa.defer-datasource-initialization=true in application.properties (mentioned here - https://stackoverflow.com/a/68086707/8219358)

Another way that worked for me was renaming data.sql to import.sql

I found it here - https://stackoverflow.com/a/53179547/8219358

I realize other solutions are more logical but none of them worked for me and this did.

Upvotes: 1

Related Questions