From f3963f0b12055ba6710b9b28ddc93a5b5fe91d67 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 22 Sep 2017 16:39:00 +0300 Subject: Initial getNotifications query * using Java 8 DateTime API where possible --- .../com/juick/service/MessagesServiceImpl.java | 33 ++++++++++++++++++++-- .../com/juick/service/PMQueriesServiceImpl.java | 6 ++-- .../java/com/juick/service/MessageServiceTest.java | 22 +++++++++++++-- 3 files changed, 53 insertions(+), 8 deletions(-) (limited to 'juick-server-jdbc/src') diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java index 3533d701..c23b04e4 100644 --- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -40,7 +40,11 @@ import org.springframework.util.Assert; import javax.inject.Inject; import java.sql.*; -import java.util.*; +import java.time.LocalDateTime; +import java.util.ArrayList; +import java.util.Collection; +import java.util.Collections; +import java.util.List; /** * Created by aalexeev on 11/13/16. @@ -74,7 +78,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ user.setBanned(rs.getBoolean(6)); msg.setUser(user); msg.TimeAgo = rs.getInt(7); - msg.setDate(rs.getTimestamp(8)); + msg.setTimestamp(rs.getTimestamp(8).toInstant()); msg.ReadOnly = rs.getBoolean(9); msg.setPrivacy(rs.getInt(10)); msg.FriendsOnly = msg.getPrivacy() < 0; @@ -322,7 +326,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ msg.getUser().setUid(rs.getInt(1)); msg.getUser().setName(rs.getString(2)); msg.setReplyto(rs.getInt(3)); - msg.setDate(rs.getTimestamp(4)); + msg.setTimestamp(rs.getTimestamp(4).toInstant()); msg.setAttachmentType(rs.getString(5)); msg.setText(rs.getString(6)); String quote = rs.getString(7); @@ -834,4 +838,27 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return reply; }, -hours); } + + @Transactional(readOnly = true) + @Override + public List getNotifications(User user, LocalDateTime before) { + return getNamedParameterJdbcTemplate().query("SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " + + "replies.user_id, users.nick, users.banned, " + + "TIMESTAMPDIFF(MINUTE, replies.ts, NOW()), replies.ts, " + + "0 as readonly, 0 as privacy, 0 as replies, " + + "replies.attach, 0 as place_id, 0 as lat, " + + "0 as lon, 0 as likes, 0 as hidden, " + + "NULL as tags, NULL as repliesby, replies.txt, " + + "IFNULL(qw.txt, t.txt) as q FROM replies INNER JOIN users " + + "ON replies.user_id = users.id " + + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " + + "LEFT JOIN messages_txt t on replies.message_id = t.message_id " + + "WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " + + "AND replies.message_id=message_id) " + + (before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) + + "ORDER BY ts DESC LIMIT 20", new MapSqlParameterSource() + .addValue("uid", user.getUid()) + .addValue("before", before), + new MessageMapper()); + } } diff --git a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java index 6b13f7bc..80be200b 100644 --- a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java +++ b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java @@ -125,7 +125,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe msg.setUser(new User()); msg.getUser().setUid(uuid); msg.setText(rs.getString(2)); - msg.setDate(rs.getTimestamp(3)); + msg.setTimestamp(rs.getTimestamp(3).toInstant()); return msg; }); @@ -149,7 +149,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe msg.getUser().setName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); + msg.setTimestamp(rs.getTimestamp(5).toInstant()); return msg; }, uid); @@ -169,7 +169,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe msg.getUser().setName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); + msg.setTimestamp(rs.getTimestamp(5).toInstant()); return msg; }, uid); diff --git a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java index 64de9424..738a1250 100644 --- a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java +++ b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java @@ -17,17 +17,22 @@ package com.juick.service; +import com.juick.Message; +import com.juick.User; import com.juick.configuration.RepositoryConfiguration; import com.juick.server.helpers.AnonymousUser; +import org.junit.Before; import org.junit.Test; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests; import javax.inject.Inject; +import java.time.ZoneId; import java.util.List; import static org.hamcrest.MatcherAssert.assertThat; import static org.hamcrest.Matchers.equalTo; +import static org.hamcrest.Matchers.is; /** * Created by aalexeev on 11/25/16. @@ -43,10 +48,14 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests { int ugnichId, freefdId, mid; - @Test - public void getMyFeed() { + @Before + public void createUgniches() { ugnichId = userService.createUser("ugnich", "secret"); freefdId = userService.createUser("freefd", "secret"); + } + + @Test + public void getMyFeed() { mid = messagesService.createMessage(ugnichId, "test", null, null); int mid2 = messagesService.createMessage(ugnichId, "test2", null, null); subscriptionService.subscribeUser(userService.getUserByUID(freefdId).orElse(AnonymousUser.INSTANCE), @@ -65,5 +74,14 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests { assertThat(messagesService.recommendMessage(mid, ugnichId), equalTo(false)); assertThat(messagesService.getMessage(mid).getLikes(), equalTo(0)); assertThat(messagesService.getAll(ugnichId, 0).size(), equalTo(3)); + + User ugnich = userService.getUserByUID(ugnichId).orElse(AnonymousUser.INSTANCE); + User freefd = userService.getUserByUID(freefdId).orElse(AnonymousUser.INSTANCE); + int mid = messagesService.createMessage(ugnichId, "yo", null, null); + subscriptionService.subscribeMessage(mid, freefdId); + int rid = messagesService.createReply(mid, 0, ugnichId, "yo", null); + Message reply = messagesService.getReply(mid, rid); + assertThat(messagesService.getNotifications(freefd, null).size(), is(1)); + assertThat(messagesService.getNotifications(freefd, reply.getTimestamp().atZone(ZoneId.systemDefault()).toLocalDateTime()).size(), is(0)); } } -- cgit v1.2.3