# The Java EE 7 Tutorial

# 37.5 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>

## 37.5.1 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 37-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 37-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.

## 37.5.2 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" />