Cheloute
Reputation: 893
How to correctly integrate HSQLDB with Spring Boot?
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
Upvotes: 3
Views: 9133
Answers (2)
xerx593
Reputation: 13261
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.
Upvotes: 3
xerx593
Reputation: 13261
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 aList<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:
.. 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:
Upvotes: 2
Related Questions
- How to generate .data file with Spring Boot and HSQLDB
- Spring data JPA with HSQLDB: user lacks privilege or object not found
- Connecting to HSQLDB
- Postgresql to Hsql
- Configure HSQLDB with maven
- Using HSQLDB as a portable database with Spring, Hibernate
- Connect HSQLDB Server
- Spring boot fails to start with HSQLDB
- Unable to access HSQLDB database in spring-boot application using Hibernate
- how to connect to hsqldb which is instantiated through spring