(J)Unit testing with in-memory DBs & JPA on Spring FWK

I am aware that this issue has been addressed by many in the community.
I'm not trying to convince why to perform unit testing against a DB, I'm just trying to explain how to do it.

The reasons of why to do it are many, but to name a few, you could say that:
A DB to use in production still doesn't exist or its structure is missing, In-Memory DBs
require no maintenance, you're testing your code against a real DB, etc...

So you are using the Spring FWK and you decided using JPA in your Data Access Layer,
and you write your code for your DAO (or Service) and your sophisticated queries,
right after you wrote the test class for that DAO of course... and now you want to run the unit test.
On runtime your DAO uses an EntityManager, which is initialized with your persistence unit
to perform actions against a DB.

But you want to make sure your code runs and passes the test before you run the build, and test
the entire application against the DB that will be used in production.

To test this DAO, obviously we need a DB to run it against.
If you still don't have a DB that has been created/configured or you just want to get going,
without having to install a new DB just for the sake of testing - In-Memory DB can offer a great solution.

Two In-Memory DBs which I have experience with are HSQLDB & H2 DB.
My preference out of experience is to use H2 DB, as it offers some functionality that HSQLDB doesn't, as well as a very comfortable console to browse through the DB you created.

The "recipe" for using In-Memory DB is as follows:
1. Download the In-Memory DB jars.

2. Include them in your class-path. (If you're running in Eclipse or intelliJ make sure the jars are there).

3. Under the root "tests" package or where your Test class is, make sure to create the "test/META-INF" directory and place the "persistence.xml" file in it.

4. Configure the persistence.xml file to use the In-Memory DB.
    For instance, if you're using Hibernate, you can configure it as follows:

<persistence>
.....
<persistence-unit name="my-unit"....>
       <provider>org.hibernate.ejb.HibernatePersistence</provider>
       <class>org.mycompany.model.MyEntity</class>
       .....

<properties>
   <!-- You have to set this value to either create-drop/create/update -->

            <property name="hibernate.hbm2ddl.auto" value="create" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
            <property name="hibernate.connection.driver_class" value="org.h2.Driver" />
            <property name="hibernate.connection.url" value="jdbc:h2:~/testdb" />
            <property name="hibernate.connection.username" value="sa" />
            <property name="hibernate.connection.password" value="" />
        </properties>
</persistence-unit>
</persistence>  

5. Your unit test class should be annotated with the following:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"/application-context.xml"})

public class MyDAOTest {

...
}

From now on, every time you run your unit test it will run against an In-Memory DB.
Something important to note is that I faced a problem when I used HSQLDB,
and had my entities direct to a schema name different than the default,
that was a schema called "conf" where configuration related data was planned to be kept.
Eventually the test failed.

This is because Hibernate fails to create those schemas.
See this link for more details: https://hibernate.onjira.com/browse/HHH-1853.
What you should do, is create those DB Schemas before the Unit Test executes.

In H2 DB there's a nice feature that lets you run a query or a SQL file when the Spring container boots.
In that file (create.sql) you can write a line to initialize your DB and create the needed Schema for instance:

CREATE SCHEMA conf ;

Which would create the "conf" schema.
What you need to do is set up the URL to the H2 DB as follows:

<property name="hibernate.connection.url" value="jdbc:h2:~/testdb;INIT=RUNSCRIPT FROM '~/create.sql'" />

Which would execute the create.sql file from your home directory (running on Linux).
You could also specify instead of the home directory ("~"), simply a lookup in the classpath:

<property name="hibernate.connection.url" value="jdbc:h2:~/testdb;INIT=RUNSCRIPT FROM 'classpath:create.sql'" />

I anyway recommend you to take a look at H2 DB website:
http://www.h2database.com/html/main.html
to get aquainted with its features closer.

The above H2 DB settings for Hibernate's connection URL will store the H2 DB data to a file (testdb.h2.db) under your home directory. This is very handy in order to get a close look of the created DB and analyze it, H2 DB offers a browser-based console when you run it's jar.

So if you refrained from testing your DAOs in your application until now because you did not
want to run against a full blown DB or didn't know exactly how to do it in the Spring environment,
I hope this post shed some light on this matter.

Happy testing.