Database Schema Creation

The persistence provider can be configured to automatically create the database tables, load data into the tables, and remove the tables during application deployment using standard properties in the application’s deployment descriptor. These tasks are typically used during the development phase of a release, not against a production database.

The following is an example of a persistence.xml deployment descriptor that specifies that the provider should drop all database artifacts using a provided script, create the artifacts with a provided script, and load data from a provided script when the application is deployed:

<?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="examplePU" transaction-type="JTA">
    <jta-data-source>java:global/ExampleDataSource</jta-data-source>
    <properties>
        <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/sql/create.sql" />
        <property name="javax.persistence.sql-load-script-source"
                  value="META-INF/sql/data.sql" />
        <property name="javax.persistence.schema-generation.drop-source"
                  value="script" />
        <property name="javax.persistence.schema-generation.drop-script-source"
                  value="META-INF/sql/drop.sql" />
    </properties>
  </persistence-unit>
</persistence>

Configuring an Application to Create or Drop Database Tables

The javax.persistence.schema-generation.database.action property is used to specify the action taken by the persistence provider when an application is deployed. If the property is not set, the persistence provider will not create or drop any database artifacts.

Table 40-3 Schema Creation Actions

Setting

Description

none

No schema creation or deletion will take place.

create

The provider will create the database artifacts on application deployment. The artifacts will remain unchanged after application redeployment.

drop-and-create

Any artifacts in the database will be deleted, and the provider will create the database artifacts on deployment.

drop

Any artifacts in the database will be deleted on application deployment.

In this example, the persistence provider will delete any remaining database artifacts and then create the artifacts when the application is deployed:

<property name="javax.persistence.schema-generation.database.action"
           value="drop-and-create"/>

By default, the object/relational metadata in the persistence unit is used to create the database artifacts. You may also supply scripts used by the provider to create and delete the database artifacts. The javax.persistence.schema-generation.create-source and javax.persistence.schema-generation.drop-source properties control how the provider will create or delete the database artifacts.

Table 40-4 Settings for Create and Delete Source Properties

Setting

Description

metadata

Use the object/relational metadata in the application to create or delete the database artifacts.

script

Use a provided script for creating or deleting the database artifacts.

metadata-then-script

Use a combination of object/relational metadata, then a user-provided script to create or delete the database artifacts.

script-then-metadata

Use a combination of a user-provided script, then the object/relational metadata to create and delete the database artifacts.

In this example, the persistence provider will use a script packaged within the application to create the database artifacts:

<property name="javax.persistence.schema-generation.create-source"
           value="script"/>

If you specify a script in create-source or drop-source, specify the location of the script using the javax.persistence.schema-generation.create-script-source or javax.persistence.schema-generation.drop-script-source property. The location of the script is relative to the root of the persistence unit:

<property name="javax.persistence.schema-generation.create-script-source"
           value="META-INF/sql/create.sql" />

In the above example, the create-script-source is set to a SQL file called create.sql in the META-INF/sql directory relative to root of the persistence unit.

Loading Data Using SQL Scripts

If you want to populate the database tables with data before the application loads, specify the location of a load script in the javax.persistence.sql-load-script-source property. The location specified in this property is relative to the root of the persistence unit.

In this example, the load script is a file called data.sql in the META-INF/sql directory relative to the root of the persistence unit:

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