org.agnitas.dao.impl.ImportProfileDaoImpl.java Source code

Java tutorial

Introduction

Here is the source code for org.agnitas.dao.impl.ImportProfileDaoImpl.java

Source

/*********************************************************************************
 * The contents of this file are subject to the Common Public Attribution
 * License Version 1.0 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.openemm.org/cpal1.html. The License is based on the Mozilla
 * Public License Version 1.1 but Sections 14 and 15 have been added to cover
 * use of software over a computer network and provide for limited attribution
 * for the Original Developer. In addition, Exhibit A has been modified to be
 * consistent with Exhibit B.
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 *
 * The Original Code is OpenEMM.
 * The Original Developer is the Initial Developer.
 * The Initial Developer of the Original Code is AGNITAS AG. All portions of
 * the code written by AGNITAS AG are Copyright (c) 2009 AGNITAS AG. All Rights
 * Reserved.
 *
 * Contributor(s): AGNITAS AG.
 ********************************************************************************/

package org.agnitas.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.agnitas.beans.ColumnMapping;
import org.agnitas.beans.ImportProfile;
import org.agnitas.beans.impl.ColumnMappingImpl;
import org.agnitas.beans.impl.ImportProfileImpl;
import org.agnitas.dao.ImportProfileDao;
import org.agnitas.util.AgnUtils;
import org.agnitas.util.ImportUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

/**
 * DAO handler for ImportProfile-Objects
 * This class is compatible with oracle and mysql datasources and databases  
 * 
 * @author Andreas Soderer
 * @date 03.05.2012
 */
public class ImportProfileDaoImpl extends BaseDaoImpl implements ImportProfileDao {
    private static final transient Logger logger = Logger.getLogger(ImportProfileDaoImpl.class);

    private static final String TABLE = "import_profile_tbl";

    private static final String FIELD_ID = "id";
    private static final String FIELD_SHORTNAME = "shortname";
    private static final String FIELD_COMPANY_ID = "company_id";
    private static final String FIELD_ADMIN_ID = "admin_id";
    private static final String FIELD_COLUMN_SEPARATOR = "column_separator";
    private static final String FIELD_TEXT_DELIMITER = "text_delimiter";
    private static final String FIELD_FILE_CHARSET = "file_charset";
    private static final String FIELD_DATE_FORMAT = "date_format";
    private static final String FIELD_IMPORT_MODE = "import_mode";
    private static final String FIELD_KEY_COLUMN = "key_column";
    private static final String FIELD_CHECK_FOR_DUPLICATES = "check_for_duplicates";
    private static final String FIELD_NULL_VALUES_ACTION = "null_values_action";
    private static final String FIELD_EXT_EMAIL_CHECK = "ext_email_check";
    private static final String FIELD_REPORT_EMAIL = "report_email";
    private static final String FIELD_MAIL_TYPE = "mail_type";
    private static final String FIELD_UPDATE_ALL_DUPLICATES = "update_all_duplicates";

    private static final String SELECT_NEXT_PROFILEID = "SELECT import_profile_tbl_seq.nextval FROM DUAL";
    private static final String SELECT_BY_ID = "SELECT * FROM " + TABLE + " WHERE " + FIELD_ID + " = ?";
    private static final String SELECT_BY_SHORTNAME = "SELECT * FROM " + TABLE + " WHERE UPPER(" + FIELD_SHORTNAME
            + ") = UPPER(?)";
    private static final String SELECT_BY_COMPANYID = "SELECT * FROM " + TABLE + " WHERE " + FIELD_COMPANY_ID
            + " = ?";
    private static final String DELETE = "DELETE FROM " + TABLE + " WHERE " + FIELD_ID + " = ?";
    private static final String UPDATE = "UPDATE " + TABLE + " SET " + FIELD_COMPANY_ID + " = ?, " + FIELD_ADMIN_ID
            + " = ?, " + FIELD_SHORTNAME + " = ?, " + FIELD_COLUMN_SEPARATOR + " = ?, " + FIELD_TEXT_DELIMITER
            + " = ?, " + FIELD_FILE_CHARSET + " = ?, " + FIELD_DATE_FORMAT + " = ?, " + FIELD_IMPORT_MODE + " = ?, "
            + FIELD_NULL_VALUES_ACTION + " = ?, " + FIELD_KEY_COLUMN + " = ?, " + FIELD_EXT_EMAIL_CHECK + " = ?, "
            + FIELD_REPORT_EMAIL + " = ?, " + FIELD_CHECK_FOR_DUPLICATES + " = ?, " + FIELD_MAIL_TYPE + " = ?, "
            + FIELD_UPDATE_ALL_DUPLICATES + " = ? WHERE " + FIELD_ID + " = ?";
    private static final String INSERT_ORACLE = "INSERT INTO " + TABLE + " (" + FIELD_ID + ", " + FIELD_COMPANY_ID
            + ", " + FIELD_ADMIN_ID + ", " + FIELD_SHORTNAME + ", " + FIELD_COLUMN_SEPARATOR + ", "
            + FIELD_TEXT_DELIMITER + ", " + FIELD_FILE_CHARSET + ", " + FIELD_DATE_FORMAT + ", " + FIELD_IMPORT_MODE
            + ", " + FIELD_NULL_VALUES_ACTION + ", " + FIELD_KEY_COLUMN + ", " + FIELD_EXT_EMAIL_CHECK + ", "
            + FIELD_REPORT_EMAIL + ", " + FIELD_CHECK_FOR_DUPLICATES + ", " + FIELD_MAIL_TYPE + ", "
            + FIELD_UPDATE_ALL_DUPLICATES + ") VALUES(" + AgnUtils.repeatString("?", 16, ", ") + ")";
    private static final String INSERT_MYSQL = "INSERT INTO " + TABLE + " (" + FIELD_COMPANY_ID + ", "
            + FIELD_ADMIN_ID + ", " + FIELD_SHORTNAME + ", " + FIELD_COLUMN_SEPARATOR + ", " + FIELD_TEXT_DELIMITER
            + ", " + FIELD_FILE_CHARSET + ", " + FIELD_DATE_FORMAT + ", " + FIELD_IMPORT_MODE + ", "
            + FIELD_NULL_VALUES_ACTION + ", " + FIELD_KEY_COLUMN + ", " + FIELD_EXT_EMAIL_CHECK + ", "
            + FIELD_REPORT_EMAIL + ", " + FIELD_CHECK_FOR_DUPLICATES + ", " + FIELD_MAIL_TYPE + ", "
            + FIELD_UPDATE_ALL_DUPLICATES + ") VALUES(" + AgnUtils.repeatString("?", 15, ", ") + ")";

    // COLUMN_MAPPING Table
    private static final String COLUMN_MAPPING_TABLE = "import_column_mapping_tbl";
    private static final String COLUMN_MAPPING_ID = "id";
    private static final String COLUMN_MAPPING_PROFILE_ID = "profile_id";
    private static final String COLUMN_MAPPING_MANDATORY = "mandatory";
    private static final String COLUMN_MAPPING_DB_COLUMN = "db_column";
    private static final String COLUMN_MAPPING_FILE_COLUMN = "file_column";
    private static final String COLUMN_MAPPING_DEFAULT_VALUE = "default_value";
    private static final String SELECT_COLUMN_MAPPINGS = "SELECT * FROM " + COLUMN_MAPPING_TABLE + " WHERE "
            + COLUMN_MAPPING_PROFILE_ID + " = ?";
    private static final String DELETE_COLUMN_MAPPINGS = "DELETE FROM " + COLUMN_MAPPING_TABLE + " WHERE "
            + COLUMN_MAPPING_PROFILE_ID + " = ?";
    private static final String INSERT_COLUMN_MAPPINGS_ORACLE = "INSERT INTO " + COLUMN_MAPPING_TABLE + " ("
            + COLUMN_MAPPING_ID + ", " + COLUMN_MAPPING_PROFILE_ID + ", " + COLUMN_MAPPING_FILE_COLUMN + ", "
            + COLUMN_MAPPING_DB_COLUMN + ", " + COLUMN_MAPPING_MANDATORY + ", " + COLUMN_MAPPING_DEFAULT_VALUE
            + ") VALUES (import_column_mapping_tbl_seq.nextval, ?, ?, ?, ?, ?)";
    private static final String INSERT_COLUMN_MAPPINGS_MYSQL = "INSERT INTO " + COLUMN_MAPPING_TABLE + " ("
            + COLUMN_MAPPING_PROFILE_ID + ", " + COLUMN_MAPPING_FILE_COLUMN + ", " + COLUMN_MAPPING_DB_COLUMN + ", "
            + COLUMN_MAPPING_MANDATORY + ", " + COLUMN_MAPPING_DEFAULT_VALUE + ") VALUES (?, ?, ?, ?, ?)";

    // GENDER_MAPPING Table
    private static final String GENDER_MAPPING_TABLE = "import_gender_mapping_tbl";
    private static final String GENDER_MAPPING_ID = "id";
    private static final String GENDER_MAPPING_PROFILE_ID = "profile_id";
    private static final String GENDER_MAPPING_STRING_GENDER = "string_gender";
    private static final String GENDER_MAPPING_INT_GENDER = "int_gender";
    private static final String SELECT_GENDER_MAPPINGS = "SELECT * FROM " + GENDER_MAPPING_TABLE + " WHERE "
            + GENDER_MAPPING_PROFILE_ID + " = ? ORDER BY " + GENDER_MAPPING_ID;
    private static final String DELETE_GENDER_MAPPINGS = "DELETE FROM " + GENDER_MAPPING_TABLE + " WHERE "
            + GENDER_MAPPING_PROFILE_ID + " = ?";
    private static final String INSERT_GENDER_MAPPINGS_ORACLE = "INSERT INTO " + GENDER_MAPPING_TABLE + " ("
            + GENDER_MAPPING_ID + ", " + GENDER_MAPPING_PROFILE_ID + ", " + GENDER_MAPPING_INT_GENDER + ", "
            + GENDER_MAPPING_STRING_GENDER + ") VALUES (import_gender_mapping_tbl_seq.nextval, ?, ?, ?)";
    private static final String INSERT_GENDER_MAPPINGS_MYSQL = "INSERT INTO " + GENDER_MAPPING_TABLE + " ("
            + GENDER_MAPPING_PROFILE_ID + ", " + GENDER_MAPPING_INT_GENDER + ", " + GENDER_MAPPING_STRING_GENDER
            + ") VALUES (?, ?, ?)";

    public int insertImportProfile(ImportProfile importProfile) {
        int profileId;
        if (AgnUtils.isOracleDB()) {
            logSqlStatement(logger, SELECT_NEXT_PROFILEID);
            profileId = getSimpleJdbcTemplate().queryForInt(SELECT_NEXT_PROFILEID);

            logSqlStatement(logger, INSERT_ORACLE);
            getSimpleJdbcTemplate().update(INSERT_ORACLE, profileId, importProfile.getCompanyId(),
                    importProfile.getAdminId(), importProfile.getName(), importProfile.getSeparator(),
                    importProfile.getTextRecognitionChar(), importProfile.getCharset(),
                    importProfile.getDateFormat(), importProfile.getImportMode(),
                    importProfile.getNullValuesAction(), importProfile.getKeyColumn(),
                    ImportUtils.getBooleanAsInt(importProfile.getExtendedEmailCheck()),
                    importProfile.getMailForReport(), importProfile.getCheckForDuplicates(),
                    importProfile.getDefaultMailType(),
                    ImportUtils.getBooleanAsInt(importProfile.getUpdateAllDuplicates()));
        } else {
            logSqlStatement(logger, INSERT_MYSQL);
            SqlUpdate sqlUpdate = new SqlUpdate(getDataSource(), INSERT_MYSQL,
                    new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER,
                            Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR,
                            Types.BOOLEAN, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.BOOLEAN });
            sqlUpdate.setReturnGeneratedKeys(true);
            sqlUpdate.setGeneratedKeysColumnNames(new String[] { FIELD_ID });
            sqlUpdate.compile();
            GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
            Object[] values = new Object[] { importProfile.getCompanyId(), importProfile.getAdminId(),
                    importProfile.getName(), importProfile.getSeparator(), importProfile.getTextRecognitionChar(),
                    importProfile.getCharset(), importProfile.getDateFormat(), importProfile.getImportMode(),
                    importProfile.getNullValuesAction(), importProfile.getKeyColumn(),
                    ImportUtils.getBooleanAsInt(importProfile.getExtendedEmailCheck()),
                    importProfile.getMailForReport(), importProfile.getCheckForDuplicates(),
                    importProfile.getDefaultMailType(),
                    ImportUtils.getBooleanAsInt(importProfile.getUpdateAllDuplicates()) };
            sqlUpdate.update(values, generatedKeyHolder);
            profileId = generatedKeyHolder.getKey().intValue();
        }

        importProfile.setId(profileId);
        insertColumnMappings(importProfile.getColumnMapping(), importProfile.getId());
        insertGenderMappings(importProfile.getGenderMapping(), importProfile.getId());

        return importProfile.getId();
    }

    public void updateImportProfile(ImportProfile importProfile) {
        logSqlStatement(logger, UPDATE);
        getSimpleJdbcTemplate().update(UPDATE, importProfile.getCompanyId(), importProfile.getAdminId(),
                importProfile.getName(), importProfile.getSeparator(), importProfile.getTextRecognitionChar(),
                importProfile.getCharset(), importProfile.getDateFormat(), importProfile.getImportMode(),
                importProfile.getNullValuesAction(), importProfile.getKeyColumn(),
                ImportUtils.getBooleanAsInt(importProfile.getExtendedEmailCheck()),
                importProfile.getMailForReport(), importProfile.getCheckForDuplicates(),
                importProfile.getDefaultMailType(),
                ImportUtils.getBooleanAsInt(importProfile.getUpdateAllDuplicates()), importProfile.getId());

        logSqlStatement(logger, DELETE_COLUMN_MAPPINGS, importProfile.getId());
        getSimpleJdbcTemplate().update(DELETE_COLUMN_MAPPINGS, importProfile.getId());
        insertColumnMappings(importProfile.getColumnMapping(), importProfile.getId());

        logSqlStatement(logger, DELETE_GENDER_MAPPINGS, importProfile.getId());
        getSimpleJdbcTemplate().update(DELETE_GENDER_MAPPINGS, importProfile.getId());
        insertGenderMappings(importProfile.getGenderMapping(), importProfile.getId());
    }

    public ImportProfile getImportProfileById(int id) {
        try {
            logSqlStatement(logger, SELECT_BY_ID, id);
            return getSimpleJdbcTemplate().queryForObject(SELECT_BY_ID, new ImportProfileRowMapper(), id);
        } catch (DataAccessException e) {
            // No ImportProfile found
            return null;
        }
    }

    public ImportProfile getImportProfileByShortname(String shortname) {
        try {
            logSqlStatement(logger, SELECT_BY_SHORTNAME, shortname);
            return getSimpleJdbcTemplate().queryForObject(SELECT_BY_SHORTNAME, new ImportProfileRowMapper(),
                    shortname);
        } catch (DataAccessException e) {
            // No ImportProfile found
            return null;
        }
    }

    public List<ImportProfile> getImportProfilesByCompanyId(int companyId) {
        logSqlStatement(logger, SELECT_BY_COMPANYID, companyId);
        return getSimpleJdbcTemplate().query(SELECT_BY_COMPANYID, new ImportProfileRowMapper(), companyId);
    }

    public void deleteImportProfile(ImportProfile profile) {
        deleteImportProfileById(profile.getId());
    }

    public void deleteImportProfileById(int profileId) {
        logSqlStatement(logger, DELETE, profileId);
        getSimpleJdbcTemplate().update(DELETE, profileId);
        logSqlStatement(logger, DELETE_COLUMN_MAPPINGS, profileId);
        getSimpleJdbcTemplate().update(DELETE_COLUMN_MAPPINGS, profileId);
        logSqlStatement(logger, DELETE_GENDER_MAPPINGS, profileId);
        getSimpleJdbcTemplate().update(DELETE_GENDER_MAPPINGS, profileId);
    }

    private void insertColumnMappings(List<ColumnMapping> mappings, int importProfileId) {
        if (mappings != null && !mappings.isEmpty()) {
            String insertStatementString = null;
            if (AgnUtils.isOracleDB()) {
                insertStatementString = INSERT_COLUMN_MAPPINGS_ORACLE;
            } else {
                insertStatementString = INSERT_COLUMN_MAPPINGS_MYSQL;
            }

            List<Object[]> parameterList = new ArrayList<Object[]>();
            for (ColumnMapping mapping : mappings) {
                parameterList.add(new Object[] { mapping.getProfileId(), mapping.getFileColumn(),
                        mapping.getDatabaseColumn(), mapping.getMandatory(), mapping.getDefaultValue() });
            }
            logSqlStatement(logger, insertStatementString);
            getSimpleJdbcTemplate().batchUpdate(insertStatementString, parameterList);
        }
    }

    private void insertGenderMappings(Map<String, Integer> mappings, int importProfileId) {
        if (mappings != null && !mappings.isEmpty()) {
            String insertStatementString = null;
            if (AgnUtils.isOracleDB()) {
                insertStatementString = INSERT_GENDER_MAPPINGS_ORACLE;
            } else {
                insertStatementString = INSERT_GENDER_MAPPINGS_MYSQL;
            }

            List<Object[]> parameterList = new ArrayList<Object[]>();
            for (Entry<String, Integer> entry : mappings.entrySet()) {
                parameterList.add(new Object[] { importProfileId, entry.getValue(), entry.getKey() });
            }
            logSqlStatement(logger, insertStatementString);
            getSimpleJdbcTemplate().batchUpdate(insertStatementString, parameterList);
        }
    }

    private class ImportProfileRowMapper implements ParameterizedRowMapper<ImportProfile> {
        @Override
        public ImportProfile mapRow(ResultSet resultSet, int row) throws SQLException {
            ImportProfile profile = new ImportProfileImpl();
            profile.setId(resultSet.getInt(FIELD_ID));
            profile.setName(resultSet.getString(FIELD_SHORTNAME));
            profile.setCompanyId(resultSet.getInt(FIELD_COMPANY_ID));
            profile.setAdminId(resultSet.getInt(FIELD_ADMIN_ID));
            profile.setSeparator(resultSet.getInt(FIELD_COLUMN_SEPARATOR));
            profile.setTextRecognitionChar(resultSet.getInt(FIELD_TEXT_DELIMITER));
            profile.setCharset(resultSet.getInt(FIELD_FILE_CHARSET));
            profile.setDateFormat(resultSet.getInt(FIELD_DATE_FORMAT));
            profile.setImportMode(resultSet.getInt(FIELD_IMPORT_MODE));
            profile.setKeyColumn(resultSet.getString(FIELD_KEY_COLUMN));
            profile.setCheckForDuplicates(resultSet.getInt(FIELD_CHECK_FOR_DUPLICATES));
            profile.setNullValuesAction(resultSet.getInt(FIELD_NULL_VALUES_ACTION));
            profile.setExtendedEmailCheck(resultSet.getBoolean(FIELD_EXT_EMAIL_CHECK));
            profile.setMailForReport(resultSet.getString(FIELD_REPORT_EMAIL));
            profile.setDefaultMailType(resultSet.getInt(FIELD_MAIL_TYPE));
            profile.setUpdateAllDuplicates(resultSet.getBoolean(FIELD_UPDATE_ALL_DUPLICATES));

            // Read additional data

            // Read ColumnMappings
            logSqlStatement(logger, SELECT_COLUMN_MAPPINGS, profile.getId());
            profile.setColumnMapping(getSimpleJdbcTemplate().query(SELECT_COLUMN_MAPPINGS,
                    new ColumnMappingRowMapper(), profile.getId()));

            // Read GenderMappings
            logSqlStatement(logger, SELECT_GENDER_MAPPINGS, profile.getId());
            List<Map<String, Object>> queryResult = getSimpleJdbcTemplate().queryForList(SELECT_GENDER_MAPPINGS,
                    profile.getId());
            Map<String, Integer> genderMappings = new HashMap<String, Integer>();
            for (Map<String, Object> resultSetRow : queryResult) {
                genderMappings.put((String) resultSetRow.get(GENDER_MAPPING_STRING_GENDER),
                        ((Number) resultSetRow.get(GENDER_MAPPING_INT_GENDER)).intValue());
            }
            profile.setGenderMapping(genderMappings);

            return profile;
        }
    }

    private class ColumnMappingRowMapper implements ParameterizedRowMapper<ColumnMapping> {
        @Override
        public ColumnMapping mapRow(ResultSet resultSet, int row) throws SQLException {
            ColumnMapping mapping = new ColumnMappingImpl();
            mapping.setId(resultSet.getInt(COLUMN_MAPPING_ID));
            mapping.setProfileId(resultSet.getInt(COLUMN_MAPPING_PROFILE_ID));
            mapping.setMandatory(resultSet.getBoolean(COLUMN_MAPPING_MANDATORY));
            mapping.setDatabaseColumn(resultSet.getString(COLUMN_MAPPING_DB_COLUMN));
            mapping.setFileColumn(resultSet.getString(COLUMN_MAPPING_FILE_COLUMN));
            String defaultValue = resultSet.getString(COLUMN_MAPPING_DEFAULT_VALUE);
            if (StringUtils.isNotEmpty(defaultValue)) {
                mapping.setDefaultValue(defaultValue);
            }
            return mapping;
        }
    }
}