Nikolas Charalambidis Nikolas Charalambidis - 3 months ago 17
Java Question

Tomcat deployment of application with HSQLDB database giving JDBCConnectionException

I'm having a struggle with my first attempt with little application based on Spring, Hibernate on HSQLDB and JSF, finally deployed with Tomcat. I face two problems now.

First of all I tried to run Java Application on console based on

main (String args[])
method getting
customerBo
bean from
spring-module.xml
and insert/delete from embedded HSQLDB with Hibernate. Works like a charm.

1. Two configuration locations

Another step was a JSF page printing out a sample bean content. Working as well. However I had a struggle with resources and configuration:


  • resources: Here is all my configuration for database.

    src/main/resources
    \____ config
    \____ database
    \____ database.properties
    \____ spring/beans
    \____ data-source.xml
    \____ hibernate-session-factory.xml
    \____ spring-module.xml ... for Java Console Application

  • webapp: Here is all related to JSF including
    WEB-INF
    folder` related to web-page alike application.

    src/main/webapp
    \___ WEB-INF
    \____ applicationContext.xml ... for web application
    \____ faces-config.xml
    \____ web.xml
    \___ default.xhtml



How to make them easily communicate with each other? If web application starts from applicationContext in webapp, it needs to work with databse saved in resources. So it forces me to prefix all imports etc. with
classpath
such as.

<import resource="classpath:/spring-module.xml"/>
... or ...
<property name="location">
<value>classpath:/config/database/database.properties</value>
</property>


Java Console application still works well with these decors. Would you suggest me a better way? I import to
applicationContext.xml
all the database-stuff stored int
spring-module.xml
:

<beans xmlns=....>
<import resource="classpath:/spring-module.xml"/>
<import resource="classpath:/bean.xml"/>

<bean id="customerBo" class="nch.spring.customer.bo.impl.CustomerBoImpl"></bean>
</beans>


This is the URL I use to connect to HSQLDB

jdbc.url=jdbc:hsqldb:database/customers


2. HSQLDB on Tomcat

I deploy on Tomcat externally, not in IDE. I run
localhost:8080
.

After injecting all beans (working well, because I tested it on console), I recieved an error on Tomcat. Here is the shortened version with the first lines of causing:


org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection

Caused by: org.hibernate.exception.JDBCConnectionException: Could not open connection
Caused by: java.sql.SQLException: No suitable driver found for jdbc:hsqldb:database/customers


My database location related to project. It's included in the WAR:

project
\____ src/main/java
\____ src/main/resources
\____ src/main/webapp
\____ database
\____ customers
\____ customers.script
\____ customers.lck
\____ customers.properties


And my pom.xml for HSQLDB:

<!-- HSQLDB -->
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.3.2</version>
</dependency>


Why I cannot print out the database content with Spring and Hibernate on JSF deployed on Tomcat, but I can print it out on console? Is there an another way to embed a database? Does Tomcat support HSQLDB at all? I have tried to work first with MySQL, but it had been so cumbersome for me.




Shortly:


  • HSQLDB works with Spring and Hibernate well, proved on Java Console Application.

  • JSF pages working as well, able to print out the content of any bean.



My application refuses to communicate with database since deployed on Tomcat. Here is my full source code on GitHub.

Answer

The core of the issue is that you're using HSQLDB with a database file, that is actually a resource of your project (i.e. inside its classpath). This means that the file will be embedded inside your WAR, and you will not be able to update it (as contents inside the WAR file will be read-only).

Your connection string for HSQLDB is:

jdbc.url=jdbc:hsqldb:database/customers

When ran inside your IDE, you need to be aware that it does not package your application in a JAR but as an exploded directory. So when you run it as a console application, it will access the database file and update it just fine. However, when packaged in a WAR, it won't find it.

If your intent is to do read-only on the database, you could configure HSQLDB with a Resource Database URL of the form

jdbc:hsqldb:res:/database/customers

res: stored in a Java resource, such as a Jar and always read-only.

This will load a database from the resource located in /database/customers of the classpath of your application.

However, if you want to update it, then you have to use another way. A typical way is to use a Server Database URL, where the database is hosted on a server, possibly localhost for testing purposes.

Comments