Generating Database Schemas with JPA 2.1

Prior JPA 2.1, most JPA providers (Hibernate, EclipseLink, OpenJPA) would have a proprietary way to generate the database schema (Data Definition Language scripts) from Entities. In the JPA 2.1 specification (JSR 338) there is a new section called 9.4 Schema Generation introducing an API and some properties to generate a database schema in a standard and portable way.

Properties in persistence.xml

In the history of JPA specification, we always had standard properties (prefixed with javax.persistence) and proprietary ones (eclipselink.xxx, hibernate.xxx, etc.). Each new release of JPA brought new standard properties and we had to wait for JPA 2.1 to deal with schema generation :

    • javax.persistence.schema-generation.database.action : for database action, values are "none", "create", "drop-and-create", "drop".
    • javax.persistence.schema-generation.scripts.action : for DDL scripts, values are "none", "create", "drop-and-create", "drop".
    • javax.persistence.schema-generation.scripts.create-target : name of the create table script
    • javax.persistence.schema-generation.scripts.drop-target : name of the drop table script

JPA 2.1 also brought a way to load data at startup. That’s a common need that we have: we usually want to have a database in a certain state with a certain number of rows, keys, foreign keys, reference tables and so on (good for integration testing for example). Before we had proprietary solutions such as the Hibernate import.sql facilities, or more traditional tools such as DBUnit or Benerator (for load testing). With JPA 2.1 we can also use the new standard property javax.persistence.sql-load-script-source.

Below is a persistence.xml file that generates a create (sampleCreate.ddl) and a drop (sampleDrop.ddl) script, but also executes a script at startup (the insert.sql script). Just add these properties in your persistence.xml, run the application, and you will see the magic happen :

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

  <persistence-unit name="samplePU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>org.agoncal.sample.loadScriptSource.Book</class>
    <properties>
      <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
      <property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create"/>
      <property name="javax.persistence.schema-generation.scripts.create-target" value="sampleCreate.ddl"/>
      <property name="javax.persistence.schema-generation.scripts.drop-target" value="sampleDrop.ddl"/>
      <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:sampleDB;create=true"/>
      <property name="javax.persistence.sql-load-script-source" value="insert.sql"/>
    </properties>
  </persistence-unit>

</persistence>

API to Generate the Schema

JPA 2.1 also brought a Java API which, based on the persistent unit name, can generate the schema. To make this happen, just take a Main class and invoke the generateSchema method passing the persistence unit name (here samplePU) :

public class Main {

  public static void main(String[] args) {
    Persistence.generateSchema("samplePU", null);
  }
}

References

Categories: Java

Tagged as: , ,

6 Comments »

  1. Great article!

    You also could just put your project with classes and persistence.xml in the classpath of jrunscript and execute:
    Java.type(“javax.persistence.Persistence”).generateSchema(“samplePU”,null)

  2. For me “verify” or “validate” is crucial. Is that going to be incorporated in JPA soon?

  3. Antonio you have any example about the configuration to JPA with hibernate provider on Weblogic 12c 1.3 ?
    Any Ideas.

    Thanks so much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s