Generating the DDL(Create and DROP) Statements of JPA Entities in EclipseLink

This blog is about generating the DDL Statements (CREATE and DROP DDL)  for the entities managed by the JPA. The JPA implementation used here is EclipseLink.

I have used the following Person class to be managed as Entity.

package com.entities;

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

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    int id;
    String fName;
    String lName;
    int age;

   //the setter, getter, methods for each of the attributes, toString,   

   //hashCode,  equals and default constructor are not shown here.
}

The persistence.xml file is as follows.

<?xml version="1.0" encoding="UTF-8"?>
<persistence:persistence version="1.0"
	xmlns:persistence="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 ../../persistence_1_0.xsd ">
<persistence:persistence-unit name="simplePersistenceUnit">
<persistence:provider>org.eclipse.persistence.jpa.PersistenceProvider</persistence:provider>
<persistence:class>com.entities.Person</persistence:class>
<persistence:properties>
<persistence:property name="eclipselink.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<persistence:property name="eclipselink.jdbc.url" value="jdbc:mysql://localhost:3306/default"/>
<persistence:property name="eclipselink.jdbc.user" value="root"/>
<persistence:property name="eclipselink.jdbc.password" value="developer"/>
	</persistence:properties>
	</persistence:persistence-unit>
</persistence:persistence>

The persistence.xml contains information about the database.

The DDL generation information is provided to the persistence manager via a java.util.Map object.

package com.test;

import java.util.HashMap;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.eclipse.persistence.config.PersistenceUnitProperties;

import org.junit.Before;
import org.junit.Test;

public class TestExampleJPA {

	EntityManager em;

	@Test
	public void testDDLGeneration() {
		Map<String, String> persistProperties = new HashMap<String, String>();
		persistProperties.put(PersistenceUnitProperties.DDL_GENERATION, "create-tables");
		persistProperties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, "sql-script");
		persistProperties.put(PersistenceUnitProperties.APP_LOCATION, "C:\MyFolder\temp");
		EntityManagerFactory emf = Persistence.createEntityManagerFactory( "simplePersistenceUnit", persistProperties);
		em = emf.createEntityManager();
	}
}

I have specified the database information in the persistence.xml file and DDL generation information into a Map object and providing it at runtime when creating the EntityManagerFactory object. Both the information can be provided fully either as part of  persistence.xml or Map object. I have combined both approaches for providing configuration information to JPA.

On executing the above test case the following sql files which contains the DDL for CREATE and DROP are created in C:\MyFolder\temp folder.

References:

http://wiki.eclipse.org/EclipseLink/Examples/JPA/OutsideContainer

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)

Take care when using SELECT SQL Statement with IN Clause

Consider the following code snippet

	public static String getMySqlStatement(ArrayList<String> dataValues) {
		if ((dataValues == null) || (dataValues.size() == 0)) {
			throw new IllegalArgumentException();
		}
		StringBuilder sql = new StringBuilder ("SELECT * FROM MY_TABLE WHERE MY_COLUMN IN (");
		StringBuilder colValues = new StringBuilder();
		for (String data: dataValues) {
			colValues.append("'").append(data).append("',");
		}
		//Remove the trailing comma(,) and add the final braces
		sql.append(colValues.substring(0, (colValues.length()-1))).append(")");
		return sql.toString();
	}

The method builds an Select query using the elements in the arraylist dataValues in the IN clause.
The SQL returned by the method executes normally when the size of dataValues is less then or equal to 1000. But, when the size of dataValues is greater than 1000, the SQL throws an exception.

This is because there is a limit on the number of parameters in the IN Clause. For Oracle 9i, the limit is 1000. The limit can vary based on database.

Hence, the above method does not work in all cases. It is required to build SQL queries based on the size of the parameters in the IN clause like breaking up into multiple SQL statements and then combining the results. The above method is modified to return multiple SQL statements if the number of parameters is greater than the limit(in this case 1000).

    public static ArrayList<String> getMySqlStatements(ArrayList<String> dataValues) {
        if ((dataValues == null) || (dataValues.size() == 0)) {
            throw new IllegalArgumentException();
        }
        final int IN_PARAMETER_LIMIT = 1000;
        ArrayList<String> sqlStatements = new ArrayList<String>();
        StringBuilder sql = new StringBuilder ("SELECT * FROM MY_TABLE WHERE MY_COLUMN IN (");
        StringBuilder colValues = null;
        for (int i=0, j=0, length= dataValues.size(); i < length; i++) {
            if (colValues == null) {
                colValues = new StringBuilder();
            }
            j++;
            String data = dataValues.get(i);
            colValues.append("'").append(data).append("',");
            if (j == IN_PARAMETER_LIMIT) {
                sqlStatements.add((sql.append(colValues.substring(0, (colValues.length()-1))).append(")")).toString());
                j=0;
                colValues = null;
            }
        }
        return sqlStatements;
    }

This error may not show up in normal circumstances unless the number of parameters is greater than 1000. To find bugs due to software limits, it is always required to unit test under maximum load conditions.