aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGravatar Alexander Alexeev2016-11-22 03:29:06 +0700
committerGravatar Vitaly Takmazov2016-11-23 13:03:08 +0300
commit37a171892ba11c578ea947103ee7488801785eac (patch)
treecfb91809a2922cff5bd9982ed949d809d5fae677
parentc3e73de64fb28f72cad4f92bc2a82935e4f4b7ee (diff)
query fixes : IN replaced by EXISTS, namedParameters used
-rw-r--r--juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java18
1 files changed, 11 insertions, 7 deletions
diff --git a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java
index fc7c239d..fa8d4041 100644
--- a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java
+++ b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java
@@ -8,7 +8,6 @@ import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
-import org.springframework.util.StringUtils;
import javax.inject.Inject;
import java.util.Collections;
@@ -62,12 +61,17 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip
.collect(Collectors.toList()));
List<Integer> tags = messagesService.getMessageTagsIDs(mid);
- if (tags.size() > 0) {
- String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray());
- List<Integer> tagUsers = getJdbcTemplate().queryForList("SELECT suser_id FROM subscr_tags " +
- "WHERE tag_id IN (" + tagsStr + ") AND suser_id!=? " +
- " AND suser_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=?)" +
- " AND suser_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))", Integer.class, uid, author.getUid());
+ if (!tags.isEmpty()) {
+ List<Integer> tagUsers = getNamedParameterJdbcTemplate().queryForList(
+ "SELECT st.suser_id FROM subscr_tags st " +
+ "WHERE st.tag_id IN (:ids) AND st.suser_id != :uid " +
+ " AND NOT EXISTS (SELECT 1 FROM bl_users bu WHERE bu.bl_user_id = :authorUid and st.suser_id = bu.user_id)" +
+ " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN (:ids) and st.suser_id = bt.user_id)",
+ new MapSqlParameterSource()
+ .addValue("ids", tags)
+ .addValue("uid", uid)
+ .addValue("authorUid", author.getUid()),
+ Integer.class);
set.addAll(tagUsers);
}
return userService.getUsersByID(set);