Example usage for org.springframework.jdbc.core.namedparam MapSqlParameterSource 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.perry.infrastructure.call.CallDaoServiceImpl.java

@Override
public Call getTruckActive(long truckId) {
    String sql = "select * from calls where truck_id = :truckId";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("truckId", truckId);
    List<Call> callList = namedParameterJdbcTemplate.query(sql, params, new CallRowMapper());
    if (!callList.isEmpty()) {
        return callList.get(0);
    }//from   w  ww.j  ava  2 s. c o  m
    return null;
}

---

From source file:org.aksw.gerbil.database.ExperimentDAOImpl.java

@Override
public void setExperimentState(int experimentTaskId, int state) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("id", experimentTaskId);
    parameters.addValue("state", state);
    java.util.Date today = new java.util.Date();
    parameters.addValue("lastChanged", new java.sql.Timestamp(today.getTime()));
    this.template.update(SET_TASK_STATE, parameters);
}

---

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

/**
 * Populate the droplet links table./*w  ww .  j av  a 2s  .  co  m*/
 * 
 * @param drops
 */
private void insertDropletLinks(List<Drop> drops) {

    // List of drop IDs in the drops list
    List<Long> dropIds = new ArrayList<Long>();
    // List of links in a drop
    Map<Long, Set<Long>> dropletLinksMap = new HashMap<Long, Set<Long>>();
    // List of drops and the link that is their original url
    final List<long[]> originalUrls = new ArrayList<long[]>();
    for (Drop drop : drops) {

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

        dropIds.add(drop.getId());

        for (Link link : drop.getLinks()) {
            Set<Long> links = null;
            if (dropletLinksMap.containsKey(drop.getId())) {
                links = dropletLinksMap.get(drop.getId());
            } else {
                links = new HashSet<Long>();
                dropletLinksMap.put(drop.getId(), links);
            }

            // Is this link the original url?
            if (drop.getOriginalUrl() != null && link.getUrl().equals(drop.getOriginalUrl().getUrl())) {
                long[] originalUrl = { drop.getId(), link.getId() };
                originalUrls.add(originalUrl);
            }

            links.add(link.getId());
        }
    }

    // Find droplet links that already exist in the db
    String sql = "SELECT droplet_id, link_id FROM droplets_links 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_links from our Set
    for (Map<String, Object> result : results) {
        long dropletId = ((Number) result.get("droplet_id")).longValue();
        long linkId = ((Number) result.get("link_id")).longValue();

        Set<Long> linkSet = dropletLinksMap.get(dropletId);
        if (linkSet != null) {
            linkSet.remove(linkId);
        }
    }

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

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

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

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

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

---

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

/**
 * Populate the droplet tags table./*from  w ww . j  a  va 2 s .co m*/
 * 
 * @param drops
 */
private void insertDropletTags(List<Drop> drops) {

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

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

        dropIds.add(drop.getId());

        for (Tag tag : drop.getTags()) {
            Set<Long> tags = null;
            if (dropletTagsMap.containsKey(drop.getId())) {
                tags = dropletTagsMap.get(drop.getId());
            } else {
                tags = new HashSet<Long>();
                dropletTagsMap.put(drop.getId(), tags);
            }

            tags.add(tag.getId());
        }
    }

    // Find droplet tags that already exist in the db
    String sql = "SELECT droplet_id, tag_id FROM droplets_tags 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_tags from our Set
    for (Map<String, Object> result : results) {
        long dropletId = ((Number) result.get("droplet_id")).longValue();
        long tagId = ((Number) result.get("tag_id")).longValue();

        Set<Long> tagSet = dropletTagsMap.get(dropletId);
        if (tagSet != null) {
            tagSet.remove(tagId);
        }
    }

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

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

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

---

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

public Collection<Integer> listConversationsSince(int id) throws DataAccessException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    try {//from  w w  w .  j a va  2s  .c  om
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(getNamedParameterJdbcTemplate());
        return template.query(SELECT_CONVERSATIONS, ID_MAPPER, params);
    } catch (EmptyResultDataAccessException erdae) {
        return Collections.emptyList();
    }
}

---

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 a va  2s  . c  o m

    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.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java

@Override
public List<Term> getTermsByFrequency(final int frequencyThreshold) {
    MONITOR.startTask("getTermsByFrequency");
    try {//ww w.  ja  v  a 2 s .c  o  m
        NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
        String sql = "SELECT " + SELECT_TERM + " FROM term" + " INNER JOIN text ON term_text_id=text_id"
                + " WHERE term_frequency >= :term_frequency" + " AND term_project_id = :term_project_id"
                + " ORDER BY term_frequency DESC, text_text";
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("term_frequency", frequencyThreshold);
        paramSource.addValue("term_project_id", this.getCurrentProjectId());

        LOG.trace(sql);
        LogParameters(paramSource);
        @SuppressWarnings("unchecked")
        List<Term> terms = jt.query(sql, paramSource, new TermMapper());

        return terms;
    } finally {
        MONITOR.endTask("getTermsByFrequency");
    }
}

---

From source file:org.aksw.gerbil.database.ExperimentDAOImpl.java

@Override
public int getExperimentState(int experimentTaskId) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("id", experimentTaskId);
    List<Integer> result = this.template.query(GET_TASK_STATE, parameters, new IntegerRowMapper());
    if (result.size() > 0) {
        return result.get(0);
    } else {//from   ww  w. j  a v  a 2 s .  c  o m
        return TASK_NOT_FOUND;
    }
}

---

From source file:Implement.DAO.CommonDAOImpl.java

@Override
public ProviderSignupForm getProviderSignupForm() {
    simpleJdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getProviderSignupForm");
    simpleJdbcCall.returningResultSet("rs1", CountryMapper.getInstance())
            .returningResultSet("rs2", BusinessTypeMapper.getInstance())
            .returningResultSet("rs3", CityDTOMapper.getInstance());
    SqlParameterSource in = new MapSqlParameterSource();
    Map<String, Object> record = simpleJdbcCall.execute(in);
    List<CountryDTO> countries = (List<CountryDTO>) record.get("rs1");
    List<BusinessTypeDTO> businessTypes = (List<BusinessTypeDTO>) record.get("rs2");
    List<CityDTO> cities = (List<CityDTO>) record.get("rs3");
    return new ProviderSignupForm(countries, businessTypes, cities);
}

---

From source file:org.jasig.schedassist.impl.statistics.SpringJDBCStatisticsDaoImpl.java

@Override
public List<AppointmentEvent> getEvents(final IScheduleOwner owner, final String visitorUsername,
        final Date startTime, final Date endTime, final List<EventType> eventTypes) {
    MapSqlParameterSource parameterSource = new MapSqlParameterSource();
    parameterSource.addValue("ownerId", owner.getId());
    parameterSource.addValue("visitorId", visitorUsername);
    parameterSource.addValue("startTime", startTime);
    parameterSource.addValue("endTime", endTime);
    parameterSource.addValue("eventTypes", eventTypes);
    List<AppointmentEvent> results = this.simpleJdbcTemplate.query(
            "select event_id,owner_id,visitor_id,event_type,event_timestamp,event_start from event_statistics where owner_id = :ownerId and visitor_id = :visitorId and event_timestamp >= :startTime and event_timestamp <= :endTime and event_type in (:eventTypes)",
            new AppointmentEventRowMapper(ownerDao), parameterSource);
    return results;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• »