Enterprise Java

JPA Database Schema Generation

For some time now, most of the main implementations of JPA, like Hibernate, EclipseLink or OpenJPA, 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.

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="MyPU" transaction-type="JTA">
        <properties>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
            <property name="javax.persistence.schema-generation.create-source" value="metadata"/> 
            <property name="javax.persistence.schema-generation.drop-source" value="metadata"/> 
            <property name="javax.persistence.sql-load-script-source" value="META-INF/load.sql"/> 
        </properties>
    </persistence-unit>
</persistence>

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

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="MyPU" transaction-type="JTA">
        <properties>
            <property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create"/>
            <property name="javax.persistence.schema-generation.scripts.create-target" value="file:/tmp/create.sql"/>
            <property name="javax.persistence.schema-generation.scripts.drop-target" value="file:/tmp/drop.sql"/>
        </properties>
    </persistence-unit>
</persistence>

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 :)

Reference: JPA Database Schema Generation from our JCG partner Roberto Cortez at the Roberto Cortez Java Blog blog.

Roberto Cortez

My name is Roberto Cortez and I was born in Venezuela, but I have spent most of my life in Coimbra – Portugal, where I currently live. I am a professional Java Developer working in the software development industry, with more than 8 years of experience in business areas like Finance, Insurance and Government. I work with many Java based technologies like JavaEE, Spring, Hibernate, GWT, JBoss AS and Maven just to name a few, always relying on my favorite IDE: IntelliJ IDEA.Most recently, I became a Freelancer / Independent Contractor. My new position is making me travel around the world (an old dream) to customers, but also to attend Java conferences. The direct contact with the Java community made me want to become an active member in the community itself. For that reason, I have created the Coimbra Java User Group, started to contribute to Open Source on Github and launched my own blog (www.radcortez.com), so I can share some of the knowledge that I gained over the years.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button