com.surveypanel.dao.DBTestCase.java Source code

Introduction

Here is the source code for com.surveypanel.dao.DBTestCase.java

Source

/*
* SurveyPanel
* Copyright (C) 2009 Serge Tan Panza
* All rights reserved.
* License: GNU/GPL License v3 , see LICENSE.txt
* SurveyPanel is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
* 
     
*/
package com.surveypanel.dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import javax.sql.DataSource;

import junit.framework.TestCase;

import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.locale.converters.DateLocaleConverter;
import org.apache.log4j.Logger;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.CompositeDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.ext.h2.H2DataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Before;

import com.surveypanel.form.TestHelper;

/**
 * @author stanpanza
 *
 */
public abstract class DBTestCase extends TestCase {

    protected static Logger logger = Logger.getLogger(DBTestCase.class);
    protected DataSource dataSource;
    protected IDatabaseConnection connection;

    public DBTestCase() {
        DateLocaleConverter dateConverter = new DateLocaleConverter();
        ConvertUtils.register(dateConverter, Date.class);
        dataSource = TestHelper.getDataSource();
    }

    /**
     * get connection to the real db
     */
    protected IDatabaseConnection getDatabaseConnection() throws Exception {
        if (connection == null || connection.getConnection().isClosed()) {
            logger.debug("creating IDatabaseConnection...");
            connection = new DatabaseConnection(dataSource.getConnection());
            DatabaseConfig config = connection.getConfig();
            config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new H2DataTypeFactory());
            connection.getConnection();
        }
        return connection;
    }

    /**
     * Implement to specify which datasets are loaded into db
     * 
     */
    public abstract String[] specifyDataSets();

    /**
     * get connection to data set
     * 
     */
    protected IDataSet getDataSet() throws DataSetException, IOException {
        String[] specifyDataSets = specifyDataSets();
        IDataSet[] dataset = new IDataSet[specifyDataSets.length];
        for (int i = 0; i < specifyDataSets.length; i++) {
            dataset[i] = new FlatXmlDataSet(getClass().getClassLoader().getResource(specifyDataSets[i]));
        }
        return new CompositeDataSet(dataset);
    }

    /**
     * count filtered rows in data set
     */
    protected int countFilteredRows(String tableName, String columnName, Object value) throws Exception {
        int counter = 0;
        ITable table = getDataSet().getTable(tableName);
        int rowNo = table.getRowCount();
        for (int i = 0; i < rowNo; i++) {
            Object valueFromXML = table.getValue(i, columnName);
            if (value.toString().equals(valueFromXML.toString())) {
                counter++;
            }
        }
        return counter;
    }

    /**
     * refresh db with a data sets (truncate & insert)
     */
    @Before
    public void setUp() throws Exception {
        IDatabaseConnection connection = getDatabaseConnection();
        try {
            executeSQLScript(connection.getConnection(), getClass().getResourceAsStream("/install.sql"));
            logger.info("preparing database...");
            IDataSet dataSet = getDataSet();
            DatabaseOperation.TRUNCATE_TABLE.execute(connection, dataSet);
            DatabaseOperation.INSERT.execute(connection, dataSet);
            logger.info("db preparation is done !");
        } catch (Exception e) {
            e.printStackTrace();
            fail();
        } finally {
            connection.close();
        }
    }

    /**
     * close and release connection.
     */
    @After
    public void tearDown() throws Exception {
        if (connection != null && !connection.getConnection().isClosed()) {
            connection.close();
        }
        connection = null;
    }

    /**
     * Executes a SQL script.
     *
     * @param con database connection.
     * @param resource an input stream for the script to execute.
     * @param autoreplace automatically replace jiveVersion with ofVersion
     * @throws IOException if an IOException occurs.
     * @throws SQLException if an SQLException occurs.
     */
    private static void executeSQLScript(Connection con, InputStream resource) throws IOException, SQLException {
        BufferedReader in = null;
        try {
            in = new BufferedReader(new InputStreamReader(resource));
            boolean done = false;
            while (!done) {
                StringBuilder command = new StringBuilder();
                while (true) {
                    String line = in.readLine();
                    if (line == null) {
                        done = true;
                        break;
                    }
                    // Ignore comments and blank lines.
                    if (isSQLCommandPart(line)) {
                        command.append(" ").append(line);
                    }
                    if (line.trim().endsWith(";")) {
                        break;
                    }
                }
                // Send command to database.
                if (!done && !command.toString().equals("")) {
                    try {
                        String cmdString = command.toString();
                        Statement stmt = con.createStatement();
                        stmt.execute(cmdString);
                        stmt.close();
                    } catch (SQLException e) {
                        // Lets show what failed
                        logger.error("SchemaManager: Failed to execute SQL:\n" + command.toString());
                        throw e;
                    }
                }
            }
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (Exception e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }
    }

    /**
     * Returns true if a line from a SQL schema is a valid command part.
     *
     * @param line the line of the schema.
     * @return true if a valid command part.
     */
    private static boolean isSQLCommandPart(String line) {
        line = line.trim();
        if (line.equals("")) {
            return false;
        }
        // Check to see if the line is a comment. Valid comment types:
        //   "//" is HSQLDB
        //   "--" is DB2 and Postgres
        //   "#" is MySQL
        //   "REM" is Oracle
        //   "/*" is SQLServer
        return !(line.startsWith("//") || line.startsWith("--") || line.startsWith("#") || line.startsWith("REM")
                || line.startsWith("/*") || line.startsWith("*"));
    }

}