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)

Answer 1

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