Java tutorial
/********************************************************************************* * 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; } } }