How to start HSQLDB in server mode from Spring boot application

Question

I have a Spring boot application, running with jpa data and hsqldb 2.3.3 (in Centos 7), the application runs fine but I would like to use HSQLDB database manager to check the data status, however it failed:

application.properties:

spring.datasource.url=jdbc:hsqldb:mem:testdb
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.database-platform=org.hibernate.dialect.HSQLDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create

Command to start HSQLDB:

java -cp /home/mycentos/.m2/repository/org/hsqldb/hsqldb/2.3.3/hsqldb-2.3.3.jar org.hsqldb.util.DatabaseManagerSwing

If I tried to log in with HSQLDB server mode, it pops Connection refused error

jdbc:hsqldb:hsql://localhost/testdb

If I tried to log in in-memory db, I can log in but no table and data showing up

jdbc:hsqldb:hsql:testdb

Question:

1. How to make it works?
2. Do I have to refer to the hsqldb.jar from tomcat deployment folder because that is the one using by the application?
3. Any configuration difference to configure hsqldb in server mode or in-memory mode from Spring application?
4. Can any method make in-memory mode working in such situation (to check data by db created Spring boot)?

Answer 1

To access the HSQL DB created by Spring boot app, you have to start HSQL server. For example, create a XML configuration file hsql_cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    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.xsd">
<bean id="hqlServer" class="org.hsqldb.server.Server" init-method="start" destroy-method="stop">
    <property name="properties"><bean class="org.hsqldb.persist.HsqlProperties">
        <constructor-arg><props>
        <prop key="server.database.0">mem:testdb</prop>
        <prop key="server.dbname.0">testdb</prop><!--DB name for network connection-->
        <prop key="server.no_system_exit">true</prop>
        <prop key="server.port">9001</prop><!--default port is 9001 -->
        </props></constructor-arg>
    </bean></property>
</bean>
</beans>

Here is a example to import the XML configuration in main application class.

@SpringBootApplication
@ImportResource(value="classpath:/package/hsql_cfg.xml")
public class MyApplication {
}

The HSQL server will start with Spring boot app. Other applications could connect to the HSQL server using JDBC url

jdbc:hsqldb:hsql://ip_address:port/testdb

Of course, hsqldb.jar is required for loading JDBC driver class.

Answer 2

Just to add to beckyang's answer, here is my approach.

Includes a hack to redirect logs to slf4j.

Includes specifying a corresponding datasource.

    import org.hsqldb.jdbc.JDBCDataSource;
    import org.hsqldb.server.Server;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;

    import javax.sql.DataSource;
    import java.io.ByteArrayOutputStream;
    import java.io.PrintWriter;

    @Configuration
    public class DataSourceConfiguration {

        private final Logger log = LoggerFactory.getLogger(getClass());

        @Bean(initMethod = "start", destroyMethod = "stop")
        @ConfigurationProperties//(prefix = "alarms.idempotent.server")
        public Server idempotentServer(@Value("${alarms.idempotent.server.path}") String path, @Value("${alarms.idempotent.port}") int port, @Value("${alarms.idempotent.name}") String name) {

            Server server = new Server();
            server.setDatabaseName(0, name);
            server.setDatabasePath(0, path);
            server.setPort(port);
            server.setLogWriter(slf4jPrintWriter());
            server.setErrWriter(slf4jPrintWriter());

            return server;

        }

        @Bean("idempotentDataSource")
        @Primary
        @ConfigurationProperties
        public DataSource idempotentDataSource(@Value("${alarms.idempotent.datasource.url}") String urlNoPath, @Value("${alarms.idempotent.name}") String name) {
            JDBCDataSource jdbcDataSource = new JDBCDataSource();
            String url = urlNoPath;
            if (!url.endsWith("/")) {
                url += "/";
            }
            url += name;
            jdbcDataSource.setUrl(url);
            jdbcDataSource.setUser("sa");
            return jdbcDataSource;
        }

        private PrintWriter slf4jPrintWriter() {
            PrintWriter printWriter = new PrintWriter(new ByteArrayOutputStream()) {
                @Override
                public void println(final String x) {
                    log.debug(x);
                }
            };
            return printWriter;
        }

    }