Running a script to create tables with HSQLDB

Question

I use hsqldb to run my unit tests that need a database access.

For the moment, when I want to create a table for a specific test, I have the following code:

private void createTable() {
    PreparedStatement ps;
    try {
        ps = getConnection().prepareStatement("CREATE TABLE T_DATE (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP)");
        ps.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The getConnection() method retrieve a DataSource defined in a Spring context:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
    <property name="url" value="jdbc:hsqldb:mem:memoryDB"/>
    <property name="username" value="SA"/>
    <property name="password" value=""/>
</bean>

Now, I want to create my table from a SQL script (of course, this script will contain more than one table creation):

CREATE TABLE T_DATE_FOO (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP);
CREATE TABLE T_DATE_BAR (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP);
...

I've seen in the HSQLDB documentation that I can ask him to run a script at the startup. However, it does not meet my requirements, as I want to run a script at the runtime.

Of course, I can read the file myself, and for every SQL statement, I run a ps.executeUpdate() command, but I don't want to use this kind of solution (except if there are no other solution).

Any idea?

Answer 1

You might give org.hsqldb.util.SqlFile a try. This class seems to be a perfect match for your problem.