delete query optimization

1 files changed, 16 insertions, 2 deletions

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 c1689249..77100f1e 100644
--- a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java
@@ -175,6 +175,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                 .collect(Collectors.toList());
     }
 
+    @Transactional(readOnly = true)
     @Override
     public List<TagStats> getTagStats() {
         return getJdbcTemplate().query(
@@ -191,10 +192,23 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
     public List<Tag> updateTags(final int mid, final Collection<Tag> newTags) {
         List<Tag> currentTags = getMessageTags(mid).stream()
                 .map(TagStats::getTag).collect(Collectors.toList());
-        newTags.stream().filter(currentTags::contains)
-                .forEach(t -> getJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id=? AND tag_id=?", mid, t.TID));
+
+        if (CollectionUtils.isEmpty(newTags))
+            return currentTags;
+
+        List<Integer> idsForDelete = newTags.stream()
+                .filter(currentTags::contains)
+                .map(tag -> tag.TID)
+                .collect(Collectors.toList());
+
+        if (!idsForDelete.isEmpty())
+            getNamedParameterJdbcTemplate().update(
+                    "DELETE FROM messages_tags WHERE message_id = :mid AND tag_id in (:ids)",
+                    new MapSqlParameterSource().addValue("ids", idsForDelete).addValue("mid", mid));
+
         newTags.stream().filter(t -> !currentTags.contains(t))
                 .forEach(t -> getJdbcTemplate().update("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", mid, t.TID));
+
         return getMessageTags(mid).stream()
                 .map(TagStats::getTag).collect(Collectors.toList());
     }