Using separate persistence.xml files for production and test with Spring 3.1

Question

OK, sorry, I looked for answers to this for hours, but it took me entering the whole question for StackOverflow to bubble up the link I was looking for. You can read a lot of relevant information here.

---

I have a Spring project created with Spring Roo to use Hibernate and MySQL. However, for testing, I want to use HSQLDB in memory because the Roo integration tests delete data with IDs (primary keys) 0 through 10 (rather than deleting the data using database assigned ids for data they've already created), which means it deletes data that's already in the database, which in my case causes a constraint violation before it gets around to rolling back the transaction.

This is a little extra difficult because I'm switching entire database providers which means different Hibernate dialects as well as different DDL settings (validate in production, create-drop in test). But it is not working like I expect and I'm stumped as to why.

If you know why it's not working, please say so, even if you don't have a solution.

This being a Roo project, I'm using Maven of course. So first thing I tried was having a test-specific src/test/resources/META-INF/persistence.xml file and likewise a test-specific src/test/resources/META-INF/spring/database.properties file. That didn't work, as when I ran mvn test everything broke, with the relevant message being

Conflicting persistence unit definitions for name 'persistenceUnit'

Why was mvn test still picking up the non-test resources?

So then I renamed src/test/resources/META-INF/spring to spring-test and copied applicationContext.xml into it. I changed the context configuration in the test classes to

@ContextConfiguration(locations = "classpath:/META-INF/spring-test/applicationContext*.xml")

Completing (or so I thought) the separation, I made a couple of edits to spring-test/applicationContext.xml:

Changed the path for properties files:

<context:property-placeholder location="classpath*:META-INF/spring/*.properties"/>

to

<context:property-placeholder location="classpath*:META-INF/spring-test/*.properties"/>

Changed the name of the persistence unit:

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="persistenceUnitName" value="persistenceUnit"/>
    <property name="dataSource" ref="dataSource"/>
</bean>

to

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="persistenceUnitName" value="testPersistenceUnit"/>
    <property name="dataSource" ref="dataSource"/>
</bean>

And I made the corresponding change in persistence unit name to src/test/resources/META-INF/persistence.xml

Well, OK, now there's no conflict, but somehow Hibernate has lost the entity mappings (e.g. for the Product entity) and I get:

org.springframework.dao.InvalidDataAccessApiUsageException:
org.hibernate.hql.ast.QuerySyntaxException: Product is not mapped [SELECT o FROM Product o]; 

Why has Spring/Hibernate lost the entity mappings in this configuration?

So next thing I tried was merging the two persistence.xml files so that one file under src/main/resources/META-INF includes both persistence units.

That Works!!??

I think it's ugly, because now I have test configuration in my production code, but it's what I'm getting by with.

What is a better way?

It is my understanding that properties are not available inside persistence.xml the way they are inside the Spring XML files. So I don't think I can do what I want with just a test-specific properties file.

Ideally I'd run tests using all the configuration under src/main/resources except for what is specifically overridden in src/test/resources. Is there a way to accomplish this?

Thanks for any insight you can provide!

Liquidity

Answer 1

In my work, I used to configure the persistence.xml without the information of database connection. The database connection is defined by Spring's context configuration. In Spring framework, there are several way to change properties of an object:

1. PropertyPlaceholderConfigurer - Using different property file to override the value of database connection or dialect of ORM. You may use resource filter to generate different values of a single property file if you are using Maven
2. Bean definition inheritance - Using another context configuration to "override" the default configuration.

Following code is an excerpt from my context of application:

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
    p:location="classpath:frontend.properties" p:ignore-resource-not-found="true"
    p:systemPropertiesModeName="SYSTEM_PROPERTIES_MODE_OVERRIDE"
/>

<util:properties id="jpaProperties">
    <prop key="hibernate.dialect">${hibernate.dialect}</prop>
</util:properties>

<!-- Global entity manager factory(may not be overrided) -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
    p:dataSource-ref="dataSource" p:jpaProperties-ref="jpaProperties"
>
    <property name="JpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
</bean>
<!-- :~) -->

<!-- Data Source -->
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"
    p:driverClass="${database.driverClass}" p:jdbcUrl="${database.url}"
    p:username="${database.username}" p:password="${database.password}"
    p:partitionCount="${database_conn.pooling.partition_count:2}"
    p:maxConnectionsPerPartition="64" p:minConnectionsPerPartition="${database_conn.pooling.min_connections:4}"
    p:acquireIncrement="4" p:statementsCacheSize="64"
    p:connectionTimeoutInMs="1800000" p:IdleConnectionTestPeriodInMinutes="420"
/>
<!-- :~) -->

Here is the configuration for testing:

<!-- import configuration of application -->
<import resource="classpath:database.xml" />

<!--
  - I override some of settings of connection pooling while testing
  -->
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"
    p:driverClass="${database.driverClass}" p:jdbcUrl="${database.url}"
    p:username="${database.username}" p:password="${database.password}"
    p:maxConnectionsPerPartition="8" p:minConnectionsPerPartition="2"
    p:acquireIncrement="2" p:statementsCacheSize="32"
/>

When I am running test, I setup the system properties in Maven surefire to configure different database. As following sample:

<plugin>
     <groupId>org.apache.maven.plugins</groupId>
     <artifactId>maven-surefire-plugin</artifactId>
     <version>2.8.1</version>
     <configuration>
         <systemPropertyVariables>
             <!-- Using HSQLDB as test database system -->
             <database.driverClass>org.hsqldb.jdbc.JDBCDriver</database.driverClass>
             <database.url>${database.hsqldb.url}</database.url>
             <database.username>any</database.username>
             <database.password>any</database.password
             <hibernate.dialect>org.hibernate.dialect.HSQLDialect</hibernate.dialect>
             <!-- :~) -->
         </systemPropertyVariables>
     </configuration>
</plugin>

Espinosa

Thanks, Mike. I keep the database info in a properties file, too. What I didn't see was how to change the Hibernate SQL dialect via properties, as I configure Hibernate in the persistence.xml. – Liquidity