Example usage for org.springframework.jdbc.core JdbcTemplate execute

List of usage examples for org.springframework.jdbc.core JdbcTemplate execute

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. execute

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate execute.

Prototype

@Override
    public void execute(final String sql) throws DataAccessException

Source Link

Usage

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public void removeTemporaryTable(String tableName, String sessionId) {
    if (AgnUtils.isOracleDB()) {
        final JdbcTemplate template = createJdbcTemplate();
        try {//from   ww  w  . ja  v  a2 s .  c o  m
            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);
        }
    }
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

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;/*w w w  .j  a  v a 2  s  .co  m*/
    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);
        }
    }
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@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 {// w  ww. j  a v a 2 s.co m
        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);
    }
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

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)";
    }/* w w w  .j ava2  s.  com*/
    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);
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

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);
        }/*from   w ww . ja v a 2 s  .  c om*/
    }

    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 "";
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Inserts new customer record in Database with a fresh customer-id
 *
 * @return true on success//from   w  ww .  ja  v  a 2 s  .  co  m
 */
@Override
public int insertNewCust(Recipient cust) {
    StringBuffer Columns = new StringBuffer("(");
    StringBuffer Values = new StringBuffer(" VALUES (");
    String aColumn = null;
    String aParameter = null;
    String ColType = null;
    int intValue = 0;
    int day, month, year;
    int hour = 0;
    int minute = 0;
    int second = 0;
    StringBuffer insertCust = new StringBuffer("INSERT INTO customer_" + cust.getCompanyID() + "_tbl ");
    boolean appendIt = false;
    boolean hasDefault = false;
    String appendColumn = null;
    String appendValue = null;
    NumberFormat aFormat1 = null;
    NumberFormat aFormat2 = null;

    if (cust.getCustDBStructure() == null) {
        cust.loadCustDBStructure();
    }
    // logic from former method getNewCustomerID
    String sqlStatement = null;
    int customerID = 0;
    int companyID = cust.getCompanyID();
    if (companyID == 0) {
        return customerID;
    }
    if (mayAdd(companyID, 1) == false) {
        return customerID;
    }
    try {
        // set customerID for Oracle
        if (AgnUtils.isOracleDB()) {
            JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
            sqlStatement = "select customer_" + companyID + "_tbl_seq.nextval FROM dual";
            customerID = tmpl.queryForInt(sqlStatement);
            cust.setCustomerID(customerID);
        }
    } catch (Exception e) {
        logger.error("Error inserting new customer", e);
        customerID = 0;

        return customerID;
    }

    if (logger.isDebugEnabled()) {
        logger.debug("new customerID: " + customerID);
    }

    // Oracle: put customerID in SQL statement at first
    // (MySQL: no customerID available, yet)
    if (AgnUtils.isOracleDB()) {
        Columns.append("customer_id");
        Values.append(Integer.toString(cust.getCustomerID()));
    }
    Iterator<String> i = cust.getCustDBStructure().keySet().iterator();
    while (i.hasNext()) {
        aColumn = i.next();
        ColType = cust.getCustDBStructure().get(aColumn);
        appendIt = false;
        hasDefault = false;
        if (!aColumn.equalsIgnoreCase("customer_id")) {
            if (aColumn.equalsIgnoreCase("creation_date") || aColumn.equalsIgnoreCase("timestamp")
                    || aColumn.equalsIgnoreCase("change_date")) {
                appendValue = "current_timestamp";
                appendColumn = aColumn;
                appendIt = true;
            } else if (ColType.equalsIgnoreCase("DATE")) {
                if (cust.getCustParameters(aColumn + "_DAY_DATE") != null
                        && cust.getCustParameters(aColumn + "_MONTH_DATE") != null
                        && cust.getCustParameters(aColumn + "_YEAR_DATE") != null) {
                    aFormat1 = new DecimalFormat("00");
                    aFormat2 = new DecimalFormat("0000");
                    try {
                        if (!cust.getCustParameters(aColumn + "_DAY_DATE").trim().equals("")) {
                            day = Integer.parseInt(cust.getCustParameters(aColumn + "_DAY_DATE"));
                            month = Integer.parseInt(cust.getCustParameters(aColumn + "_MONTH_DATE"));
                            year = Integer.parseInt(cust.getCustParameters(aColumn + "_YEAR_DATE"));
                            if ((cust.getCustParameters(aColumn + "_HOUR_DATE") != null)
                                    && !cust.getCustParameters(aColumn + "_HOUR_DATE").trim().equals("")) {
                                hour = Integer.parseInt(cust.getCustParameters(aColumn + "_HOUR_DATE"));
                            }
                            if ((cust.getCustParameters(aColumn + "_MINUTE_DATE") != null)
                                    && !cust.getCustParameters(aColumn + "_MINUTE_DATE").trim().equals("")) {
                                minute = Integer.parseInt(cust.getCustParameters(aColumn + "_MINUTE_DATE"));
                            }
                            if ((cust.getCustParameters(aColumn + "_SECOND_DATE") != null)
                                    && !cust.getCustParameters(aColumn + "_SECOND_DATE").trim().equals("")) {
                                second = Integer.parseInt(cust.getCustParameters(aColumn + "_SECOND_DATE"));
                            }

                            if (AgnUtils.isOracleDB()) {
                                appendValue = "to_date('" + aFormat1.format(day) + "." + aFormat1.format(month)
                                        + "." + aFormat2.format(year) + " " + aFormat1.format(hour) + ":"
                                        + aFormat1.format(minute) + ":" + aFormat1.format(second)
                                        + "', 'DD.MM.YYYY HH24:MI:SS')";
                            } else {
                                appendValue = "STR_TO_DATE('" + aFormat1.format(day) + "-"
                                        + aFormat1.format(month) + "-" + aFormat2.format(year) + " "
                                        + aFormat1.format(hour) + ":" + aFormat1.format(minute) + ":"
                                        + aFormat1.format(second) + "', '%d-%m-%Y %H:%i:%s')";
                            }
                            appendColumn = aColumn;
                            appendIt = true;
                        } else {
                            ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);
                            if (tmp != null) {
                                String defaultValue = tmp.getDefaultValue();
                                if (!StringUtils.isBlank(defaultValue)) {
                                    appendValue = createDateDefaultValueExpression(defaultValue);

                                    hasDefault = true;
                                }
                            }
                            if (!hasDefault) {
                                appendValue = "null";
                            }
                            appendColumn = aColumn;
                            appendIt = true;
                        }
                    } catch (Exception e1) {
                        logger.error("insertNewCust: (" + aColumn + ") " + e1.getMessage(), e1);
                    }
                } else {
                    ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);

                    if (tmp != null) {
                        String defaultValue = tmp.getDefaultValue();

                        if (!StringUtils.isBlank(defaultValue)) {
                            appendValue = createDateDefaultValueExpression(defaultValue);

                            hasDefault = true;
                        }
                    }
                    if (hasDefault) {
                        appendColumn = aColumn;
                        appendIt = true;
                    }
                }
            }
            if (ColType.equalsIgnoreCase("INTEGER") || ColType.equalsIgnoreCase("DOUBLE")) {
                aParameter = cust.getCustParameters(aColumn);
                if (!StringUtils.isEmpty(aParameter)) {
                    try {
                        intValue = Integer.parseInt(aParameter);
                    } catch (Exception e1) {
                        intValue = 0;
                    }
                    appendValue = Integer.toString(intValue);
                    appendColumn = aColumn;
                    appendIt = true;
                } else {
                    ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);

                    if (tmp != null) {
                        String defaultValue = tmp.getDefaultValue();

                        if (!StringUtils.isBlank(defaultValue)) {
                            appendValue = defaultValue;
                            hasDefault = true;
                        }
                    }
                    if (hasDefault) {
                        appendColumn = aColumn;
                        appendIt = true;
                    }
                }
            }
            if (ColType.equalsIgnoreCase("VARCHAR") || ColType.equalsIgnoreCase("CHAR")) {
                aParameter = cust.getCustParameters(aColumn);
                if (!StringUtils.isEmpty(aParameter)) {
                    appendValue = "'" + SafeString.getSQLSafeString(aParameter) + "'";
                    appendColumn = aColumn;
                    appendIt = true;
                } else {
                    ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);
                    if (tmp != null) {
                        String defaultValue = tmp.getDefaultValue();
                        if (!StringUtils.isBlank(defaultValue)) {
                            appendValue = "'" + defaultValue + "'";
                            hasDefault = true;
                        }
                    }
                    if (hasDefault) {
                        appendColumn = aColumn;
                        appendIt = true;
                    }
                }
            }

            if (appendIt) {
                // if Columns contains more than "(", i.e. customerID was set
                if (!Columns.toString().equals("(")) {
                    Columns.append(", ");
                    Values.append(", ");
                }
                Columns.append(appendColumn.toLowerCase());
                Values.append(appendValue);
            }
        }
    }

    Columns.append(")");
    Values.append(")");

    insertCust.append(Columns.toString());
    insertCust.append(Values.toString());

    if (AgnUtils.isOracleDB()) {
        try {
            JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
            tmpl.execute(insertCust.toString());

            if (logger.isDebugEnabled()) {
                logger.debug("insertCust: " + insertCust.toString());
            }
        } catch (Exception e3) {
            logger.error("insertNewCustomer in Oracle", e3);
            cust.setCustomerID(0);
            return 0;
        }
    } else {
        try {
            SqlUpdate sqlUpdate = new SqlUpdate((DataSource) this.applicationContext.getBean("dataSource"),
                    insertCust.toString());
            sqlUpdate.setReturnGeneratedKeys(true);
            sqlUpdate.compile();
            GeneratedKeyHolder key = new GeneratedKeyHolder();
            sqlUpdate.update(null, key);
            customerID = key.getKey().intValue();
            cust.setCustomerID(customerID);
        } catch (Exception e3) {
            logger.error("insertNewCust in MySQL", e3);
            cust.setCustomerID(0);
            return 0;
        }
    }

    return cust.getCustomerID();
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Updates Customer in DB. customerID must be set to a valid id, customer-data is taken from this.customerData
 *
 * @return true on success/*  www .  j a v a 2 s . co  m*/
 */
@Override
public boolean updateInDB(Recipient cust) {
    String currentTimestamp = AgnUtils.getSQLCurrentTimestampName();
    String aColumn;
    String colType = null;
    boolean appendIt = false;
    StringBuffer updateCust = new StringBuffer("UPDATE customer_" + cust.getCompanyID() + "_tbl SET "
            + AgnUtils.changeDateName() + "=" + currentTimestamp);
    NumberFormat aFormat1 = null;
    NumberFormat aFormat2 = null;
    int day, month, year;
    int hour = 0;
    int minute = 0;
    int second = 0;
    String aParameter = null;
    int intValue;
    String appendValue = null;
    boolean result = true;

    if (cust.getCustDBStructure() == null) {
        cust.loadCustDBStructure();
    }

    if (cust.getCustomerID() == 0) {
        if (logger.isInfoEnabled()) {
            logger.info("updateInDB: creating new customer");
        }

        if (this.insertNewCust(cust) == 0) {
            result = false;
        }
    } else {
        if (cust.isChangeFlag()) { // only if something has changed
            Iterator<String> i = cust.getCustDBStructure().keySet().iterator();
            while (i.hasNext()) {
                aColumn = i.next();
                colType = (String) cust.getCustDBStructure().get(aColumn);
                appendIt = false;

                if (aColumn.equalsIgnoreCase("customer_id") || aColumn.equalsIgnoreCase("change_date")
                        || aColumn.equalsIgnoreCase("timestamp") || aColumn.equalsIgnoreCase("creation_date")
                        || aColumn.equalsIgnoreCase("datasource_id")) {
                    continue;
                }

                if (colType.equalsIgnoreCase("DATE")) {
                    if ((cust.getCustParameters().get(aColumn + "_DAY_DATE") != null)
                            && (cust.getCustParameters().get(aColumn + "_MONTH_DATE") != null)
                            && (cust.getCustParameters().get(aColumn + "_YEAR_DATE") != null)) {
                        aFormat1 = new DecimalFormat("00");
                        aFormat2 = new DecimalFormat("0000");
                        try {
                            if (!((String) cust.getCustParameters().get(aColumn + "_DAY_DATE")).trim()
                                    .equals("")) {
                                day = Integer
                                        .parseInt((String) cust.getCustParameters().get(aColumn + "_DAY_DATE"));
                                month = Integer.parseInt(
                                        (String) cust.getCustParameters().get(aColumn + "_MONTH_DATE"));
                                year = Integer.parseInt(
                                        (String) cust.getCustParameters().get(aColumn + "_YEAR_DATE"));
                                if ((cust.getCustParameters().get(aColumn + "_HOUR_DATE") != null)
                                        && !cust.getCustParameters(aColumn + "_HOUR_DATE").trim().equals("")) {
                                    hour = Integer.parseInt(
                                            (String) cust.getCustParameters().get(aColumn + "_HOUR_DATE"));
                                }
                                if ((cust.getCustParameters().get(aColumn + "_MINUTE_DATE") != null) && !cust
                                        .getCustParameters(aColumn + "_MINUTE_DATE").trim().equals("")) {
                                    minute = Integer.parseInt(
                                            (String) cust.getCustParameters().get(aColumn + "_MINUTE_DATE"));
                                }
                                if ((cust.getCustParameters().get(aColumn + "_SECOND_DATE") != null) && !cust
                                        .getCustParameters(aColumn + "_SECOND_DATE").trim().equals("")) {
                                    second = Integer.parseInt(
                                            (String) cust.getCustParameters().get(aColumn + "_SECOND_DATE"));
                                }
                                if (AgnUtils.isOracleDB()) {
                                    appendValue = aColumn.toLowerCase() + "=to_date('" + aFormat1.format(day)
                                            + "." + aFormat1.format(month) + "." + aFormat2.format(year) + " "
                                            + aFormat1.format(hour) + ":" + aFormat1.format(minute) + ":"
                                            + aFormat1.format(second) + "', 'DD.MM.YYYY HH24:MI:SS')";
                                } else {
                                    appendValue = aColumn.toLowerCase() + "=STR_TO_DATE('"
                                            + aFormat1.format(day) + "-" + aFormat1.format(month) + "-"
                                            + aFormat2.format(year) + " " + aFormat1.format(hour) + ":"
                                            + aFormat1.format(minute) + ":" + aFormat1.format(second)
                                            + "', '%d-%m-%Y %H:%i:%s')";
                                }
                                appendIt = true;
                            } else {
                                appendValue = aColumn.toLowerCase() + "=null";
                                appendIt = true;
                            }
                        } catch (Exception e1) {
                            logger.error("updateInDB: Could not parse Date " + aColumn + " because of "
                                    + e1.getMessage(), e1);
                        }
                    } else {
                        logger.error("updateInDB: Parameter missing!");
                    }
                } else if (colType.equalsIgnoreCase("INTEGER")) {
                    aParameter = (String) cust.getCustParameters(aColumn);
                    if (!StringUtils.isEmpty(aParameter)) {
                        try {
                            intValue = Integer.parseInt(aParameter);
                        } catch (Exception e1) {
                            intValue = 0;
                        }
                        appendValue = aColumn.toLowerCase() + "=" + intValue;
                        appendIt = true;
                    } else {
                        appendValue = aColumn.toLowerCase() + "=null";
                        appendIt = true;
                    }
                } else if (colType.equalsIgnoreCase("DOUBLE")) {
                    double dValue;
                    aParameter = (String) cust.getCustParameters(aColumn);
                    if (!StringUtils.isEmpty(aParameter)) {
                        try {
                            dValue = Double.parseDouble(aParameter);
                        } catch (Exception e1) {
                            dValue = 0;
                        }
                        appendValue = aColumn.toLowerCase() + "=" + dValue;
                        appendIt = true;
                    } else {
                        appendValue = aColumn.toLowerCase() + "=null";
                        appendIt = true;
                    }
                } else /* if(colType.equalsIgnoreCase("VARCHAR") || colType.equalsIgnoreCase("CHAR"))*/ {
                    aParameter = (String) cust.getCustParameters(aColumn);
                    if (!StringUtils.isEmpty(aParameter)) {
                        appendValue = aColumn.toLowerCase() + "='" + SafeString.getSQLSafeString(aParameter)
                                + "'";
                        appendIt = true;
                    } else {
                        appendValue = aColumn.toLowerCase() + "=null";
                        appendIt = true;
                    }
                }
                if (appendIt) {
                    updateCust.append(", ");
                    updateCust.append(appendValue);
                }
            }

            updateCust.append(" WHERE customer_id=" + cust.getCustomerID());
            try {
                JdbcTemplate tmpl = new JdbcTemplate(
                        (DataSource) this.applicationContext.getBean("dataSource"));

                if (logger.isInfoEnabled()) {
                    logger.info("updateInDB: " + updateCust.toString());
                }

                tmpl.execute(updateCust.toString());

                if (cust.getCustParameters("DATASOURCE_ID") != null) {
                    String sql = "select datasource_id from customer_" + cust.getCompanyID()
                            + "_tbl where customer_id = ?";
                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> list = tmpl.queryForList(sql,
                            new Object[] { new Integer(cust.getCustomerID()) });
                    Iterator<Map<String, Object>> id = list.iterator();
                    if (!id.hasNext()) {
                        aParameter = (String) cust.getCustParameters("DATASOURCE_ID");
                        if (!StringUtils.isEmpty(aParameter)) {
                            try {
                                intValue = Integer.parseInt(aParameter);
                                sql = "update customer_" + cust.getCompanyID() + "_tbl set datasource_id = "
                                        + intValue + " where customer_id = " + cust.getCustomerID();
                                tmpl.execute(sql);

                            } catch (Exception e1) {
                                logger.error("Error updating customer", e1);
                            }
                        }
                    }
                }
            } catch (Exception e3) {
                // Util.SQLExceptionHelper(e3,dbConn);
                logger.error("updateInDB: " + e3.getMessage(), e3);
                result = false;
            }
        } else {
            if (logger.isInfoEnabled()) {
                logger.info("updateInDB: nothing changed");
            }
        }
    }
    return result;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

@Override
public boolean createImportTables(int companyID, int datasourceID, CustomerImportStatus status) {
    JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    String prefix = "cust_" + companyID + "_tmp";
    String tabName = prefix + datasourceID + "_tbl";
    String keyIdx = prefix + datasourceID + "$KEYCOL$IDX";
    String custIdx = prefix + datasourceID + "$CUSTID$IDX";
    String sql = null;//from   ww  w.  jav  a 2 s  .  c  om

    try {
        sql = "create temporary table " + tabName + " as (select * from customer_" + companyID
                + "_tbl where 1=0)";
        jdbc.execute(sql);

        sql = "alter table " + tabName + " modify change_date timestamp null default null";
        jdbc.execute(sql);

        sql = "alter table " + tabName + " modify creation_date timestamp null default current_timestamp";
        jdbc.execute(sql);

        sql = "create index " + keyIdx + " on " + tabName + " ("
                + SafeString.getSQLSafeString(status.getKeycolumn()) + ")";
        jdbc.execute(sql);

        sql = "create index " + custIdx + " on " + tabName + " (customer_id)";
        jdbc.execute(sql);
    } catch (Exception e) {
        logger.error("createTemporaryTables: " + sql, e);
        e.printStackTrace();
        return false;
    }
    return true;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

@Override
public boolean deleteImportTables(int companyID, int datasourceID) {
    JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    String tabName = "cust_" + companyID + "_tmp" + datasourceID + "_tbl";

    if (AgnUtils.isOracleDB()) {
        try {/*from w w w.j av  a2s  .  co m*/
            jdbc.execute("drop table " + tabName);
        } catch (Exception e) {
            logger.error("deleteTemporarytables (table: " + tabName + ")", e);
            e.printStackTrace();
            return false;
        }
    }
    return true;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

@Override
public boolean deleteRecipients(int companyID, String target) {
    boolean returnValue = false;
    JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
    String sql;//from   www.  j a  v  a 2s. c om

    sql = "DELETE FROM customer_" + companyID
            + "_binding_tbl WHERE customer_id in (select customer_id from customer_" + companyID
            + "_tbl cust where " + target + ")";
    try {
        tmpl.execute(sql);
    } catch (Exception e) {
        logger.error("error deleting recipient bindings", e);
        returnValue = false;
    }

    sql = "delete ";
    if (AgnUtils.isMySQLDB()) {
        sql = sql + "cust ";
    }
    sql = sql + "from customer_" + companyID + "_tbl cust where " + target;
    try {
        tmpl.execute(sql);
        returnValue = true;
    } catch (Exception e) {
        logger.error("error deleting recipients", e);
        returnValue = false;
    }
    return returnValue;
}