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);