From 3d7f60c1c0b48dad2f58bffe3352d1c5c1b93f00 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Thu, 1 Dec 2016 13:56:53 +0300 Subject: juick-api: Tags controller from perl, refactoring --- .../java/com/juick/service/MessagesService.java | 4 -- .../com/juick/service/MessagesServiceImpl.java | 26 --------- .../com/juick/service/SubscriptionServiceImpl.java | 11 +++- .../main/java/com/juick/service/TagService.java | 8 ++- .../java/com/juick/service/TagServiceImpl.java | 68 ++++++++++++++++++---- 5 files changed, 71 insertions(+), 46 deletions(-) (limited to 'juick-server/src/main/java/com/juick/service') diff --git a/juick-server/src/main/java/com/juick/service/MessagesService.java b/juick-server/src/main/java/com/juick/service/MessagesService.java index 5990f408..f7175967 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesService.java +++ b/juick-server/src/main/java/com/juick/service/MessagesService.java @@ -32,10 +32,6 @@ public interface MessagesService { User getMessageAuthor(int mid); - List getMessageTags(int mid); - - List getMessageTagsIDs(int mid); - List getMessageRecommendations(int mid); List getAll(int visitorUid, int before); diff --git a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java index 948e9137..9cf103a1 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -7,7 +7,6 @@ import com.juick.server.helpers.PrivacyOpts; import com.juick.service.search.SearchService; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; -import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.ConnectionCallback; @@ -344,31 +343,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ null : list.get(0); } - @Transactional(readOnly = true) - @Override - public List getMessageTags(final int mid) { - return getJdbcTemplate().query( - "SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + - "INNER JOIN messages_tags ON (messages_tags.message_id = ? AND messages_tags.tag_id = tags.tag_id)", - new Object[]{mid}, - (rs, num) -> { - com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3))); - t.TID = rs.getInt(1); - t.SynonymID = rs.getInt(2); - t.UsageCnt = rs.getInt(4); - return t; - }); - } - - @Transactional(readOnly = true) - @Override - public List getMessageTagsIDs(final int mid) { - return getJdbcTemplate().queryForList( - "SELECT tag_id FROM messages_tags WHERE message_id = ?", - new Object[]{mid}, - Integer.class); - } - @Transactional(readOnly = true) @Override public List getMessageRecommendations(final int mid) { 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 bd915961..767c62fc 100644 --- a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java @@ -23,9 +23,11 @@ import java.util.stream.Collectors; public class SubscriptionServiceImpl extends BaseJdbcService implements SubscriptionService { private final UserService userService; private final MessagesService messagesService; + private final TagService tagService; @Inject - public SubscriptionServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, MessagesService messagesService) { + public SubscriptionServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, + MessagesService messagesService, TagService tagService) { super(jdbcTemplate, null); Assert.notNull(userService); @@ -33,6 +35,9 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip Assert.notNull(messagesService); this.messagesService = messagesService; + + Assert.notNull(tagService); + this.tagService = tagService; } @Transactional(readOnly = true) @@ -60,7 +65,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip .map(User::getUid) .collect(Collectors.toList())); - List tags = messagesService.getMessageTagsIDs(mid); + List tags = tagService.getMessageTagsIDs(mid); if (!tags.isEmpty()) { List tagUsers = getNamedParameterJdbcTemplate().queryForList( "SELECT st.suser_id FROM subscr_tags st " + @@ -94,7 +99,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip @Transactional(readOnly = true) @Override public List getUsersSubscribedToUserRecommendations(final int uid, final int mid, final int muid) { - List tags = messagesService.getMessageTagsIDs(mid); + List tags = tagService.getMessageTagsIDs(mid); 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) " + diff --git a/juick-server/src/main/java/com/juick/service/TagService.java b/juick-server/src/main/java/com/juick/service/TagService.java index 03dcd842..2fcc7097 100644 --- a/juick-server/src/main/java/com/juick/service/TagService.java +++ b/juick-server/src/main/java/com/juick/service/TagService.java @@ -1,6 +1,7 @@ package com.juick.service; import com.juick.Tag; +import com.juick.server.helpers.TagStats; import java.util.Collection; import java.util.List; @@ -19,13 +20,18 @@ public interface TagService { int createTag(String name); - List getUserTagsAll(int uid); + List getUserTagStats(int uid); List getUserBLTags(int uid); List getPopularTags(); + List getTagStats(); List updateTags(int mid, Collection newTags); List fromString(String txt, boolean tagsOnly); + + List getMessageTags(int mid); + + List getMessageTagsIDs(int mid); } diff --git a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java index 8f420ba9..23b8d2a9 100644 --- a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java @@ -1,11 +1,13 @@ package com.juick.service; import com.juick.Tag; +import com.juick.server.helpers.TagStats; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; @@ -14,9 +16,7 @@ import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; import javax.inject.Inject; -import java.sql.PreparedStatement; -import java.sql.Statement; -import java.sql.Types; +import java.sql.*; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; @@ -132,19 +132,27 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { return holder.getKey().intValue(); } + private class TagStatsMapper implements RowMapper { + + @Override + public TagStats mapRow(ResultSet rs, int rowNum) throws SQLException { + Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1))); + TagStats s = new TagStats(); + s.setTag(t); + s.setUsageCount(rs.getInt(2)); + return s; + } + } + @Transactional(readOnly = true) @Override - public List getUserTagsAll(final int uid) { + public List getUserTagStats(final int uid) { return getJdbcTemplate().query( "SELECT tags.name,COUNT(messages.message_id) " + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + "AND messages.message_id=messages_tags.message_id)) " + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", - (rs, rowNum) -> { - Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1))); - t.UsageCnt = rs.getInt(2); - return t; - }, + new TagStatsMapper(), uid); } @@ -167,13 +175,24 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { .collect(Collectors.toList()); } + @Override + public List getTagStats() { + return getJdbcTemplate().query( + "SELECT tags.name,COUNT(DISTINCT messages.user_id) AS cnt " + + "FROM (messages INNER JOIN messages_tags ON (messages.ts>TIMESTAMPADD(DAY,-3,NOW()) " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id " + + "WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) " + + "GROUP BY tags.tag_id ORDER BY cnt DESC LIMIT 20", new TagStatsMapper()); + } + @Transactional @Override public List updateTags(final int mid, final Collection newTags) { - List currentTags = messagesService.getMessageTags(mid); + List currentTags = getMessageTags(mid); if (CollectionUtils.isEmpty(newTags)) - return currentTags; + return currentTags.stream().map(TagStats::getTag).collect(Collectors.toList()); List ids = new ArrayList<>(newTags.size()); List params = new ArrayList<>(newTags.size()); @@ -198,7 +217,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { .addValue("mid", mid) .addValue("ids", ids)); - return messagesService.getMessageTags(mid); + return getMessageTags(mid).stream().map(TagStats::getTag).collect(Collectors.toList()); } @Override @@ -219,4 +238,29 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { } return Collections.emptyList(); } + + @Transactional(readOnly = true) + @Override + public List getMessageTags(final int mid) { + return getJdbcTemplate().query( + "SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + + "INNER JOIN messages_tags ON (messages_tags.message_id = ? AND messages_tags.tag_id = tags.tag_id)", + (rs, num) -> { + com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3))); + t.TID = rs.getInt(1); + t.SynonymID = rs.getInt(2); + TagStats s = new TagStats(); + s.setTag(t); + s.setUsageCount(rs.getInt(4)); + return s; + }, mid); + } + + @Transactional(readOnly = true) + @Override + public List getMessageTagsIDs(final int mid) { + return getJdbcTemplate().queryForList( + "SELECT tag_id FROM messages_tags WHERE message_id = ?", + Integer.class, mid); + } } -- cgit v1.2.3