How to perform Hsqldb SET Operation dynamically in Spring

Question

We are using HSQL Database Engine 2.3.2 with Spring 4.1.0.RELEASE while my spring configuration is as following:

Here is applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd        
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">

    <bean class="com.chorke.spring.bootstarp.HyperSqlDbServer" id="hsqldb" init-method="start">
        <constructor-arg>
            <props>
                <prop key="server.port">9002</prop>
                <prop key="server.dbname.0">chorke</prop>
                <prop key="server.remote_open">true</prop>
                <prop key="server.database.0">file:~/.hsqldb/chorke/data;sql.syntax_mys=true;user=admin;password=pa55word</prop>
                <prop key="hsqldb.default_table_type">text</prop>
                <prop key="hsqldb.reconfig_logging">false</prop>
            </props>
        </constructor-arg>
    </bean>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
        <property name="url" value="jdbc:hsqldb:hsql://localhost:9002/chorke" />
        <property name="username" value="admin" />
        <property name="password" value="pa55word" />
    </bean>

    <jdbc:initialize-database  data-source="dataSource">
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:data.sql"/>
    </jdbc:initialize-database>

</beans>

Here is com.chorke.spring.bootstarp.HyperSqlDbServer

package com.chorke.spring.bootstarp;

import java.io.IOException;
import java.util.Properties;

import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.hsqldb.server.ServerAcl.AclFormatException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.SmartLifecycle;

public class HyperSqlDbServer implements SmartLifecycle {
    private final Logger logger = LoggerFactory.getLogger(HyperSqlDbServer.class);
    private HsqlProperties properties;
    private Server server;
    private boolean running = false;

    public HyperSqlDbServer(Properties props) {
        properties = new HsqlProperties(props);
    }

    @Override
    public boolean isRunning() {
        if (server != null)
            server.checkRunning(running);
        return running;
    }

    @Override
    public void start() {
        if (server == null) {
            logger.info("Starting HSQL server...");
            server = new Server();
            try {
                server.setProperties(properties);
                server.start();
                running = true;
            } catch (AclFormatException afe) {
                logger.error("Error starting HSQL server.", afe);
            } catch (IOException e) {
                logger.error("Error starting HSQL server.", e);
            }
        }
    }

    @Override
    public void stop() {
        logger.info("Stopping HSQL server...");
        if (server != null) {
            server.stop();
            running = false;
        }
    }

    @Override
    public int getPhase() {
        return 0;
    }

    @Override
    public boolean isAutoStartup() {
        return true;
    }

    @Override
    public void stop(Runnable runnable) {
        stop();
        runnable.run();
    }
}

Here is schema.sql

CREATE TABLE IF NOT EXISTS t001i001(pf_id INTEGER,df_name VARCHAR(20));
--SET TABLE t001i001 SOURCE 't001i001.csv;fs=|;vs=.';

Here is data.sql

insert into t001i001(pf_id, df_name) values(1, 'Shefat Hossain');

Where I want to add some script to perform some SET operation dynamically as like as what was commented in schema.sql on second line. using spring.

---

Any way to perform SET operation logically/dynamically inside Spring Application Context?

Answer 1

Here is wrong assumption of me as As I declared the property at applicationContext.xml on hsqldb bean as below:

<prop key="hsqldb.default_table_type">text</prop>

It's suppose to me that it would be help us to defaulting database table engine TEXT instead of MEMORY. As the table default engine was MEMORY so the 2nd line of schema.sql not executing. after editing schema.sql it working fine, no problem on SET operation as following:

CREATE TEXT TABLE IF NOT EXISTS t001i001(pf_id INTEGER,df_name VARCHAR(20));
SET TABLE t001i001 SOURCE 't001i001.csv;fs=|;vs=.';