Featured Image with Sidebar

Standardized schema generation and data loading with JPA 2.1

By Thorben Janssen

Configuration

The creation of database schemas was finally standardized with JPA 2.1. Don’t get me wrong, that doesn’t mean that there were no mechanisms to setup the database before. There were plenty of them. Each JPA implementation provided its own way and configuration parameters for it.

While this was better than nothing, it was also very annoying. If you need to support a different JPA implementation, you have to provide two different persistence.xml files or implement your custom way to set up the database.

This has changed with JPA 2.1. The updated specification defines a set of configuration parameters, which can be used in the persistence.xml file to define the creation of the database and to fill it with an initial set of data. I will explain the different parameters at the beginning of this article and use them afterward to create different example configurations.

If you want to learn more about the other features introduced in JPA 2.1, have a look at JPA 2.1 – 12 features every developer should know and make sure to download the New Features in JPA 2.1 cheat sheet.

Lots of new parameters

JPA 2.1 standardized the configuration parameters to create the database schema but using them is a little bit tricky. There is now a huge set of different parameters and some of them depend on each other to be effective.

Let’s start with an overview of the different parameters and follow that up with some example configurations.

ParameterAllowed ValuesDescription
javax.persistence.schema-generation.database.actionnone, create, drop-and-create, dropDefines whether the persistence provider shall create the database, first drop, and then recreate it, only drop it or do nothing at all.
If this property is not provided, no schema will be created.
javax.persistence.schema-generation.scripts.actionnone, create, drop-and-create, dropDefines which scripts the persistence provider shall create.
A script target needs to be defined for each script to be created.
javax.persistence.schema-generation.create-sourcemetadata, script, metadata-then-script, script-then-metadataDefines how the schema shall be created. It can be done based on the mapping metadata, a script, or a combination of both.
If this parameter is not provided, the persistence provider will use only the mapping metadata or a script, if the javax.persistence.schema-generation.create-script-source property is defined.
javax.persistence.schema-generation.drop-sourcemetadata, script, metadata-then-script, script-then-metadataDefines how the schema shall be dropped. It can be done based on the mapping metadata, a script or a combination of both.
If this parameter is not provided, the persistence provider will use only the mapping metadata or a script, if the javax.persistence.schema-generation.drop-script-source property is defined.
javax.persistence.schema-generation.create-database-schemastrue, falseDefines if the persistence provider shall also create the required database schemas or only the other database objects like tables, sequences, etc..
This property defaults to false.
javax.persistence.schema-generation.scripts.create-targetfile URL, java.IO.WriterDefines the target location of the create script generated by the persistence provider.
javax.persistence.schema-generation.scripts.drop-targetfile URL, java.IO.WriterDefines the target location of the drop script generated by the persistence provider.
javax.persistence.database-product-nameStringThe name of the target database as it is returned by the JDBC DatabaseMetaData method getDatabaseProductName. This parameter is required if the persistence provider shall create scripts without a connection to the database.
javax.persistence.database-major-versionStringThe major version of the target database as it is returned by the JDBC getDatabaseMajorVersion method.
javax.persistence.database-minor-versionStringThe minor version of the target database as it is returned by the JDBC getDatabaseMinorVersion method.
javax.persistence.schema-generation.create-script-sourcefile URL, java.IO.WriterDefines the location of the create script. This parameter has to be defined, if the javax.persistence.schema-generation.create-source property was set to script, metadata-then-script or script-then-metadata.
javax.persistence.schema-generation.drop-script-sourcefile URL, java.IO.WriterDefines the location of the drop script. This parameter has to be defined, if the javax.persistence.schema-generation.drop-source property was set to script, metadata-then-script or script-then-metadata.
javax.persistence.schema-generation.connectionStringSpecifies the JDBC connection that shall be used to create the database.
javax.persistence.sql-load-script-sourcefile URL, java.IO.WriterDefines the location of the SQL script that shall be used load data into the database.

Example Configurations

OK, the sheer amount of configuration parameters and the dependencies between them is a little confusing. So let’s have a look at some example configurations.

Simple configuration to create the database

The first example configuration is also the simplest one. It creates the database based on the mapping metadata. The only thing you have to do is to set the parameter javax.persistence.schema-generation.database.action to create.

This configuration can be used to create the initial version of a database for an application. But be careful, you need to remove the database manually before you can start the application again or switch the javax.persistence.schema-generation.database.action parameter to none or drop-and-create. Otherwise the creation of the database will fail on the second attempt.

The typical test database setup

The second example is the typical database setup for automated tests. The persistence provider will drop the existing database and create a new one based on the mapping metadata. Afterwards an SQL script is used to fill the database with some initial data. Therefore you need to set the parameter javax.persistence.schema-generation.database.action to drop-and-create and provide the path to the SQL script via the parameter javax.persistence.sql-load-script-source.

Script based setup of a test database

The previous example used the mapping metadata to setup the test database. But normally, the production database is created via SQL scripts. So we should do the same for our test setup.

This is done by providing some additional parameters on top of the previous configuration. Setting the parameters javax.persistence.schema-generation.create-source and javax.persistence.schema-generation.drop-source to script tells the persistence provider to use SQL scripts instead of the mapping metadata to create the database. The path to the SQL scripts is defined via javax.persistence.schema-generation.create-script-source and javax.persistence.schema-generation.drop-script-source.

Generate the create and drop scripts based on mapping metadata

The persistence provider can also be used to generate create and drop scripts based on the mapping metadata. Therefore you need to activate the script generation by setting the javax.persistence.schema-generation.scripts.action parameter to drop-and-create and define the path to the new scripts via javax.persistence.schema-generation.scripts.create-target and javax.persistence.schema-generation.scripts.drop-target.

Conclusion

The JPA 2.1 specification introduced a set of new configuration parameter for the persistence.xml and finally standardized the database schema generation. By using these parameters, you can tell the persistence provider to generate the database based on mapping metadata or SQL scripts and load an initial dataset into the database. The persistence provider can also generate SQL scripts based on the mapping metadata which then can be used to setup the database.

Before you leave, download the free “New Features in JPA 2.1” cheat sheet, which provides all you need to remember about this and other features introduced in JPA 2.1.

About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

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

  1. Hi Thorben,
    Very useful enhancements in JPA 2.1. I liked it most about the “Constructor result Mapping” and the new schema generation features.
    I developed a proof of concept using JOOQ. Have you ever tried it?
    I’ve always used JPA/Hibernate but I found JOOQ very interesting. IMHO it required less effort to build queries than using JPQL or Criteria API.
    By the way, I liked the way you write straight to the point. Congrats for the initiative.

    1. Hi Elvis,

      thank you 🙂

      I tried jOOQ in the past and I liked it. I think it’s a good option to write SQL queries and it’s probably a good idea to use it together with Hibernate. Maybe I should write a post about it …

      Regards,
      Thorben

    1. You can set it as the value of the javax.persistence.schema-generation.create-script-source property:

      <?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
      <persistence xmlns=”http://xmlns.jcp.org/xml/ns/persistence” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” version=”2.1″ xsi:schemaLocation=”http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd“>
      <persistence-unit name=”my-persistence-unit” transaction-type=”JTA”>
      <description>Forge Persistence Unit</description>
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
      <exclude-unlisted-classes>false</exclude-unlisted-classes>
      <properties>
      <property name=”hibernate.dialect” value=”org.hibernate.dialect.PostgreSQLDialect”/>

      <property name=”javax.persistence.schema-generation.database.action” value=”drop-and-create”/>
      <property name=”javax.persistence.schema-generation.create-source” value=”script”/>
      <property name=”javax.persistence.schema-generation.create-script-source” value=”META-INF/create.sql”/>
      <property name=”javax.persistence.schema-generation.drop-source” value=”script”/>
      <property name=”javax.persistence.schema-generation.drop-script-source” value=”META-INF/drop.sql”/>

      <property name=”javax.persistence.sql-load-script-source” value=”META-INF/data.sql”/>
      </properties>
      </persistence-unit>
      </persistence>

  2. Hi, I have been unable to download your JPA 2.1 cheat sheet. When I submit the request I get the generic error “Unable to subscribe you to the list.” . Not sure why this is since I am a subscriber already. I have tried updating my profile thinking that might help and unfortunately it doesn’t.

    Any suggestions as to how to get the JPA 2.1 cheat sheet other than through the obvious links?

    Regards,

    Gary

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}