diff options
Diffstat (limited to 'juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java')
| -rw-r--r-- | juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java | 144 |
1 files changed, 75 insertions, 69 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 7e6b9d0a..fc7c239d 100644 --- a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java @@ -3,15 +3,18 @@ package com.juick.service; import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.NotifyOpts; -import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; +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.*; +import java.util.Collections; +import java.util.HashSet; +import java.util.List; +import java.util.Set; import java.util.stream.Collectors; /** @@ -36,21 +39,13 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip @Transactional(readOnly = true) @Override public List<String> getJIDSubscribedToUser(final int uid, final boolean friendsonly) { - if (friendsonly == false) { - return getJdbcTemplate().queryForList( - "SELECT jids.jid FROM subscr_users INNER JOIN jids " + - "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1", - String.class, - uid); - } else { - return getJdbcTemplate().queryForList( - "SELECT jids.jid FROM subscr_users INNER JOIN jids " + - "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 " + - "AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)", - String.class, - uid, - uid); - } + return getNamedParameterJdbcTemplate().queryForList( + "SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id = :uid AND subscr_users.suser_id = jids.user_id) WHERE jids.active = 1 " + + (friendsonly ? + " AND EXISTS (SELECT 1 FROM wl_users w WHERE w.user_id = :uid and jids.user_id = w.wl_user_id)" : ""), + new MapSqlParameterSource("uid", uid), + String.class); } @Transactional(readOnly = true) @@ -61,7 +56,11 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip List<User> userids = userService.getUserReaders(uid); Set<Integer> set = new HashSet<>(); - set.addAll(userids.stream().map(User::getUid).collect(Collectors.toList())); + set.addAll( + userids.stream() + .map(User::getUid) + .collect(Collectors.toList())); + List<Integer> tags = messagesService.getMessageTagsIDs(mid); if (tags.size() > 0) { String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray()); @@ -71,7 +70,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip " AND suser_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))", Integer.class, uid, author.getUid()); set.addAll(tagUsers); } - return userService.getUsersByID(new ArrayList<>(set)); + return userService.getUsersByID(set); } @Transactional(readOnly = true) @@ -80,13 +79,12 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip List<Integer> userids = getJdbcTemplate().queryForList( "SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", Integer.class, - mid, - ignore_uid); - if (userids.size() > 0) { + mid, ignore_uid); + + if (!userids.isEmpty()) return userService.getUsersByID(userids); - } else { - return Collections.emptyList(); - } + + return Collections.emptyList(); } @Transactional(readOnly = true) @@ -94,20 +92,27 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip public List<User> getUsersSubscribedToUserRecommendations(final int uid, final int mid, final int muid) { List<Integer> tags = messagesService.getMessageTagsIDs(mid); - String query = "SELECT suser_id FROM subscr_users WHERE user_id=" + uid; - query += " AND user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + muid + ")"; - query += " AND user_id NOT IN (SELECT suser_id FROM subscr_users WHERE user_id=" + muid + ")"; - query += " AND user_id NOT IN (SELECT suser_id FROM subscr_messages WHERE message_id=" + mid + ")"; - query += " AND user_id NOT IN (SELECT user_id FROM favorites WHERE message_id=" + mid + ")"; - query += " AND user_id NOT IN (SELECT subscr_users.suser_id FROM subscr_users INNER JOIN favorites ON (favorites.message_id=" + mid + " AND subscr_users.user_id=favorites.user_id AND favorites.user_id!=" + uid + "))"; + String query = "SELECT s.suser_id FROM subscr_users s WHERE s.user_id = :uid " + + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.bl_user_id = :muid and b.user_id = s.user_id) " + + " AND NOT EXISTS (SELECT 1 FROM subscr_users s1 WHERE s1.user_id = :muid AND s.user_id = s1.suser_id) " + + " AND NOT EXISTS (SELECT 1 FROM subscr_messages sm WHERE sm.message_id = :mid AND s.user_id = sm.suser_id) " + + " AND NOT EXISTS (SELECT 1 FROM favorites WHERE f.message_id = :mid AND f.user_id = s.user_id) " + + " AND s.user_id NOT IN (SELECT s2.suser_id FROM subscr_users s2 " + + " INNER JOIN favorites f ON (f.message_id = :mid AND s2.user_id = f.user_id AND f.user_id != :uid))"; + + MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource() + .addValue("uid", uid) + .addValue("muid", muid) + .addValue("mid", mid); if (!tags.isEmpty()) { - String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray()); - query += " AND user_id NOT IN (SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + tagsStr + "))"; - query += " AND user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))"; + sqlParameterSource.addValue("ids", tags); + query += " AND NOT EXISTS (SELECT 1 FROM subscr_tags st WHERE st.tag_id IN (:ids) AND s.user_id = st.suser_id) " + + " AND NOT EXISTS (SELECT 1 FROM bl_tags b WHERE b.tag_id IN (:ids) AND s.user_id = b.user_id)"; } - List<Integer> userids = getJdbcTemplate().queryForList(query, Integer.class); + List<Integer> userids = getNamedParameterJdbcTemplate().queryForList( + query, sqlParameterSource, Integer.class); return userService.getUsersByID(userids); } @@ -116,79 +121,80 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip @Override public boolean subscribeMessage(final int mid, final int vuid) { return getJdbcTemplate().update( - "INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + "INSERT IGNORE INTO subscr_messages(suser_id, message_id) VALUES (?, ?)", vuid, mid) == 1; } @Transactional @Override public boolean unSubscribeMessage(final int mid, final int vuid) { return getJdbcTemplate().update( - "DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", - mid, vuid) > 0; + "DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", mid, vuid) > 0; } @Transactional @Override public boolean subscribeUser(final User user, final User toUser) { return getJdbcTemplate().update( - "INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", - toUser.getUid(), user.getUid()) == 1; + "INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", toUser.getUid(), user.getUid()) == 1; } @Transactional @Override public boolean unSubscribeUser(final User user, final User fromUser) { return getJdbcTemplate().update( - "DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", - user.getUid(), fromUser.getUid()) > 0; + "DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", user.getUid(), fromUser.getUid()) > 0; } @Transactional @Override public boolean subscribeTag(final User user, final Tag toTag) { return getJdbcTemplate().update( - "INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", - toTag.TID, user.getUid()) == 1; + "INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", toTag.TID, user.getUid()) == 1; } @Transactional @Override public boolean unSubscribeTag(final User user, final Tag toTag) { return getJdbcTemplate().update( - "DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", - toTag.TID, user.getUid()) > 0; + "DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", toTag.TID, user.getUid()) > 0; } @Transactional(readOnly = true) @Override public NotifyOpts getNotifyOptions(final User user) { - try { - return getJdbcTemplate().queryForObject( - "SELECT jnotify,subscr_notify,recommendations FROM useroptions WHERE user_id=?", - (rs, num) -> { - NotifyOpts options = new NotifyOpts(); - options.setRepliesEnabled(rs.getInt(1) > 0); - options.setSubscriptionsEnabled(rs.getInt(2) > 0); - options.setRecommendationsEnabled(rs.getInt(3) > 0); - return options; - }, - user.getUid()); - } catch (EmptyResultDataAccessException e) { - return new NotifyOpts(); - } + List<NotifyOpts> list = getJdbcTemplate().query( + "SELECT jnotify,subscr_notify,recommendations FROM useroptions WHERE user_id=?", + (rs, num) -> { + NotifyOpts options = new NotifyOpts(); + options.setRepliesEnabled(rs.getInt(1) > 0); + options.setSubscriptionsEnabled(rs.getInt(2) > 0); + options.setRecommendationsEnabled(rs.getInt(3) > 0); + return options; + }, + user.getUid()); + + return list.isEmpty() ? + new NotifyOpts() : list.get(0); } @Transactional @Override public boolean setNotifyOptions(final User user, final NotifyOpts options) { - return getJdbcTemplate().update( - "UPDATE useroptions SET jnotify=? WHERE user_id=?", options.isRepliesEnabled() ? 1 : 0, - user.getUid()) > 0 && - getJdbcTemplate().update( - "UPDATE useroptions SET subscr_notify=? WHERE user_id=?", options.isSubscriptionsEnabled() ? 1 : 0, - user.getUid()) > 0 && - getJdbcTemplate().update( - "UPDATE useroptions SET recommendations=? WHERE user_id=?", options.isRecommendationsEnabled() ? 1 : 0, - user.getUid()) > 0; + int jnotify = getJdbcTemplate().update( + "UPDATE useroptions SET jnotify=? WHERE user_id=?", + options.isRepliesEnabled() ? 1 : 0, + user.getUid()); + + int subscr_notify = getJdbcTemplate().update( + "UPDATE useroptions SET subscr_notify=? WHERE user_id=?", + options.isSubscriptionsEnabled() ? 1 : 0, + user.getUid()); + + int recommendations = getJdbcTemplate().update( + "UPDATE useroptions SET recommendations=? WHERE user_id=?", + options.isRecommendationsEnabled() ? 1 : 0, + user.getUid()); + + return jnotify > 0 && subscr_notify > 0 && recommendations > 0; } } |