Will url using file: always start Hsqldb?

  • Lucille Wilson

    Lucille Wilson - 2013-01-24

    I want to have Hqldb run in Server mode (i.e. in it's own jvm).  I'd like to use cached tables, so I need the database to reside on the file system. 

    If I use jdbc:hsqldb:file:/clientmessagedispatcherdatabase/rrcacheserverdb in the url, is it always true that I would be inadvertently trying to start an other Hsqldb engine?

    For example if I have a bat file that says:
    java -Xms750m -Xmx1550m -cp ./hsqldb.jar org.hsqldb.Server -database.0 file:/clientmessagedispatcherdatabase/rrcacheserverdb -dbname.0 rrcacheserverdb

    Then in my application (which runs on tomcat, uses hibernate and c3p0),
    I have a singleton which controls the c3p0 connection pool:

    import java.util.HashMap;
    import javax.sql.DataSource;
    import com.mchange.v2.c3p0.DataSources;

    * This class is a singleton containing the connection pool information for the JDBC calls to the cache database.
    * This class contains the parameters to set up the connection pool for JDBC calls.
    * This class is not used to contain the connection pool parameters for hibernate calls.
    * @author rsi
    public class DataSourceConnectionPoolSingleton {
    DataSource pooled = null;
    private static DataSourceConnectionPoolSingleton _instance = null;

    protected DataSourceConnectionPoolSingleton() {

    * @return The unique instance of this class.
    public static DataSourceConnectionPoolSingleton instance() {
    if(null == _instance) {
    _instance = new DataSourceConnectionPoolSingleton();
    return _instance;
    private void init()  {
    HashMap c3p0overrides = new HashMap();
    c3p0overrides.put("maxStatements", "3200");   
    c3p0overrides.put("minPoolSize", new Integer(15));
    c3p0overrides.put("maxPoolSize", new Integer(100));
    c3p0overrides.put("acquireIncrement", new Integer(5));
    c3p0overrides.put("idleConnectionTestPeriod", new Integer(6000));
    c3p0overrides.put("maxIdleTime", new Integer(6000));

    try {
    DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:file:/clientmessagedispatcherdatabase/rrcacheserverdb",
    pooled = DataSources.pooledDataSource( unpooled, c3p0overrides );
    } catch(Exception e) {
    WriteLoggerUtilities.writeLogMessage("rrServerDatabase.DataSourceConnectionPoolSingleton.init()" + e.getMessage(), LoggerMessage.INFO_LEVEL);

    public DataSource getPooledDataSource() {
    return pooled;


    It seems as if the connection pool is hanging or gives the following error:

    com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector - APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

    I believe this error is occuring because the url of
    DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:file:/clientmessagedispatcherdatabase/rrcacheserverdb",
    is starting a second instance of the Hsqldb Server.

    Am I correct?

    If so, can you please update the documentation to mention this?

  • Fred Toussi

    Fred Toussi - 2013-01-24

    The documentation already covers this.

    >> I want to have Hqldb run in Server mode (i.e. in it's own jvm).
    You are starting the server correctly.
    Just look at the first chapter and the chapter on servers (Listeners) . After starting the server, the connection URL is not
    jdbc:hsqldb:file:etc, but (for your server):


    >>If I use jdbc:hsqldb:file:/clientmessagedispatcherdatabase/rrcacheserverdb in the url, is it always true that I would be inadvertently trying to start an other Hsqldb engine?

    If you use that jdbc:hsqldb:file:ect URL, you are trying to establish a direct file connection to the database that is already opened by the server. This is not what you want, and  will not work anyway.

  • Lucille Wilson

    Lucille Wilson - 2013-01-25

    Dear Fred, Thank you for clarifying.  I'd like to bring up a pattern, though.  When the url is for a memory database, in all instances (meaning the bat file and the DataSourceConnectionPoolSingleton and the hiber config file), the value of the url will have mem: in it, and multiple instances of the memory database will not be instantiated.  However, for file: the Hsqldb engine behaves differently, and multiple instances will be opened.

    To be specific, if I open a memory database in Server mode, my bat file will say:
    java -Xms750m -Xmx1550m -cp ./hsqldb.jar org.hsqldb.Server -database.0 mem:rrcacheserverdb -dbname.0 rrcacheserverdb

    But my code snippet of DataSourceConnectionPoolSingleton, the url will have:
    DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:mem:rrcacheserverdb","sa","");

    the format will say mem: in both places.  I used the principal of analogy when I tried to use the same url format in all places, and I discovered that for mem: only one instance of the database was created, but for file, two instances were started.

    I wrote a blog post about this, in order to try to keep this all straight in my head. Would you care to look and see if I got this right?
    It is http://wilsonsoftware.blogspot.com/2013/01/using-hsqldb-in-server-mode-with-file.html#!/2013/01/using-hsqldb-in-server-mode-with-file.html

    Thank you.

  • Fred Toussi

    Fred Toussi - 2013-01-25

    Your blog is correct regarding file databases.

    But what you say here about mem: databases is not correct.  There is no difference on the client side when the server is serving a file or mem database.

    bat file will say: java -Xms750m -Xmx1550m -cp ./hsqldb.jar org.hsqldb.Server -database.0 mem:rrcacheserverdb -dbname.0 rrcacheserverdb

    And the url will have: DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:hsql://localhost/rrcacheserverdb","sa","");

  • Lucille Wilson

    Lucille Wilson - 2013-01-25

    Dear Fred,
    You are right about the mem.
    Let's see if I've corrected myself.
    If you are running a memory database in process (meaning in the same jvm as your application) then do not use a bat file, and the url is going to use mem: nomenclature.
    DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:mem:rrcacheserverdb","sa", "");

    If you are using hslqdb in its own jvm for either a memory database in server mode, or a file database in server mode, then use the bat file to start up the hsqldb engine.  The bat file will either use the mem: or file: nomenclature.

    Then refer to the database by this nomenclature:
    DataSource unpooled = DataSources.unpooledDataSource("jdbc:hsqldb:hsql://localhost/rrcacheserverdb","sa","");

  • Fred Toussi

    Fred Toussi - 2013-01-25

    Yes, this is correct.


Log in to post a comment.