HSQLDB Table not found in statement with Spring 3 jUnit test

Question

I have two jUnit test classes, one for testing my ItemService class, the other for testing my LocationService class. When I run an ItemService test, it passes. When I run a LocationService test, it fails with:

Caused by: java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM locations] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)

I have a schema file being loaded into the database, and the first table it creates is the one ItemService uses. Maybe the locations table that my LocationService test uses does not get created, even though it's in the same schema file?

This is in my test-context.xml file:

<jdbc:embedded-database
    id="myTestDB"
    type="HSQL">
    <jdbc:script
        location="classpath:/test-ddl.sql" />
    <jdbc:script
        location="classpath:/test-data.sql" />
</jdbc:embedded-database>

<bean
    id="dataSourceTest"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property
        name="driverClassName"
        value="org.hsqldb.jdbcDriver" />
    <property
        name="url"
        value="jdbc:hsqldb:mem:myTestDB" />
    <property
        name="username"
        value="sa" />
    <property
        name="password"
        value="" />
</bean>

I don't understand why one test passes with no errors thrown about the items table being missing, while another test fails because locations doesn't exist in the database. I saw other posts about using HSQLDB and Hibernate, but I'm not using Hibernate. These are the tables I'm creating in the test-ddl.sql file:

CREATE MEMORY TABLE "ITEMS" (
    "ID" INTEGER NOT NULL IDENTITY,
    "NAME" VARCHAR(50) NOT NULL,
    "LOCATION_ID" INTEGER NOT NULL,
    "ITEM_TYPE_ID" INTEGER NOT NULL
);

CREATE MEMORY TABLE "ITEM_TYPES" (
    "ID" INTEGER NOT NULL IDENTITY,
    "NAME" VARCHAR(50) NOT NULL,
    "ICON_CLASS" VARCHAR(50)
);

CREATE CACHED TABLE "LOCATIONS" (
    "ID" INTEGER NOT NULL IDENTITY,
    "NAME" VARCHAR(50) NOT NULL,
    "PHOTO" LONGVARBINARY,
    "PHOTO_CONTENT_TYPE" VARCHAR(60),
    "PHOTO_WIDTH" INTEGER,
    "PHOTO_HEIGHT" INTEGER
);

I also set some UNIQUE and FOREIGN KEY constraints in the schema after the table creation statements, but surely it would tell me if it couldn't create something. I'm using the HSQLDB 1.8.1.3 JAR. Why can I not query locations but I can query items?

Edit: thought based on this question there might be a case-sensitivity issue, but even trying to SELECT from the LOCATIONS table fails:

Caused by: java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM LOCATIONS]

Edit: my magical working ItemService test:

@ContextConfiguration("/test-context.xml")
@RunWith(SpringJUnit4ClassRunner.class)
@TransactionConfiguration(transactionManager = "transactionManager")
public class ItemServiceTest {
    private EmbeddedDatabase _db;
    private ItemService _svc;

    @Before
    public void setUp() throws Exception {
        _db = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL)
            .setName("myTestDB").build();
        assertThat(_db, is(notNullValue()));
        _svc = new ItemService();
        _svc.setDataSource(_db);
    }

    @After
    public void tearDown() throws Exception {
        _db.shutdown();
    }

    @Test
    public void testGetCount() {
        assertThat(_svc.getCount(), is(not(0)));
    }
}

My failing LocationService test is the exact same, but with LocationService instead of ItemService. The getCount() method shows up in both LocationService and in ItemService:

public int getCount() {
    String sql = "SELECT COUNT(*) FROM " + TABLE_NAME;
    return _jdbcTmpl.queryForInt(sql, (Map<String, Object>)null);
}

The TABLE_NAME varies between ItemService:

public static final String TABLE_NAME = "ITEMS";

and LocationService:

public static final String TABLE_NAME = "LOCATIONS";

Both ItemService and LocationService have the following:

private NamedParameterJdbcTemplate _jdbcTmpl;

@Resource(name = "dataSource")
public void setDataSource(DataSource dataSource) {
    _jdbcTmpl = new NamedParameterJdbcTemplate(dataSource);
}

Edit: new twist to the problem. In my LocationService test, I had some other test methods that weren't fleshed out yet, e.g.

@Test
public void testFind() {
    fail("Not yet implemented");
}

While in my ItemService test, the only @Test method was the testGetCount that passes. When I add the above testFind to the ItemService test class, suddenly its testGetCount fails just like in my LocationService test class:

Caused by: java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM ITEMS]

Edit: stepping through with the debugger when I'm not using static @BeforeClass/@AfterClass methods (i.e., when the test couldn't find the table), I noticed the following console output:

Apr 10, 2012 10:22:44 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase INFO: Creating embedded database 'myTestDB' Apr 10, 2012 10:22:44 AM org.springframework.jdbc.datasource.init.ResourceDatabasePopulator executeSqlScript INFO: Executing SQL script from class path resource [test-ddl.sql] Apr 10, 2012 10:22:44 AM org.springframework.jdbc.datasource.init.ResourceDatabasePopulator executeSqlScript INFO: Done executing SQL script from class path resource [test-ddl.sql] in 30 ms. Apr 10, 2012 10:22:44 AM org.springframework.jdbc.datasource.init.ResourceDatabasePopulator executeSqlScript INFO: Executing SQL script from class path resource [test-data.sql] Apr 10, 2012 10:22:44 AM org.springframework.jdbc.datasource.init.ResourceDatabasePopulator executeSqlScript INFO: Done executing SQL script from class path resource [test-data.sql] in 46 ms. Apr 10, 2012 10:22:55 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase INFO: Creating embedded database 'myTestDB' Apr 10, 2012 10:23:08 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase INFO: Creating embedded database 'myTestDB'

So it looks like it sets up the database and runs my create-tables and populate-tables scripts initially, then any time after that when I reinitialize _db, the scripts don't get run again. So the tables don't exist for subsequent test methods. So if I could find a way to force Spring to re-run those scripts each time, I could go back to using @Before and @After with instance methods for my setup and teardown methods.

Answer 1

addScript was the answer. I removed the initialization scripts from my test-context.xml, so my jdbc:embedded-database tag looks like:

<jdbc:embedded-database
    id="myTestDB"
    type="HSQL" />

In my setUp() method in my test class, I changed my _db initialization to:

_db = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL)
    .setName("myTestDB").addScript("test-ddl.sql")
    .addScript("test-data.sql").build();

My setUp and tearDown methods remain instance methods, annotated with @Before and @After, respectively. Using the debugger, I see console output for each test saying my two initialization SQL scripts were run, so the tables exist for each test.

Answer 2

@BeforeClass and static! I changed my test classes to have the following setup and teardown methods:

private static EmbeddedDatabase _db;
private static LocationService _svc;

@BeforeClass
public static void setUp() throws Exception {
    _db = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL)
        .setName("myTestDB").build();
    assertThat(_db, is(notNullValue()));
    _svc = new LocationService();
    _svc.setDataSource(_db);
}

@AfterClass
public static void tearDown() throws Exception {
    _db.shutdown();
}

Now both my ItemService and LocationService tests are passing, even with other tests in the class. The "table not found in statement" error I was getting before seems a little bizarre in response to before-every-test setup versus before-all-tests-in-class setup...