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

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

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

Introduction

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

Prototype

public MapSqlParameterSource()

Source Link

Document

Create an empty MapSqlParameterSource, with values to be added via addValue .

Usage

From source file:com.joliciel.lefff.LefffDaoImpl.java

public Category loadCategory(String categoryCode) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_CATEGORY + " FROM lef_category WHERE category_code=:category_code";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("category_code", categoryCode);

    LOG.info(sql);// ww w.  j a v  a 2s.  c om
    LefffDaoImpl.LogParameters(paramSource);
    Category category = null;
    try {
        category = (Category) jt.queryForObject(sql, paramSource,
                new CategoryMapper(this.getLefffServiceInternal()));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return category;
}

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;// www  . ja  v  a  2  s. co 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:com.ushahidi.swiftriver.core.api.dao.impl.JpaPlaceDao.java

/**
 * Populate the droplet places table.//from  w  ww .  j ava 2 s. com
 * 
 * @param drops
 */
private void insertDropletPlaces(List<Drop> drops) {

    // List of drop IDs in the drops list
    List<Long> dropIds = new ArrayList<Long>();
    // List of places in a drop
    Map<Long, Set<Long>> dropletPlacesMap = new HashMap<Long, Set<Long>>();
    for (Drop drop : drops) {

        if (drop.getPlaces() == null)
            continue;

        dropIds.add(drop.getId());

        for (Place place : drop.getPlaces()) {
            Set<Long> places = null;
            if (dropletPlacesMap.containsKey(drop.getId())) {
                places = dropletPlacesMap.get(drop.getId());
            } else {
                places = new HashSet<Long>();
                dropletPlacesMap.put(drop.getId(), places);
            }

            places.add(place.getId());
        }
    }

    // Find droplet places that already exist in the db
    String sql = "SELECT droplet_id, place_id FROM droplets_places WHERE droplet_id in (:ids)";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", dropIds);

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

    // Remove already existing droplet_places from our Set
    for (Map<String, Object> result : results) {
        long dropletId = ((Number) result.get("droplet_id")).longValue();
        long placeId = ((Number) result.get("place_id")).longValue();

        Set<Long> placeSet = dropletPlacesMap.get(dropletId);
        if (placeSet != null) {
            placeSet.remove(placeId);
        }
    }

    // Insert the remaining items in the set into the db
    sql = "INSERT INTO droplets_places (droplet_id, place_id) VALUES (?,?)";

    final List<long[]> dropletPlacesList = new ArrayList<long[]>();
    for (Long dropletId : dropletPlacesMap.keySet()) {
        for (Long placeId : dropletPlacesMap.get(dropletId)) {
            long[] dropletPlace = { dropletId, placeId };
            dropletPlacesList.add(dropletPlace);
        }
    }
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            long[] dropletPlace = dropletPlacesList.get(i);
            ps.setLong(1, dropletPlace[0]);
            ps.setLong(2, dropletPlace[1]);
        }

        public int getBatchSize() {
            return dropletPlacesList.size();
        }
    });
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public JochreImage loadJochreImage(int imageId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_IMAGE + " FROM ocr_image WHERE image_id=:image_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("image_id", imageId);

    LOG.debug(sql);/* w  w  w  .  j av  a 2 s .  c o  m*/
    logParameters(paramSource);
    JochreImage image = null;
    try {
        image = (JochreImage) jt.queryForObject(sql, paramSource,
                new JochreImageMapper(this.getGraphicsServiceInternal()));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return image;
}

From source file:com.ushahidi.swiftriver.core.api.service.DropIndexService.java

/**
 * Sets the <code>riverIds</code> property for each {@link Drop}
 * in <code>drops</code>// ww  w  .  j  ava2  s. c  o  m
 * 
 * @param drops
 */
private void populateRiverIds(List<Drop> drops) {
    // Store the drop index
    Map<Long, Integer> dropIndex = new HashMap<Long, Integer>();
    List<Long> dropIds = new ArrayList<Long>();
    int index = 0;
    for (Drop drop : drops) {
        dropIds.add(drop.getId());
        dropIndex.put(drop.getId(), index);
        index++;
    }

    String sql = "SELECT `droplet_id`, `river_id` " + "FROM `rivers_droplets` WHERE `droplet_id` IN (:dropIds)";

    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("dropIds", dropIds);

    for (Map<String, Object> row : namedJdbcTemplate.queryForList(sql, paramMap)) {
        Long dropId = ((Number) row.get("droplet_id")).longValue();
        Long riverId = ((Number) row.get("river_id")).longValue();

        Drop drop = drops.get(dropIndex.get(dropId));
        if (drop.getRiverIds() == null) {
            drop.setRiverIds(new ArrayList<Long>());
        }
        drop.getRiverIds().add(riverId);
    }

}

From source file:org.terasoluna.gfw.tutorial.selenium.DBLogAssertOperations.java

/**
 * ??(??)???????????/*from  w ww  . j av  a 2s . c om*/
 * <p>
 * X-Track,?????????????
 * </p>
 * @param xTrack ????????????null
 * @param loggerNamePattern ??????????null
 * @param messagePattern ?
 * @param exceptionMessagePattern ?()
 */
public void assertContainsByRegexExceptionMessage(String xTrack, String loggerNamePattern,
        String messagePattern, String exceptionMessagePattern) {

    StringBuilder sql = new StringBuilder();
    StringBuilder where = new StringBuilder();
    sql.append("SELECT COUNT(e.*) FROM logging_event e");
    where.append(" WHERE e.formatted_message REGEXP :message");

    sql.append(" JOIN logging_event_exception ee ON ee.event_id = e.event_id");
    where.append(" AND ee.I = '0' AND ee.TRACE_LINE REGEXP :exceptionMessage");

    if (StringUtils.hasText(xTrack)) {
        sql.append(" JOIN logging_event_property ep ON ep.event_id = e.event_id");
        where.append(" AND ep.mapped_key = 'X-Track' AND ep.mapped_value = :xTrack");
    }
    if (StringUtils.hasText(loggerNamePattern)) {
        where.append(" AND e.logger_name REGEXP :loggerName");
    }
    sql.append(where);

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("xTrack", xTrack);
    params.addValue("loggerName", loggerNamePattern);
    params.addValue("message", messagePattern);
    params.addValue("exceptionMessage", exceptionMessagePattern);
    Long count = jdbcOperations.queryForObject(sql.toString(), params, Long.class);
    assertThat(count, is(1L));
}

From source file:org.owasp.proxy.http.dao.JdbcMessageDAO.java

public int getMessageContentId(int headerId) throws DataAccessException {
    try {//from ww w .  j  av  a  2 s .com
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, headerId, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(getNamedParameterJdbcTemplate());
        return template.queryForInt(SELECT_CONTENT_ID, params);
    } catch (EmptyResultDataAccessException erdae) {
        return -1;
    }
}

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

@Transactional(propagation = Propagation.MANDATORY)
public Long createTempStringList(final Long listId, final Collection<String> list) {
    Assert.notNull(listId);//from  ww w.j  a v  a 2s  .  c  om
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryStringListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<String> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryStringListItemQuery, batchArgs);
    return listId;
}

From source file:com.p5solutions.core.jpa.orm.EntityPersisterImpl.java

/**
 * Process.//from ww w  .j ava 2 s .  c  o  m
 * 
 * @param entityClass
 *          the entity class
 * @param entity
 *          the entity
 * @return the map sql parameter source
 * @see com.p5solutions.core.jpa.orm.EntityPersister#process(java.lang.Class, java.lang.Object)
 */
@Override
public MapSqlParameterSource process(Class<?> entityClass, Object entity) {

    // TODO needs serious cleaning up... some sort of state-machine type
    // processing.

    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    EntityDetail<?> entityDetail = getEntityUtility().getEntityDetail(entityClass);
    List<ParameterBinder> pbs = entityDetail.getParameterBinders();

    boolean isDebug = logger.isDebugEnabled();
    logger.debug("** Mapping values from entity -> " + entity.getClass() + " to DML parameter source.");

    for (ParameterBinder pb : pbs) {
        Object value = null;
        String bindingPath = pb.getBindingPath();
        String bindingPathSQL = pb.getBindingPathSQL();
        String debugMessage = "     ";

        if (pb.isPrimaryKey()) {
            if (isDebug) {
                debugMessage += "[* Id] ";
            }
            // TODO probably a good idea to implement @EmbeddedId
            // TODO probably a good idea to implement @IdClass

            // let the below code handle the value retrieval.
            // value = mapUtility.get(pb, entity, bindingPath);
        }

        if (pb.isColumn()) {
            value = mapUtility.get(pb, entity, bindingPath);
        } else if (pb.isEmbedded()) {
            value = mapUtility.get(pb, entity, bindingPath);
        } else if (pb.isJoinColumn()) {
            value = getJoinColumnValue(entity, pb);
        }

        if (isDebug) {
            debugMessage = "Binding parameter [" + pb.toString() + "]";
        }

        // check if the value is a generated value
        if (pb.isGeneratedValue() && value == null) {
            String sequenceName = pb.getSequenceName();
            value = getTransactionTemplate().getSequenceValue(sequenceName);

            // Map the sequence value to the entity's parameter
            mapUtility.map(pb, entity, value, bindingPath);

            if (isDebug) {
                debugMessage += " <Generated> using sequence name " + sequenceName;
            }
        }

        if (isDebug) {
            if (value != null) {
                debugMessage += " with value of " + value;
            } else {
                debugMessage += " with value of <DBNull>";
            }

            logger.debug(debugMessage);
        }

        // use the binding path as the binding name of the sql paramater
        // source since embedded, or join objects can be multi-level depths.
        paramSource.addValue(bindingPathSQL, value);
    }
    return paramSource;
}

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

/**
 * Populate the droplet media table.// w  ww.j  a v  a2s.  com
 * 
 * @param drops
 */
private void insertDropletMedia(List<Drop> drops) {

    // List of drop IDs in the drops list
    List<Long> dropIds = new ArrayList<Long>();
    // List of media in a drop
    Map<Long, Set<Long>> dropletMediaMap = new HashMap<Long, Set<Long>>();
    // List of drops and the media that is the drop image
    final List<long[]> dropImages = new ArrayList<long[]>();
    for (Drop drop : drops) {

        if (drop.getMedia() == null)
            continue;

        dropIds.add(drop.getId());

        for (Media media : drop.getMedia()) {
            Set<Long> m = null;
            if (dropletMediaMap.containsKey(drop.getId())) {
                m = dropletMediaMap.get(drop.getId());
            } else {
                m = new HashSet<Long>();
                dropletMediaMap.put(drop.getId(), m);
            }

            // Is this the drop image?
            if (drop.getImage() != null && media.getUrl().equals(drop.getImage().getUrl())) {
                long[] dropImage = { drop.getId(), media.getId() };
                dropImages.add(dropImage);
            }

            m.add(media.getId());
        }
    }

    // Find droplet media that already exist in the db
    String sql = "SELECT droplet_id, media_id FROM droplets_media WHERE droplet_id in (:ids)";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", dropIds);

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

    // Remove already existing droplet_media from our Set
    for (Map<String, Object> result : results) {
        long dropletId = ((Number) result.get("droplet_id")).longValue();
        long mediaId = ((Number) result.get("media_id")).longValue();

        Set<Long> mediaSet = dropletMediaMap.get(dropletId);
        if (mediaSet != null) {
            mediaSet.remove(mediaId);
        }
    }

    // Insert the remaining items in the set into the db
    sql = "INSERT INTO droplets_media (droplet_id, media_id) VALUES (?,?)";

    final List<long[]> dropletMediaList = new ArrayList<long[]>();
    for (Long dropletId : dropletMediaMap.keySet()) {
        for (Long mediaId : dropletMediaMap.get(dropletId)) {
            long[] dropletMedia = { dropletId, mediaId };
            dropletMediaList.add(dropletMedia);
        }
    }
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            long[] dropletMedia = dropletMediaList.get(i);
            ps.setLong(1, dropletMedia[0]);
            ps.setLong(2, dropletMedia[1]);
        }

        public int getBatchSize() {
            return dropletMediaList.size();
        }
    });

    if (dropImages.size() > 0) {
        sql = "UPDATE droplets SET droplet_image = ? WHERE id = ?";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                long[] update = dropImages.get(i);
                ps.setLong(1, update[1]);
                ps.setLong(2, update[0]);
            }

            public int getBatchSize() {
                return dropImages.size();
            }
        });
    }
}