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 org.agnitas.beans.BindingEntry; import org.agnitas.beans.ImportProfile; import org.agnitas.beans.ProfileRecipientFields; import org.agnitas.beans.impl.ImportKeyColumnsKey; import org.agnitas.beans.impl.PaginatedListImpl; import org.agnitas.dao.ImportRecipientsDao; import org.agnitas.service.NewImportWizardService; import org.agnitas.service.csv.Toolkit; import org.agnitas.service.impl.CSVColumnState; import org.agnitas.util.AgnUtils; import org.agnitas.util.ImportUtils; import org.agnitas.util.ImportRecipientsToolongValueException; import org.agnitas.util.importvalues.ImportMode; import org.agnitas.util.importvalues.NullValuesAction; import org.apache.commons.lang.StringUtils; import org.apache.commons.validator.GenericValidator; import org.apache.commons.validator.ValidatorResults; import org.apache.log4j.Logger; import org.displaytag.pagination.PaginatedList; import org.springframework.dao.DataAccessResourceFailureException; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DataSourceUtils; import org.springframework.jdbc.datasource.SingleConnectionDataSource; import org.springframework.jdbc.support.rowset.SqlRowSet; import javax.sql.DataSource; import java.sql.*; import java.text.MessageFormat; import java.text.SimpleDateFormat; import java.util.*; import java.util.Date; import java.math.BigDecimal; /** * @author Viktor Gema */ public class ImportRecipientsDaoImpl extends AbstractImportDao implements ImportRecipientsDao { private static final transient Logger logger = Logger.getLogger(ImportRecipientsDaoImpl.class); private SingleConnectionDataSource temporaryConnection; private static final SimpleDateFormat DB_DATE_FORMAT = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); public static final int MAX_WRITE_PROGRESS = 90; public static final int MAX_WRITE_PROGRESS_HALF = MAX_WRITE_PROGRESS / 2; @Override public LinkedHashMap<String, Map<String, Object>> getColumnInfoByColumnName(int companyId, String column) { DataSource ds = (DataSource) applicationContext.getBean("dataSource"); LinkedHashMap<String, Map<String, Object>> list = new LinkedHashMap<String, Map<String, Object>>(); ResultSet resultSet = null; Connection con = DataSourceUtils.getConnection(ds); try { if (AgnUtils.isOracleDB()) { resultSet = con.getMetaData().getColumns(null, AgnUtils.getDefaultValue("jdbc.username").toUpperCase(), "CUSTOMER_" + companyId + "_TBL", column.toUpperCase()); } else { resultSet = con.getMetaData().getColumns(null, null, "customer_" + companyId + "_tbl", column); } if (resultSet != null) { while (resultSet.next()) { String type; String col = resultSet.getString(4).toLowerCase(); Map<String, Object> mapping = new HashMap<String, Object>(); mapping.put("column", col); mapping.put("shortname", col); type = ImportUtils.dbtype2string(resultSet.getInt(5)); mapping.put("type", type); mapping.put("length", resultSet.getInt(7)); if (resultSet.getInt(11) == DatabaseMetaData.columnNullable) { mapping.put("nullable", 1); } else { mapping.put("nullable", 0); } list.put((String) mapping.get("shortname"), mapping); } } resultSet.close(); } catch (Exception e) { logger.error(MessageFormat.format("Failed to get column ({0}) info for admin ({1})", column, companyId), e); // TODO: Check this: is "admin" in combination with companyId correct here??? } finally { DataSourceUtils.releaseConnection(con, ds); } return list; } @Override public void createRecipients(final Map<ProfileRecipientFields, ValidatorResults> recipientBeansMap, final Integer adminID, final ImportProfile profile, final Integer type, int datasource_id, CSVColumnState[] columns) { if (recipientBeansMap.isEmpty()) { return; } final JdbcTemplate template = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; final ProfileRecipientFields[] recipients = recipientBeansMap.keySet() .toArray(new ProfileRecipientFields[recipientBeansMap.keySet().size()]); String keyColumn = profile.getKeyColumn(); List<String> keyColumns = profile.getKeyColumns(); String duplicateSql = ""; String duplicateSqlParams = ""; if (keyColumns.isEmpty()) { duplicateSql += " column_duplicate_check_0 "; duplicateSqlParams = "?"; } else { for (int i = 0; i < keyColumns.size(); i++) { duplicateSql += "column_duplicate_check_" + i; duplicateSqlParams += "?"; if (i != keyColumns.size() - 1) { duplicateSql += ","; duplicateSqlParams += ","; } } } final List<CSVColumnState> temporaryKeyColumns = new ArrayList<CSVColumnState>(); for (CSVColumnState column : columns) { if (keyColumns.isEmpty()) { if (column.getColName().equals(keyColumn) && column.getImportedColumn()) { temporaryKeyColumns.add(column); } } else { for (String columnName : keyColumns) { if (column.getColName().equals(columnName) && column.getImportedColumn()) { temporaryKeyColumns.add(column); break; } } } } final String query = "INSERT INTO " + tableName + " (recipient, validator_result, temporary_id, status_type, " + duplicateSql + ") VALUES (?,?,?,?," + duplicateSqlParams + ")"; final BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setBytes(1, ImportUtils.getObjectAsBytes(recipients[i])); ps.setBytes(2, ImportUtils.getObjectAsBytes(recipientBeansMap.get(recipients[i]))); ps.setString(3, recipients[i].getTemporaryId()); ps.setInt(4, type); for (int j = 0; j < temporaryKeyColumns.size(); j++) { setPreparedStatmentForCurrentColumn(ps, 5 + j, temporaryKeyColumns.get(j), recipients[i], profile, recipientBeansMap.get(recipients[i])); } if (logger.isInfoEnabled()) { logger.info("Import ID: " + profile.getImportId() + " Adding recipient to temp-table: " + Toolkit.getValueFromBean(recipients[i], profile.getKeyColumn())); } } public int getBatchSize() { return recipientBeansMap.size(); } }; template.batchUpdate(query, setter); } @Override public HashMap<ProfileRecipientFields, ValidatorResults> getRecipientsByType(int adminID, Integer[] types, int datasource_id) { final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; final String typesAsString = StringUtils.join(types, ","); String sqlStatement = "SELECT recipient, validator_result FROM " + tableName + " " + "WHERE status_type IN (" + typesAsString + ")"; List<Map> resultList = aTemplate.queryForList(sqlStatement); HashMap<ProfileRecipientFields, ValidatorResults> recipients = new HashMap<ProfileRecipientFields, ValidatorResults>(); for (Map row : resultList) { Object recipientBean = ImportUtils.deserialiseBean((byte[]) row.get("recipient")); final ProfileRecipientFields recipient = (ProfileRecipientFields) recipientBean; ValidatorResults validatorResults = null; if (row.get("validator_result") != null) { Object validatorResultsBean = ImportUtils.deserialiseBean((byte[]) row.get("validator_result")); validatorResults = (ValidatorResults) validatorResultsBean; } recipients.put(recipient, validatorResults); } return recipients; } @Override public Map<Integer, Integer> assiggnToMailingLists(List<Integer> mailingLists, int companyID, int datasourceId, int mode, int adminId, NewImportWizardService importWizardHelper) { Map<Integer, Integer> mailinglistStat = new HashMap<Integer, Integer>(); if (mailingLists == null || mailingLists.isEmpty() || mode == ImportMode.TO_BLACKLIST.getIntValue()) { return mailinglistStat; } JdbcTemplate jdbc = createJdbcTemplate(); String currentTimestamp = AgnUtils.getHibernateDialect().getCurrentTimestampSQLFunctionName(); String sql = null; importWizardHelper.setCompletedPercent(0); double count = 0; double diffComplete = 0; int newRecipientsCount = jdbc.queryForInt( "SELECT COUNT(*) FROM customer_" + companyID + "_tbl WHERE datasource_id = " + datasourceId); if (mode != ImportMode.ADD.getIntValue()) { count = newRecipientsCount != 0 ? (newRecipientsCount / NewImportWizardService.BLOCK_SIZE) * mailingLists.size() : 1; Integer[] types = { NewImportWizardService.RECIPIENT_TYPE_DUPLICATE_RECIPIENT }; final int updatedRecipients = getRecipientsCountByType(types, adminId, datasourceId); if (updatedRecipients > 0) { diffComplete = MAX_WRITE_PROGRESS_HALF / (count != 0 ? count : 1); } else { diffComplete = MAX_WRITE_PROGRESS / (count != 0 ? count : 1); } } else { count = newRecipientsCount != 0 ? (newRecipientsCount / NewImportWizardService.BLOCK_SIZE) * mailingLists.size() : 1; diffComplete = MAX_WRITE_PROGRESS / (count != 0 ? count : 1); } double intNumber = 0; // assign new recipients to mailing lists for (Integer mailingList : mailingLists) { mailinglistStat.put(mailingList, 0); if (mode == ImportMode.ADD.getIntValue() || mode == ImportMode.ADD_AND_UPDATE.getIntValue()) { int position = 1; int recipientIterator = newRecipientsCount; int added = 0; while (recipientIterator > 0 || (position == 1 && newRecipientsCount > 0)) { final ImportProfile importProfile = importWizardHelper.getImportProfile(); if (importProfile != null) { if (logger.isInfoEnabled()) { logger.info("Import ID: " + importProfile.getImportId() + " Assigning new recipients to mailinglist with ID " + mailingList + ", datasourceID: " + datasourceId); } } else { if (logger.isInfoEnabled()) { logger.info("Import ID is undefined"); } } if (AgnUtils.isMySQLDB()) { sql = "INSERT INTO customer_" + companyID + "_binding_tbl (customer_id, user_type, user_status, user_remark, creation_date, exit_mailing_id, mailinglist_id) " + "(SELECT customer_id, 'W', 1, 'CSV File Upload', " + currentTimestamp + ", 0," + mailingList + " FROM customer_" + companyID + "_tbl " + "WHERE datasource_id = " + datasourceId + " LIMIT " + (position - 1) * NewImportWizardService.BLOCK_SIZE + "," + NewImportWizardService.BLOCK_SIZE + " )"; } if (AgnUtils.isOracleDB()) { sql = "INSERT INTO customer_" + companyID + "_binding_tbl (customer_id, user_type, user_status, user_remark, creation_date, exit_mailing_id, mailinglist_id) " + "(SELECT customer_id, 'W', 1, 'CSV File Upload', " + currentTimestamp + ", 0," + mailingList + " FROM (SELECT customer_id, datasource_id, rownum r FROM customer_" + companyID + "_tbl WHERE datasource_id = " + datasourceId + " AND 1=1) " + " WHERE r BETWEEN " + (((position - 1) * NewImportWizardService.BLOCK_SIZE) + 1) + " AND " + (((position - 1) * NewImportWizardService.BLOCK_SIZE) + NewImportWizardService.BLOCK_SIZE) + " )"; } added = added + jdbc.update(sql); mailinglistStat.put(mailingList, added); intNumber = intNumber + diffComplete; if (intNumber >= 1) { importWizardHelper.setCompletedPercent( (int) (importWizardHelper.getCompletedPercent() + Math.floor(intNumber))); intNumber = intNumber - Math.floor(intNumber); } position++; recipientIterator = recipientIterator - NewImportWizardService.BLOCK_SIZE; } } } if (mode != ImportMode.ADD.getIntValue()) { Integer[] types = { NewImportWizardService.RECIPIENT_TYPE_DUPLICATE_RECIPIENT }; final int updatedRecipients = getRecipientsCountByType(types, adminId, datasourceId); count = count + updatedRecipients != 0 ? updatedRecipients / NewImportWizardService.BLOCK_SIZE : 1; if (newRecipientsCount > 0) { diffComplete = MAX_WRITE_PROGRESS_HALF / (count != 0 ? count : 1); } else { diffComplete = MAX_WRITE_PROGRESS / (count != 0 ? count : 1); } } // assign updated recipients to mailing lists if (mode != ImportMode.ADD.getIntValue()) { Integer[] types = { NewImportWizardService.RECIPIENT_TYPE_DUPLICATE_RECIPIENT }; int page = 0; int rowNum = NewImportWizardService.BLOCK_SIZE; HashMap<ProfileRecipientFields, ValidatorResults> recipients = null; while (recipients == null || recipients.size() >= rowNum) { recipients = getRecipientsByTypePaginated(types, page, rowNum, adminId, datasourceId); List<Integer> updatedRecipients = new ArrayList<Integer>(); for (ProfileRecipientFields recipient : recipients.keySet()) { if (recipient.getUpdatedIds() != null && !recipient.getUpdatedIds().isEmpty()) { updatedRecipients.addAll(recipient.getUpdatedIds()); } } updateMailinglists(mailingLists, companyID, datasourceId, mode, mailinglistStat, jdbc, currentTimestamp, updatedRecipients); page++; importWizardHelper .setCompletedPercent((int) (importWizardHelper.getCompletedPercent() + diffComplete)); } } importWizardHelper.setCompletedPercent(MAX_WRITE_PROGRESS); return mailinglistStat; } @Override public void removeTemporaryTable(String tableName, String sessionId) { if (AgnUtils.isOracleDB()) { final JdbcTemplate template = createJdbcTemplate(); try { String query = "select count(*) from user_tables where table_name = '" + tableName.toUpperCase() + "'"; int totalRows = template.queryForInt(query); if (totalRows != 0) { template.execute("DROP TABLE " + tableName); template.execute("DELETE FROM IMPORT_TEMPORARY_TABLES WHERE SESSION_ID='" + sessionId + "'"); } } catch (Exception e) { logger.error("deleteTemporaryTables: " + e.getMessage() + " (table: " + tableName + ")", e); } } } @Override public List<String> getTemporaryTableNamesBySessionId(String sessionId) { List<String> result = new ArrayList<String>(); final JdbcTemplate template = createJdbcTemplate(); String query = "SELECT TEMPORARY_TABLE_NAME FROM IMPORT_TEMPORARY_TABLES WHERE SESSION_ID='" + sessionId + "'"; List<Map> resultList = template.queryForList(query); for (Map row : resultList) { final String temporaryTableName = (String) row.get("TEMPORARY_TABLE_NAME"); result.add(temporaryTableName); } return result; } private void updateMailinglists(List<Integer> mailingLists, int companyID, int datasourceId, int mode, Map<Integer, Integer> mailinglistStat, JdbcTemplate jdbc, String currentTimestamp, List<Integer> updatedRecipients) { String sql; for (Integer mailinglistId : mailingLists) { try { if (mode == ImportMode.ADD.getIntValue() || mode == ImportMode.ADD_AND_UPDATE.getIntValue() || mode == ImportMode.UPDATE.getIntValue()) { int added = 0; createRecipientBindTemporaryTable(companyID, datasourceId, updatedRecipients, jdbc); sql = "DELETE FROM cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl WHERE customer_id IN (SELECT customer_id FROM customer_" + companyID + "_binding_tbl WHERE mailinglist_id=" + mailinglistId + ")"; jdbc.execute(sql); sql = "INSERT INTO customer_" + companyID + "_binding_tbl (customer_id, user_type, user_status, user_remark, creation_date, exit_mailing_id, mailinglist_id) (SELECT customer_id, 'W', 1, 'CSV File Upload', " + currentTimestamp + ", 0," + mailinglistId + " FROM cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl)"; added += jdbc.update(sql); mailinglistStat.put(mailinglistId, mailinglistStat.get(mailinglistId) + added); } else if (mode == ImportMode.MARK_OPT_OUT.getIntValue()) { int changed = changeStatusInMailingList(companyID, updatedRecipients, jdbc, mailinglistId, BindingEntry.USER_STATUS_OPTOUT, "Mass Opt-Out by Admin", currentTimestamp); mailinglistStat.put(mailinglistId, mailinglistStat.get(mailinglistId) + changed); } else if (mode == ImportMode.MARK_BOUNCED.getIntValue()) { int changed = changeStatusInMailingList(companyID, updatedRecipients, jdbc, mailinglistId, BindingEntry.USER_STATUS_BOUNCED, "Mass Bounce by Admin", currentTimestamp); mailinglistStat.put(mailinglistId, mailinglistStat.get(mailinglistId) + changed); } } catch (Exception e) { logger.error("writeContent: " + e.getMessage(), e); } finally { removeBindTemporaryTable(companyID, datasourceId, jdbc); } } } @Override public HashMap<ProfileRecipientFields, ValidatorResults> getRecipientsByTypePaginated(Integer[] types, int page, int rownums, Integer adminID, int datasourceId) { HashMap<ProfileRecipientFields, ValidatorResults> recipients = new HashMap<ProfileRecipientFields, ValidatorResults>(); if (types == null || types.length == 0) { return recipients; } final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasourceId + "_tbl"; String typesStr = "(" + StringUtils.join(types, ",") + ")"; int offset = (page) * rownums; String sqlStatement = "SELECT * FROM " + tableName + " WHERE status_type IN " + typesStr; if (AgnUtils.isMySQLDB()) { sqlStatement = sqlStatement + " LIMIT " + offset + " , " + rownums; } if (AgnUtils.isOracleDB()) { sqlStatement = "SELECT * FROM ( SELECT recipient, validator_result, rownum r FROM ( " + sqlStatement + " ) WHERE 1=1 ) WHERE r BETWEEN " + (offset + 1) + " AND " + (offset + rownums); } List<Map> tmpList = aTemplate.queryForList(sqlStatement); for (Map row : tmpList) { Object recipientBean = ImportUtils.deserialiseBean((byte[]) row.get("recipient")); final ProfileRecipientFields recipient = (ProfileRecipientFields) recipientBean; ValidatorResults validatorResults = null; if (row.get("validator_result") != null) { Object validatorResultsBean = ImportUtils.deserialiseBean((byte[]) row.get("validator_result")); validatorResults = (ValidatorResults) validatorResultsBean; } recipients.put(recipient, validatorResults); } return recipients; } @Override public int getRecipientsCountByType(Integer[] types, Integer adminID, int datasourceId) { final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasourceId + "_tbl"; String typesStr = "(" + StringUtils.join(types, ",") + ")"; int totalRows = aTemplate .queryForInt("SELECT count(temporary_id) FROM " + tableName + " WHERE status_type IN " + typesStr); return totalRows; } @Override public PaginatedList getInvalidRecipientList(CSVColumnState[] columns, String sort, String direction, int page, int rownums, int previousFullListSize, Integer adminID, int datasource_id) throws Exception { final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; int totalRows = aTemplate.queryForInt("SELECT count(temporary_id) FROM " + tableName + " WHERE status_type=" + NewImportWizardService.RECIPIENT_TYPE_FIELD_INVALID); if (previousFullListSize == 0 || previousFullListSize != totalRows) { page = 1; } int offset = (page - 1) * rownums; String sqlStatement = "SELECT * FROM " + tableName + " where status_type=" + NewImportWizardService.RECIPIENT_TYPE_FIELD_INVALID; if (AgnUtils.isMySQLDB()) { sqlStatement = sqlStatement + " LIMIT " + offset + " , " + rownums; } if (AgnUtils.isOracleDB()) { sqlStatement = "SELECT * from ( select recipient, validator_result, rownum r from ( " + sqlStatement + " ) where 1=1 ) where r between " + (offset + 1) + " and " + (offset + rownums); } List<Map> tmpList = aTemplate.queryForList(sqlStatement); List<Map> result = new ArrayList<Map>(); for (Map row : tmpList) { Map newBean = new HashMap(); final ProfileRecipientFields recipient = (ProfileRecipientFields) ImportUtils .deserialiseBean((byte[]) row.get("recipient")); final ValidatorResults validatorResult = (ValidatorResults) ImportUtils .deserialiseBean((byte[]) row.get("validator_result")); for (CSVColumnState column : columns) { if (column.getImportedColumn()) { newBean.put(column.getColName(), Toolkit.getValueFromBean(recipient, column.getColName())); } } newBean.put(NewImportWizardService.VALIDATOR_RESULT_RESERVED, validatorResult); newBean.put(NewImportWizardService.ERROR_EDIT_RECIPIENT_EDIT_RESERVED, recipient); result.add(newBean); } PaginatedListImpl paginatedList = new PaginatedListImpl(result, totalRows, rownums, page, sort, direction); return paginatedList; } @Override public Set<String> loadBlackList(int companyID) throws Exception { final JdbcTemplate jdbcTemplate = createJdbcTemplate(); SqlRowSet rset = null; Set<String> blacklist = new HashSet<String>(); try { if (AgnUtils.isOracleDB()) { // ignore cust_ban_tbl so that global blacklisted addresses can be imported to local blacklist rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust" + companyID + "_ban_tbl"); } else { rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust_ban_tbl"); } while (rset.next()) { blacklist.add(rset.getString(1).toLowerCase()); } } catch (Exception e) { logger.error("loadBlacklist: " + e.getMessage(), e); throw new Exception(e.getMessage()); } return blacklist; } @Override public HashMap<ProfileRecipientFields, ValidatorResults> getDuplicateRecipientsFromNewDataOnly( Map<ProfileRecipientFields, ValidatorResults> listOfValidBeans, ImportProfile profile, CSVColumnState[] columns, Integer adminID, int datasource_id) { final HashMap<ProfileRecipientFields, ValidatorResults> result = new HashMap<ProfileRecipientFields, ValidatorResults>(); if (listOfValidBeans.isEmpty()) { return result; } final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; final HashMap<ImportKeyColumnsKey, ProfileRecipientFields> columnKeyValueToTemporaryIdMap = new HashMap<ImportKeyColumnsKey, ProfileRecipientFields>(); final JdbcTemplate template = getJdbcTemplateForTemporaryTable(); List parameters = new ArrayList(); Map<String, List<Object>> parametersMap = new HashMap<String, List<Object>>(); String columnKeyBuffer = "("; for (ProfileRecipientFields profileRecipientFields : listOfValidBeans.keySet()) { ImportKeyColumnsKey keyValue = ImportKeyColumnsKey.createInstance(profile, profileRecipientFields, columns); if (columnKeyValueToTemporaryIdMap.containsKey(keyValue)) { result.put(profileRecipientFields, null); continue; } columnKeyBuffer += keyValue.getParametersString(); keyValue.addParameters(parametersMap); columnKeyValueToTemporaryIdMap.put(keyValue, profileRecipientFields); } columnKeyBuffer = columnKeyBuffer.substring(0, columnKeyBuffer.length() - 1); columnKeyBuffer = columnKeyBuffer + ")"; ImportKeyColumnsKey keyColumnsKey = columnKeyValueToTemporaryIdMap.keySet().iterator().next(); Iterator<String> keyColumnIterator = keyColumnsKey.getKeyColumnsMap().keySet().iterator(); StringBuffer sqlQuery = new StringBuffer("SELECT "); StringBuffer wherePart = new StringBuffer(""); int index = 0; while (keyColumnIterator.hasNext()) { String keyColumnName = keyColumnIterator.next(); CSVColumnState columnState = keyColumnsKey.getKeyColumnsMap().get(keyColumnName); String column = "i.column_duplicate_check_" + index; String columnAlias = ImportKeyColumnsKey.KEY_COLUMN_PREFIX + keyColumnName; sqlQuery.append(column + " AS " + columnAlias + ","); int type = columnState.getType(); if (AgnUtils.isOracleDB() && (keyColumnName.equals("email") || type == CSVColumnState.TYPE_NUMERIC || type == CSVColumnState.TYPE_DATE)) { wherePart.append(column); } else { wherePart.append("LOWER(" + column + ")"); } wherePart.append(" IN " + columnKeyBuffer + " AND "); // gather parameters List<Object> objectList = parametersMap.get(keyColumnName); if (objectList != null) { parameters.addAll(objectList); } index++; } sqlQuery.delete(sqlQuery.length() - 1, sqlQuery.length()); sqlQuery.append(" FROM " + tableName + " i WHERE ("); sqlQuery.append(wherePart); sqlQuery.append("(i.status_type=" + NewImportWizardService.RECIPIENT_TYPE_VALID + " OR i.status_type=" + NewImportWizardService.RECIPIENT_TYPE_FIXED_BY_HAND + " OR i.status_type=" + NewImportWizardService.RECIPIENT_TYPE_DUPLICATE_RECIPIENT + "))"); final List<Map> resultList = template.queryForList(sqlQuery.toString(), parameters.toArray()); for (Map row : resultList) { ImportKeyColumnsKey columnsKey = ImportKeyColumnsKey.createInstance(row); ProfileRecipientFields recipientFields = columnKeyValueToTemporaryIdMap.get(columnsKey); if (recipientFields != null) { result.put(recipientFields, null); } } return result; } private Date createDateValue(Date date) { return (AgnUtils.isOracleDB()) ? new Timestamp(date.getTime()) : new Timestamp(date.getTime()); } @Override public void updateRecipients(final Map<ProfileRecipientFields, ValidatorResults> recipientBeans, Integer adminID, final int type, final ImportProfile profile, int datasource_id, CSVColumnState[] columns) { if (recipientBeans.isEmpty()) { return; } final JdbcTemplate template = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; final ProfileRecipientFields[] recipients = recipientBeans.keySet() .toArray(new ProfileRecipientFields[recipientBeans.keySet().size()]); String keyColumn = profile.getKeyColumn(); List<String> keyColumns = profile.getKeyColumns(); String duplicateSql = ""; if (keyColumns.isEmpty()) { duplicateSql += " column_duplicate_check_0=? "; } else { for (int i = 0; i < keyColumns.size(); i++) { duplicateSql += " column_duplicate_check_" + i + "=? "; if (i != keyColumns.size() - 1) { duplicateSql += ", "; } } } final String query = "UPDATE " + tableName + " SET recipient=?, validator_result=?, status_type=?, " + duplicateSql + " WHERE temporary_id=?"; final List<CSVColumnState> temporaryKeyColumns = new ArrayList<CSVColumnState>(); for (CSVColumnState column : columns) { if (keyColumns.isEmpty()) { if (column.getColName().equals(keyColumn) && column.getImportedColumn()) { temporaryKeyColumns.add(column); } } else { for (String columnName : keyColumns) { if (column.getColName().equals(columnName) && column.getImportedColumn()) { temporaryKeyColumns.add(column); break; } } } } final BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setBytes(1, ImportUtils.getObjectAsBytes(recipients[i])); ps.setBytes(2, ImportUtils.getObjectAsBytes(recipientBeans.get(recipients[i]))); ps.setInt(3, type); for (int j = 0; j < temporaryKeyColumns.size(); j++) { setPreparedStatmentForCurrentColumn(ps, 4 + j, temporaryKeyColumns.get(j), recipients[i], profile, recipientBeans.get(recipients[i])); } ps.setString(4 + temporaryKeyColumns.size(), recipients[i].getTemporaryId()); if (logger.isInfoEnabled()) { logger.info("Import ID: " + profile.getImportId() + " Updating recipient in temp-table: " + Toolkit.getValueFromBean(recipients[i], profile.getKeyColumn())); } } public int getBatchSize() { return recipientBeans.size(); } }; template.batchUpdate(query, setter); } @Override public void addNewRecipients(final Map<ProfileRecipientFields, ValidatorResults> validRecipients, Integer adminId, final ImportProfile importProfile, final CSVColumnState[] columns, final int datasourceID) throws Exception { if (validRecipients.isEmpty()) { return; } String currentTimestamp = AgnUtils.getHibernateDialect().getCurrentTimestampSQLFunctionName(); final JdbcTemplate template = createJdbcTemplate(); final ProfileRecipientFields[] recipientsBean = validRecipients.keySet() .toArray(new ProfileRecipientFields[validRecipients.size()]); final int[] newcustomerIDs = getNextCustomerSequences(importProfile.getCompanyId(), recipientsBean.length); final String tableName = "customer_" + importProfile.getCompanyId() + "_tbl"; String query = "INSERT INTO " + tableName + " ("; if (AgnUtils.isOracleDB()) { query = query + "customer_id,"; } boolean isGenderMapped = false; query = query + "mailtype, datasource_id, "; for (CSVColumnState column : columns) { if (column.getColName().equals("creation_date")) { throw new Exception(" creation_date column is not allowed to be imported"); } if (column.getImportedColumn() && !column.getColName().equals("mailtype")) { query = query + column.getColName() + ", "; } if (column.getImportedColumn() && column.getColName().equals("gender")) { isGenderMapped = true; } } if (!isGenderMapped) { if (AgnUtils.isOracleDB()) { query = query + "gender, "; } } query = query.substring(0, query.length() - 2); query = query + ", creation_date) VALUES ("; if (AgnUtils.isOracleDB()) { query = query + "?, "; } for (CSVColumnState column : columns) { if (column.getImportedColumn() && !column.getColName().equals("mailtype")) { query = query + "?, "; } } if (!isGenderMapped) { if (AgnUtils.isOracleDB()) { query = query + "?, "; } } query = query + "?, ?, "; query = query.substring(0, query.length() - 2); query = query + ", " + currentTimestamp + ")"; final Boolean finalIsGenderMapped = isGenderMapped; final BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ProfileRecipientFields profileRecipientFields = recipientsBean[i]; Integer mailtype = Integer.valueOf(profileRecipientFields.getMailtype()); int index = 0; if (AgnUtils.isOracleDB()) { ps.setInt(1, newcustomerIDs[i]); ps.setInt(2, mailtype); ps.setInt(3, datasourceID); index = 4; } if (AgnUtils.isMySQLDB()) { ps.setInt(1, mailtype); ps.setInt(2, datasourceID); index = 3; } for (CSVColumnState column : columns) { if (column.getImportedColumn() && !column.getColName().equals("mailtype")) { setPreparedStatmentForCurrentColumn(ps, index, column, profileRecipientFields, importProfile, null); index++; } } if (!finalIsGenderMapped) { if (AgnUtils.isOracleDB()) { ps.setInt(index, 2); } } if (logger.isInfoEnabled()) { logger.info("Import ID: " + importProfile.getImportId() + " Adding recipient to recipient-table: " + Toolkit.getValueFromBean(profileRecipientFields, importProfile.getKeyColumn())); } } public int getBatchSize() { return validRecipients.size(); } }; template.batchUpdate(query, setter); } private void setPreparedStatmentForCurrentColumn(PreparedStatement ps, int index, CSVColumnState column, ProfileRecipientFields bean, ImportProfile importProfile, ValidatorResults validatorResults) throws SQLException { String value = Toolkit.getValueFromBean(bean, column.getColName()); if (column.getType() == CSVColumnState.TYPE_NUMERIC && column.getColName().equals("gender")) { if (StringUtils.isEmpty(value) || value == null) { ps.setInt(index, 2); } else { if (GenericValidator.isInt(value) && Integer.valueOf(value) <= 5 && Integer.valueOf(value) >= 0) { ps.setInt(index, Integer.valueOf(value)); } else { final Integer intValue = importProfile.getGenderMapping().get(value); ps.setInt(index, intValue); } } } else if (column.getType() == CSVColumnState.TYPE_CHAR) { if (value == null) { ps.setNull(index, Types.VARCHAR); } else { String columnName = column.getColName(); if (columnName.equals("email")) { value = value.toLowerCase(); if (validatorResults != null && !ImportUtils.checkIsCurrentFieldValid(validatorResults, "email", "checkRange")) { throw new ImportRecipientsToolongValueException(value); } } else if (importProfile.getKeyColumns().contains(columnName) || (importProfile.getKeyColumns().isEmpty() && columnName.equals(importProfile.getKeyColumn()))) { // range validation for keyColumn if (validatorResults != null && !ImportUtils.checkIsCurrentFieldValid(validatorResults, columnName, "checkRange")) { throw new ImportRecipientsToolongValueException(value); } } if (AgnUtils.isOracleDB()) { ps.setString(index, value); } else if (AgnUtils.isMySQLDB()) { if (column.isNullable() && value.isEmpty()) { ps.setNull(index, Types.VARCHAR); } else { ps.setString(index, value); } } } } else if (column.getType() == CSVColumnState.TYPE_NUMERIC) { if (StringUtils.isEmpty(value) || value == null) { ps.setNull(index, Types.NUMERIC); } else { ps.setDouble(index, Double.valueOf(value)); } } else if (column.getType() == CSVColumnState.TYPE_DATE) { if (StringUtils.isEmpty(value) || value == null) { ps.setNull(index, Types.DATE); } else { Date date = ImportUtils.getDateAsString(value, importProfile.getDateFormat()); ps.setTimestamp(index, new Timestamp(date.getTime())); } } } @Override public HashMap<ProfileRecipientFields, ValidatorResults> getDuplicateRecipientsFromExistData( Map<ProfileRecipientFields, ValidatorResults> listOfValidBeans, ImportProfile profile, CSVColumnState[] columns) { final HashMap<ProfileRecipientFields, ValidatorResults> result = new HashMap<ProfileRecipientFields, ValidatorResults>(); if (listOfValidBeans.isEmpty()) { return result; } final HashMap<ImportKeyColumnsKey, ProfileRecipientFields> columnKeyValueToTemporaryIdMap = new HashMap<ImportKeyColumnsKey, ProfileRecipientFields>(); final JdbcTemplate template = createJdbcTemplate(); List parameters = new ArrayList(); Map<String, List<Object>> parametersMap = new HashMap<String, List<Object>>(); String columnKeyBuffer = "("; for (ProfileRecipientFields profileRecipientFields : listOfValidBeans.keySet()) { ImportKeyColumnsKey keyValue = ImportKeyColumnsKey.createInstance(profile, profileRecipientFields, columns); if (columnKeyValueToTemporaryIdMap.containsKey(keyValue)) { result.put(profileRecipientFields, null); continue; } columnKeyBuffer += keyValue.getParametersString(); keyValue.addParameters(parametersMap); columnKeyValueToTemporaryIdMap.put(keyValue, profileRecipientFields); } columnKeyBuffer = columnKeyBuffer.substring(0, columnKeyBuffer.length() - 1); columnKeyBuffer = columnKeyBuffer + ")"; ImportKeyColumnsKey keyColumnsKey = columnKeyValueToTemporaryIdMap.keySet().iterator().next(); Iterator<String> keyColumnIterator = keyColumnsKey.getKeyColumnsMap().keySet().iterator(); StringBuffer sqlQuery = new StringBuffer("SELECT customer_id, "); StringBuffer wherePart = new StringBuffer(""); Map<String, Integer> columnTypes = new HashMap<String, Integer>(); while (keyColumnIterator.hasNext()) { String keyColumnName = keyColumnIterator.next(); CSVColumnState columnState = keyColumnsKey.getKeyColumnsMap().get(keyColumnName); String column = keyColumnName; String columnAlias = ImportKeyColumnsKey.KEY_COLUMN_PREFIX + keyColumnName; sqlQuery.append(column + " AS " + columnAlias + ","); int type = columnState.getType(); if (AgnUtils.isOracleDB() && (keyColumnName.equals("email") || type == CSVColumnState.TYPE_NUMERIC || type == CSVColumnState.TYPE_DATE)) { wherePart.append(column); } else { wherePart.append("LOWER(" + column + ")"); } wherePart.append(" IN " + columnKeyBuffer + " AND "); // gather parameters List<Object> objectList = parametersMap.get(keyColumnName); if (objectList != null) { parameters.addAll(objectList); } columnTypes.put(columnAlias, type); } sqlQuery.delete(sqlQuery.length() - 1, sqlQuery.length()); wherePart.delete(wherePart.length() - 4, wherePart.length()); sqlQuery.append(" FROM customer_" + profile.getCompanyId() + "_tbl c WHERE ("); sqlQuery.append(wherePart); sqlQuery.append(")"); final List<Map> resultList = template.queryForList(sqlQuery.toString(), parameters.toArray()); for (Map row : resultList) { ImportKeyColumnsKey columnsKey = ImportKeyColumnsKey.createInstance(row); ProfileRecipientFields recipientFields = columnKeyValueToTemporaryIdMap.get(columnsKey); if (recipientFields != null) { result.put(recipientFields, null); if (profile.getUpdateAllDuplicates() || (recipientFields.getUpdatedIds() == null || recipientFields.getUpdatedIds().size() == 0)) { recipientFields.addUpdatedIds(((Number) row.get("customer_id")).intValue()); } } } return result; } @Override public void updateExistRecipients(final Collection<ProfileRecipientFields> recipientsForUpdate, final ImportProfile importProfile, final CSVColumnState[] columns, Integer adminId) { if (recipientsForUpdate.isEmpty()) { return; } final JdbcTemplate template = createJdbcTemplate(); final ProfileRecipientFields[] recipientsBean = recipientsForUpdate .toArray(new ProfileRecipientFields[recipientsForUpdate.size()]); final String[] querys = new String[recipientsForUpdate.size()]; for (int i = 0; i < querys.length; i++) { String query = "UPDATE customer_" + importProfile.getCompanyId() + "_tbl SET "; if (recipientsBean[i].getMailtypeDefined().equals(ImportUtils.MAIL_TYPE_DEFINED)) query = query + "mailtype=" + recipientsBean[i].getMailtype() + ", "; for (CSVColumnState column : columns) { if (column.getImportedColumn() && !column.getColName().equals("mailtype")) { String value = Toolkit.getValueFromBean(recipientsBean[i], column.getColName()); // @todo: agn: value == null if (StringUtils.isEmpty(value) && importProfile.getNullValuesAction() == NullValuesAction.OVERWRITE.getIntValue() && !column.getColName().equals("gender")) { query = query + column.getColName() + "=NULL, "; } else if (!StringUtils.isEmpty(value)) { if (column.getColName().equals("gender")) { if (StringUtils.isEmpty(value)) { query = query + column.getColName() + "=2, "; } else { if (GenericValidator.isInt(value)) { query = query + column.getColName() + "=" + value + ", "; } else { final Integer intValue = importProfile.getGenderMapping().get(value); query = query + column.getColName() + "=" + intValue + ", "; } } } else { switch (column.getType()) { case CSVColumnState.TYPE_CHAR: if (column.getColName().equals("email")) { value = value.toLowerCase(); } if (AgnUtils.isOracleDB()) { query = query + column.getColName() + "='" + value.replace("'", "''") + "', "; } else if (AgnUtils.isMySQLDB()) { query = query + column.getColName() + "='" + value.replace("\\", "\\\\").replace("'", "\\'") + "', "; } break; case CSVColumnState.TYPE_NUMERIC: query = query + column.getColName() + "=" + value + ", "; break; case CSVColumnState.TYPE_DATE: if (StringUtils.isEmpty(value) || value == null) { query = query + column.getColName() + "=null, "; } else { final int format = importProfile.getDateFormat(); Date date = ImportUtils.getDateAsString(value, format); if (AgnUtils.isMySQLDB()) { String temTimestamp = new Timestamp(date.getTime()).toString(); query = query + column.getColName() + "='" + temTimestamp.substring(0, temTimestamp.indexOf(" ")) + "', "; } if (AgnUtils.isOracleDB()) { final String dateAsFormatedString = DB_DATE_FORMAT.format(date); query = query + column.getColName() + "=to_date('" + dateAsFormatedString + "', 'dd.MM.YYYY HH24:MI:SS'), "; } } break; } } } } } query = query.substring(0, query.length() - 2); String value = Toolkit.getValueFromBean(recipientsBean[i], importProfile.getKeyColumn()); value = value.toLowerCase(); if (!importProfile.getUpdateAllDuplicates()) { query = query + " WHERE customer_id = " + recipientsBean[i].getUpdatedIds().get(0); } else { query = query + " WHERE customer_id IN("; final int countUpdatedRecipients = recipientsBean[i].getUpdatedIds().size(); for (int index = 0; index < countUpdatedRecipients; index++) { query = query + recipientsBean[i].getUpdatedIds().get(index) + ((index + 1) != countUpdatedRecipients ? "," : ""); } query = query + ")"; } if (logger.isInfoEnabled()) { logger.info("Import ID: " + importProfile.getImportId() + " Updating recipient in recipient-table: " + Toolkit.getValueFromBean(recipientsBean[i], importProfile.getKeyColumn())); } querys[i] = query; } template.batchUpdate(querys); } @Override public void importInToBlackList(final Collection<ProfileRecipientFields> recipients, final int companyId) { if (recipients.isEmpty()) { return; } final JdbcTemplate template = createJdbcTemplate(); final ProfileRecipientFields[] recipientsArray = recipients .toArray(new ProfileRecipientFields[recipients.size()]); String query; if (AgnUtils.isOracleDB()) { query = "INSERT INTO cust" + companyId + "_ban_tbl (email) VALUES (?)"; } else { query = "INSERT INTO cust_ban_tbl (company_id, email) VALUES (?,?)"; } final BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { if (AgnUtils.isOracleDB()) { ps.setString(1, recipientsArray[i].getEmail().toLowerCase()); } else { ps.setInt(1, companyId); ps.setString(2, recipientsArray[i].getEmail().toLowerCase()); } } public int getBatchSize() { return recipients.size(); } }; template.batchUpdate(query, setter); } @Override public void createTemporaryTable(int adminID, int datasource_id, String keyColumn, int companyId, String sessionId) { createTemporaryTable(adminID, datasource_id, keyColumn, new ArrayList<String>(), companyId, sessionId); } @Override public void createTemporaryTable(int adminID, int datasource_id, String keyColumn, List<String> keyColumns, int companyId, String sessionId) { final DataSource dataSource = (DataSource) applicationContext.getBean("dataSource"); try { if (temporaryConnection != null) { temporaryConnection.destroy(); temporaryConnection = null; } SingleConnectionDataSource scds = null; scds = new SingleConnectionDataSource(dataSource.getConnection(), true); setTemporaryConnection(scds); } catch (SQLException e) { throw new DataAccessResourceFailureException("Unable to create single connection data source", e); } final JdbcTemplate template = getJdbcTemplateForTemporaryTable(); final String prefix = "cust_" + adminID + "_tmp_"; final String tableName = prefix + datasource_id + "_tbl"; String indexSql = ""; String duplicateSql = ""; if (keyColumns.isEmpty()) { duplicateSql += keyColumn + " as column_duplicate_check_0 "; indexSql = "column_duplicate_check_0"; } else { for (int i = 0; i < keyColumns.size(); i++) { duplicateSql += keyColumns.get(i) + " as column_duplicate_check_" + i; indexSql += "column_duplicate_check_" + i; if (i != keyColumns.size() - 1) { duplicateSql += ", "; indexSql += ", "; } } } duplicateSql += " from customer_" + companyId + "_tbl where 1=0)"; if (AgnUtils.isMySQLDB()) { String query = "CREATE TEMPORARY TABLE IF NOT EXISTS " + tableName + " as (select "; query += duplicateSql; template.execute(query); query = "ALTER TABLE " + tableName + " ADD (recipient mediumblob NOT NULL, " + "validator_result mediumblob NOT NULL, " + "temporary_id varchar(128) NOT NULL, " + "INDEX (" + indexSql + "), " + "status_type int(3) NOT NULL)"; template.execute(query); query = "alter table " + tableName + " collate utf8_unicode_ci"; template.execute(query); } else if (AgnUtils.isOracleDB()) { // @todo: we need to decide when all those tables will be removed String query = "CREATE TABLE " + tableName + " as (select "; query += duplicateSql; template.execute(query); query = "ALTER TABLE " + tableName + " ADD (recipient blob NOT NULL, " + "validator_result blob NOT NULL, " + "temporary_id varchar2(128) NOT NULL, " + "status_type number(3) NOT NULL)"; template.execute(query); String indexquery = "create index " + tableName + "_cdc on " + tableName + " (" + indexSql + ") nologging"; template.execute(indexquery); query = " INSERT INTO IMPORT_TEMPORARY_TABLES (SESSION_ID, TEMPORARY_TABLE_NAME) VALUES('" + sessionId + "', '" + tableName + "')"; template.execute(query); } } private int changeStatusInMailingList(int companyID, List<Integer> updatedRecipients, JdbcTemplate jdbc, int mailinglistId, int newStatus, String remark, String currentTimestamp) { if (updatedRecipients.size() == 0) { return 0; } String recipientsStr = StringUtils.join(updatedRecipients, ','); String sql = "UPDATE customer_" + companyID + "_binding_tbl SET user_status=" + newStatus + ", exit_mailing_id=0, user_remark='" + remark + "', " + AgnUtils.changeDateName() + "=" + currentTimestamp + " WHERE mailinglist_id=" + mailinglistId + " AND customer_id IN (" + recipientsStr + ") AND user_status=" + BindingEntry.USER_STATUS_ACTIVE; return jdbc.update(sql); } private void createRecipientBindTemporaryTable(int companyID, int datasourceId, final List<Integer> updatedRecipients, JdbcTemplate jdbc) { String sql = removeBindTemporaryTable(companyID, datasourceId, jdbc); if (AgnUtils.isMySQLDB()) { sql = "CREATE TEMPORARY TABLE cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl (`customer_id` int(10) unsigned NOT NULL)"; } else if (AgnUtils.isOracleDB()) { sql = "CREATE TABLE cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl (customer_id NUMBER(10) NOT NULL)"; } jdbc.execute(sql); if (updatedRecipients.isEmpty()) { return; } sql = "INSERT INTO cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl (customer_id) VALUES (?)"; final BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, updatedRecipients.get(i)); } public int getBatchSize() { return updatedRecipients.size(); } }; jdbc.batchUpdate(sql, setter); } private String removeBindTemporaryTable(int companyID, int datasourceId, JdbcTemplate jdbc) { final String tablename = "cust_" + companyID + "_exist1_tmp" + datasourceId + "_tbl"; if (AgnUtils.isOracleDB()) { String query = "select count(*) from user_tables where table_name = '" + tablename.toUpperCase() + "'"; int totalRows = jdbc.queryForInt(query); if (totalRows != 0) { String sql = "DROP TABLE " + tablename; jdbc.execute(sql); } } if (AgnUtils.isMySQLDB()) { String sql = "DROP TABLE IF EXISTS " + tablename; try { jdbc.execute(sql); } catch (Exception e) { if (logger.isInfoEnabled()) { logger.info("Tried to remove table that doesn't exist", e); } } } return ""; } private int getNextCustomerSequence(int companyID, JdbcTemplate template) { String query = " SELECT customer_" + companyID + "_tbl_seq.nextval FROM DUAL "; return template.queryForInt(query); } private int[] getNextCustomerSequences(int companyID, int amount) { int[] customerids = new int[amount]; if (AgnUtils.isOracleDB()) { JdbcTemplate template = createJdbcTemplate(); for (int i = 0; i < amount; i++) { customerids[i] = getNextCustomerSequence(companyID, template); } } return customerids; } private JdbcTemplate getJdbcTemplateForTemporaryTable() { return new JdbcTemplate(temporaryConnection); } @Override public SingleConnectionDataSource getTemporaryConnection() { return temporaryConnection; } @Override public void setTemporaryConnection(SingleConnectionDataSource temporaryConnection) { this.temporaryConnection = temporaryConnection; } }