List of usage examples for java.sql PreparedStatement setLong
void setLong(int parameterIndex, long x) throws SQLException;
long value. From source file:com.mirth.connect.donkey.test.util.TestUtils.java
public static void showContent(String testMessage, String channelId, String serverId, StorageSettings storageSettings) throws Exception { Channel channel = createDefaultChannel(channelId, serverId, true, 1, 1, storageSettings); channel.deploy();/* w ww.j ava2 s. c o m*/ channel.start(null); DispatchResult dispatchResult = null; try { dispatchResult = channel.getSourceConnector().dispatchRawMessage(new RawMessage(testMessage)); channel.getSourceConnector().finishDispatch(dispatchResult); } finally { channel.stop(); channel.undeploy(); } long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId); Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; try { connection = getConnection(); statement = connection .prepareStatement("SELECT * FROM d_mc" + localChannelId + " WHERE message_id = ?"); statement.setLong(1, dispatchResult.getMessageId()); resultSet = statement.executeQuery(); while (resultSet.next()) { String content = resultSet.getString("content"); ContentType contentType = ContentType.fromCode(resultSet.getInt("content_type")); System.out.printf("%-20s%-5d%s\n", contentType.name(), resultSet.getInt("metadata_id"), content.substring(0, Math.min(10, content.length()))); } } finally { close(connection); close(statement); close(resultSet); } System.out.println(); }
From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaDropDao.java
/** * Populates the river_tag_trends table/*w ww . j a v a2s. c om*/ * * @param drops * @param dropIndex * @param riverDropChannelList * @throws Exception */ private void insertRiverTagTrends(List<Drop> drops, Map<Long, Integer> dropIndex, List<Map<String, Long>> riverDropChannelList) throws Exception { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd H:00:00"); Map<String, RiverTagTrend> trendsData = new HashMap<String, RiverTagTrend>(); for (Map<String, Long> entry : riverDropChannelList) { Long dropletId = entry.get("dropletId"); Long riverId = entry.get("riverId"); River river = new River(); river.setId(riverId); Drop drop = drops.get(dropIndex.get(dropletId)); String datePublishedStr = dateFormat.format(drop.getDatePublished()); Date datePublished = dateFormat.parse(datePublishedStr); // Tags if (drop.getTags() != null) { for (Tag tag : drop.getTags()) { String hash = MD5Util.md5Hex(riverId.toString(), datePublishedStr, tag.getTag(), tag.getType()); RiverTagTrend tagTrend = trendsData.remove(hash); if (tagTrend == null) { tagTrend = new RiverTagTrend(); tagTrend.setRiver(river); tagTrend.setDatePublished(datePublished); tagTrend.setTag(tag.getTag()); tagTrend.setTagType(tag.getType()); tagTrend.setHash(hash); tagTrend.setCount(1L); } else { Long count = new Long(tagTrend.getCount() + 1L); tagTrend.setCount(count); } trendsData.put(hash, tagTrend); } } // Places if (drop.getPlaces() != null) { for (Place place : drop.getPlaces()) { String hash = MD5Util.md5Hex(riverId.toString(), datePublishedStr, place.getPlaceName(), "place"); RiverTagTrend tagTrend = trendsData.remove(hash); if (tagTrend == null) { tagTrend = new RiverTagTrend(); tagTrend.setRiver(river); tagTrend.setDatePublished(datePublished); tagTrend.setTag(place.getPlaceName()); tagTrend.setTagType("place"); tagTrend.setHash(hash); tagTrend.setCount(1L); } else { Long count = new Long(tagTrend.getCount() + 1L); tagTrend.setCount(count); } trendsData.put(hash, tagTrend); } } } if (trendsData.keySet().isEmpty()) return; // Check for existing trends String sql = "SELECT `id`, `hash` FROM `river_tag_trends` WHERE `hash` IN (:hashes)"; MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("hashes", trendsData.keySet()); // List of trend IDs whose count is to be updated final List<long[]> trendCountUpdate = new ArrayList<long[]>(); for (Map<String, Object> row : namedJdbcTemplate.queryForList(sql, params)) { String hash = (String) row.get("hash"); long trendId = ((Number) row.get("id")).longValue(); RiverTagTrend tagTrend = trendsData.remove(hash); long[] counters = { trendId, tagTrend.getCount() }; trendCountUpdate.add(counters); } // Update existing counters if (!trendCountUpdate.isEmpty()) { sql = "UPDATE `river_tag_trends` SET `count` = `count` + ? WHERE `id` = ?"; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { long[] updateIndex = trendCountUpdate.get(i); ps.setLong(1, updateIndex[1]); ps.setLong(2, updateIndex[0]); } public int getBatchSize() { return trendCountUpdate.size(); } }); } if (trendsData.isEmpty()) { return; } Sequence sequence = sequenceDao.findById("river_tag_trends"); final long startKey = sequenceDao.getIds(sequence, trendsData.size()); // SQL to update the river_tag_trends table sql = "INSERT INTO river_tag_trends(`id`, `hash`, `river_id`, `date_pub`, `tag`, " + "`tag_type`, `count`) VALUES(?, ?, ?, ?, ?, ?, ?)"; final List<Entry<String, RiverTagTrend>> tagTrendsList = new ArrayList<Entry<String, RiverTagTrend>>(); tagTrendsList.addAll(trendsData.entrySet()); jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { long id = startKey + i; Entry<String, RiverTagTrend> entry = tagTrendsList.get(i); RiverTagTrend tagTrend = entry.getValue(); ps.setLong(1, id); ps.setString(2, entry.getKey()); ps.setLong(3, tagTrend.getRiver().getId()); ps.setTimestamp(4, new java.sql.Timestamp(tagTrend.getDatePublished().getTime())); ps.setString(5, tagTrend.getTag()); ps.setString(6, tagTrend.getTagType()); ps.setLong(7, tagTrend.getCount()); } public int getBatchSize() { return tagTrendsList.size(); } }); }
From source file:com.mirth.connect.donkey.test.util.TestUtils.java
public static Integer getSendAttempts(String channelId, long messageId) throws SQLException { long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId); Connection connection = null; PreparedStatement statement = null; ResultSet result = null;/* www . j ava 2 s . c o m*/ try { connection = getConnection(); statement = connection.prepareStatement( "SELECT send_attempts FROM d_mm" + localChannelId + " WHERE message_id = ? AND id = ?"); statement.setLong(1, messageId); statement.setInt(2, 1); result = statement.executeQuery(); if (result.next()) { return result.getInt(1); } return null; } finally { close(result); close(statement); close(connection); } }
From source file:org.miloss.fgsms.services.rs.impl.reports.ws.ThroughputByHostingServer.java
@Override public void generateReport(OutputStreamWriter data, List<String> urls, String path, List<String> files, TimeRange range, String currentuser, SecurityWrapper classification, WebServiceContext ctx) throws IOException { if (!UserIdentityUtil.hasGlobalAdministratorRole(currentuser, "INVOCATIONS_BY_HOSTING_SERVER", classification, ctx)) {/*ww w. j a v a 2s .c om*/ data.write("<h2>Access for " + GetDisplayName() + " was denied for non-global admin users</h2>"); return; } double d = range.getEnd().getTimeInMillis() - range.getStart().getTimeInMillis(); double day = d / 86400000; double hours = d / 3600000; double min = d / 60000; double sec = d / 1000; Connection con = Utility.getPerformanceDBConnection(); try { PreparedStatement cmd = null; ResultSet rs = null; DefaultCategoryDataset set = new DefaultCategoryDataset(); JFreeChart chart = null; data.append("<hr /><h2>").append(GetDisplayName()).append("</h2>"); data.append(GetHtmlFormattedHelp() + "<br />"); data.append( "<table class=\"table table-hover\"><tr><th>Service Hostname</th><th>Invocations</th><th>Msg/Day</th><th>Msg/Hour</th><th>Msg/Min</th><th>Msg/Sec</th></tr>"); // for (int i = 0; i < urls.size(); i++) { List<String> hosts = new ArrayList<String>(); try { cmd = con.prepareStatement( "select hostingsource from rawdata where (UTCdatetime > ?) and (UTCdatetime < ?) group by hostingsource ;"); cmd.setLong(1, range.getStart().getTimeInMillis()); cmd.setLong(2, range.getEnd().getTimeInMillis()); rs = cmd.executeQuery(); while (rs.next()) { String s = rs.getString(1); if (!Utility.stringIsNullOrEmpty(s)) { s = s.trim(); } if (!Utility.stringIsNullOrEmpty(s)) { hosts.add(s); } } } catch (Exception ex) { } finally { DBUtils.safeClose(rs); DBUtils.safeClose(cmd); } for (int i = 0; i < hosts.size(); i++) { long count = 0; try { cmd = con.prepareStatement("select count(*) from RawData where hostingsource=? and " + "(UTCdatetime > ?) and (UTCdatetime < ?) ;"); cmd.setString(1, hosts.get(i)); cmd.setLong(2, range.getStart().getTimeInMillis()); cmd.setLong(3, range.getEnd().getTimeInMillis()); rs = cmd.executeQuery(); try { if (rs.next()) { count = rs.getLong(1); } } catch (Exception ex) { log.log(Level.DEBUG, null, ex); } } catch (Exception ex) { log.log(Level.ERROR, "Error opening or querying the database.", ex); } finally { DBUtils.safeClose(rs); DBUtils.safeClose(cmd); } data.append("<tr><td>").append(Utility.encodeHTML(hosts.get(i))).append("</td><td>"); data.append(count + ""); data.append("</td><td>").append(format.format((double) ((double) count / day))).append("</td><td>") .append(format.format((double) ((double) count / hours))).append("</td><td>") .append(format.format((double) ((double) count / min))).append("</td><td>") .append(format.format((double) ((double) count / sec))).append("</td></tr>"); if (count > 0) { set.addValue((double) ((double) count / day), hosts.get(i), hosts.get(i)); } } chart = org.jfree.chart.ChartFactory.createBarChart(GetDisplayName(), "Service URL", "", set, PlotOrientation.HORIZONTAL, true, false, false); data.append("</table>"); try { ChartUtilities.saveChartAsPNG(new File( path + getFilePathDelimitor() + "image_" + this.getClass().getSimpleName() + ".png"), chart, 1500, pixelHeightCalc(hosts.size())); } catch (IOException ex) { log.log(Level.ERROR, "Error saving chart image for request", ex); } data.append("<img src=\"image_").append(this.getClass().getSimpleName()).append(".png\">"); files.add(path + getFilePathDelimitor() + "image_" + this.getClass().getSimpleName() + ".png"); } catch (Exception ex) { log.log(Level.ERROR, null, ex); } finally { DBUtils.safeClose(con); } }
From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaDropDao.java
/** * Populates the buckets_droplets table//from w ww . j ava 2 s . c o m * * @param drops */ private void insertBucketDrops(final List<Drop> drops) { // Stores the drop id against the destination bucket ids Map<Long, Set<Long>> dropBucketsMap = new HashMap<Long, Set<Long>>(); // Stores the drop id against its index in the drops list final Map<Long, Integer> dropsIndex = new HashMap<Long, Integer>(); int i = 0; for (Drop drop : drops) { if (drop.getBucketIds() == null) continue; Set<Long> bucketSet = new HashSet<Long>(); bucketSet.addAll(drop.getBucketIds()); dropBucketsMap.put(drop.getId(), bucketSet); dropsIndex.put(drop.getId(), i); i++; } if (dropsIndex.isEmpty()) return; // Exclude existing drops String existsSQL = "SELECT `bucket_id`, `droplet_id` " + "FROM `buckets_droplets` WHERE `droplet_id` IN (:ids)"; MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("ids", dropsIndex.keySet()); for (Map<String, Object> row : namedJdbcTemplate.queryForList(existsSQL, params)) { Long dropId = ((Number) row.get("droplet_id")).longValue(); Long bucketId = ((Number) row.get("bucket_id")).longValue(); if (dropBucketsMap.containsKey(dropId)) { Set<Long> bucketIdSet = dropBucketsMap.get(dropId); bucketIdSet.remove(bucketId); } } // List of arrays comprised of the drop id and bucket id final List<Long[]> bucketDropList = new ArrayList<Long[]>(); for (Map.Entry<Long, Set<Long>> entry : dropBucketsMap.entrySet()) { for (Long bucketId : entry.getValue()) { Long[] bucketDrop = { bucketId, entry.getKey() }; bucketDropList.add(bucketDrop); } } if (bucketDropList.isEmpty()) return; // Store for the no. of drops inserted per bucket final Map<Long, Integer> bucketDropCount = new HashMap<Long, Integer>(); // Query for populating TABLE buckets_droplets String insertSQL = "INSERT INTO `buckets_droplets` (`bucket_id`, `droplet_id`, `droplet_date_added`) " + "VALUES (?, ?, ?)"; jdbcTemplate.batchUpdate(insertSQL, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int index) throws SQLException { Long[] bucketDrop = bucketDropList.get(index); Long bucketId = bucketDrop[0]; ps.setLong(1, bucketId); ps.setLong(2, bucketDrop[1]); ps.setTimestamp(3, new java.sql.Timestamp(new Date().getTime())); Integer count = bucketDropCount.remove(bucketId); count = (count == null) ? 1 : new Integer(count.intValue() + 1); bucketDropCount.put(bucketId, count); } @Override public int getBatchSize() { return bucketDropList.size(); } }); // Update the drop count for the updated buckets final List<Entry<Long, Integer>> bucketDropCountList = new ArrayList<Map.Entry<Long, Integer>>(); bucketDropCountList.addAll(bucketDropCount.entrySet()); String updateSQL = "UPDATE `buckets` SET `drop_count` = `drop_count` + ? WHERE `id` = ?"; jdbcTemplate.batchUpdate(updateSQL, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Entry<Long, Integer> entry = bucketDropCountList.get(i); ps.setLong(1, entry.getValue()); ps.setLong(2, entry.getKey()); } public int getBatchSize() { return bucketDropCountList.size(); } }); }
From source file:com.uber.stream.kafka.chaperone.collector.reporter.DbAuditReporter.java
private void addOrUpdateOffsets(PreparedStatement offsetInsertStmt, Map<String, Map<Integer, Long>> topicOffsetsMap) throws SQLException { logger.info("Newest offset info={}", topicOffsetsMap); for (Map.Entry<String, Map<Integer, Long>> topicEntry : topicOffsetsMap.entrySet()) { String topicName = topicEntry.getKey(); for (Map.Entry<Integer, Long> offsetEntry : topicEntry.getValue().entrySet()) { offsetInsertStmt.setString(1, topicName); offsetInsertStmt.setInt(2, offsetEntry.getKey()); offsetInsertStmt.setLong(3, offsetEntry.getValue()); offsetInsertStmt.setLong(4, offsetEntry.getValue()); offsetInsertStmt.executeUpdate(); }//from ww w . j av a2s. c o m } }
From source file:com.amazonbird.announce.ProductMgrImpl.java
public List<Product> getProductsByKeyWord(String keyword) { List<Product> searchResultList = new ArrayList<Product>(); String query = "select product.id as id, " + "product.amazonid as amazonid, " + "product.dateadded as dateadded, " + "product.price as price, " + "product.name as name, " + "product.active as active, " + "product.image as image, " + "product.customdestination as customdestination, " + "product.destination as destination, " + "product.alternativeDestination as alternativeDestination," + "product.locale as locale, " + "product.announcerid as announcerid " + "from productmessage, product, reason, reasonproduct " + "where product.id = productmessage.productid " + "and reason.id = reasonproduct.reasonid " + "and reasonproduct.productid = product.id " + "and reasonproduct.productid = productmessage.productid " + "and (reason.value like ? or productmessage.text like ?) " + "order by active desc, dateadded desc ;"; Connection connection = null; PreparedStatement ps = null; ResultSet rs = null;//from w ww .j a va 2 s . com try { connection = dbMgr.getConnection(); ps = connection.prepareStatement(query); ps.setString(1, "%" + keyword + "%"); ps.setString(2, "%" + keyword + "%"); rs = ps.executeQuery(); while (rs.next()) { Product product = new Product(); product.getDataFromResultSet(rs); searchResultList.add(product); } for (Product product : searchResultList) { ps = connection.prepareStatement(LOAD_PRODUCT_PICTURES); ps.setLong(1, product.getId()); rs = ps.executeQuery(); ArrayList<String> pictureUrlList = new ArrayList<String>(); while (rs.next()) { pictureUrlList.add(rs.getString("imageurl")); } String[] urlArr = new String[pictureUrlList.size()]; int i = 0; for (String url : pictureUrlList) { urlArr[i] = FileUtil.getInstance().getFilePathLogical() + url; i++; } product.setPictureUrls(urlArr); } logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString()); } catch (SQLException ex) { logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex); } finally { dbMgr.closeResources(connection, ps, rs); } return searchResultList; }
From source file:com.cloud.migration.Db20to21MigrationUtil.java
private void updateDiskOfferingReferences(long oldDiskOfferingId, long newDiskOfferingId) { Transaction txn = Transaction.open(Transaction.CLOUD_DB); try {/*from ww w . ja v a 2 s. c o m*/ PreparedStatement pstmt = null; pstmt = txn.prepareAutoCloseStatement("UPDATE vm_disk SET disk_offering_id=? WHERE disk_offering_id=?"); pstmt.setLong(1, newDiskOfferingId); pstmt.setLong(2, oldDiskOfferingId); int rows = pstmt.executeUpdate(); pstmt.close(); s_logger.info("Update vm_disk for disk offering change (" + oldDiskOfferingId + "->" + newDiskOfferingId + "), affected rows: " + rows); pstmt = txn.prepareAutoCloseStatement("UPDATE volumes SET disk_offering_id=? WHERE disk_offering_id=?"); pstmt.setLong(1, newDiskOfferingId); pstmt.setLong(2, oldDiskOfferingId); rows = pstmt.executeUpdate(); pstmt.close(); s_logger.info("Update volumes for disk offering change (" + oldDiskOfferingId + "->" + newDiskOfferingId + "), affected rows: " + rows); } catch (SQLException e) { s_logger.error("Unhandled exception: ", e); } finally { txn.close(); } }
From source file:mysql5.MySQL5PlayerDAO.java
@Override public int getCharacterCountForRace(Race race) { Connection con = null;/* ww w . j a va 2s . c o m*/ int count = 0; try { con = DatabaseFactory.getConnection(); PreparedStatement stmt = con.prepareStatement( "SELECT COUNT(DISTINCT(`account_name`)) AS `count` FROM `players` WHERE `race` = ? AND `exp` >= ?"); stmt.setString(1, race.name()); stmt.setLong(2, DataManager.PLAYER_EXPERIENCE_TABLE.getStartExpForLevel(GSConfig.RATIO_MIN_REQUIRED_LEVEL)); ResultSet rs = stmt.executeQuery(); rs.next(); count = rs.getInt("count"); rs.close(); stmt.close(); } catch (Exception e) { return 0; } finally { DatabaseFactory.close(con); } return count; }