2

I saw this post : How to start HSQLDB in server mode from Spring boot application but can't manage to get it working.

My goal is to build a Spring boot app that

  • Start HSQLDB in server mode, with file persistance (to get it back when I restart my app)
  • Expose a public API that communicates with this hsqldb instance
  • Let me the possibility to connect to this hsqldb server remotely, for instance using the embedded Swing app contained into hsqldb.jar.

So I tried to reproduce the marked answer of the quoted post:

<?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">file:/tmp/mydb.db</prop>
        <prop key="server.dbname.0">mydb</prop><!--DB name for network connection-->
        <prop key="server.no_system_exit">true</prop>
        <prop key="server.port">9001</prop><!--default port is 9001 -->
        <prop key="server.remote_open">true</prop>
        </props></constructor-arg>
    </bean></property>
</bean>
</beans>

I understand I next could be able to connect to jdbc:hsqldb:hsql://localhost:9001/mydb, right?

Well, I specified all the parameters into my application.yml

spring:
  profiles:
    active: dev
  datasource:
    driver-class-name: org.hsqldb.jdbc.JDBCDriver 
    url: jdbc:hsqldb:hsql://localhost:9001/mydb
    username: sa
    password: 
  jpa:
    database-platform: org.hibernate.dialect.HSQLDialect
    show-sql: true
    hibernate:
      ddl-auto: update

and finally, developed the App part (Controller, Services and JPA layer).

I also included the xml part into the App

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

But, obviously, it doesn't work and gives me the following error:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
[32m :: Spring Boot :: [39m      [2m (v2.2.2.RELEASE)[0;39m

[2m2021-11-17 15:19:56.295[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mtest.MyApp                              [0;39m [2m:[0;39m Starting MyApp on iMac.local with PID 47627 (/Users/ludovic/workspace/test-hsqldb/target/classes started by ludovic in /Users/ludovic/workspace/test-hsqldb)
[2m2021-11-17 15:19:56.298[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mtest.MyApp                              [0;39m [2m:[0;39m The following profiles are active: dev
[2m2021-11-17 15:19:56.709[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Bootstrapping Spring Data JPA repositories in DEFAULT mode.
[2m2021-11-17 15:19:56.724[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Finished Spring Data repository scanning in 8ms. Found 0 JPA repository interfaces.
[2m2021-11-17 15:19:57.009[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.hibernate.jpa.internal.util.LogHelper [0;39m [2m:[0;39m HHH000204: Processing PersistenceUnitInfo [name: default]
[2m2021-11-17 15:19:57.057[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36morg.hibernate.Version                   [0;39m [2m:[0;39m HHH000412: Hibernate Core {5.4.9.Final}
[2m2021-11-17 15:19:57.166[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.hibernate.annotations.common.Version  [0;39m [2m:[0;39m HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
[2m2021-11-17 15:19:57.241[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mcom.zaxxer.hikari.HikariDataSource      [0;39m [2m:[0;39m HikariPool-1 - Starting...
[2m2021-11-17 15:19:57.285[0;39m [31mERROR[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mcom.zaxxer.hikari.pool.HikariPool       [0;39m [2m:[0;39m HikariPool-1 - Exception during pool initialization.

java.sql.SQLTransientConnectionException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCDriver$1.run(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
Caused by: org.hsqldb.HsqlException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.ClientConnection.openConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.initConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 2 common frames omitted
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[na:1.8.0_241]
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:606) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:555) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:451) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:228) ~[na:1.8.0_241]
    at org.hsqldb.server.HsqlSocketFactory.createSocket(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 5 common frames omitted

[2m2021-11-17 15:19:57.286[0;39m [33m WARN[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.e.j.e.i.JdbcEnvironmentInitiator    [0;39m [2m:[0;39m HHH000342: Could not obtain connection to query metadata : java.net.ConnectException: Connection refused (Connection refused)
[2m2021-11-17 15:19:57.296[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36morg.hibernate.dialect.Dialect           [0;39m [2m:[0;39m HHH000400: Using dialect: org.hibernate.dialect.HSQLDialect
[2m2021-11-17 15:19:57.439[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mcom.zaxxer.hikari.HikariDataSource      [0;39m [2m:[0;39m HikariPool-1 - Starting...
[2m2021-11-17 15:19:57.441[0;39m [31mERROR[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mcom.zaxxer.hikari.pool.HikariPool       [0;39m [2m:[0;39m HikariPool-1 - Exception during pool initialization.

java.sql.SQLTransientConnectionException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCDriver$1.run(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
Caused by: org.hsqldb.HsqlException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.ClientConnection.openConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.initConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 2 common frames omitted
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[na:1.8.0_241]
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:606) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:555) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:451) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:228) ~[na:1.8.0_241]
    at org.hsqldb.server.HsqlSocketFactory.createSocket(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 5 common frames omitted

[2m2021-11-17 15:19:57.442[0;39m [33m WARN[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m SQL Error: -1301, SQLState: 08001
[2m2021-11-17 15:19:57.442[0;39m [31mERROR[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m java.net.ConnectException: Connection refused (Connection refused)
[2m2021-11-17 15:19:57.445[0;39m [33m WARN[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36ms.c.a.AnnotationConfigApplicationContext[0;39m [2m:[0;39m Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
[2m2021-11-17 15:19:57.450[0;39m [32m INFO[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mConditionEvaluationReportLoggingListener[0;39m [2m:[0;39m 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
[2m2021-11-17 15:19:57.452[0;39m [31mERROR[0;39m [35m47627[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.s.boot.SpringApplication              [0;39m [2m:[0;39m Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]
    at test.MyApp.main(MyApp.java:11) [classes/:na]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    ... 15 common frames omitted
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:40) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:155) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:320) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1237) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
    ... 19 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.jdbc.JDBCDriver$1.run(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
Caused by: org.hsqldb.HsqlException: java.net.ConnectException: Connection refused (Connection refused)
    at org.hsqldb.ClientConnection.openConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.initConnection(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    at org.hsqldb.ClientConnection.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 2 common frames omitted
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[na:1.8.0_241]
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[na:1.8.0_241]
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:606) ~[na:1.8.0_241]
    at java.net.Socket.connect(Socket.java:555) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:451) ~[na:1.8.0_241]
    at java.net.Socket.<init>(Socket.java:228) ~[na:1.8.0_241]
    at org.hsqldb.server.HsqlSocketFactory.createSocket(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0]
    ... 5 common frames omitted

I'm starting thinking that my app is trying to connect before server is started, is it possible? is there a way to tell to Spring to firstly start the server and then, make its magic?

Thanks

Cheloute
  • 783
  • 2
  • 11
  • 27

2 Answers2

1

These are quite "unconditional goals" and I would not call that "correctly integrated", but there you go:

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.6</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.stackoverflow</groupId>
    <artifactId>hsqldb-runner</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId><!-- as compile(!) dependency -->
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId><!-- for the endpoints -->
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId><!-- for simple jdbc access -->
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId><!-- just for fun -->
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId><!-- TODO! -->
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

With friendly help of link, an additional @Component annotation and some Properties loading, we do that:

package com.stackoverflow.hsqldbrunner;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
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.beans.factory.annotation.Value;
import org.springframework.context.SmartLifecycle;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.stereotype.Component;

@Component
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(@Value("classpath:/hsqldb.properties") Resource serverProps) throws IOException {
        Properties props = PropertiesLoaderUtils.loadProperties(serverProps);
        if (props.elements().hasMoreElements() && logger.isDebugEnabled()) {
            StringWriter writer = new StringWriter();
            props.list(new PrintWriter(writer));
            logger.debug(writer.toString());
        }
        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();
    }
}

Main class/Config/Controller:

package com.stackoverflow.hsqldbrunner;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@SpringBootApplication
@RestController
public class HsqldbRunnerApplication {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        SpringApplication.run(HsqldbRunnerApplication.class, args);
    }

    @GetMapping("/query")
    public List<Map<String, Object>> query(@RequestParam String sql) {
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/execute")
    public void execute(@RequestParam String sql) {
        jdbcTemplate.execute(sql);
    }
}

then with this src/main/resources/hsqldb.properties (for the server):

server.database.0=file:C:/hsqldb/demobase 
server.dbname.0=demobase
#server.database.n
#server.dbname.n    
server.silent=false 
server.trace=false
server.daemon=false
server.remote_open=true

and this src/main/resources/application.properties (for the client):

spring.datasource.url=jdbc:hsqldb:hsql://localhost/demobase
spring.datasource.user=SA
spring.datasource.password=

..when we run:

mvn spring-boot:run

We get:

  • Start HSQLDB in server mode, with file persistence (to get it back when I restart my app)

see here:

2021-11-17 17:35:58.652  INFO 3352 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-11-17 17:35:58.652  INFO 3352 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 313 ms
2021-11-17 17:35:58.780  INFO 3352 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2021-11-17 17:35:58.787  INFO 3352 --- [  restartedMain] c.s.hsqldbrunner.HyperSqlDbServer        : Starting HSQL server...
[Server@71142d78]: [Thread[restartedMain,5,main]]: start() entered
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: run() entered
[Server@71142d78]: Initiating startup sequence...
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.maxdatabases=10
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.remote_open=true
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.tls=false
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.daemon=false  
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.trace=false
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.database.0=file:C:/hsqldb/demobase 
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.restart_on_shutdown=false
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.no_system_exit=true
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.silent=false 
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.default_page=index.html
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.dbname.0=demobase
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.address=0.0.0.0
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: server.root=.
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: openServerSocket() entered
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: Got server socket: ServerSocket[addr=0.0.0.0/0.0.0.0,localport=9001]
[Server@71142d78]: Server socket opened successfully in 0 ms.
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: openServerSocket() exiting
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: openDatabases() entered
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: Opening database: [file:C:/hsqldb/demobase ]
[Server@71142d78]: Database [index=0, id=0, db=file:C:/hsqldb/demobase , alias=demobase] opened successfully in 0 ms.
[Server@71142d78]: [Thread[HSQLDB Server @71142d78,5,main]]: openDatabases() exiting
[Server@71142d78]: Startup sequence completed in 0 ms.
[Server@71142d78]: 2021-11-17 16:35:58.789 HSQLDB server 2.6.0 is online on port 9001
[Server@71142d78]: To close normally, connect and execute SHUTDOWN SQL
[Server@71142d78]: From command line, use [Ctrl]+[C] to abort abruptly
[Server@71142d78]: [Thread[restartedMain,5,main]]: start() exiting
2021-11-17 17:35:58.896  INFO 3352 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2021-11-17 17:35:58.900  INFO 3352 --- [  restartedMain] c.s.h.HsqldbRunnerApplication            : Started HsqldbRunnerApplication in 0.603 seconds (JVM running for 1330.867)
  • Expose a public API that communicates with this hsqldb instance:
  • /execute: executes (arbitrary) sql without expected result (jdbcTemplate.execute():void)
  • /query: to query (for a List<Map<String, Object>>)

e.g. let's take this sql snippet:

CREATE TABLE demo(id INT NOT NULL,title VARCHAR(50),submission_date DATE,PRIMARY KEY (id));

and issue it to our browser like:

http://localhost:8080/execute?sql=CREATE%20TABLE%20demo(id%20INT%20NOT%20NULL,title%20VARCHAR(50),submission_date%20DATE,PRIMARY%20KEY%20(id))%3B

then

INSERT INTO demo VALUES (1,'foo',now()),(2,'bar',now());

to:

http://localhost:8080/execute?sql=INSERT%20INTO%20demo%20VALUES%20(1%2C%27foo%27%2Cnow())%2C(2%2C%27bar%27%2Cnow())%3B

A query:

SELECT * FROM demo;

looks like:

http://localhost:8080/query?sql=SELECT%20*%20FROM%20demo%3B

and returns:

[{"ID":1,"TITLE":"foo","SUBMISSION_DATE":"2021-11-17"},{"ID":2,"TITLE":"bar","SUBMISSION_DATE":"2021-11-17"}]
  • Let me the possibility to connect to this hsqldb server remotely, for instance using the embedded Swing app contained into hsqldb.jar.

(Remote it has to be tested, but) looks from localhost like this:

HSQLDB Swing Client screenshot

.. AND we get (all) hsqldb server logging to our spring application.

All combined in github repo. Have fun, enjoy & DON'T USE THAT in production! ;)

more links:

xerx593
  • 12,237
  • 5
  • 33
  • 64
1

Even replacing the above server component with this:

@Bean(initMethod = "start", destroyMethod = "stop")
public Server hsqlServer(@Value("classpath:/hsqldb.properties") Resource props) throws IOException, AclFormatException {
    Server bean = new org.hsqldb.server.Server();
    bean.setProperties(PropertiesLoaderUtils.loadProperties(props));
    return bean;
}

,which is analogous to this xml configuration, shows NO ISSUES (works like charm).

BUT, adding this dependency:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

breaks everything! (Context doesn't load with:)

Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

As soon "fixing" this:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.HSQLDialect

We get Exception:

...Caused by: java.net.ConnectException: Connection refused: connect

..somewhere in HikariPool....

Solution/Workaround:

import java.io.IOException;
import javax.sql.DataSource;
import org.hsqldb.server.Server;
import org.hsqldb.server.ServerAcl.AclFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.DependsOn;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

@SpringBootApplication
@EnableConfigurationProperties
public class HsqldbRunnerApplication {

    public static void main(String[] args) {
        SpringApplication.run(HsqldbRunnerApplication.class, args);
    }

    @Bean(initMethod = "start", destroyMethod = "stop")
    public Server hsqlServer(@Value("classpath:/hsqldb.properties") Resource props) throws IOException, AclFormatException {
        Server bean = new Server();
        bean.setProperties(PropertiesLoaderUtils.loadProperties(props));
        return bean;
    }

    @Bean
    @DependsOn("hsqlServer") // This is important!!
    public DataSource getDataSource(
            @Autowired DataSourceProperties dsProps) {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(dsProps.getDriverClassName());
        dataSourceBuilder.url(dsProps.getUrl());
        dataSourceBuilder.username(dsProps.getUsername());
        dataSourceBuilder.password(dsProps.getPassword());
        return dataSourceBuilder.build();
    }

}

So, we need to:

  • define our own data source bean, and depend that (make it wait) on our "hsqlServer".

  • move Controller to new class(es) (which is not bad, and was only in place for "brevity"), otherwise we get circular dependencies/unset datasources.

xerx593
  • 12,237
  • 5
  • 33
  • 64
  • Ooook ! I missed the DependsOn on the getDataSource Bean :P Thanks for the explanation, i wasn't actually understanding why it was working without the JPA layer, but not once the JPA layer developed. Crystal clear now, thank you very much! – Cheloute Nov 18 '21 at 08:47
  • hat must i define for the hsqldb.properties file? – Tony B Aug 09 '22 at 21:04
  • [actually used/sample](https://github.com/xerx593/soq70006118/blob/main/src/main/resources/hsqldb.properties) ..for more details: [hsql documentation](http://hsqldb.org/doc/2.0/guide/index.html), @TonyB ;) – xerx593 Aug 10 '22 at 05:14