Can i use HSQLDB for junit testing by cloning mySQL database

Beyond the suggestions, please note that depending on your needs, HSQL and MySQL do not have the same features as merge joins and other non-standard SQL features. Because of this (in our case) we always run our tests against the embedded Mysql.

The embedded MySQL is provided with mysql-connector-mxj. If you are using Maven you can do it like this:

<dependency>
   <groupId>mysql</groupId>
       <artifactId>mysql-connector-mxj</artifactId>
       <version>5.0.12</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-mxj-db-files</artifactId>
        <version>5.0.12</version>
</dependency>

      

        
        
        
      

    

Once the driver is in the path of your project, you can simply start the database from Java. In our case, we have this utility class that starts the database:

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mysql.management.MysqldResource;
import com.mysql.management.MysqldResourceI;

public class EmbeddedMySQLDb {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    private MysqldResource mysqldResource;
    private String baseDatabaseDir = System.getProperty("java.io.tmpdir");
    private String databaseName = "test_db_" + System.nanoTime();
    private int port = 13306;
    private String username = "root";
    private String password = "password";

    /**
     * Starts the mysql database
     * @return
     */
    public void startDatabase() {
        if (logger.isDebugEnabled()) {
            logger.debug("=============== Starting Embedded MySQL using these parameters ===============");
            logger.debug("baseDatabaseDir : " + baseDatabaseDir);
            logger.debug("databaseName : " + databaseName);
            logger.debug("host : localhost (hardcoded)");
            logger.debug("port : " + port);
            logger.debug("username : " + username);
            logger.debug("password : " + password);
            logger.debug("=============================================================================");
        }

        File databaseDir = new File(new File(baseDatabaseDir), databaseName);

        mysqldResource = new MysqldResource(databaseDir);

        Map<String, String> database_options = new HashMap<String, String>();
        database_options.put(MysqldResourceI.PORT, Integer.toString(port));
        database_options.put(MysqldResourceI.INITIALIZE_USER, "true");
        database_options.put(MysqldResourceI.INITIALIZE_USER_NAME, username);
        database_options.put(MysqldResourceI.INITIALIZE_PASSWORD, password);

        mysqldResource.start("embedded-mysqld-thread-" + System.currentTimeMillis(), 
                             database_options);

        if (!mysqldResource.isRunning()) {
            throw new RuntimeException("MySQL did not start.");
        }

        logger.info("MySQL started successfully @ " + System.currentTimeMillis());


    }

    /**
     * Shutdowns the mysql database
     * @return
     */
    public void shutdownDatabase() {
        mysqldResource.shutdown();
        if (mysqldResource.isRunning() == false) {
            logger.info(">>>>>>>>>> DELETING MYSQL BASE DIR [" + mysqldResource.getBaseDir() + "] <<<<<<<<<<");
            try {
                FileUtils.forceDelete(mysqldResource.getBaseDir());
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }



    //-------------------------------------------------------------------------
    /**
     * @return the baseDatabaseDir
     */
    public final String getBaseDatabaseDir() {
        return baseDatabaseDir;
    }

    /**
     * @param baseDatabaseDir the baseDatabaseDir to set
     */
    public final void setBaseDatabaseDir(String baseDatabaseDir) {
        this.baseDatabaseDir = baseDatabaseDir;
    }

    /**
     * @return the databaseName
     */
    public final String getDatabaseName() {
        return databaseName;
    }

    /**
     * @param databaseName the databaseName to set
     */
    public final void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    /**
     * @return the port
     */
    public final int getPort() {
        return port;
    }

    /**
     * @param port the port to set
     */
    public final void setPort(int port) {
        this.port = port;
    }

    /**
     * @return the username
     */
    public final String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public final void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public final String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public final void setPassword(String password) {
        this.password = password;
    }   
}

      

        
        
        
      

    

To connect to the DB once created, you should simply use something like this as the DB url:

String url = "jdbc:mysql:mxj://localhost:" + port + "/" + dbName;

Hello,