Execute sql script after jpa/EclipseLink created tables?

Question

Hello,

is there a possibility to execute an sql script, after EclipseLink generated the ddl?
In other words, is it possible that the EclipseLink property "eclipselink.ddl-generation" with "drop-and-create-tables" is used and EclipseLink executes another sql-file (to insert some data into some tables just created) after creating the table definition?

I'm using EclipseLink 2.x and JPA 2.0 with GlassFish v3.

Or can I init the tables within a java method which is called on the project (war with ejb3) deployment?

Answer 1

Have a look at Running a SQL Script on startup in EclipseLink that describes a solution presented as a kind of equivalent to Hibernate's import.sql feature¹. Credits to Shaun Smith:

Running a SQL Script on startup in EclipseLink

Sometimes, when working with DDL generation it's useful to run a script to clean up the database first. In Hibernate if you put a file called "import.sql" on your classpath its contents will be sent to the database. Personally I'm not a fan of magic filenames but this can be a useful feature.

There's no built in support for this in EclipseLink but it's easy to do thank's to EclipseLink's high extensibility. Here's a quick solution I came up with: I simply register an event listener for the session postLogin event and in the handler I read a file and send each SQL statement to the database--nice and clean. I went a little further and supported setting the name of the file as a persistence unit property. You can specify this all in code or in the persistence.xml.

The ImportSQL class is configured as a SessionCustomizer through a persistence unit property which, on the postLogin event, reads the file identified by the "import.sql.file" property. This property is also specified as a persistence unit property which is passed to createEntityManagerFactory. This example also shows how you can define and use your own persistence unit properties.

import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.SessionEvent;
import org.eclipse.persistence.sessions.SessionEventAdapter;
import org.eclipse.persistence.sessions.UnitOfWork;

public class ImportSQL implements SessionCustomizer {

    private void importSql(UnitOfWork unitOfWork, String fileName) {
        // Open file
        // Execute each line, e.g.,
        // unitOfWork.executeNonSelectingSQL("select 1 from dual");
    }

    @Override
    public void customize(Session session) throws Exception {
        session.getEventManager().addListener(new SessionEventAdapter() {
            @Override
            public void postLogin(SessionEvent event) {
                String fileName = (String) event.getSession().getProperty("import.sql.file");
                UnitOfWork unitOfWork = event.getSession().acquireUnitOfWork();
                importSql(unitOfWork, fileName);
                unitOfWork.commit() 
            }    
        });
    }

---

public static void main(String[] args) {
    Map<String, Object> properties = new HashMap<String, Object>();

    // Enable DDL Generation
    properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
    properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
    // Configure Session Customizer which will pipe sql file to db before DDL Generation runs
    properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "model.ImportSQL");
    properties.put("import.sql.file","/tmp/someddl.sql");

    EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("employee", properties);
}

I'm not sure it's a strict equivalent though, I'm not sure the script will run after the database generation. Testing required. If it doesn't, maybe it can be adapted.

_{¹ Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the SessionFactory creation right after the database schema generation to import data in a fresh database. You just need to add a file named import.sql in your classpath root and set either create or create-drop as your hibernate.hbm2ddl.auto property.}

Answer 2

It is called BEFORE ddl-execution. And there seems to be no nice way to adapt it, as there is no suitable event one could use.