Spring 3 / Hibernate - execute DB dump during runtime

Question

I am developing a application with spring roo, which uses spring 3 and hibernate.
Due to the fact that the Backend is built to be not a productive one, I net an option to set the data back to a dump.

This dump should be "executed" if the user submits a jsp form. I can load the Dump.sql file and everything is fine except that I do not know how to execute it. I tried several ways:
1. Native Query with entity Manager:

Query q = entityManager.createNativeQuery(dump);  
q.executeUpdate();

But it doesn't work (hibernate exception) I think it's because hibernate cannot "read" a mysql exported Dump.sql file"
2. Way was to use just hibernate:

Configuration cfg = new Configuration();  
File configFile = new     File(getClass().getClassLoader().getResource("/METAINF/persistence.xml").toURI());  
cfg.configure(configFile);  
SessionFactory sf=cfg.buildSessionFactory();  
Session sess=sf.openSession();  
Statement st;  
st = sess.connection().createStatement();

But it didn't work either:

org.hibernate.MappingException: invalid configuration Caused by: org.xml.sax.SAXParseException: Document is invalid: no grammar found.

Any suggestions?

can't you start the mysql recoverytool? – Firo Feb 16 '12 at 08:19 — Firo, Feb 16 '12 at 08:19

score 3 · Accepted Answer · answered Feb 16 '12 at 08:36

I once write a Java class which dumps data from a database, and then imports it into another database (but it does not use the dump generated by MySQL). Maybe you'd take a look at it.

This class depends on DdlUtils and DbUnit :

import java.io.IOException;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * Dumper between databases.
 * 
 * @author ndeverge
 */
public final class DatabaseDumper {

    /**
     * Le logger.
     */
    private static final Log LOGGER = LogFactory.getLog(DatabaseDumper.class);

    /**
     * Environment (dev, continuous integration etc...).
     */
    @Value("#{envProperties['env']}")
    private String env;

    /**
     * The db to dump data from.
     */
    @Autowired
    @Qualifier("referenceDataSource")
    private DataSource sourceDataSource;

    /**
     * the db where to write data to.
     */
    @Autowired
    @Qualifier("dataSource")
    private DataSource destDataSource;

    /**
     * Do we need to run the dump ?
     */
    private boolean doRun;

    /**
     * @return the doRun
     */
    public boolean isDoRun() {
        if (doRun) {
            return true;
        }
        // run the dump only on continuous-integration environment
        if ("continuous-integration".equalsIgnoreCase(env)) {
            return true;
        }
        return false;
    }

    /**
     * @param aDoRun
     *            the doRun to set
     */
    public void setDoRun(final boolean aDoRun) {
        doRun = aDoRun;
    }

    /**
     * Set datasources if not initialized by Spring.<br>
     * This method is used when this utility is started from command line.
     * 
     * @throws SQLException
     *             on errors
     */
    private void initDataSources() throws SQLException {

        if (sourceDataSource == null || destDataSource == null) {
            ApplicationContext context = new ClassPathXmlApplicationContext("spring/dbDumperContext.xml");

            sourceDataSource = (DataSource) context.getBean("referenceDataSource");

            destDataSource = (DataSource) context.getBean("dataSource");
        }

    }

    /**
     * Dumper execution.
     * 
     * @throws Exception
     *             on errors
     */
    public void execute() throws Exception {

        if (!isDoRun()) {
            LOGGER.debug("Do not run the dump for environment \"" + env + "\"");
        } else {

            LOGGER.warn("WARNING !!! Running the database dump, it may take some time...");

            long start = System.currentTimeMillis();

            // extract schema
            Database schema = dumpSchema(sourceDataSource);

            // create schema
            createSchema(destDataSource, schema);

            // extract data
            IDataSet dataSet = dumpData(sourceDataSource);

            // import data
            importData(destDataSource, dataSet);

            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("Database dump duration = " + (System.currentTimeMillis() - start) + " ms");
            }
        }
    }

    /**
     * Extract schema using ddlutils.
     * 
     * @param aSourceDataSource
     *            source db
     * @return an outputstream containing the schema
     * @throws DatabaseUnitException
     *             on errors
     * @throws SQLException
     *             on errors
     * @throws IOException
     *             on errors
     */
    private IDataSet dumpData(final DataSource aSourceDataSource) throws DatabaseUnitException, SQLException,
            IOException {
        IDatabaseConnection sourceConnection = new DatabaseConnection(aSourceDataSource.getConnection());

        return sourceConnection.createDataSet();
    }

    /**
     * Extract data using dbUnit.
     * 
     * @param aSourceDataSource
     *            source db
     * @return an outputstream containing the data
     */
    private Database dumpSchema(final DataSource aSourceDataSource) {
        return PlatformFactory.createNewPlatformInstance(aSourceDataSource).readModelFromDatabase("sourceModel");

    }

    /**
     * Create schema in destination db.
     * 
     * @param aDestDataSource
     *            the destination db
     * @param schema
     *            the schema
     */
    private void createSchema(final DataSource aDestDataSource, final Database schema) {
        Platform destPlatform = PlatformFactory.createNewPlatformInstance(aDestDataSource);

        // create schema by droping tables firts (2nd parameter = true)
        destPlatform.createTables(schema, true, true);
    }

    /**
     * Data import.
     * 
     * @param aDestDataSource
     *            the destination db
     * @param dataSet
     *            the data
     * @throws SQLException
     *             on errors
     * @throws DatabaseUnitException
     *             on errors
     */
    private void importData(final DataSource aDestDataSource, final IDataSet dataSet) throws DatabaseUnitException,
            SQLException {
        IDatabaseConnection destConnection = new DatabaseConnection(aDestDataSource.getConnection());

        DatabaseOperation.CLEAN_INSERT.execute(destConnection, dataSet);
    }

    /**
     * Launch the dumper from commande line.
     * 
     * @param args
     *            paramètres
     */
    public static void main(final String[] args) {
        try {
            DatabaseDumper dumper = new DatabaseDumper();
            dumper.setDoRun(true);
            dumper.initDataSources();
            dumper.execute();
        } catch (Exception e) {
            LOGGER.error("", e);
        }
    }

}

thanks for this, but I would prefer a solution which goes without dependencies on additional frameworks — Alexander, Feb 16 '12 at 10:07

Spring 3 / Hibernate - execute DB dump during runtime

1 Answers1