Change hibernate.connection.url from within Spring

Question

I am using Hibernate in combination with Spring. As database I am currently using HSQL, which stores its data in a file (like SQLite). The path to the HSQL file is currently hard-coded in the persistence.xml. How can I access and change this value at runtime, so a user can load and save from/to an arbitrary HSQL file?

persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
                                 http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
             version="1.0">

    <persistence-unit name="something-unit">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <properties>
            <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver" />
            <property name="hibernate.connection.url" value="jdbc:hsqldb:file:~/something-db/somethingdb" />
            <property name="hibernate.connection.username" value="sa" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
            <property name="hibernate.hbm2ddl.auto" value="update" />
        </properties>
    </persistence-unit>

</persistence>

Spring applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:data="http://www.springframework.org/schema/data/jpa"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
               http://www.springframework.org/schema/beans
               http://www.springframework.org/schema/beans/spring-beans.xsd
               http://www.springframework.org/schema/context
               http://www.springframework.org/schema/context/spring-context-2.5.xsd
               http://www.springframework.org/schema/data/jpa
               http://www.springframework.org/schema/data/jpa/spring-jpa.xsd
               http://www.springframework.org/schema/tx
               http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">


    <!-- Database Setup -->

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="something-unit" />
    </bean>

    <data:repositories base-package="com.something.playlist"/>

    <!-- Transaction Setup -->

    <tx:annotation-driven/>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>

</beans>

Thanks for any hint!

Answer 1

You can specify a JNDI datasource and pass it to Hibernate. Or you can define your own plugin strategy for obtaining JDBC connections by implementing the interface org.hibernate.connection.ConnectionProvider

For more hints see: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html

Edit 2/16: There is an example on StackOverflow on creating a custom ConnectionProvider: How can I set Datasource when I'm creating Hibernate SessionFactory?

If you are going to change the data source on the fly, rather than at the startup, you will have to restart the Hibernate session factory. To do it correctly, you will have to make sure that no transactions are running in it at the time of the restart. Following question/answers would help you with that: Hibernate Sessionfactory restart | Spring

Answer 2

A commonly used strategy is to define all runtime configurations in one or several *.properties files and use spring's PropertyPlaceholderConfigurer to load the values and substitute the placeholder in applicationContext.xml, read more here: Best ways to deal with properties values in XML file in Spring, Maven and Eclipses.

app.properties:

# Dadabase connection settings:
hibernate.connection.driver_class=org.hsqldb.jdbcDriver
hibernate.connection.url=jdbc:hsqldb:file:~/something-db/somethingdb
hibernate.connection.username=sa
hibernate.connection.password=changeit
hibernate.dialect=org.hibernate.dialect.HSQLDialect
hbm2ddl.auto=update
... ...

applicationContext-dataStore.xml:

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  <property name="locations">
    <list>
      <!-- Default location inside war file -->
      <value>classpath:app.properties</value>
      <!-- Environment specific location, a fixed path on deployment server -->
      <value>file:///opt/my-app/conf/app.properties</value>
    </list>
  </property>
  <property name="ignoreResourceNotFound" value="true"/>
</bean>

... ...

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="${hibernate.connection.driver_class}" />
  <property name="url" value="${hibernate.connection.url}" />
  <property name="username" value="${hibernate.connection.username}" />
  <property name="password" value="${hibernate.connection.password}" />
</bean>

One problem here is the PropertyPlaceholderConfigurer doesn't parse persistence.xml, the solution is to move all hibernate configuration into Spring's applicationContext.xml, as it is not necessary to set them in persistence.xml. read more here: loading .properties in spring-context.xml and persistence.xml.

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
    version="1.0">
  <persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL"/>
</persistence>

applicationContext-datSource.xml:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="${hibernate.connection.driver_class}"/>
  <property name="url" value="${hibernate.connection.url}"/>
  <property name="username" value="${hibernate.connection.username}"/>
  <property name="password" value="${hibernate.connection.password}"/>
</bean>

... ...

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> 
  <property name="persistenceXmlLocation" value="classpath:./META-INF/persistence.xml"/>
  <property name="persistenceUnitName" value="JPAService"/>
  <property name="dataSource" ref="dataSource"/>

  <property name="jpaVendorAdapter"> 
      <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> 
          <property name="databasePlatform" value="${hibernate.dialect}"/> 
          <property name="showSql" value="true" /> 
          <property name="generateDdl" value="true"/>
      </bean> 
  </property>
  <property name="jpaProperties">
    <!-- set extra properties here, e.g. for Hibernate: -->
    <props>
      <prop key="hibernate.hbm2ddl.auto">${hbm2ddl.auto}</prop>
    </props>
  </property>
</bean>

Note that the web application need to be restarted every time you alter the configuration in /opt/my-app/conf/app.properties, in order to make changes take effect.

Hope this helps.