How to auto-create database, schema and table only if they do not already exist

Question

I want to create a simple webapp that

• allow remote clients to track some content on a post request
• persist all the tracking in a lightweight database
• give back all the tracking on a get request

About the database, I would like

1. put its location within a properties file of my webapp (and use this location for hibernate.location.url set in persistence.xml)
2. that the database is created with schema and table if they do not exist yet
3. use the existing database and schema and table and data if they exist

So I created a maven project with:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.my.tracker</groupId>
    <artifactId>tracker-webapp</artifactId>
    <version>0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <properties>
        <hibernate.version>4.3.8.Final</hibernate.version>
        <h2.version>1.4.185</h2.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>false</downloadJavadocs>
                </configuration>
            </plugin>
            <!-- Set a compiler level -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.6</version>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- Servlet API -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>
        <!-- JPA Provider (Hibernate) -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <!-- Database (H2) -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
        </dependency>

    </dependencies>

</project>

src/main/resources/META-INF/persistence.xml

<?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_1_0.xsd" version="1.0">

  <persistence-unit name="thePersistenceUnit" transaction-type="RESOURCE_LOCAL">
     <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <class>org.my.tracker.Event</class>

    <properties>
        <property name="connection.driver_class" value="org.h2.Driver"/>
        <property name="hibernate.connection.url" value="jdbc:h2:./db/repository"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
        <property name="hibernate.hbm2ddl.auto" value="create"/>
        <property name="hibernate.show_sql" value="true" />
    </properties>
</persistence-unit>
</persistence>

src/main/webapp/WEB-INF/web.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app 
   xmlns="http://java.sun.com/xml/ns/javaee" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 
   version="2.5">
  <servlet>
    <servlet-name>orc-event</servlet-name>
    <servlet-class>org.my.tracker.EventServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>orc-event</servlet-name>
    <url-pattern>/event/*</url-pattern>
  </servlet-mapping>
</web-app>

src/main/java/org/my/tracker/EventServlet.java

package org.my.tracker;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.gson.Gson;

public class EventServlet extends HttpServlet {
    private static final Logger LOGGER = LoggerFactory.getLogger(EventServlet.class);

    private static final long serialVersionUID = 1L;
    public static final String HTML_START="<html><body>";
    public static final String HTML_END="</body></html>";
    private static EntityManager manager;

    static {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("thePersistenceUnit");
        manager = factory.createEntityManager();
    }
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        PrintWriter out = response.getWriter();
        try {
            manager.getTransaction().begin();
            @SuppressWarnings("unchecked")
            List<Event> events = manager.createQuery("from Event").getResultList();
            Gson gson = new Gson();
            out.print(gson.toJson(events));
            manager.getTransaction().commit();
        } catch (Exception e) {
            manager.getTransaction().rollback();
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String body = (String)request.getParameter("body");
        if (null != body) {
            try {
                manager.getTransaction().begin();
                Event event = new Event();
                event.setBody(body);
                manager.persist(event);
                manager.getTransaction().commit();
            } catch (Exception e) {
                manager.getTransaction().rollback();
            }
        } else {
            LOGGER.error("null body, cannot track");
        }
    }

}

src/main/java/org/my/tracker/Event.java

package org.my.tracker;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Event {
    @Id
    @GeneratedValue
    private Integer id;
    private String body;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBody() {
        return body;
    }

    public void setBody(String body) {
        this.body = body;
    }

}

Well, thanks to all these powerful tools, this is simple and works quite nicely, except that I could not

1. see how to set my db location through a property (is it only possible through tomcat server.xml tune (that I cannot do for it does not belong to me) or spring (which is quite a weak reason to put some spring in my project only for 1 property injection...),

2. (and 3.) each time I launch my webapp, my database is empty. Of course, you will tell me, since I put "create" value in persistence.xml

       <property name="hibernate.hbm2ddl.auto" value="create"/>
   

Well, I could put

• create-drop : same result as "create" in my case (which is kind of expected, for "drop" does mean a lot like the opposite of "keep everything at shutdown" :D )
• none : but then the database is not created the first time the webapp is not deployed
• validate : well I can recover my content at webapp restart, but as none, at first if it does not exist, the database schema and table are not created and my webapp cannot work with it :)

So I would like if possible to keep things as simple as they are above, is it possible ?

If there is no other way than writing an sql script to initialize my db only if it does not exist, I would appreciate you give the code or some hints, for I am a complete dumb in this field.

Edit

So as I put in one comment, actually, "update" is the value I was looking for. Work like a charm.

I did not find any other way than spring to pass variables instead of constant values. But hey that is quite common in such field anyway :) (and spring its orm layer too)

Solution

JPA 2.1 provides standard properties for creating schema. Look for

javax.persistence.schema-generation.database.action

and set to create or drop-and-create. Similarly there are properties for executing scripts at startup of an EMF. Docs for any compliant JPA implementation should document them

Comments

[Zzirconium]

If I replace <property name="hibernate.hbm2ddl.auto" value="create"/> by <property name="javax.persistence.schema-generation.database.action" value="create"/> it fails at the second launch because it tries to create the EVENT TABLE although it already exists. if I set drop-and-create, it wipes out the existing TABLE and I lose my already existing persisted events.

[Neil Stockton]

indeed, and that is what "create" and "drop-and-create" are supposed to do. If you want to do other things you use the scripts option. Would have been nice if the JPA spec people had allowed "validate" and "validate-and-create" options also. Don't know if your JPA provider has those, the one I use has a validate option

[Zzirconium]

Thanks for your update. Actually, writing script will need to go into the explicit schema and table creation. Is not there any way to keep the automatic database populate, but with a simple update behavior instead of either "only use existing" (which fails the first time) or "wipe everything" (which wipes everything out and loses the whole database persistency usefulness...)

[Neil Stockton]

does your JPA provider have a "validate" option? I use DataNucleus JPA and it has.

[Zzirconium]

I did mention the "validate" option of hibernate in my question and it does not initially create the database... But hey, I think I just didn't try the ovious one, there is an "update" value, and it seems to do the trick ! I cannot believe it I have to check again. All this writing for that... :D

[Zzirconium]

Now I just miss the property thing : how to set the db file location with a property file and use this property in persistence.xml

[Neil Stockton]

Good you got part of it working. Can't help on the other. I remember seeing Spring has such things

[Jose Mhlanga]

Thanks, works like a charm, all others do not work