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.joliciel.jochre.doc.DocumentDaoJdbc.java

@Override
public Author loadAuthor(int authorId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_AUTHOR + " FROM ocr_author WHERE author_id=:author_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("author_id", authorId);

    LOG.info(sql);/*from   w w w. j  a v a 2  s.  c om*/
    logParameters(paramSource);
    Author author = null;
    try {
        author = (Author) jt.queryForObject(sql, paramSource,
                new AuthorMapper(this.getDocumentServiceInternal()));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return author;
}

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

@Override
public void replaceAuthors(JochreDocument doc) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();

    paramSource.addValue("docauthor_doc_id", doc.getId());
    String sql = "DELETE FROM ocr_doc_author_map WHERE docauthor_doc_id = :docauthor_doc_id";
    LOG.info(sql);/*from   www . ja va  2s  .  c o m*/
    logParameters(paramSource);
    jt.update(sql, paramSource);

    for (Author author : doc.getAuthors()) {
        paramSource = new MapSqlParameterSource();

        paramSource.addValue("docauthor_doc_id", doc.getId());
        paramSource.addValue("docauthor_author_id", author.getId());

        sql = "INSERT INTO ocr_doc_author_map (docauthor_doc_id, docauthor_author_id)"
                + " VALUES (:docauthor_doc_id, :docauthor_author_id)";

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

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

/**
 * Populates the buckets for each of the {@link Drop} in <code>drops</code>
 * //  w  w  w.  jav a 2 s . com
 * @param drops
 * @param queryingAccount
 * @param dropSource
 */
public void populateBuckets(List<Drop> drops, Account queryingAccount) {
    Map<Long, Integer> dropsIndex = new HashMap<Long, Integer>();
    int i = 0;
    for (Drop drop : drops) {
        dropsIndex.put(drop.getId(), i);
        i++;
    }

    // Query to fetch the buckets
    String sql = "SELECT `buckets_droplets`.`droplet_id` AS `id`, `buckets`.`id` AS `bucket_id`, `buckets`.`bucket_name` ";
    sql += "FROM `buckets` ";
    sql += "INNER JOIN `buckets_droplets` ON (`buckets`.`id` = `buckets_droplets`.`bucket_id`) ";
    sql += "WHERE `buckets_droplets`.`droplet_id` IN (:dropletIds) ";
    sql += "AND `buckets`.`bucket_publish` = 1 ";
    sql += "UNION ALL ";
    sql += "SELECT `buckets_droplets`.`droplet_id` AS `id`, `buckets`.`id` AS `bucket_id`, `buckets`.`bucket_name` ";
    sql += "FROM `buckets` ";
    sql += "INNER JOIN `buckets_droplets` ON (`buckets`.`id` = `buckets_droplets`.`bucket_id`) ";
    sql += "LEFT JOIN `accounts` ON (`buckets`.`account_id` = `accounts`.`id` AND `buckets`.`account_id` = :accountId) ";
    sql += "LEFT JOIN `bucket_collaborators` ON (`bucket_collaborators`.`bucket_id` = `buckets`.`id` AND `bucket_collaborators`.`account_id` = :accountId) ";
    sql += "WHERE `buckets_droplets`.`droplet_id` IN (:dropletIds) ";
    sql += "AND `buckets`.`bucket_publish` = 0 ";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("dropletIds", dropsIndex.keySet());
    params.addValue("accountId", (Long) queryingAccount.getId());
    List<Map<String, Object>> results = this.namedJdbcTemplate.queryForList(sql, params);

    // Group the buckets per drop 
    Map<Long, List<Bucket>> dropBucketsMap = new HashMap<Long, List<Bucket>>();
    for (Map<String, Object> row : results) {

        Long bucketDropId = ((Number) row.get("id")).longValue();
        List<Bucket> dropBuckets = dropBucketsMap.get(bucketDropId);
        if (dropBuckets == null) {
            dropBuckets = new ArrayList<Bucket>();
        }

        // Create the bucket
        Bucket bucket = new Bucket();
        bucket.setId(((Number) row.get("bucket_id")).longValue());
        bucket.setName((String) row.get("bucket_name"));

        // Add to the list of buckets for the current drop
        dropBuckets.add(bucket);
        dropBucketsMap.put(bucketDropId, dropBuckets);
    }

    // Populate the buckets for the submitted drops
    for (Map.Entry<Long, List<Bucket>> entry : dropBucketsMap.entrySet()) {
        Long dropId = entry.getKey();

        // Retrieve the drop
        Drop drop = drops.get(dropsIndex.get(dropId));
        drop.setBuckets(entry.getValue());
    }
}

From source file:me.ronghai.sa.dao.impl.AbstractModelDAOWithJDBCImpl.java

@Override
public List<E> find(List<Object> ids) {
    if (ids == null || ids.isEmpty())
        return null;
    String sql = "SELECT * FROM " + table(entityClass) + " WHERE id IN (:ids) ";
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", ids);
    List<E> entity = this.databaseHandler.query(sql, parameters, createRowMapper());
    if (delegate != null) {
        return delegate.afterFind(entity);
    }//  ww  w.j  a va 2s.  com
    return entity;
}

From source file:me.ronghai.sa.dao.impl.AbstractModelDAOWithJDBCImpl.java

@Override
public int remove(boolean force, Collection<Long> ids, String configure) {
    if (ids == null || ids.isEmpty()) {
        return 0;
    }//from   w  w w .  j  a  v a 2  s  .co m
    this.beforeRemove(force, ids, configure);
    String sql;
    String table = table(entityClass);
    if (force) {
        sql = "DELETE FROM " + table + "  e  WHERE id IN (:ids) ";
    } else {
        sql = "UPDATE " + table + "  SET disabled = 1  WHERE id IN (:ids) ";
    }
    if (StringUtils.isNotEmpty(configure)) {
        if (!configure.trim().toUpperCase().startsWith("AND")) {
            sql += " AND  ";
        }
        sql += configure;
    }
    logger.info("remove ids " + ids);
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", new ArrayList<>(ids));
    int n = this.databaseHandler.update(sql, parameters);

    this.afterRemove(force, ids, configure);
    return n;

}

From source file:com.epam.catgenome.dao.BiologicalDataItemDao.java

/**
 * Finds files with names matching a specified file name, performs substring, case insensitive search
 * @param name search query//from  w w w .j  a  v  a 2s  .com
 * @return {@code List} of files with a matching name
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<BiologicalDataItem> loadFilesByName(final String name) {
    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemParameters.NAME.name(), "%" + name.toLowerCase() + "%");
    return getNamedParameterJdbcTemplate().query(loadBiologicalDataItemsByNameQuery, params, getRowMapper());
}

From source file:com.epam.catgenome.dao.BiologicalDataItemDao.java

/**
 * Finds files with a specified file name, checks name for strict, case sensitive equality
 * @param name search query/*from www  .ja  va 2s. c o  m*/
 * @return {@code List} of files with a matching name
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<BiologicalDataItem> loadFilesByNameStrict(final String name) {
    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemParameters.NAME.name(), name);
    return getNamedParameterJdbcTemplate().query(loadBiologicalDataItemsByNameStrictQuery, params,
            getRowMapper());
}

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

@Override
public void saveAuthor(Author author) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    AuthorInternal iAuthor = (AuthorInternal) author;

    paramSource.addValue("author_first_name", author.getFirstName());
    paramSource.addValue("author_last_name", author.getLastName());
    paramSource.addValue("author_first_name_local", author.getFirstNameLocal());
    paramSource.addValue("author_last_name_local", author.getLastNameLocal());
    String sql = null;//from w w w  . ja  v a  2 s.  c  o  m

    if (author.isNew()) {
        sql = "SELECT nextval('ocr_author_id_seq')";
        LOG.info(sql);
        int authorId = jt.queryForInt(sql, paramSource);
        paramSource.addValue("author_id", authorId);

        sql = "INSERT INTO ocr_author (author_id, author_first_name, author_last_name"
                + ", author_first_name_local, author_last_name_local) "
                + "VALUES (:author_id, :author_first_name, :author_last_name"
                + ", :author_first_name_local, :author_last_name_local)";

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

        iAuthor.setId(authorId);
    } else {
        paramSource.addValue("author_id", author.getId());

        sql = "UPDATE ocr_author" + " SET author_first_name = :author_first_name"
                + ", author_last_name = :author_last_name"
                + ", author_first_name_local = :author_first_name_local"
                + ", author_last_name_local = :author_last_name_local" + " WHERE author_id = :author_id";

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

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

@Transactional(propagation = Propagation.MANDATORY)
public void updateReferenceGeneFileId(long referenceId, Long geneFileId) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(GenomeParameters.REFERENCE_GENOME_ID.name(), referenceId);
    params.addValue(GenomeParameters.GENE_ITEM_ID.name(), geneFileId);

    getNamedParameterJdbcTemplate().update(updateReferenceGeneFileIdQuery, params);
}

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

@Transactional(propagation = Propagation.MANDATORY)
public List<Long> loadGenomeIdsByAnnotationDataItemId(Long annotationFileBiologicalItemId) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemDao.BiologicalDataItemParameters.BIO_DATA_ITEM_ID.name(),
            annotationFileBiologicalItemId);
    return getNamedParameterJdbcTemplate().query(loadGenomeIdsByAnnotationDataItemIdQuery, params,
            GenomeParameters.ID_MAPPER);
}