Example usage for org.springframework.jdbc.object SqlUpdate SqlUpdate

Introduction

In this page you can find the example usage for org.springframework.jdbc.object SqlUpdate SqlUpdate.

Prototype

public SqlUpdate(DataSource ds, String sql, int[] types) 

Source Link

Document

Construct an update object with a given DataSource, SQL and anonymous parameters.

Usage

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

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 {/*from w w  w.ja v a 2s . c  o  m*/
        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();
}

---

From source file:org.easyrec.store.dao.plugin.impl.LogEntryDAOMysqlImpl.java

protected LogEntryDAOMysqlImpl(DataSource dataSource, SqlScriptService sqlScriptService,
        PluginRegistry pluginRegistry) {
    super(sqlScriptService);
    setDataSource(dataSource);//from  w ww  . j a v a2s .  co m

    startEntry = new SqlUpdate(dataSource,
            "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, "
                    + "configuration) VALUES (?, ?, ?, ?, ?, ?)",
            new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR,
                    Types.BLOB });
    startEntry.compile();

    endEntry = new SqlUpdate(dataSource,
            "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, configuration, "
                    + "endDate, statistics) VALUES (?, ?, ?, ?, ?, ?, ?, ?) "
                    + "ON DUPLICATE KEY UPDATE endDate = ?, statistics = ?",
            new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.BLOB,
                    Types.TIMESTAMP, Types.BLOB, Types.TIMESTAMP, Types.BLOB });
    endEntry.compile();

    endAllEntries = new SqlUpdate(dataSource,
            "UPDATE plugin_log SET endDate = ?, statistics = ? WHERE endDate IS NULL",
            new int[] { Types.TIMESTAMP, Types.BLOB });
    endAllEntries.compile();

    getRunningTenants = new MappingSqlQuery<Integer>(dataSource,
            "SELECT tenantId FROM plugin_log WHERE endDate IS NULL") {
        @Override
        protected Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getInt("tenantId");
        }
    };
    getRunningTenants.compile();

    getLogEntries = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log ORDER BY endDate DESC, id DESC LIMIT ?, ?");
    getLogEntries.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntries.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntries.compile();

    getLogEntriesForTenant = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE tenantId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getLogEntriesForTenant.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesForTenant.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesForTenant.compile();

    getLogEntriesWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER));
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesWithAssocType.compile();

    getLogEntriesForTenantWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE tenantId = ? AND assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.compile();

    getNumberOfLogEntries = new SqlFunction<Integer>(dataSource,
            "SELECT count(*) AS entry_count FROM plugin_log");
    getNumberOfLogEntries.compile();

    getNumberOfLogEntriesForTenant = new SqlFunction<Integer>(dataSource,
            "SELECT count(*) AS entry_count FROM plugin_log WHERE tenantId = ?");
    getNumberOfLogEntriesForTenant.setResultType(Integer.class);
    getNumberOfLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getNumberOfLogEntriesForTenant.compile();

    deleteLogEntries = new SqlUpdate(dataSource, "TRUNCATE plugin_log");
    deleteLogEntries.compile();

    getComputationDurationForDate = new SqlFunction<Integer>(dataSource,
            "SELECT sum(timestampdiff(second, startDate, endDate)) AS sum_seconds FROM plugin_log WHERE endDate BETWEEN ? AND ?");
    getComputationDurationForDate.setResultType(Integer.class);
    getComputationDurationForDate.declareParameter(new SqlParameter("start", Types.DATE));
    getComputationDurationForDate.declareParameter(new SqlParameter("end", Types.DATE));
    getComputationDurationForDate.compile();

    deleteLogEntryStatement = new SqlUpdate(dataSource,
            "DELETE FROM plugin_log WHERE tenantId = ? AND pluginId = ? AND pluginVersion = ? AND startDate = ? AND assocTypeId = ?");
    deleteLogEntryStatement.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    deleteLogEntryStatement.declareParameter(new SqlParameter("pluginId", Types.VARCHAR));
    deleteLogEntryStatement.declareParameter(new SqlParameter("pluginVersion", Types.VARCHAR));
    deleteLogEntryStatement.declareParameter(new SqlParameter("startDate", Types.TIMESTAMP));
    deleteLogEntryStatement.declareParameter(new SqlParameter("assocTypeId", Types.VARCHAR));
    deleteLogEntryStatement.compile();
}

---