diff options
Diffstat (limited to 'src/main/java/com/juick/service/TagServiceImpl.java')
| -rw-r--r-- | src/main/java/com/juick/service/TagServiceImpl.java | 67 |
1 files changed, 40 insertions, 27 deletions
diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index 81c0c3ba..1ae98c0f 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -36,6 +36,9 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.time.Instant; +import java.time.ZoneOffset; +import java.time.temporal.ChronoUnit; import java.util.Arrays; import java.util.Collection; import java.util.List; @@ -64,8 +67,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { }, tid); - return list.isEmpty() ? - null : list.get(0); + return list.isEmpty() ? null : list.get(0); } @Transactional @@ -84,8 +86,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { }, tag); - Tag ret = list.isEmpty() ? - null : list.get(0); + Tag ret = list.isEmpty() ? null : list.get(0); if (ret == null && autoCreate) { ret = new Tag(tag); @@ -97,8 +98,9 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Override public List<Tag> getTags(Stream<String> tags, final boolean autoCreate) { - return tags.filter(StringUtils::isNotBlank).map(tag -> getTag(tag, autoCreate)).filter(Objects::nonNull).distinct() - .collect(Collectors.toList()); + return tags.filter(StringUtils::isNotBlank).map(tag -> getTag(tag, autoCreate)).filter(Objects::nonNull) + .distinct() + .collect(Collectors.toList()); } @Transactional(readOnly = true) @@ -124,7 +126,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { }, holder); - return holder.getKey().intValue(); + return holder.getKeys().size() > 1 ? ((Number) holder.getKeys().get("tag_id")).intValue() + : holder.getKey().intValue(); } private class TagStatsMapper implements RowMapper<TagStats> { @@ -164,19 +167,26 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Override public List<String> getPopularTags() { return getJdbcTemplate().queryForList( - "select name from tags where noindex=0 order by stat_messages desc limit 20", String.class); + "select name from tags where noindex=false order by stat_messages desc limit 20", String.class); } @Transactional(readOnly = true) @Override public List<TagStats> 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,-14,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 HAVING cnt > 1 ORDER BY cnt DESC LIMIT 20", new TagStatsMapper()); + var ts = Instant.now().minus(14, ChronoUnit.DAYS); + var sql = """ + SELECT tags.name,COUNT(DISTINCT messages.user_id) AS cnt + FROM (messages INNER JOIN messages_tags ON (messages.ts > :ts + 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 HAVING COUNT(DISTINCT messages.user_id) > 1 + ORDER BY cnt DESC LIMIT 20 + """; + return getNamedParameterJdbcTemplate() + .query(sql, new MapSqlParameterSource() + .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE), + new TagStatsMapper()); } @Transactional @@ -202,17 +212,19 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { new MapSqlParameterSource().addValue("ids", idsForDelete).addValue("mid", mid)); List<Tag> addedTags = newTags.stream().filter(t -> !currentTags.contains(t)).collect(Collectors.toList()); - getJdbcTemplate().batchUpdate("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", new BatchPreparedStatementSetter() { - @Override - public void setValues(@Nonnull PreparedStatement ps, int i) throws SQLException { - ps.setInt(1, mid); - ps.setInt(2, addedTags.get(i).TID); - } - @Override - public int getBatchSize() { - return addedTags.size(); - } - }); + getJdbcTemplate().batchUpdate("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", + new BatchPreparedStatementSetter() { + @Override + public void setValues(@Nonnull PreparedStatement ps, int i) throws SQLException { + ps.setInt(1, mid); + ps.setInt(2, addedTags.get(i).TID); + } + + @Override + public int getBatchSize() { + return addedTags.size(); + } + }); return getMessageTags(mid).stream() .map(TagStats::getTag).collect(Collectors.toSet()); @@ -257,7 +269,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Override public boolean blacklistTag(User user, Tag tag) { - int rowcount = getNamedParameterJdbcTemplate().update("DELETE FROM bl_tags WHERE tag_id = :tid AND user_id = :uid", + int rowcount = getNamedParameterJdbcTemplate().update( + "DELETE FROM bl_tags WHERE tag_id = :tid AND user_id = :uid", new MapSqlParameterSource().addValue("tid", tag.TID).addValue("uid", user.getUid())); return rowcount <= 0 && getNamedParameterJdbcTemplate() .update("INSERT INTO bl_tags(user_id, tag_id) VALUES(:uid,:tid)", |