Running a script to create tables with HSQLDB

Question

Hi all,

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

I had the same problem. I ended up splitting the text file by ; and executing each statement separately. It was OK because we had no inserts therefore no semicolons inside statements. I haven't found an easy way to run an SQL script at that time

Answer 2

since you're already using spring, you might want to use the SimpleJdbcUtils.executeSQLScript method which executes an SQL script where the statements are separated with semicolon. this class is in the spring-test module (JAR).

Answer 3

First of all, I do not know implications of this. I used it long time back it worked for me. The SQLExec class is from ant.jar, you can probably look into the ant source to create your own utility class,

SQLExec sqlExec=new SQLExec();
sqlExec.setUserid("user");
sqlExec.setPassword("passowrd");
sqlExec.setUrl("jdbc:mysql://localhost:3306/dbname");
sqlExec.setDriver("com.mysql.jdbc.Driver");
sqlExec.setProject(new Project());
sqlExec.setSrc(new File("c:/test.sql"));
sqlExec.execute();