Standardized schema generation and data loading with JPA

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

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.

Parameter	Allowed Values	Description
javax.persistence.schema-generation.database.action	none, create, drop-and-create, drop	Defines 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.action	none, create, drop-and-create, drop	Defines 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-source	metadata, script, metadata-then-script, script-then-metadata	Defines 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-source	metadata, script, metadata-then-script, script-then-metadata	Defines 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-schemas	true, false	Defines 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-target	file URL, java.IO.Writer	Defines the target location of the create script generated by the persistence provider.
javax.persistence.schema-generation.scripts.drop-target	file URL, java.IO.Writer	Defines the target location of the drop script generated by the persistence provider.
javax.persistence.database-product-name	String	The 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-version	String	The major version of the target database as it is returned by the JDBC getDatabaseMajorVersion method.
javax.persistence.database-minor-version	String	The minor version of the target database as it is returned by the JDBC getDatabaseMinorVersion method.
javax.persistence.schema-generation.create-script-source	file URL, java.IO.Writer	Defines 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-source	file URL, java.IO.Writer	Defines 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.connection	String	Specifies the JDBC connection that shall be used to create the database.
javax.persistence.sql-load-script-source	file URL, java.IO.Writer	Defines 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.

<?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="create"/>
    </properties>
  </persistence-unit>
</persistence>

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.

<?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.sql-load-script-source" value="META-INF/data.sql"/>
    </properties>
  </persistence-unit>
</persistence>

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.

<?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>

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.

<?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="EFS2015-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.scripts.action" value="drop-and-create"/>
      <property name="javax.persistence.schema-generation.scripts.create-target" value="./create.sql"/>
      <property name="javax.persistence.schema-generation.scripts.drop-target" value="./drop.sql"/>
    </properties>
  </persistence-unit>
</persistence>

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.