Example usage for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue

List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.namedparam.*
  5. MapSqlParameterSource
  6. addValue

Introduction

In this page you can find the example usage for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue.

Prototype

public MapSqlParameterSource addValue(String paramName, @Nullable Object value)

Source Link

Document

Add a parameter to this parameter source.

Usage

From source file:com.epam.catgenome.dao.reference.ReferenceGenomeDao.java

/**
 * Saves {@code List} of {@code Chromosome} entities with a specified ID in the database
 * as one reference/*from   ww w. ja v  a  2  s  .c  o  m*/
 * @param referenceId for the chromosomes
 * @param chromosomes {@code List} of {@code Chromosome} entities to store int the database
 * @return an array containing the numbers of rows affected by each update in the batch
 */
@Transactional(propagation = Propagation.MANDATORY)
public int[] saveChromosomes(final Long referenceId, final List<Chromosome> chromosomes) {
    final int count = chromosomes.size();
    final List<Long> chromosomeIds = daoHelper.createIds(chromosomeSequenceName, count);
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[count];
    for (int i = 0; i < count; i++) {
        final Chromosome chromosome = chromosomes.get(i);
        chromosome.setId(chromosomeIds.get(i));
        chromosome.setReferenceId(referenceId);
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(GenomeParameters.NAME.name(), chromosome.getName());
        params.addValue(GenomeParameters.SIZE.name(), chromosome.getSize());
        params.addValue(GenomeParameters.PATH.name(), chromosome.getPath());
        params.addValue(GenomeParameters.HEADER.name(), chromosome.getHeader());
        params.addValue(GenomeParameters.CHROMOSOME_ID.name(), chromosome.getId());
        params.addValue(GenomeParameters.REFERENCE_GENOME_ID.name(), chromosome.getReferenceId());
        batchArgs[i] = params;
    }
    return getNamedParameterJdbcTemplate().batchUpdate(createChromosomeQuery, batchArgs);
}

From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaBucketDropDao.java

/**
 * Populate custom field for the given drops.
 * /*from  w  ww .j  ava2s  . com*/
 * @param drops
 */
@SuppressWarnings("rawtypes")
public void populateForms(List<Drop> drops) {

    Map<Long, Integer> dropIndex = new HashMap<Long, Integer>();
    int i = 0;
    for (Drop drop : drops) {
        dropIndex.put(drop.getId(), i);
        i++;
    }

    String sql = "SELECT form.id, form.drop_id, form_id, field.field_id, field.value ";
    sql += "FROM bucket_droplet_form form, bucket_droplet_form_field field ";
    sql += "WHERE form.id = field.droplet_form_id ";
    sql += "AND drop_id IN (:drop_ids)  ";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("drop_ids", dropIndex.keySet());
    List<Map<String, Object>> results = this.namedJdbcTemplate.queryForList(sql, params);

    // Add form values to their respective drops.
    Map<Long, BucketDropForm> dropForms = new HashMap<Long, BucketDropForm>();
    for (Map<String, Object> result : results) {

        Long dropId = ((Number) result.get("drop_id")).longValue();
        Drop drop = drops.get(dropIndex.get(dropId));
        if (drop.getForms() == null) {
            drop.setForms(new ArrayList<DropForm>());
        }

        Long dropFormId = ((Number) result.get("id")).longValue();
        BucketDropForm dropForm = dropForms.get(dropFormId);

        if (dropForm == null) {
            dropForm = new BucketDropForm();
            dropForm.setId(dropFormId);
            Form form = new Form();
            form.setId(((Number) result.get("form_id")).longValue());
            dropForm.setForm(form);
            dropForm.setValues(new ArrayList<BucketDropFormField>());

            dropForms.put(dropFormId, dropForm);
        }

        BucketDropFormField value = new BucketDropFormField();
        FormField field = new FormField();
        field.setId(((Number) result.get("field_id")).longValue());
        value.setField(field);
        value.setValue((String) result.get("value"));

        List<BucketDropFormField> values = dropForm.getValues();
        values.add(value);

        if (!drop.getForms().contains(dropForm)) {
            drop.getForms().add(dropForm);
        }
    }
}

From source file:com.joliciel.jochre.doc.DocumentDaoJdbc.java

@Override
public void saveJochreDocument(JochreDocument jochreDocument) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    JochreDocumentInternal iJochreDocument = (JochreDocumentInternal) jochreDocument;

    paramSource.addValue("doc_filename", jochreDocument.getFileName());
    paramSource.addValue("doc_name", jochreDocument.getName());
    paramSource.addValue("doc_owner_id", jochreDocument.getOwnerId());
    paramSource.addValue("doc_locale", jochreDocument.getLocale().getLanguage());
    paramSource.addValue("doc_name_local", jochreDocument.getNameLocal());
    paramSource.addValue("doc_publisher", jochreDocument.getPublisher());
    paramSource.addValue("doc_city", jochreDocument.getCity());
    paramSource.addValue("doc_year", jochreDocument.getYear());
    paramSource.addValue("doc_reference", jochreDocument.getReference());

    String sql = null;/*from w w  w  .  ja  v a  2s .  c o  m*/

    if (jochreDocument.isNew()) {
        sql = "SELECT nextval('ocr_doc_id_seq')";
        LOG.info(sql);
        int jochreDocumentId = jt.queryForInt(sql, paramSource);
        paramSource.addValue("doc_id", jochreDocumentId);

        sql = "INSERT INTO ocr_document (doc_id, doc_filename, doc_name, doc_locale, doc_owner_id"
                + ", doc_name_local, doc_publisher, doc_city, doc_year, doc_reference) "
                + "VALUES (:doc_id, :doc_filename, :doc_name, :doc_locale, :doc_owner_id"
                + ", :doc_name_local, :doc_publisher, :doc_city, :doc_year, :doc_reference)";

        LOG.info(sql);
        logParameters(paramSource);
        jt.update(sql, paramSource);

        iJochreDocument.setId(jochreDocumentId);
    } else {
        paramSource.addValue("doc_id", jochreDocument.getId());

        sql = "UPDATE ocr_document" + " SET doc_filename = :doc_filename" + ", doc_name = :doc_name"
                + ", doc_locale = :doc_locale" + ", doc_owner_id = :doc_owner_id"
                + ", doc_name_local = :doc_name_local" + ", doc_publisher = :doc_publisher"
                + ", doc_city = :doc_city" + ", doc_year = :doc_year" + ", doc_reference = :doc_reference"
                + " WHERE doc_id = :doc_id";

        LOG.info(sql);
        logParameters(paramSource);
        jt.update(sql, paramSource);
    }
}

From source file:net.algem.security.UserDaoImpl.java

@Override
public boolean isMemberOnYear(String login, String start, String end) {
    try {//from w w  w.  j  ava  2s. c o  m
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("login", login);
        params.addValue("start", new java.sql.Date(GemConstants.DATE_FORMAT.parse(start).getTime()));
        params.addValue("end", new java.sql.Date(GemConstants.DATE_FORMAT.parse(end).getTime()));
        params.addValue("accounts", getMemberShipAccounts());
        //      String debug = login +","+start+","+end+","+getMemberShipAccounts();
        //      LOGGER.log(Level.INFO, debug);
        String query = "SELECT e.paye FROM echeancier2 e JOIN " + TABLE + " l ON (e.adherent = l.idper)"
                + " WHERE l.login = :login AND e.echeance BETWEEN :start AND :end AND e.compte IN(:accounts)";
        List<Boolean> result = namedJdbcTemplate.query(query, params, new RowMapper<Boolean>() {

            @Override
            public Boolean mapRow(ResultSet rs, int i) throws SQLException {
                return rs.getBoolean(1);
            }
        });
        for (Boolean b : result) {
            if (b) {
                return true;
            }
        }
        return false;
    } catch (ParseException | DataAccessException ex) {
        LOGGER.log(Level.SEVERE, null, ex);
        return false;
    }

}

From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaBucketDao.java

@Override
public List<Drop> getDrops(Long bucketId, DropFilter filter, int page, int dropCount, Account queryingAccount) {
    String sql = "SELECT droplets.id, buckets_droplets.id AS tracking_id, droplet_title, ";
    sql += "droplet_content, droplets.channel, identities.id AS identity_id, identity_name, ";
    sql += "identity_avatar, droplets.droplet_date_pub, droplet_orig_id, ";
    sql += "user_scores.score AS user_score, links.id AS original_url_id, ";
    sql += "links.url AS original_url, comment_count, bucket_droplets_read.buckets_droplets_id AS drop_read ";
    sql += "FROM buckets_droplets ";
    sql += "INNER JOIN droplets ON (buckets_droplets.droplet_id = droplets.id) ";
    sql += "INNER JOIN identities ON (droplets.identity_id = identities.id) ";
    sql += "LEFT JOIN droplet_scores AS user_scores ON (user_scores.droplet_id = droplets.id AND user_scores.user_id = :userId) ";
    sql += "LEFT JOIN links ON (droplets.original_url = links.id) ";
    sql += "LEFT JOIN bucket_droplets_read ON (bucket_droplets_read.buckets_droplets_id = buckets_droplets.id AND bucket_droplets_read.account_id = :accountId) ";
    sql += "WHERE buckets_droplets.droplet_date_added > '1970-01-01 00:00:00' ";
    sql += "AND buckets_droplets.bucket_id = :bucketId ";

    // Check for channel parameter
    if (filter.getChannels() != null && !filter.getChannels().isEmpty()) {
        sql += "AND droplets.channel IN (:channels) ";
    }//from   w w w . j  av a 2 s  .  c om

    if (filter.getPhotos() != null && filter.getPhotos()) {
        sql += "AND droplets.droplet_image > 0 ";
    }

    if (filter.getRead() != null) {
        if (filter.getRead()) {
            sql += "AND bucket_droplets_read.buckets_droplets_id IS NOT NULL ";
        } else {
            sql += "AND bucket_droplets_read.buckets_droplets_id IS NULL ";
        }
    }

    // Check for since_id
    if (filter.getSinceId() != null) {
        sql += " AND buckets_droplets.id > :since_id ";
    }

    if (filter.getMaxId() != null) {
        sql += " AND buckets_droplets.id <= :max_id ";
    }

    if (filter.getDateFrom() != null) {
        sql += "AND droplets.droplet_date_pub >= :date_from ";
    }

    if (filter.getDateTo() != null) {
        sql += "AND droplets.droplet_date_pub <= :date_to ";
    }

    if (filter.getDropIds() != null && !filter.getDropIds().isEmpty()) {
        sql += "AND `droplets`.`id` IN (:dropIds) ";
    }

    boolean newer = filter.getSinceId() != null;
    if (newer) {
        sql += "ORDER BY buckets_droplets.droplet_date_added ASC ";
    } else {
        sql += "ORDER BY buckets_droplets.droplet_date_added DESC ";
    }

    sql += "LIMIT " + dropCount + " OFFSET " + dropCount * (page - 1);

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("userId", queryingAccount.getOwner().getId());
    params.addValue("accountId", queryingAccount.getId());
    params.addValue("bucketId", bucketId);

    // since_id
    if (filter.getSinceId() != null) {
        params.addValue("since_id", filter.getSinceId());
    }

    // max_id
    if (filter.getMaxId() != null) {
        params.addValue("max_id", filter.getMaxId());
    }

    if (filter.getChannels() != null && !filter.getChannels().isEmpty()) {
        params.addValue("channels", filter.getChannels());
    }

    if (filter.getDateFrom() != null) {
        params.addValue("date_from", filter.getDateFrom());
    }

    if (filter.getDateTo() != null) {
        params.addValue("date_to", filter.getDateTo());
    }

    if (filter.getDropIds() != null && !filter.getDropIds().isEmpty()) {
        params.addValue("dropIds", filter.getDropIds());
    }

    List<Map<String, Object>> results = this.jdbcTemplate.queryForList(sql, params);

    List<Drop> drops = new ArrayList<Drop>();

    for (Map<String, Object> result : results) {
        Drop drop = new Drop();

        // Set the drop properties
        drop.setId(((Number) result.get("id")).longValue());
        drop.setTrackingId(((Number) result.get("tracking_id")).longValue());
        drop.setTitle((String) result.get("droplet_title"));
        drop.setContent((String) result.get("droplet_content"));
        drop.setChannel((String) result.get("channel"));
        drop.setDatePublished((Date) result.get("droplet_date_pub"));
        drop.setOriginalId((String) result.get("droplet_orig_id"));
        drop.setCommentCount(((Number) result.get("comment_count")).intValue());
        drop.setRead((Long) result.get("drop_read") != null);

        Identity identity = new Identity();
        identity.setId(((Number) result.get("identity_id")).longValue());
        identity.setName((String) result.get("identity_name"));
        identity.setAvatar((String) result.get("identity_avatar"));
        drop.setIdentity(identity);

        // Set drop url
        if (result.get("original_url_id") != null) {
            Link originalUrl = new Link();
            originalUrl.setId(((Number) result.get("original_url_id")).longValue());
            originalUrl.setUrl((String) result.get("original_url"));
            drop.setOriginalUrl(originalUrl);
        }

        drops.add(drop);
    }

    if (!drops.isEmpty()) {
        // Populate the metadata
        dropDao.populateMetadata(drops, queryingAccount);
    }

    return drops;
}

From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaRiverDao.java

public List<Drop> getDrops(Long riverId, DropFilter filter, int page, int dropCount, Account queryingAccount) {

    String sql = "SELECT droplets.id, rivers_droplets.id AS tracking_id, ";
    sql += "droplet_title, droplet_content, droplets.channel, ";
    sql += "identities.id AS identity_id, identity_name, identity_avatar, ";
    sql += "rivers_droplets.droplet_date_pub, droplet_orig_id, ";
    sql += "user_scores.score AS user_score, links.id as original_url_id, ";
    sql += "links.url AS original_url, comment_count, river_droplets_read.rivers_droplets_id AS drop_read ";
    sql += "FROM rivers_droplets ";
    sql += "INNER JOIN droplets ON (rivers_droplets.droplet_id = droplets.id) ";
    sql += "INNER JOIN identities ON (droplets.identity_id = identities.id) ";

    if (filter.getChannelIds() != null && !filter.getChannelIds().isEmpty()) {
        sql += "INNER JOIN river_channels ON (rivers_droplets.river_channel_id = river_channels.id) ";
    }/*from   w ww  .  jav  a2s  .co m*/

    sql += "LEFT JOIN droplet_scores AS user_scores ON (user_scores.droplet_id = droplets.id AND user_scores.user_id = :userId) ";
    sql += "LEFT JOIN links ON (links.id = droplets.original_url) ";
    sql += "LEFT JOIN river_droplets_read ON (river_droplets_read.rivers_droplets_id = rivers_droplets.id AND river_droplets_read.account_id = :accountId) ";
    sql += "WHERE rivers_droplets.droplet_date_pub > '1970-01-01 00:00:00' ";
    sql += "AND rivers_droplets.river_id = :riverId ";

    if (filter.getSinceId() != null) {
        sql += "AND rivers_droplets.id > :since_id ";
    }

    if (filter.getMaxId() != null) {
        sql += "AND rivers_droplets.id <= :max_id ";
    }

    if (filter.getChannels() != null && !filter.getChannels().isEmpty()) {
        sql += "AND `droplets`.`channel` IN (:channels) ";
    }

    if (filter.getChannelIds() != null && !filter.getChannelIds().isEmpty()) {
        sql += "AND rivers_droplets.river_channel_id IN (:channel_ids) ";
    }

    if (filter.getRead() != null) {
        if (filter.getRead()) {
            sql += "AND river_droplets_read.rivers_droplets_id IS NOT NULL ";
        } else {
            sql += "AND river_droplets_read.rivers_droplets_id IS NULL ";
        }
    }

    if (filter.getPhotos() != null && filter.getPhotos()) {
        sql += "AND `droplets`.`droplet_image` > 0 ";
    }

    if (filter.getDateFrom() != null) {
        sql += "AND rivers_droplets.droplet_date_pub >= :date_from ";
    }

    if (filter.getDateTo() != null) {
        sql += "AND rivers_droplets.droplet_date_pub <= :date_to ";
    }

    if (filter.getDropIds() != null && !filter.getDropIds().isEmpty()) {
        sql += "AND `droplets`.`id` IN (:dropIds) ";
    }

    boolean newer = filter.getSinceId() != null;

    if (newer) {
        sql += "ORDER BY rivers_droplets.droplet_date_pub ASC ";
    } else {
        sql += "ORDER BY rivers_droplets.droplet_date_pub DESC ";
    }

    sql += "LIMIT " + dropCount + " OFFSET " + dropCount * (page - 1);

    // Set the query parameters
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("userId", queryingAccount.getOwner().getId());
    params.addValue("accountId", queryingAccount.getId());
    params.addValue("riverId", riverId);

    if (filter.getSinceId() != null) {
        params.addValue("since_id", filter.getSinceId());
    }

    if (filter.getMaxId() != null) {
        params.addValue("max_id", filter.getMaxId());
    }

    if (filter.getChannels() != null && !filter.getChannels().isEmpty()) {
        params.addValue("channels", filter.getChannels());
    }

    if (filter.getChannelIds() != null && !filter.getChannelIds().isEmpty()) {
        params.addValue("channel_ids", filter.getChannelIds());
    }

    if (filter.getDateFrom() != null) {
        params.addValue("date_from", filter.getDateFrom());
    }

    if (filter.getDateTo() != null) {
        params.addValue("date_to", filter.getDateTo());
    }

    if (filter.getDropIds() != null && !filter.getDropIds().isEmpty()) {
        params.addValue("dropIds", filter.getDropIds());
    }

    List<Map<String, Object>> results = this.jdbcTemplate.queryForList(sql, params);

    return formatDrops(results, queryingAccount);
}

From source file:org.inbio.modeling.core.dao.impl.LayerDAOImpl.java

@Override
public void create(Layer newLayer) {

    String createStatement = null;
    MapSqlParameterSource args = null;

    createStatement = "INSERT INTO " + this.table + "( display_name, "
            + "\"name\", description, uri, \"year\", last_update, is_species_map, source, visualization_scale, data_scale, generation_procedure)"
            + "VALUES (:display_name, :name, :description, :uri, :year, :last_update, :is_species_map, :source, :viz_scale, :data_scale, :generation_procedure);";

    args = new MapSqlParameterSource();
    args.addValue("name", newLayer.getName());
    args.addValue("display_name", newLayer.getDisplayName());
    args.addValue("uri", newLayer.getUri());
    args.addValue("source", newLayer.getSource());
    args.addValue("year", newLayer.getYear());
    args.addValue("viz_scale", newLayer.getVizScale());
    args.addValue("data_scale", newLayer.getDataScale());
    args.addValue("generation_procedure", newLayer.getGenerationProcedure());
    args.addValue("last_update", Calendar.getInstance().getTime());
    args.addValue("description", newLayer.getDescription());
    args.addValue("is_species_map", newLayer.isSpeciesMap());

    getSimpleJdbcTemplate().update(createStatement, args);
}

From source file:org.inbio.modeling.core.dao.impl.LayerDAOImpl.java

@Override
public void update(Layer updatedLayer) {
    String createStatement = null;
    MapSqlParameterSource args = null;

    createStatement = "UPDATE " + this.table + " " + " SET name = :name, " + " display_name = :display_name, "
            + " description = :description, " + " uri = :uri, " + " source = :source, " + " year = :year, "
            + " data_scale = :data_scale, " + " visualization_scale = :viz_scale, "
            + " last_update = :last_update, " + " generation_procedure = :generation_procedure, "
            + " is_species_map= :is_species_map " + " WHERE id = :layer_id";

    args = new MapSqlParameterSource();
    args.addValue("name", updatedLayer.getName());
    args.addValue("display_name", updatedLayer.getDisplayName());
    args.addValue("uri", updatedLayer.getUri());
    args.addValue("source", updatedLayer.getSource());
    args.addValue("year", updatedLayer.getYear());
    args.addValue("viz_scale", updatedLayer.getVizScale());
    args.addValue("data_scale", updatedLayer.getDataScale());
    args.addValue("generation_procedure", updatedLayer.getGenerationProcedure());
    args.addValue("last_update", Calendar.getInstance().getTime());
    args.addValue("description", updatedLayer.getDescription());
    args.addValue("is_species_map", updatedLayer.isSpeciesMap());
    args.addValue("layer_id", updatedLayer.getId());

    getSimpleJdbcTemplate().update(createStatement, args);

}

From source file:com.stehno.sjdbcx.reflection.DefaultParamMapper.java

@Override
public SqlParameterSource mapByName(final AnnotatedArgument[] annotatedArguments) {
    final MapSqlParameterSource parameterSource = new MapSqlParameterSource();

    if (annotatedArguments != null) {
        for (final AnnotatedArgument arg : annotatedArguments) {
            if (arg.findAnnotation(Ignore.class) == null) {
                final Annotation paramAnno = arg.findAnnotation(Param.class);
                if (paramAnno == null) {
                    addBean(parameterSource, arg.getValue());

                } else {
                    Object value = arg.getValue();
                    if (value != null && value.getClass().isEnum()) {
                        value = ((Enum) value).name();
                    }/*  www.j  a va2  s  .c  om*/

                    parameterSource.addValue(((Param) paramAnno).value(), value);
                }
            }
        }
    }

    return parameterSource;
}

From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java

public Context loadContext(int contextId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_CONTEXT + " FROM context WHERE context_id=:context_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("context_id", contextId);

    LOG.trace(sql);/*from  ww  w. j a  va  2s.  c o  m*/
    LogParameters(paramSource);
    Context context = null;
    try {
        context = (Context) jt.queryForObject(sql, paramSource, new ContextMapper());
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return context;
}