From 37a171892ba11c578ea947103ee7488801785eac Mon Sep 17 00:00:00 2001 From: Alexander Alexeev Date: Tue, 22 Nov 2016 03:29:06 +0700 Subject: query fixes : IN replaced by EXISTS, namedParameters used --- .../com/juick/service/SubscriptionServiceImpl.java | 18 +++++++++++------- 1 file changed, 11 insertions(+), 7 deletions(-) (limited to 'juick-server') 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 tags = messagesService.getMessageTagsIDs(mid); - if (tags.size() > 0) { - String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray()); - List 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 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); -- cgit v1.2.3