List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue
public MapSqlParameterSource addValue(String paramName, @Nullable Object value)
From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java
@Override public void saveRowOfShapes(RowOfShapes row) { try {//from w w w . j a v a 2 s . co m NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); RowOfShapesInternal iRow = (RowOfShapesInternal) row; paramSource.addValue("row_paragraph_id", row.getParagraphId()); paramSource.addValue("row_index", row.getIndex()); paramSource.addValue("row_height", row.getXHeight()); String sql = null; if (row.isNew()) { sql = "SELECT nextval('ocr_row_id_seq')"; LOG.debug(sql); int rowId = jt.queryForInt(sql, paramSource); paramSource.addValue("row_id", rowId); ByteArrayOutputStream os = new ByteArrayOutputStream(); ImageIO.write(row.getImage(), "png", os); os.flush(); paramSource.addValue("row_image", os.toByteArray()); os.close(); sql = "INSERT INTO ocr_row (row_id, row_paragraph_id, row_index, row_image, row_height) " + "VALUES (:row_id, :row_paragraph_id, :row_index, :row_image, :row_height)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); iRow.clearMemory(); iRow.setId(rowId); } else { paramSource.addValue("row_id", row.getId()); sql = "UPDATE ocr_row" + " SET row_paragraph_id = :row_paragraph_id" + ", row_index = :row_index" + ", row_height = :row_height" + " WHERE row_id = :row_id"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); } } catch (IOException ioe) { throw new RuntimeException(ioe); } }
From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java
@Override public void saveJochreImage(JochreImage image) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); JochreImageInternal iImage = (JochreImageInternal) image; paramSource.addValue("image_name", image.getName()); paramSource.addValue("image_width", image.getWidth()); paramSource.addValue("image_height", image.getHeight()); paramSource.addValue("image_black_threshold", image.getBlackThreshold()); paramSource.addValue("image_sep_threshold", image.getSeparationThreshold()); paramSource.addValue("image_black_limit", image.getBlackLimit()); paramSource.addValue("image_white_limit", image.getWhiteLimit()); paramSource.addValue("image_white_gap_fill_factor", image.getWhiteGapFillFactor()); paramSource.addValue("image_page_id", image.getPageId()); paramSource.addValue("image_index", image.getIndex()); paramSource.addValue("image_imgstatus_id", image.getImageStatus().getId()); paramSource.addValue("image_owner_id", image.getOwnerId()); String sql = null;//from w ww .ja va 2s. com if (image.isNew()) { sql = "SELECT nextval('ocr_image_id_seq')"; LOG.debug(sql); int imageId = jt.queryForInt(sql, paramSource); paramSource.addValue("image_id", imageId); sql = "INSERT INTO ocr_image (image_id, image_name, image_width, image_height, image_black_threshold," + " image_page_id, image_index, image_sep_threshold, image_black_limit, image_white_limit," + " image_white_gap_fill_factor, image_imgstatus_id, image_owner_id) " + "VALUES (:image_id, :image_name, :image_width, :image_height, :image_black_threshold," + " :image_page_id, :image_index, :image_sep_threshold, :image_black_limit, :image_white_limit," + " :image_white_gap_fill_factor, :image_imgstatus_id, :image_owner_id)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); iImage.setId(imageId); } else { paramSource.addValue("image_id", image.getId()); sql = "UPDATE ocr_image" + " SET image_name = :image_name" + ", image_width = :image_width" + ", image_height = :image_height" + ", image_black_threshold = :image_black_threshold" + ", image_sep_threshold = :image_sep_threshold" + ", image_black_limit = :image_black_limit" + ", image_white_limit = :image_white_limit" + ", image_white_gap_fill_factor = :image_white_gap_fill_factor" + ", image_page_id = :image_page_id" + ", image_index = :image_index" + ", image_imgstatus_id = :image_imgstatus_id" + ", image_owner_id = :image_owner_id" + " WHERE image_id = :image_id"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); } }
From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java
@Override public void saveShape(Shape shape) { // note: update will not update the pixels (not strictly required). try {// w w w.j a v a2s .c o m NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); ShapeInternal iShape = (ShapeInternal) shape; paramSource.addValue("shape_top", shape.getTop()); paramSource.addValue("shape_left", shape.getLeft()); paramSource.addValue("shape_bottom", shape.getBottom()); paramSource.addValue("shape_right", shape.getRight()); paramSource.addValue("shape_cap_line", shape.getCapLine()); paramSource.addValue("shape_mean_line", shape.getMeanLine()); paramSource.addValue("shape_base_line", shape.getBaseLine()); paramSource.addValue("shape_letter", shape.getLetter()); paramSource.addValue("shape_original_guess", shape.getOriginalGuess()); paramSource.addValue("shape_group_id", shape.getGroupId()); paramSource.addValue("shape_index", shape.getIndex()); String sql = null; if (shape.isNew()) { sql = "SELECT nextval('ocr_shape_id_seq')"; LOG.debug(sql); int shapeId = jt.queryForInt(sql, paramSource); paramSource.addValue("shape_id", shapeId); ByteArrayOutputStream os = new ByteArrayOutputStream(); ImageIO.write(shape.getImage(), "png", os); os.flush(); paramSource.addValue("shape_pixels", os.toByteArray()); os.close(); sql = "INSERT INTO ocr_shape (shape_id, shape_top, shape_left, shape_bottom, shape_right" + ", shape_cap_line, shape_mean_line, shape_base_line, shape_pixels, shape_letter, shape_group_id" + ", shape_index, shape_original_guess) " + "VALUES (:shape_id, :shape_top, :shape_left, :shape_bottom, :shape_right" + ", :shape_cap_line, :shape_mean_line, :shape_base_line, :shape_pixels, :shape_letter, :shape_group_id" + ", :shape_index, :shape_original_guess)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); iShape.setId(shapeId); } else { paramSource.addValue("shape_id", shape.getId()); sql = "UPDATE ocr_shape" + " SET shape_top = :shape_top" + ", shape_left = :shape_left" + ", shape_bottom = :shape_bottom" + ", shape_right = :shape_right" + ", shape_cap_line = :shape_cap_line" + ", shape_mean_line = :shape_mean_line" + ", shape_base_line = :shape_base_line" + ", shape_letter = :shape_letter" + ", shape_group_id = :shape_group_id" + ", shape_index = :shape_index " + ", shape_original_guess = :shape_original_guess " + " WHERE shape_id = :shape_id"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); } } catch (IOException e) { throw new RuntimeException(e); } }
From source file:info.raack.appliancelabeler.data.JDBCDatabase.java
private EnergyMeasurementQueryItems getEnergyMeasurementQueryItems(EnergyMonitor energyMonitor, Date start, Date end, int ticks) { List<Long> dates = new ArrayList<Long>(); long totalSeconds = (end.getTime() - start.getTime()) / 1000; float spaceBetweenTicks = totalSeconds / ticks; MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue("id", energyMonitor.getId()); String sql = ""; if (spaceBetweenTicks > 1.0f) { // create explicitly dates on which to query. This would seem to be the most efficient way to query, since I have a composite index on energy_monitor_id and reading_time, rather than using rownums and the % operator (which would need to create extra logger.debug("Running query for specific dates"); float currentIncrement = 0f; Calendar c = new GregorianCalendar(); c.setTime(end);/* ww w .ja v a 2 s . com*/ for (int i = 0; i < ticks; i++) { dates.add(c.getTimeInMillis()); // increment by seconds, roughly currentIncrement -= spaceBetweenTicks; c.setTime(end); c.add(Calendar.SECOND, (int) currentIncrement); } Collections.reverse(dates); logger.debug("First date: " + dates.get(0)); logger.debug("Last date: " + dates.get(dates.size() - 1)); parameters.addValue("timestamps", dates); sql = queryForTedFeedMeasurementsOnSpecificSeconds; } else { // we want to get every tick, since we can't split a second logger.debug("Running query for all dates"); parameters.addValue("min", start.getTime()); parameters.addValue("max", end.getTime()); sql = queryForTedFeedMeasurementsWithTimeBoundaries; } EnergyMeasurementQueryItems items = new EnergyMeasurementQueryItems(); items.sql = sql; items.parameters = parameters; return items; }
From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaDropDao.java
/** * Populate the rivers_droplets table// w w w . j a v a 2s. com * * @param drops */ private void insertRiverDrops(final List<Drop> drops) { // Get a lock on rivers_droplets Sequence seq = sequenceDao.findById("rivers_droplets"); // Mapping of drop id to list index position final Map<Long, Integer> dropIndex = new HashMap<Long, Integer>(); // List of rivers in a drop Map<Long, Set<Long>> dropRiversMap = new HashMap<Long, Set<Long>>(); Map<Long, Set<Long>> dropChannelsMap = new HashMap<Long, Set<Long>>(); // Registry for all channels and rivers Set<Long> allChannelIds = new HashSet<Long>(); int i = 0; for (Drop drop : drops) { if (drop.getRiverIds() == null || drop.getChannelIds() == null) { logger.debug("No rivers or channels for drop {}", drop.getId()); continue; } Set<Long> rivers = new HashSet<Long>(); Set<Long> channels = new HashSet<Long>(); rivers.addAll(drop.getRiverIds()); channels.addAll(drop.getChannelIds()); dropRiversMap.put(drop.getId(), rivers); dropChannelsMap.put(drop.getId(), channels); allChannelIds.addAll(channels); dropIndex.put(drop.getId(), i++); } // No rivers found, exit if (dropIndex.size() == 0) return; // Find already existing rivers_droplets String sql = "SELECT droplet_id, river_id FROM rivers_droplets WHERE droplet_id in (:ids)"; MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("ids", dropIndex.keySet()); List<Map<String, Object>> results = this.namedJdbcTemplate.queryForList(sql, params); logger.debug("Skipping {} entries from rivers_droplets", results.size()); // Remove existing rivers_droplets entries from our Set for (Map<String, Object> row : results) { Long dropletId = ((Number) row.get("droplet_id")).longValue(); Long riverId = ((Number) row.get("river_id")).longValue(); Set<Long> riverSet = dropRiversMap.remove(dropletId); if (riverSet != null) { riverSet.remove(riverId); // Only add back the destination rivers if the set is non empty if (!riverSet.isEmpty()) { dropRiversMap.put(dropletId, riverSet); } } } // If all drops are duplicates, return early if (dropRiversMap.isEmpty()) { logger.info("No drops to add to the rivers"); return; } // Associate the channels with active rivers sql = "SELECT rc.id, rc.river_id " + "FROM river_channels rc " + "INNER JOIN rivers r ON (rc.river_id = r.id) " + "WHERE rc.id IN (:channelIds) " + "AND r.river_active = 1"; MapSqlParameterSource channelParams = new MapSqlParameterSource(); channelParams.addValue("channelIds", allChannelIds); Map<Long, Long> riverChannelsMap = new HashMap<Long, Long>(); for (Map<String, Object> row : namedJdbcTemplate.queryForList(sql, channelParams)) { Long channelId = ((Number) row.get("id")).longValue(); Long riverId = ((Number) row.get("river_id")).longValue(); riverChannelsMap.put(channelId, riverId); } // Map to hold the association between a drop, river and channel // During the association, we verify that the river is in the drop's // destination river list final List<Map<String, Long>> riverDropChannelList = new ArrayList<Map<String, Long>>(); Set<RiverDropKey> riverDropKeySet = new HashSet<JpaDropDao.RiverDropKey>(); for (Long dropletId : dropChannelsMap.keySet()) { for (Long channelId : dropChannelsMap.get(dropletId)) { if (riverChannelsMap.containsKey(channelId)) { Long riverId = riverChannelsMap.get(channelId); // Does the river drop key already exist? RiverDropKey riverDropKey = new RiverDropKey(riverId, dropletId); if (riverDropKeySet.contains(riverDropKey)) continue; // Does not exist. Add to the in-memory registry riverDropKeySet.add(riverDropKey); if (dropRiversMap.containsKey(dropletId) && dropRiversMap.get(dropletId).contains(riverId)) { Map<String, Long> entry = new HashMap<String, Long>(); entry.put("dropletId", dropletId); entry.put("channelId", channelId); entry.put("riverId", riverId); riverDropChannelList.add(entry); } } } } logger.debug("Posting drops to rivers"); // Insert the remaining items in the set into the DB sql = "INSERT INTO `rivers_droplets` (`id`, `droplet_id`, `river_id`, " + "`river_channel_id`, `droplet_date_pub`) " + "VALUES (?, ?, ?, ?, ?)"; final long startKey = sequenceDao.getIds(seq, riverDropChannelList.size()); // Map to hold to hold the no. of drops created per channel final Map<Long, Long> channelDropCountMap = new HashMap<Long, Long>(); // A map to hold the new max_drop_id and drop_count per river final Map<Long, long[]> riverDropsMap = new HashMap<Long, long[]>(); jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Map<String, Long> dropEntry = riverDropChannelList.get(i); long id = startKey + i; Long dropletId = dropEntry.get("dropletId"); Long riverId = dropEntry.get("riverId"); Long channelId = dropEntry.get("channelId"); Drop drop = drops.get(dropIndex.get(dropletId)); ps.setLong(1, id); ps.setLong(2, dropletId); ps.setLong(3, riverId); ps.setLong(4, channelId); ps.setTimestamp(5, new java.sql.Timestamp(drop.getDatePublished().getTime())); // Get updated max_drop_id and drop_count for the rivers table long[] update = riverDropsMap.get(riverId); if (update == null) { long[] u = { id, 1 }; riverDropsMap.put(riverId, u); } else { update[0] = Math.max(update[0], id); update[1] = update[1] + 1; } // Update the drop count for the channel Long channelDropCount = channelDropCountMap.remove(channelId); channelDropCount = (channelDropCount == null) ? 1L : Long.valueOf(channelDropCount.longValue() + 1); channelDropCountMap.put(channelId, channelDropCount); } public int getBatchSize() { return riverDropChannelList.size(); } }); logger.debug("Drops successfully posted to rivers"); // Update river max_drop_id and drop_count logger.debug("Updating river drop counters"); sql = "UPDATE rivers SET max_drop_id = ?, drop_count = drop_count + ? WHERE id = ?"; final List<Entry<Long, long[]>> riverUpdate = new ArrayList<Entry<Long, long[]>>(); riverUpdate.addAll(riverDropsMap.entrySet()); this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Entry<Long, long[]> entry = riverUpdate.get(i); ps.setLong(1, entry.getValue()[0]); ps.setLong(2, entry.getValue()[1]); ps.setLong(3, entry.getKey()); } public int getBatchSize() { return riverUpdate.size(); } }); logger.debug("{} rivers successfully updated", riverUpdate.size()); // Update the drop_count in TABLE `river_channels` logger.debug("Updating river channel statistics"); sql = "UPDATE river_channels SET drop_count = drop_count + ? WHERE id = ?"; final List<Entry<Long, Long>> riverChannelUpdate = new ArrayList<Entry<Long, Long>>(); riverChannelUpdate.addAll(channelDropCountMap.entrySet()); this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Entry<Long, Long> entry = riverChannelUpdate.get(i); ps.setLong(1, entry.getValue()); ps.setLong(2, entry.getKey()); } @Override public int getBatchSize() { return riverChannelUpdate.size(); } }); logger.debug("{} channels updated", riverChannelUpdate.size()); // Insert the trend data logger.debug("Updating trend statistics"); try { insertRiverTagTrends(drops, dropIndex, riverDropChannelList); } catch (Exception e) { logger.error("An error occurred while inserting the trend data", e); } }
From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java
public List<JochreImage> findImages(ImageStatus[] imageStatuses) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_IMAGE + " FROM ocr_image WHERE image_imgstatus_id in (:image_imgstatus_id)" + " ORDER BY image_id"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); List<Integer> imageStatusList = new ArrayList<Integer>(); for (ImageStatus imageStatus : imageStatuses) imageStatusList.add(imageStatus.getId()); paramSource.addValue("image_imgstatus_id", imageStatusList); LOG.debug(sql);/* w w w . j a v a2 s . c o m*/ logParameters(paramSource); @SuppressWarnings("unchecked") List<JochreImage> images = jt.query(sql, paramSource, new JochreImageMapper(this.getGraphicsServiceInternal())); return images; }
From source file:org.springframework.cloud.stream.app.jdbc.sink.JdbcSinkConfiguration.java
@Bean @ServiceActivator(autoStartup = "false", inputChannel = Sink.INPUT) public JdbcMessageHandler jdbcMessageHandler(DataSource dataSource) { final MultiValueMap<String, Expression> columnExpressionVariations = new LinkedMultiValueMap<>(); for (Map.Entry<String, String> entry : properties.getColumns().entrySet()) { String value = entry.getValue(); columnExpressionVariations.add(entry.getKey(), spelExpressionParser.parseExpression(value)); if (!value.startsWith("payload")) { columnExpressionVariations.add(entry.getKey(), spelExpressionParser.parseExpression("payload." + value)); }/*w w w . ja v a2s . com*/ } JdbcMessageHandler jdbcMessageHandler = new JdbcMessageHandler(dataSource, generateSql(properties.getTableName(), columnExpressionVariations.keySet())); jdbcMessageHandler.setSqlParameterSourceFactory(new SqlParameterSourceFactory() { @Override public SqlParameterSource createParameterSource(Object o) { if (!(o instanceof Message)) { throw new IllegalArgumentException("Unable to handle type " + o.getClass().getName()); } Message<?> message = (Message<?>) o; MapSqlParameterSource parameterSource = new MapSqlParameterSource(); for (String key : columnExpressionVariations.keySet()) { List<Expression> spels = columnExpressionVariations.get(key); Object value = NOT_SET; EvaluationException lastException = null; for (Expression spel : spels) { try { value = spel.getValue(evaluationContext, message); break; } catch (EvaluationException e) { lastException = e; } } if (value == NOT_SET) { if (lastException != null) { logger.info( "Could not find value for column '" + key + "': " + lastException.getMessage()); } parameterSource.addValue(key, null); } else { if (value instanceof JsonPropertyAccessor.ToStringFriendlyJsonNode) { // Need to do some reflection until we have a getter for the Node DirectFieldAccessor dfa = new DirectFieldAccessor(value); JsonNode node = (JsonNode) dfa.getPropertyValue("node"); Object valueToUse; if (node == null || node.isNull()) { valueToUse = null; } else if (node.isNumber()) { valueToUse = node.numberValue(); } else if (node.isBoolean()) { valueToUse = node.booleanValue(); } else { valueToUse = node.textValue(); } parameterSource.addValue(key, valueToUse); } else { parameterSource.addValue(key, value); } } } return parameterSource; } }); return jdbcMessageHandler; }
From source file:com.opengamma.masterdb.batch.DbBatchWriter.java
protected StatusEntry.Status getStatus(Map<Pair<Long, Long>, StatusEntry> statusCache, String calcConfName, ComputationTargetSpecification ct) { Long calcConfId = _calculationConfigurations.get(calcConfName); Long computationTargetId = _computationTargets.get(ct); // first check to see if this status has already been queried for // and if the answer could therefore be found in the cache Pair<Long, Long> key = Pair.of(calcConfId, computationTargetId); if (statusCache.containsKey(key)) { StatusEntry existingStatusEntryInDb = statusCache.get(key); if (existingStatusEntryInDb != null) { // status entry in db. return existingStatusEntryInDb.getStatus(); } else {/*w w w . j a v a2 s . c o m*/ // no status entry in db. return StatusEntry.Status.NOT_RUNNING; } } MapSqlParameterSource args = new MapSqlParameterSource(); args.addValue("calculation_configuration_id", calcConfId); args.addValue("computation_target_id", computationTargetId); try { StatusEntry statusEntry = getJdbcTemplate().queryForObject(getElSqlBundle().getSql("SelectStatusEntry"), args, DbBatchUtils.ROW_MAPPER); // status entry in db found. statusCache.put(key, statusEntry); return statusEntry.getStatus(); } catch (IncorrectResultSizeDataAccessException e) { // no status entry in the db. statusCache.remove(key); return StatusEntry.Status.NOT_RUNNING; } }
From source file:com.p5solutions.core.jpa.orm.EntityPersisterImpl.java
/** * Delete./*from ww w . ja v a2s.com*/ * * @param <T> * the generic type * @param tableClass * the table class * @param id * the id * @return the int * @throws Exception * the exception * @see com.p5solutions.core.jpa.orm.EntityPersister#delete(java.lang.Class, java.lang.Object) */ @Override public <T> int delete(Class<T> tableClass, Object id) throws Exception { // T t = saveUpdateOrDelete(entity, OperationType.DELETE); // TODO check for null on entity detail EntityDetail<T> entityDetail = getEntityUtility().getEntityDetail(tableClass); // get the dml operation DMLOperation operation = getEntityUtility().getDMLOperation(tableClass, OperationType.DELETE); MapSqlParameterSource paramSource = new MapSqlParameterSource(); // build the parameter value list List<ParameterBinder> pkParameterBinders = entityDetail.getPrimaryKeyParameterBinders(); if (pkParameterBinders == null) { String msg = "Primary key parameter binders cannot be null for given table-entity class type of " + tableClass; logger.error(msg); throw new NullPointerException(msg); } else if (pkParameterBinders.size() != 1) { String msg = "There is a total of " + pkParameterBinders.size() + " when there should only be one, when calling delete(clazz, id);"; logger.error(msg); throw new RuntimeException(msg); } ParameterBinder pkpb = pkParameterBinders.get(0); paramSource.addValue(pkpb.getBindingName(), id); Integer updated = getJdbcTemplate().execute(operation.getStatement(), paramSource, new PersistPreparedStatementCallback<Integer>()); return updated; // return the number of rows affected //return ret instanceof Integer ? ((Integer) ret).intValue() : 0; }
From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java
public void savePhraseDescendantMapping(Phrase parent, Phrase descendant) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("pchild_phrase_id", parent.getId()); paramSource.addValue("pchild_child_id", descendant.getId()); String sql = "INSERT INTO ftb_phrase_child (pchild_phrase_id, pchild_child_id) VALUES (:pchild_phrase_id, :pchild_child_id)"; LOG.info(sql);/*from ww w .j a va 2 s. co m*/ TreebankDaoImpl.LogParameters(paramSource); jt.update(sql, paramSource); }