query fixes : IN replaced by EXISTS, namedParameters used

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);