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

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

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

Introduction

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

Prototype

@Override
    public int update(String sql, @Nullable Object... args) throws DataAccessException

Source Link

Usage

From source file:org.cloudfoundry.identity.uaa.mock.zones.IdentityProviderEndpointsMockMvcTests.java

protected void addScopeToIdentityClient(String scope) {
    JdbcTemplate template = getWebApplicationContext().getBean(JdbcTemplate.class);
    String scopes = template.queryForObject(
            "select scope from oauth_client_details where identity_zone_id='uaa' and client_id='identity'",
            String.class);
    boolean update = false;
    if (!StringUtils.hasText(scopes)) {
        scopes = scope;//  w ww . j  a  v a2s  .  c  o m
        update = true;
    } else if (!scopes.contains(scope)) {
        scopes = scopes + "," + scope;
        update = true;
    }
    if (update) {
        assertEquals(1, template.update(
                "UPDATE oauth_client_details SET scope=? WHERE identity_zone_id='uaa' AND client_id='identity'",
                scopes));
    }
}

From source file:org.cloudfoundry.identity.uaa.oauth.token.UaaTokenStore.java

@Override
public OAuth2Authentication consumeAuthorizationCode(String code) throws InvalidGrantException {
    performExpirationClean();//  w w  w.  java2s  .c  om
    JdbcTemplate template = new JdbcTemplate(dataSource);
    try {
        TokenCode tokenCode = (TokenCode) template.queryForObject(SQL_SELECT_STATEMENT, rowMapper, code);
        if (tokenCode != null) {
            try {
                if (tokenCode.isExpired()) {
                    logger.debug("[oauth_code] Found code, but it expired:" + tokenCode);
                    return null;
                } else if (tokenCode.getExpiresAt() == 0) {
                    return SerializationUtils.deserialize(tokenCode.getAuthentication());
                } else {
                    return deserializeOauth2Authentication(tokenCode.getAuthentication());
                }
            } finally {
                template.update(SQL_DELETE_STATEMENT, code);
            }
        }
    } catch (EmptyResultDataAccessException x) {
    }
    return null;
}

From source file:org.cloudfoundry.identity.uaa.oauth.token.UaaTokenStore.java

protected void performExpirationClean() {
    long last = lastClean.get();
    //check if we should expire again
    if ((System.currentTimeMillis() - last) > getExpirationTime()) {
        //avoid concurrent deletes from the same UAA - performance improvement
        if (lastClean.compareAndSet(last, last + getExpirationTime())) {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            int expired = template.update(SQL_EXPIRE_STATEMENT, System.currentTimeMillis());
            logger.debug("[oauth_code] Removed " + expired + " expired entries.");
            expired = template.update(SQL_CLEAN_STATEMENT,
                    new Timestamp(System.currentTimeMillis() - getExpirationTime()));
            logger.debug("[oauth_code] Removed " + expired + " old entries.");
        }//w  w  w .  ja  v a 2  s  .  co  m
    }

}

From source file:org.cloudfoundry.identity.uaa.oauth.UaaTokenStore.java

@Override
public OAuth2Authentication consumeAuthorizationCode(String code) throws InvalidGrantException {
    performExpirationClean();//from  w ww  .  j av a 2 s .  c  o m
    JdbcTemplate template = new JdbcTemplate(dataSource);
    try {
        TokenCode tokenCode = (TokenCode) template.queryForObject(SQL_SELECT_STATEMENT, rowMapper, code);
        if (tokenCode != null) {
            try {
                if (tokenCode.isExpired()) {
                    logger.debug("[oauth_code] Found code, but it expired:" + tokenCode);
                    throw new InvalidGrantException("Authorization code expired: " + code);
                } else if (tokenCode.getExpiresAt() == 0) {
                    return SerializationUtils.deserialize(tokenCode.getAuthentication());
                } else {
                    return deserializeOauth2Authentication(tokenCode.getAuthentication());
                }
            } finally {
                template.update(SQL_DELETE_STATEMENT, code);
            }
        }
    } catch (EmptyResultDataAccessException x) {
    }
    throw new InvalidGrantException("Invalid authorization code: " + code);
}

From source file:org.cloudfoundry.identity.uaa.oauth.UaaTokenStore.java

protected void performExpirationClean() {
    long last = lastClean.get();
    //check if we should expire again
    if ((System.currentTimeMillis() - last) > getExpirationTime()) {
        //avoid concurrent deletes from the same UAA - performance improvement
        if (lastClean.compareAndSet(last, last + getExpirationTime())) {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            int expired = template.update(SQL_EXPIRE_STATEMENT, System.currentTimeMillis());
            logger.debug("[oauth_code] Removed " + expired + " expired entries.");
            expired = template.update(SQL_CLEAN_STATEMENT,
                    new Timestamp(System.currentTimeMillis() - LEGACY_CODE_EXPIRATION_TIME));
            logger.debug("[oauth_code] Removed " + expired + " old entries.");
        }/*from w w w . ja v a 2  s. co m*/
    }

}

From source file:org.gbif.portal.harvest.workflow.activity.tag.CollectorTagActivity.java

/**
 * @see org.gbif.portal.util.workflow.Activity#execute(org.gbif.portal.util.workflow.ProcessContext)
 *//*from   w w w.ja v  a 2 s  .c o  m*/
@SuppressWarnings("unchecked")
public ProcessContext execute(ProcessContext processContext) throws Exception {

    logger.info("Generating collect tags");
    Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId);
    if (dataResourceId == null) {
        throw new ContextCorruptException("No data resource id in context");
    }
    JdbcTemplate template = new JdbcTemplate(dataSource);
    Connection conn = dataSource.getConnection();

    //clear old tags for this resource
    template.update("DELETE FROM string_tag WHERE tag_id=? AND entity_id=?",
            new Object[] { TagIds.DATA_RESOURCE_COLLECTOR, dataResourceId });

    PreparedStatement ps = conn.prepareStatement("INSERT INTO string_tag "
            + "(tag_id,entity_id,value,is_system_generated) " + "VALUES (?,?,?,true)");

    List<Map<String, Object>> collectorNames = template.queryForList(
            "SELECT DISTINCT collector_name FROM raw_occurrence_record "
                    + "WHERE data_resource_id=? AND collector_name IS NOT NULL LIMIT ?",
            new Object[] { dataResourceId, maxNumberOfCollectorsToRecord });

    int count = 0;

    for (Map<String, Object> collectorNameMap : collectorNames) {
        try {
            String collectorName = (String) collectorNameMap.get("collector_name");
            //escape single quotes
            StringBuffer oldSe = new StringBuffer("'");
            StringBuffer newSe = new StringBuffer("\\'");
            collectorName = collectorName.replace(oldSe, newSe);
            ps.setInt(1, TagIds.DATA_RESOURCE_COLLECTOR);
            ps.setLong(2, dataResourceId);
            ps.setString(3, collectorName);
            ps.execute();
            count++;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }
    ps.close();
    conn.close();
    logger.info("Collector tags inserted: " + count);
    return processContext;
}

From source file:org.gbif.portal.harvest.workflow.activity.tag.CommonNameTagActivity.java

@SuppressWarnings("unchecked")
public ProcessContext execute(ProcessContext processContext) throws Exception {

    logger.info("Generating common name tags");
    Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId);
    if (dataResourceId == null) {
        throw new ContextCorruptException("No data resource id in context");
    }//from   w  w  w  . j  ava  2  s . c  om

    JdbcTemplate template = new JdbcTemplate(dataSource);
    Connection conn = dataSource.getConnection();

    //clear old tags for this resource
    template.update("DELETE from bi_relation_tag where tag_id=? and from_entity_id=?",
            new Object[] { TagIds.DATA_RESOURCE_COMMON_NAMES, dataResourceId });

    List<Map> commonNames = template.queryForList(
            "SELECT DISTINCT cn.id as id FROM taxon_concept tc "
                    + "INNER JOIN taxon_concept nc ON tc.partner_concept_id=nc.id "
                    + "INNER JOIN common_name cn ON cn.taxon_concept_id=nc.id " + "WHERE tc.data_resource_id=?",
            new Object[] { dataResourceId });

    int count = 0;
    PreparedStatement ps = conn.prepareStatement("INSERT INTO bi_relation_tag "
            + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)");

    for (Map commonName : commonNames) {
        if (count >= maxCommonNamesToStore)
            break;
        Integer commonNameId = (Integer) commonName.get("id");
        ps.setLong(1, TagIds.DATA_RESOURCE_COMMON_NAMES);
        ps.setLong(2, dataResourceId);
        ps.setLong(3, commonNameId);
        ps.execute();
        count++;
    }
    ps.close();
    conn.close();
    return processContext;
}

From source file:org.gbif.portal.harvest.workflow.activity.tag.PolygonTagActivity.java

/**
 * @see org.gbif.portal.harvest.workflow.activity.tag.BaseTagActivity#execute(org.gbif.portal.util.workflow.ProcessContext)
 *///from  www.  jav  a2 s .c om
@SuppressWarnings("unchecked")
@Override
public ProcessContext execute(ProcessContext processContext) throws Exception {

    logger.info("Generating polygon tags");
    Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId);
    if (dataResourceId == null) {
        throw new ContextCorruptException("No data resource id in context");
    }
    JdbcTemplate template = new JdbcTemplate(dataSource);
    Connection conn = dataSource.getConnection();

    //clear old tags for this resource
    template.update("DELETE from string_tag where tag_id=? and entity_id=?",
            new Object[] { TagIds.DATA_RESOURCE_OCCURRENCES_WKT_POLYGON, dataResourceId });

    List<Map> results = template.queryForList("select distinct(latitude), longitude from occurrence_record"
            + " where data_resource_id=? and latitude is not null", new Object[] { dataResourceId });

    Coordinate[] coordinates = new Coordinate[results.size()];
    int i = 0;
    for (Map result : results) {
        coordinates[i] = new Coordinate((Float) result.get("longitude"), (Float) result.get("latitude"));
        i++;
    }

    GeometryFactory gf = new GeometryFactory();
    ConvexHull convexHull = new ConvexHull(coordinates, gf);
    Geometry g = convexHull.getConvexHull();
    Coordinate[] coords = g.getCoordinates();
    for (Coordinate coord : coords) {
        coord.x = (Math.floor(coord.x * 100)) / 100;
        coord.y = (Math.floor(coord.y * 100)) / 100;
    }

    String polygon = g.toString();
    if (polygon.startsWith(WKT_POLYGON_PREFIX)) {
        PreparedStatement ps = conn.prepareStatement("INSERT INTO string_tag "
                + "(tag_id,entity_id,value,is_system_generated) " + "VALUES (?, ?, ?, true)");
        //escape single quotes
        ps.setLong(1, TagIds.DATA_RESOURCE_OCCURRENCES_WKT_POLYGON);
        ps.setLong(2, dataResourceId);
        ps.setString(3, polygon);
        ps.execute();
        ps.close();
    }
    conn.close();
    return processContext;
}

From source file:org.gbif.portal.harvest.workflow.activity.tag.TaxonomicCoverageTagActivity.java

/**
 * @see launcher.DataResourceTagLoader#process(javax.sql.DataSource, org.springframework.jdbc.core.JdbcTemplate, java.lang.Integer)
 *///from  w w w  .  java 2s.  com
@SuppressWarnings("unchecked")
public ProcessContext execute(ProcessContext processContext) throws Exception {

    logger.info("Generating Taxonomic scope and associated kingdom tags");
    Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId);
    if (dataResourceId == null) {
        throw new ContextCorruptException("No data resource id in context");
    }
    JdbcTemplate template = new JdbcTemplate(dataSource);
    Connection conn = dataSource.getConnection();

    //clear old tags for this resource
    template.update("DELETE from string_tag where (tag_id=? or tag_id=?) and entity_id=?", new Object[] {
            TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE, TagIds.DATA_RESOURCE_ASSOCIATED_KINGDOM, dataResourceId });

    //determine root taxa
    List<Map> rootTaxaResults = template.queryForList(
            "SELECT tc.id id,tc.partner_concept_id as partner_concept_id,tc.rank, "
                    + "nc.kingdom_concept_id as kingdom_concept_id " + "FROM taxon_concept tc "
                    + "LEFT OUTER JOIN taxon_concept nc ON tc.partner_concept_id = nc.id "
                    + "WHERE tc.parent_concept_id IS NULL AND tc.data_resource_id=? ORDER BY tc.rank",
            new Object[] { dataResourceId });

    boolean tagsInserted = false;

    int taxonomicScopeTagsAdded = 0;
    int MAX_TAGS = 10;

    PreparedStatement ps = conn.prepareStatement("INSERT INTO bi_relation_tag "
            + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?, ?, ?,true)");

    //for each kingdom, go down each root until there is more than one child
    for (Map rootTaxa : rootTaxaResults) {

        Long taxonConceptId = (Long) rootTaxa.get("id");
        Long nubConceptId = (Long) rootTaxa.get("partner_concept_id");

        //determine root taxa
        List<Map> childResults = template.queryForList(
                "SELECT id,partner_concept_id, rank FROM taxon_concept tc "
                        + "WHERE tc.parent_concept_id is null AND data_resource_id=?",
                new Object[] { dataResourceId });

        //while there is only one child, iterate down
        while (childResults.size() == 1) {
            taxonConceptId = (Long) childResults.get(0).get("id");
            nubConceptId = (Long) childResults.get(0).get("partner_concept_id");
            childResults = template.queryForList("SELECT tc.id, tc.partner_concept_id FROM taxon_concept tc "
                    + "WHERE tc.parent_concept_id = ?", new Object[] { taxonConceptId });
        }

        if (nubConceptId != null) {
            //we have found the root taxon for this branch - create root taxon tag
            ps.setLong(1, TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE);
            ps.setLong(2, dataResourceId);
            ps.setLong(3, nubConceptId);
            ps.execute();
            tagsInserted = true;
            taxonomicScopeTagsAdded++;
            if (taxonomicScopeTagsAdded > MAX_TAGS)
                break;
        }
    }
    ps.close();

    //if nothing inserted, insert tags for the first few concepts in root list
    if (!tagsInserted) {

        ps = conn.prepareStatement("INSERT INTO bi_relation_tag "
                + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)");

        int count = 0;
        for (Map rootTaxa : rootTaxaResults) {
            if (count >= maxConceptsToStore)
                break;
            Long nubConceptId = (Long) rootTaxa.get("partner_concept_id");
            if (nubConceptId != null) {
                ps.setLong(1, TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE);
                ps.setLong(2, dataResourceId);
                ps.setLong(3, nubConceptId);
                ps.execute();
                count++;
            }
        }
        ps.close();
    }

    //set kingdom coverage
    ps = conn.prepareStatement("INSERT INTO bi_relation_tag "
            + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)");

    List<Long> addedKingdoms = new ArrayList<Long>();
    for (Map rootTaxa : rootTaxaResults) {
        Long nubConceptId = (Long) rootTaxa.get("kingdom_concept_id");
        if (nubConceptId != null && !addedKingdoms.contains(nubConceptId)) {
            ps.setLong(1, TagIds.DATA_RESOURCE_ASSOCIATED_KINGDOM);
            ps.setLong(2, dataResourceId);
            ps.setLong(3, nubConceptId);
            ps.execute();
            addedKingdoms.add(nubConceptId);
        }
    }
    ps.close();
    conn.close();
    return processContext;
}

From source file:org.projectforge.database.DatabaseUpdateDao.java

public void insertInto(final String table, final String[] columns, final Object[] values) {
    final StringBuffer buf = new StringBuffer();
    buf.append("insert into ").append(table).append(" (").append(StringHelper.listToString(",", columns))
            .append(") values (");
    boolean first = true;
    for (int i = 0; i < values.length; i++) {
        first = StringHelper.append(buf, first, "?", ",");
    }//from  ww w. j a  v  a2s .c o  m
    buf.append(")");
    final JdbcTemplate jdbc = new JdbcTemplate(dataSource);
    final String sql = buf.toString();
    log.info(sql + "; values = " + StringHelper.listToString(", ", values));
    jdbc.update(sql, values);
}