title: HSQL file based database from script
body: <p>I have a question. I was playing around with JSF/ Spring/ Hibernate and HSQL.
  I have a small test application for this purpose and for one feature I wanted to implement I needed a possiblity to persist some data. I didn't wanted to have a full fledged database, so I choose HSQL. Starting from this tutorial (<a href="http://devcrumb.com/hibernate/hibernate-jpa-spring-and-hsqldb" rel="nofollow">http://devcrumb.com/hibernate/hibernate-jpa-spring-and-hsqldb</a>) I build my application. I had some problems with datatypes and I have chosen to build my database from a script.</p>
  
  <p>Like in the tuorial I had defined my datasource like this in my application.xml, except that I had changed the url property to use a file.</p>
  
  <pre><code>    &lt;bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
          &lt;property name="driverClassName"&gt;
              &lt;value&gt;org.hsqldb.jdbcDriver&lt;/value&gt;
          &lt;/property&gt;
          &lt;property name="url"&gt;
              &lt;value&gt;jdbc:hsqldb:hsql://localhost/testdb&lt;/value&gt;
          &lt;/property&gt;
          &lt;property name="username"&gt;
              &lt;value&gt;sa&lt;/value&gt;
          &lt;/property&gt;
          &lt;property name="password"&gt;
              &lt;value&gt;&lt;/value&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  
  &lt;bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
          &lt;property name="dataSource" ref="dataSource" /&gt;
          &lt;property name="persistenceUnitName" value="jpaData" /&gt;
          &lt;property name="jpaVendorAdapter"&gt;
              &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" /&gt;
          &lt;/property&gt;
          &lt;property name="jpaProperties"&gt;
              &lt;props&gt;
                  &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.HSQLDialect&lt;/prop&gt;
                  &lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;
                  &lt;prop key="hibernate.format_sql"&gt;false&lt;/prop&gt;
                  &lt;prop key="hibernate.hbm2ddl.auto"&gt;update&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>Now, I have changed the datasource to something like this:</p>
  
  <pre><code>&lt;jdbc:embedded-database id="dataSource" type="HSQL"&gt;
      &lt;jdbc:script location="classpath:embeddedDbStructure.sql" /&gt;
  &lt;/jdbc:embedded-database&gt;
  </code></pre>
  
  <p>Everything works fine so far.
  My problem is, that this database is deleted when the server is shut down. Is there a possibility to use both? A script as base for the structure of my database but the db itself is written to a file on my filesystem so that it is available after a server restart? I know there are propably a few solutions to achieve this via some code, but is there a possibility to configure it that way?</p>
  
  <p>Thanks in advance</p>