JPA/Hibernate: create database schema and import data on first run, update schema on subsequent runs

Question

In my projects I often use JPA/Hibernate stack for database.

When defining persistence.xml you have couple options you can set hibernate.hbm2ddl.auto. If set to create tables will be recreated on every application run (persisted data will be lost of course). It is also possible to import initial data by setting db fixture with hibernate.hbm2ddl.import_files. When set to update only tables for new entities will be created (persisted data in existing tables will be preserved).

The thing is that this is not that convenient while developing and I'd like behavior like this:

• on first application run (when there is no tables in the database) - act like hibernate.hbm2ddl.auto is set to create (create tables based on entities) and import predefined database fixture
• on all subsequent runs - act like hibernate.hbm2ddl.auto is set to update (create new tables for new entities, leave tables/data for old entities).

Is this possible to implement something like this?

More Info on my typical stack: Spring web application, running on Tomacat, database is MySql, JPA/Hibernate for database access.

My typical persistence.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <property name="hibernate.hbm2ddl.import_files" value="/META-INF/spring/import.sql"/>
        </properties>
    </persistence-unit>
</persistence>

Please, comment if you need other info about my application configuration/structure.

Domitian

Answer 1

Please keep in mind that using hbm2ddl.auto you can only update the structure of the database, not it's values.

For example: let's say you have

@Version
private Long version;

After a while you find out that you would rather have

@Version
@Temporal(TemporalType.TIMESTAMP)
private Date lastModified;

Hibernate does not have much of a clue about the change in the semantic meaning and for sure would not know how to convert the field accordingly or which default to set.

The properties hbm2ddl.auto and hbm2ddl.import_files are convenient for short lived (in-memory) databases (and thus for automated integration or acceptance tests), but are simply not suitable for production environments.

I would strongly suggest something like scriptella or liquibase. I personally use the latter. Nathan does a great job on the tool, though it's documentation lacks a bit of detail.

That being said:

-Dhibernate.hbm2ddl.auto=create -Dhibernate.hbm2ddl.import_files=foo.sql

on the first start of your app should do the trick. Just set the value of hbm2ddl.auto to update in your persistence.xml.

Roodepoortmaraisburg

Answer 2

Just use update -- if the database is empty then the result for update and create is the same.

Soar

Thanks. Sorry for confusion, but using update won't be enough. I do want to import some initial data on database creation. I usually use hibernate.hbm2ddl.import_files property to specify initial db fixture but this approach works only with hibernate.hbm2ddl.auto set to create or create-drop. I'll update my post to reflect this. – Domitian

Answer 3

I had the same problem and I didn't find any Hibernate/JPA solutions. Does any no Hibernate solution an option for you? I can suggest to use liquibase. You may define some initial sqls and then liquibase will execute their on startup. It has an maven pluggin, so you can configure it in your pom and then use it with maven.

Muticous