Hibernate - Auto-import Sample Data

In early stages of a project, it is very common to have the need to produce tests against a consistent set of data. Knowing exactly what to expect from our database queries, simplifies our life.

There are several utilities out there to assist us on the task as datafactory, but some times they don’t fit our exact needs, as not generating data in our language or forcing us to include code in our application tests (to be deleted later), that we do not want to.

It comes up, that if we are using the Hibernate ORM framework, a very handy feature is included, that will automatically run a SQL Script every time we run our app. All we need to do is properly set-up our persistence.xml file.

Persistence.xml is a standard JPA configuration file, and it has to be included in the META-INF folder of the jar where our entities classes live. Usually this refers to /src/main/resources/META-INF/persistence.xml This is where we define all the configurations so that Hibernate (or any other JPA compliant ORM) knows which database it should work with, username, password, etc. This is also where we should place a reference to all our entity classes.

let’s look at an example of the Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="testDB" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <class>dbexperiment.User</class>
        <class>dbexperiment.Product</class>
        <properties>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testDB;shutdown=true"/>
            <property name="javax.persistence.jdbc.user" value="SA"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="javax.persistence.sql-load-script-source" value="META-INF/import.sql"/>
        </properties>
    </persistence-unit>
</persistence>

We are using hsqldb configured as an in-memory database. This is handy in early stages, as it is blazing fast and supports a wide range of SQL standard features, but what really matter for the purpose of this post are the properties defined in lines _16_ and _17_.

In line _16_ we set the value of Data definition Language (DDL) to Create. This means, that when our application starts, it will scan the entity classes defined in the file and will create the correspondent tables in our database, actually dropping them if they previously existed.

This ensures that the tables will be created exactly as defined in our entities anotations. (in the above example we have indicated that class User and Product of the package dbExperiment are entity classes)

In line _17_ we have the magic property javax.persistence.sql-load-script-source that will load and execute the SQL script file. All we need to do is place the file import.sql in the indicated folder, and voilá..Every time we run our application, Hibernate will take care of creating the tables and executing the SQL script.

An example import.sql file, could be something like ,

INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user1', 'user1@test.com');
INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user2', 'user2@test.com');
INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user3', 'user3@test.com');
INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user4', 'user4@test.com');
INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user5', 'user5@test.com');
INSERT INTO User (user_id, user_name, email) VALUES(NULL, 'user7', 'user6@test.com');

INSERT INTO Product (product_id, product_name) VALUES (NULL, 'product_1');
INSERT INTO Product (product_id, product_name) VALUES (NULL, 'product_2');
INSERT INTO Product (product_id, product_name) VALUES (NULL, 'product_3');
INSERT INTO Product (product_id, product_name) VALUES (NULL, 'product_4');

We do not use create or drop statements in the example SQl script, because we’ve delegated that task to Hibernate, although we could set the DDL property to none, and then include in the SQL script the statements for dropping and create our tables.

Do we need to mention all our Entity classes ?

Actually, no. We can make use of another interesting property hibernate.archive.autodetection, which will instruct Hibernate which elements will be auto discovered by Hibernate Entity Manager.

By changing the above Persistence.xml file to,

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="testDB" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>
            <property name="hibernate.archive.autodetection" value="class, hbm"/>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testDB;shutdown=true"/>
            <property name="javax.persistence.jdbc.user" value="SA"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="javax.persistence.sql-load-script-source" value="META-INF/import.sql"/>
        </properties>
    </persistence-unit>
</persistence>

In this case we are specifying in line _6_ that our entities will be defined in class files and hbm files (Hibernate Mapping files), so it will scan for both). This is quite helpful, as forgetting to declare a entity class in Persistence.xml is quite common, so as long as your entities are defined in the same JAR where Persistence.xml is located and properly annotated the auto-scan property is a plus.

However, if we’re not using auto detection, then we do need to include all our entity classes in Persistence.xml.

You can find a very simple working example with two entities in this git-hub link.