JPA Database Schema Generation
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:
| Property | Values |
|---|---|
|
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:
| Property | Values |
|---|---|
|
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <?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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <?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 🙂
