Spring Data JPA xml configuration : "Table not found" with create-drop

Question

I probably forgot something in my conf somewhere and I can't figure out what.

I have an @Entity :

@Entity
public class MyEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Long id;

I have a JpaRepository

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {

}

Here is my appContext.xml

<!-- CORE -->
<context:annotation-config />
<context:component-scan base-package="com.foo.project" />

<!-- WEB -->
<mvc:annotation-driven />
<mvc:resources mapping="/resources/**" location="/resources/" />

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  <property name="prefix" value="/WEB-INF/pages/" />
  <property name="suffix" value=".jsp" />
</bean>

<!-- DATA -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="org.h2.Driver" />
  <property name="url" value="jdbc:h2:mem:test" />
  <property name="username" value="sa" />
  <property name="password" value="" />
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="packagesToScan" value="com.foo.project.domain" />
  <property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
  </property>
  <property name="jpaProperties">
    <props>
      <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
      <prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</prop>
    </props>
  </property>
</bean>

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

<tx:annotation-driven />

<jpa:repositories base-package="com.foo.project.repository" />

And i have a unit test :

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:**/appContext.xml" })
public class MyEntityRepositoryTest {
    @Autowired private MyEntityRepository myEntityRepository;

When i run the test, i get an error :

ERROR: Table "MYENTITY" not found;

This is the full log :

sept. 04, 2014 8:19:47 AM org.hibernate.jpa.internal.util.LogHelper logPersistenceUnitInformation
INFO: HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
sept. 04, 2014 8:19:47 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.6.Final}
sept. 04, 2014 8:19:47 AM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
sept. 04, 2014 8:19:47 AM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
sept. 04, 2014 8:19:48 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
sept. 04, 2014 8:19:48 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
sept. 04, 2014 8:19:49 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
sept. 04, 2014 8:19:49 AM org.hibernate.validator.internal.util.Version <clinit>
INFO: HV000001: Hibernate Validator 5.1.2.Final
sept. 04, 2014 8:19:50 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000227: Running hbm2ddl schema export
sept. 04, 2014 8:19:50 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
sept. 04, 2014 8:19:52 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 42102, SQLState: 42S02
sept. 04, 2014 8:19:52 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Table "MYENTITY" not found; SQL statement:
select count(*) as col_0_0_ from MyEntity myEntity0_ [42102-181]

What did I miss?

Answer 1

You have the following configuration

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:test" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean

The problem with this configuration is that at startup Hibernate will open a connection and create the database and close the connection. At the moment the connection is closed the database is destroyed, as soon as you start your test the database is empty again.

There are 3 solutions to your problem

1. Use the Spring embedded database tag
2. Add DB_CLOSE_DELAY=-1 to your URL
3. Use a SingleConnectionDataSource

Embedded Database

Replace your datasource definition with a embedded-database tag from the jdbc namespace.

<jdbc:embedded-database id="dataSource" type="H2" />

Added DB_CLOSE_DELAY

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean

Use a SingleConnectionDataSource

<bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
    <constructor-arg index="0" value="org.h2.Driver" />
    <constructor-arg index="1" value="jdbc:h2:mem:test" />
    <constructor-arg index="2" value="sa" />
    <constructor-arg index="3" value="" />
    <constructor-arg index="4" value="true"/>
</bean>

This will open a single Connection and reuse it for all other calls, as it suppresses the closing of the connection.

Either of these options will keep the database alive. Option 1 and 2 are basically the same as the EmbeddedDatabaseBuilder uses a jdbc URL which includes the mentioned property.

Answer 2

(Just tested): And using the jdbc:embedded-database element, instead of a normal bean element works. You'll need the jdbc namespace

<xmlns:jdbc="http://www.springframework.org/schema/jdbc">
<xsi:schemaLocation="http://www.springframework.org/schema/jdbc           
                  http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">

<!-- ... -->

<jdbc:embedded-database id="dataSource" type="H2">
</jdbc:embedded-database>

<!-- DATA  ========= get rid of this =================
<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:test" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>
-->

I don't really have a great explanation (or any for that matter :-), I just know it works. I'm going to dig into this deeper when I get a chance.