type=page
status=published
title=Database Schema Creation
next=persistence-intro007.html
prev=persistence-intro005.html
~~~~~~
= Database Schema Creation


[[CHDBEGIC]]

[[database-schema-creation]]
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:

[source,oac_no_warn]
----
<?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>
----

[[sthref161]]

[[configuring-an-application-to-create-or-drop-database-tables]]
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.

[[sthref162]][[sthref163]]

*Table 40-3 Schema Creation Actions*

[width="75%",cols="15%,60%"]
|=======================================================================
|*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:

[source,oac_no_warn]
----
<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.

[[sthref164]][[sthref165]]

*Table 40-4 Settings for Create and Delete Source Properties*

[width="75%",cols="15%,60%"]
|=======================================================================
|*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:

[source,oac_no_warn]
----
<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:

[source,oac_no_warn]
----
<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.

[[sthref166]]

[[loading-data-using-sql-scripts]]
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:

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