我有以下格式的數(shù)據(jù)： HashMap<PageID, Set<SubscriberIDS>>我需要檢查的是MySQL表中已經(jīng)不存在每個(gè)表中的數(shù)量。表具有 和 列。SubscriberIDSPageIDsPageIDSubscriberID這就是我到目前為止所擁有的：String NEW_SUBSCRIBER_COUNT = "SELECT ? - COUNT(*) as new_subscribers from FB_SUBSCRIPTIONS WHERE PAGEID=? AND SUBSCRIBERID IN (?)";第一個(gè)參數(shù)是，第二個(gè)參數(shù)是第三個(gè)參數(shù)numberOFSubscriberIDsPageIdSubscriberIds但這需要為每個(gè)頁(yè)面Id打一個(gè)。我如何修改它以使用單個(gè)查詢?yōu)槊總€(gè)提供我。number of new subscribersPageID
1 回答
www說
TA貢獻(xiàn)1775條經(jīng)驗(yàn) 獲得超8個(gè)贊
是否有任何特定需要在一個(gè)查詢中執(zhí)行此操作？因?yàn)殡m然可以，但實(shí)際上使用原始解決方案并調(diào)用每個(gè)頁(yè)面 ID 的查詢可能更具可讀性。無論如何，你想要的東西不能在一行中完成，所以你需要期望在給定的點(diǎn)循環(huán)。
// Build dynamic query
StringBuilder whereClause = new StringBuilder();
Iterator<PageID> it = yourMap.keySet().iterator();
while(it.hasNext()){
PageID key = it.next();
Set<SubscriberIDS> value = yourMap.get(key);
// You need to fill the 'IN' clause with multiple parameters, one for each subscriber id
StringBuilder inClause = new StringBuilder();
for(SubscriberIDS subId : value){
if(inClause.length > 0){
inClause.append(", ");
}
inClause.append("?");
preparedStatement.setInt(paramIndex++, subId.getId());
}
// For each page id we append a new 'OR' to our query
if(whereClause.lenght > 0){
whereClause.append(" OR ");
}
whereClause.append("(PAGEID=? AND SUBSCRIBERID IN (").append(inClause.toString()).append("))");
}
String query = "SELECT PAGEID, COUNT(SUBSCRIBERID) AS SUBSCRIBERS FROM FB_SUBSCRIPTIONS WHERE " + whereClause.toString() + " GROUP BY PAGEID";
// Create prepared statement and set parameters
PreparedStatement preparedStatement = connection.prepareStatement(query);
int paramIndex = 0;
it = yourMap.keySet().iterator();
while(it.hasNext()){
PageID key = it.next();
Set<SubscriberIDS> value = yourMap.get(key);
preparedStatement.setInt(paramIndex++, key.getId());
for(SubscriberIDS subId : value){
preparedStatement.setInt(paramIndex++, subId.getId());
}
}
// Execute query, loop over result and calculate new subscriptions
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
int pageId = rs.getInt("PAGEID");
int newSubscriptions = yourMap.get(pageId).size() - rs.getInt("SUBSCRIBERS");
System.out.println(pageId + ", " + newSubscriptions);
}
在地圖中給定以下數(shù)據(jù)：
PAGEID SUBSCRIBERIDS
1 1,3,4,5,9
2 3,4,5,6,8,9
3 2,5,6
以及數(shù)據(jù)庫(kù)中的以下數(shù)據(jù)：
PAGEID SUBSCRIBERIDS
1 3,4,10,11
2 1,2,5,7
3 1,2,5,6,7,8,9
這應(yīng)該給出以下輸出：
1,3
2,6
3,0
我實(shí)際上還沒有運(yùn)行代碼，所以它可能需要一些調(diào)整，但它給了你一般的想法......
添加回答
舉報(bào)
0/150
提交
取消