From 4a40963da9e1030781fbfa2a3adc1d562902fcf6 Mon Sep 17 00:00:00 2001 From: Alexander Alexeev Date: Tue, 20 Dec 2016 00:34:07 +0700 Subject: delete query optimization --- .../main/java/com/juick/service/TagServiceImpl.java | 18 ++++++++++++++++-- 1 file 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 getTagStats() { return getJdbcTemplate().query( @@ -191,10 +192,23 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { public List updateTags(final int mid, final Collection newTags) { List 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 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()); } -- cgit v1.2.3