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](https://stackoverflow.com/questions/3121128/problems-with-escaping-table-and-field-names-in-derby-and-hsqldb) 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.