Auto Create Schema Script with: Ant, Hibernate 3 and JPA 2

Hello, how are you?

Have you noticed how boring is to create sql scripts by yourself? It is complicated to a developer to know the syntax of a “create table” to each database (Oracle, MySQL, …). Let us say that sometimes it is hard to remember how to declare a String to use in the Oracle, Microsoft SQL Server, MySQL and others.

To make our life easier about scripts we have the Hibernate to help us with this script creation.

The Hibernate can help us in two ways when we talk about sql: creating the scripts files and doing an automatic update in the database – without the scripts.

Talking very fast about the automatic way:
The automatic way was implemented in the last post, Tutorial Hibernate 3 with JPA 2. You just have to add the bellow code in your “persistence.xml” that your app will automatically update the database:

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

Just to remember: “It is not a good idea to leave the above code activated in a production environment. You will be updating important data that may raise some exception. The ideal is to have a script file to do this database updating. By example, with the script file, a DBA will have the opportunity to see the impact of the script in the database.”
Automatically creating a script with the schema reduces the bug chances created by developers. Some of the usual errors are: scripts without the schema, some primary key identifier, FK, syntax error and others.

Let us finish the chatting and start with some code. We will use the same code created in the first post about Business: Tutorial Hibernate 3 with JPA 2.

We will work with a tool named Ant. It is nothing more that a task handler. It executes takes correctly listed in a configuration file. Let us start by creating this file.

Create a file name “build.xml” in the project root. Right button click in the project name > New > File. Put the file name and click “Finish”.

Auto Create Schema Ant Hibernate 3 e JPA 2
Double click in the created file to start its edition.

Let us create a simple Hello World to test the ant. Take a look how our first code will look like:

<project name="HibernateJPA" basedir=".">
    <target name="Hello">
        <echo message="Hello World Ant!"  />
    </target>
</project>

Notice that we created something like a tag, named “target”. “Target” is an ant action, this means that, every ant action must be inside a “target”. We can have an unlimited targets number in each file.

To finish this Hello World, let us add our file “build.xml” in the ant view (if you do not know how to open a view, in this link there is a sample Creating a WebServer). Just drag and drop the “build.xml” file inside the “Ant” view. Double click in the Hello target and our message will be displayed in the Console view.

Auto Create Schema Ant Hibernate 3 e JPA 2
Auto Create Schema Ant Hibernate 3 e JPA 2

We have to do the Hibernate Tools download; the Hibernate Tools is the be responsible to create the script file for us. I downloaded the jar file that you can find it in here (Hibernate Tools), but if you want to download in the Hibernate site, you will need to download the JBoss Tools; inside it you will find the Hibernate Tools.

Uncompress the .zip file and put the .jar file inside the lib folder in our system (if you have any problem, in the first post we do this unzip process step by step, Tutorial Hibernate 3 with JPA 2).

We need to add our lib in the Build Path (If you do not know how to do it, you can take a look at the first post and read how this process is done, Tutorial Hibernate 3 with JPA 2).

Auto Create Schema Ant Hibernate 3 e JPA 2
We need to add our application paths in the “build.xml” file:

<project name="HibernateJPA" basedir=".">
    <property name="compiledFiles" location="bin" />
    <property name="libraries" location="lib" />
    <property name="sql" location="sql" />

    <path id="lib.path">
        <pathelement location="${libraries}" />
        <fileset dir="${libraries}">
            <include name="*.jar" />
        </fileset>
    </path>

    <target name="Hello">
        <echo message="Hello World Ant!"/>
    </target>
</project>

In the build.xml file we added the libraries path, the compiled files path and a folder path that does not exists yet, “sql”. A little Eclipse detail is that it has a configuration that hides the “bin” folder from your eyes. If you want to see it through the Windows you can find it in, “…/workspace/yourProject/bin”.

And now let us see the code that will create the “.sql” file:

<project name="HibernateJPA" basedir=".">
    <property name="compiledFiles" location="bin" />
    <property name="libraries" location="lib" />
    <property name="sql" location="sql" />

    <path id="lib.path">
        <pathelement location="${libraries}" />
        <fileset dir="${libraries}">
            <include name="*.jar" />
        </fileset>
    </path>

    <target name="Create MySchema" description="Export schema to DDL file">
        <taskdef name="hibernatetool" classname="org.hibernate.tool.ant.HibernateToolTask" classpathref="lib.path" />
        <mkdir dir="${sql}" />
        <touch file="${sql}/script.sql" />
        <hibernatetool destdir="sql">
            <jpaconfiguration persistenceunit="Hello" />
            <classpath>
                <path location="${compiledFiles}" />
            </classpath>
            <hbm2ddl outputfilename="script.sql" format="true" export="false" drop="true" />
        </hibernatetool>
        <echo message="The file was saved in: ${sql}" />
    </target>    

    <target name="Hello">
        <echo message="Hello World Ant!"/>
    </target>
</project>

After you execute the task, you will see a message at the Console view and it will show where our file was saved (click on the project with the left button > “Refresh”; the folder “sql” will appear).

Auto Create Schema Ant Hibernate 3 e JPA 2
You may think: “I still have to put the schema in the created sql file by myself. This file does not help me in nothing.”

To finish this post, let us change a configuration file so the Hibernate will create the script file for us with the schema. Let us add the next line inside our “persistence.xml”:

<property name="hibernate.default_schema" value="Hello"/>

With the above line, the Hibernate will create the sql file with a default schema. Our file “persistence.xml” will look like:

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.0"
    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_0.xsd">

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

        <properties>
            <property name="hibernate.show_sql" value="true" />
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://127.0.0.1:3306/Hello"/>
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="root" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.default_schema" value="Hello"/>
    </persistence-unit>
</persistence>

Notice that we removed the code that updates the database automatically. Just to make it clear that once we have the database script we do not need an automatically update in the database.

Let us run again our target and re-create the sql file. You will see that our file will be created with the default schema in it.

If you want to create a script to another database, you just have to change the properties values: “javax.persistence.jdbc.driver” and “hibernate.dialect”. You just have to download the right connectors and done!

I hope this post can be useful to you.

Any doubts just post it.

See you soon.

2 thoughts on “Auto Create Schema Script with: Ant, Hibernate 3 and JPA 2

  1. Hi,

    at the time of running I am facing bellow issue.

    Buildfile: E:WorkSpaceworkspaceHibernateJPAbuild.xml
    Create MySchema:
    [hibernatetool] Executing Hibernate Tool with a JPA Configuration
    [hibernatetool] 1. task: hbm2ddl (Generates database schema)
    [hibernatetool] SLF4J: Failed to load class “org.slf4j.impl.StaticLoggerBinder”.
    [hibernatetool] SLF4J: Defaulting to no-operation (NOP) logger implementation
    [hibernatetool] SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.

    BUILD FAILED
    E:WorkSpaceworkspaceHibernateJPAbuild.xml:16: java.lang.NoClassDefFoundError: org/apache/commons/logging/LogFactory

    Total time: 1 second

Leave a Comment