Database schema creation in JPA using SQL scripts

Recent versions of JPA provide a feature to automatically create the database objects (like tables, sequences or indexes), load initial data into database on application deployment; and also remove them after the application is undeployed.

All that is needed is to define several properties in persistence.xml file:

<?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="mainPU" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>java:/orcl</jta-data-source>
    <class>com.example.periodictable.Element</class>
    <class>com.example.periodictable.ElementCategory</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
      <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform"/>
      
      <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="dbscripts/create.sql"/>
      <property name="javax.persistence.schema-generation.drop-source" value="script"/>
      <property name="javax.persistence.schema-generation.drop-script-source" value="dbscripts/drop.sql"/>
      <property name="javax.persistence.sql-load-script-source" value="dbscripts/load.sql"/>
      
      <property name="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />
      <property name="hibernate.show_sql" value="true"/>

    </properties>
  </persistence-unit>
</persistence>

Property javax.persistence.schema-generation.database.action defines which action should be taken on database when the application is deployed:

none – Takes no action on database. Nothing will be created or dropped.
create – JPA provider will create the database schema on application deployment.
drop – JPA provider will drop the database schema on application deployment.
drop-and-create – JPA provider will first drop the old database schema and then will create the database schema on application deployment.

If property javax.persistence.schema-generation.database.action is not specified, then none is assumed by default. In practice drop-and-create is very useful in simple, test applications and none in real production applications in which the database schema is created elsewhere.

Property javax.persistence.schema-generation.create-source informs JPA provider what should be used as a source of database schema:

metadata – JPA provider will use entity metadata (e.g. annotations) to generate the database schema. This is the default.
script – JPA provider will run provided SQL script to create database schema. The script should create tables, indexes, sequences and other necessary database artifacts.
metadata-then-script – The combination of metadata and then script in that order.
script-then-metadata – The combination of script and then metadata in that order.

Finally, property javax.persistence.schema-generation.create-script-source specifies the location of SQL script to run on application deployment. The location can be a file URL but usually is a relative path to the SQL script packaged into application JAR/WAR.

Properties javax.persistence.schema-generation.drop-source and javax.persistence.schema-generation.drop-script-source have similar values and meaning as their create* counterparts but of course these are used to drop database schema.

There is also one additional property javax.persistence.sql-load-script-source which can be used to load the initial data into the database tables. This SQL script is run after the database schema was created.

Hibernate

Hibernate requires (by default) that the SQL script contains maximum one line per statement. In short it means that the SQL statement cannot be split into multiple lines for better readability which is a common thing for CREATE TABLE commands. This inconvenience can be resolved by specifying following Hibernate specific property:

<property name="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />

Conclusion

The above properties are very useful for simple, test applications which does not require the database data to survive the application undeployment. In production applications property javax.persistence.schema-generation.database.action should be set to none to prevent the loss data from the database in case the application is temporarily undeployed.

The sample application using these properties is available at