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