HSQLDB Table not found in statement with Spring 3 jUnit test
hsqldb, java, junit, spring, sql
Solution
`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.
Problem
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.