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 in the beginning of this article and use them afterwards 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 about the different parameters and follow that up with some example configurations.
|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.|
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.
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.