JPA Database Schema Generation

posted by Roberto Cortez on

For some time now, most of the main implementations of JPA, like Hibernate, EclipseLink, OpenJPA or DataNucleus, offered ways to generate database schema objects. These include generation of tables, primary keys, foreign keys, indexes and other objects. Unfortunately, these are not standard between the implementations, when dealing with multiple environments. Only in the latest JPA 2.1 specification, the Schema Generation standardization was introduced.

From now on, if you are using Java EE 7, you don’t have to worry about the differences between the providers. Just use the new standard properties and you are done. Of course, you might be thinking that these are not needed at all, since database schemas for environments should not be managed like this. Still, these are very useful for development or testing purposes.

Schema Generation

Properties
If you wish to use the new standards for Schema Generation, just add any of the following properties to your properties section of the persistence.xml:

PropertyValues
javax.persistence.schema-generation.database.action

Specifies the action to be taken regarding to the database schema. Possible values are self-explanatory. If this property is not specific no actions are performed in the database.
none, create, drop-and-create, drop
javax.persistence.schema-generation.create-source

Specifies how the database schema should be created. It can be by just using the annotation metadata specified in the application entities, by executing a SQL script or a combination of both. You can also define the order. This property does not need to be specified for schema generation to occur. The default value is metadata. You need to be careful if you use a combination of create actions. The resulting actions may generate unexpected behaviour in the database schema and lead to failure.
metadata, script, metadata-than-script, script-then-metadata
javax.persistence.schema-generation.drop-source

Same as javax.persistence.schema-generation.create-source, but for drop actions.
metadata, script, metadata-than-script, script-then-metadata
javax.persistence.schema-generation.create-script-source, javax.persistence.schema-generation.drop-script-source

Specifies the target location to a SQL script file to execute on create or drop of the database schema.
String for the file URL to execute
javax.persistence.sql-load-script-source

Specifies the target location to a SQL file to load data into the database.
String for the file URL to execute

Additionally, it’s also possible to generate SQL scripts with the Schema Generation actions:

PropertyValues
javax.persistence.schema-generation.scripts.action

Specifies which SQL scripts should be generated. Scripts are only generated if the corresponding generation location targets are specified.
none, create, drop-and-create, drop
javax.persistence.schema-generation.scripts.create-target, javax.persistence.schema-generation.scripts.drop-target

Specifies the target location to generate the SQL script file to create or drop of the database schema.
String for the file URL to execute

Samples

The following sample, drops and creates the database schema objects needed by the JPA application. Relies on the annotations metadata of the entities and also executes an arbitrary SQL file named load.sql.

Another sample that generates the database schema objects to be created and dropped in the target locations:

Both samples can also be combined for dropping and creating the database objects and generating the corresponding scripts that perform these operations. You can find these and other samples in the Java EE Samples project hosted on Github.

Limitations

As I mentioned before, I recommend that you use these properties for development or testing purposes only. A wrong setting, might easily destroy or mess with your production database.

There are no actions to update or just validate the schema. I couldn’t find the reason why they didn’t make it into the specification, but here is an issue with the improvement suggestion.

The database schema actions are only performed on the application deployment in a Java EE environment. For development, you might want to perform the actions on the server restart.

Support

Both Hibernate and EclipseLink, which are bundled with Wildfly and Glassfish support these properties.

OpenJPA, currently does not support these properties, but I’ve been working in the OpenJPA support for standard Schema Generation. If you’re curious or want to follow the progress, check my Github repo, here. This was actually my main motivation to write this post, since I’m a bit involved in the implementation of the feature.

I hope you enjoyed the post 🙂

Comments ( 9 )

  1. ReplyLukas Eder

    The fact that this particular feature has been standardised into JPA has always been obscure to me. I mean, DDL is so much about storage and so little about the actual tables / relationships, and storage isn’t covered at all by the standard…

    But still, it sure was fun to implement!

    • ReplyRoberto Cortez

      Hey Lukas,

      In my opinion this is most useful for prototyping / development / testing, where you don’t care much about storage. I would never think on relying in this for production.

  2. ReplyAndy

    You maybe ought to mention DataNucleus (http://www.datanucleus.org) which implements JPA 2.1 and has done since before Hibernate did.

  3. ReplyNsanchez

    Hola Roberto, me parece genial tu blog, estoy sumamente interesado en este tema.
    Probé la configuración jpa que publicaste, me gustaria saber si es posible acceder al contenido de las tablas desde otra aplicacion, segun tengo entendido esta configuracion crea una “base de datos” que trabaja solo dentro de la aplicacion, de ser asi como puedo hacer para crear una base de datos(por ejemplo de mysql) accesible desde cualquier aplicacion?..
    Disculpa no soy muy bueno escribiendo en ingles, pero si leyendo.
    Saludos desde Argentina y espero que sigas iluminandonos con tu conocimiento 😀

  4. Replybalazs

    Hi!

    Thanks for your blog, i have a problem inserting accented characters for example: éáűőúöüóíÍÉÁŰŐÚÖÜÓ. The inserted characters are not correct unfortunatelly, however my database is UTF-16. Thanks

    • ReplyRoberto Cortez

      Hi Balasz,

      Thank you for your feedback. What database are you using?

      Cheers,
      Roberto

  5. ReplyJulien

    Wow dude! Thank you very much for this guide! Really well explained! Now I finally was able to create DB from SQL script and populate it with data!

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>