Using H2 and HikariCP - Table Not Found

Question

I'm trying to set up a test with HikariCP and H2 along with rxjava-jdbc. My code works fine when connecting to a real database, but when switching to H2, I can't seem to get the table to stick, even though I'm not doing in-memory.

Code:

@RunWith(SpringRunner.class)
@ContextConfiguration(loader=AnnotationConfigContextLoader.class)
public class BidsRepositoryTest {

    private static final Logger log = LoggerFactory.getLogger(BidsRepositoryTest.class);

    @Configuration
    static class ContextConfiguration {

        // this bean will be injected into the OrderServiceTest class
        @Bean
        public BidsRepository bidsRepository() {
            BidsRepository bidsRepository = new BidsRepositoryImpl();
            // set properties, etc.
            return bidsRepository;
        }

        @Bean(destroyMethod = "close")
        public DataSource dataSource() throws SQLException {
            HikariConfig config = new HikariConfig();
            config.setDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
            config.setConnectionTestQuery("VALUES 1");
            config.addDataSourceProperty("URL", "jdbc:h2:~/testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MSSQLServer");
            config.addDataSourceProperty("user", "sa");
            config.addDataSourceProperty("password", "sa");
            HikariDataSource ds = new HikariDataSource(config);

            HikariDataSource dataSource = new HikariDataSource(config);

            return dataSource;
        }

        @Bean
        public ConnectionProvider connectionProvider(){
            return new CustomConnectionProvider();
        }
    }

    @Autowired
    private BidsRepository bidsRepository;

    @Autowired
    ConnectionProvider connectionProvider;


    @Test
    public void shouldSelectEntityForMatchingIds(){

        int userId = 1012;
        int listingId = 2;

        Database db = Database.builder().connectionProvider(connectionProvider).build();
        db.update("create table Bids\n" +
                "(\n" +
                "\tID int not null,\n" +
                "\tUserID int not null\n" +
                "\tAmount money not null,\n" +
                "\tParticipation money default 0 not null,\n" +
                "\tMinRate decimal(6,3) not null,\n" +
                "\tListingID int not null\n" +
                "\tStandingBidID int default (-1) not null,\n" +
                "\tFundingAccountID int not null\n" +
                "\tCreationDate datetime default getdate() not null,\n" +
                "\tModifiedDate datetime default getdate() not null,\n" +
                "\tCollectionAgencyID int\n" +
                "\tWithdrawn bit default 0 not null,\n" +
                "\tAnonymous bit default 0 not null,\n" +
                "\tAltKey varchar(30) default substring(convert(varchar50,newid()),1,4) + convert(varchar50,(convert(bigint,getdate()) * 86400000 + datepart(hour,getdate()) * 3600000 + datepart(minute,getdate()) * 60000 + datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()) + abs(checksum(newid())))) + substring(convert(varchar50,newid()),30,6) not null,\n" +
                "\tAltKeySearchID as checksum([AltKey]),\n" +
                "\tBidSourceID tinyint not null\n" +
                "\tMinYield decimal(6,3) not null,\n" +
                "\tInvestmentOrderID int\n" +
                "\tconstraint PK_Bids\n" +
                "\t\tprimary key (ID, ListingID)\n" +
                ") go");


        db.update("INSERT INTO Bids (ID, UserId, Amount, Participation, MinRate, ListingId, BidSourceId, MinYield) "
                + " VALUES(1, 1012, 10000, 3000, 0.06, 2, 2, 12000) go");


        bidsRepository.getBid(userId, listingId).forEach( a-> {
                    assertThat(a.getUserId()).isEqualTo(userId);
                    assertThat(a.getListingId()).isEqualTo(listingId);
                }
        );
    }
}

Exception: rx.exceptions.OnErrorNotImplementedException: Table "Bids" not found; SQL statement: (omitted);

Edit: Went through this thread, but was unable to find a solution: H2 in-memory database. Table not found

score 0 · Accepted Answer · answered Apr 14 '17 at 18:16

The problem with this code is not the setup of h2. H2 is performing as it should, it was my misunderstanding of the rxjava-jdbc code - the create/insert statements needed a .execute() on the end to run them.

Database db = Database.builder().connectionProvider(connectionProvider).build();
        db.update("create table Bids\n" +
                "(\n" +
                "\tID int not null,\n" +
                "\tUserID int not null,\n" +
                "\tAmount decimal not null,\n" +
                "\tParticipation decimal default 0 not null,\n" +
                "\tMinRate decimal(6,3) not null,\n" +
                "\tListingID int not null,\n" +
                "\tStandingBidID int default (-1) not null,\n" +
                "\tFundingAccountID int not null,\n" +
                "\tCreationDate datetime default getdate() not null,\n" +
                "\tModifiedDate datetime default getdate() not null,\n" +
                "\tCollectionAgencyID int,\n" +
                "\tWithdrawn bit default 0 not null,\n" +
                "\tAnonymous bit default 0 not null,\n" +
                "\tAltKey varchar(30) default 'ABC123' not null,\n" +
                "\tAltKeySearchID  VARCHAR(30),\n" +
                "\tBidSourceID tinyint not null,\n" +
                "\tMinYield decimal(6,3) not null,\n" +
                "\tInvestmentOrderID int\n" +
                ")").execute();

Using H2 and HikariCP - Table Not Found

1 Answers1