aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2018-04-15 22:11:41 +0300
committerGravatar Vitaly Takmazov2018-04-15 22:11:41 +0300
commit312a462685fce047d4712780ae46f2b910f5d057 (patch)
treebdb37547a6a3d8fc1976f3c9435de26b24310482
parentfa9788960e934d9b78e5968eb2c384254218090a (diff)
jdbc: update lastmessage user column
-rw-r--r--juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java34
-rw-r--r--juick-server-jdbc/src/main/resources/schema.sql2
-rw-r--r--juick-server/src/test/java/com/juick/server/tests/ServerTests.java4
3 files changed, 19 insertions, 21 deletions
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 1401cfce..996fb550 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
@@ -33,8 +33,7 @@ import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
-import org.springframework.jdbc.support.GeneratedKeyHolder;
-import org.springframework.jdbc.support.KeyHolder;
+import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@@ -112,24 +111,17 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional
@Override
public int createMessage(final int uid, final String txt, final String attachment, final Collection<com.juick.Tag> tags) {
- KeyHolder holder = new GeneratedKeyHolder();
- getJdbcTemplate().update(
- con -> {
- PreparedStatement stmt = con.prepareStatement(
- "INSERT INTO messages(user_id, attach) VALUES (?, ?)",
- Statement.RETURN_GENERATED_KEYS);
- stmt.setInt(1, uid);
- if (attachment != null)
- stmt.setString(2, attachment);
- else
- stmt.setNull(2, Types.CHAR);
-
- return stmt;
- },
- holder);
-
- int mid = holder.getKey().intValue();
-
+ SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages")
+ .usingColumns("user_id", "attach", "ts")
+ .usingGeneratedKeyColumns("message_id");
+ Map<String, Object> insertMap = new HashMap<>();
+ insertMap.put("user_id", uid);
+ Instant now = Instant.now();
+ insertMap.put("ts", Timestamp.from(now));
+ if (attachment != null) {
+ insertMap.put("attach", attachment);
+ }
+ int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue();
if (mid > 0) {
String tagsNames = StringUtils.EMPTY;
@@ -159,6 +151,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
"INSERT INTO messages_txt(message_id, tags, txt) VALUES (?, ?, ?)",
new Object[]{mid, tagsNames, txt},
new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR});
+ getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", Timestamp.from(now), uid);
}
return mid;
@@ -187,6 +180,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
"UPDATE messages SET replies = replies + 1, updated=? WHERE message_id = ?",
ts, mid);
setLastReadComment(user, mid, ridnew);
+ getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", ts, user.getUid());
}
return ridnew;
}
diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql
index 66b4d7ac..ec28a456 100644
--- a/juick-server-jdbc/src/main/resources/schema.sql
+++ b/juick-server-jdbc/src/main/resources/schema.sql
@@ -327,7 +327,7 @@ CREATE TABLE IF NOT EXISTS `users` (
`passw` char(32) NOT NULL,
`lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__',
`banned` tinyint(3) unsigned NOT NULL DEFAULT '0',
- `lastmessage` int(11) NOT NULL DEFAULT '0',
+ `lastmessage` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lastpm` int(11) NOT NULL DEFAULT '0',
`lastphoto` int(11) NOT NULL DEFAULT '0',
`karma` smallint(6) NOT NULL DEFAULT '0',
diff --git a/juick-server/src/test/java/com/juick/server/tests/ServerTests.java b/juick-server/src/test/java/com/juick/server/tests/ServerTests.java
index 1acc0c15..d09a509d 100644
--- a/juick-server/src/test/java/com/juick/server/tests/ServerTests.java
+++ b/juick-server/src/test/java/com/juick/server/tests/ServerTests.java
@@ -606,6 +606,8 @@ public class ServerTests {
assertTrue(yoyoMsg.getNewMessage().isPresent());
assertThat(yoyoMsg.getNewMessage().get().getTags().get(0), is(yo));
int mid = yoyoMsg.getNewMessage().get().getMid();
+ Timestamp last = jdbcTemplate.queryForObject("SELECT lastmessage FROM users WHERE id=?", Timestamp.class, user.getUid());
+ assertThat(last.toInstant(), equalTo(yoyoMsg.getNewMessage().get().getTimestamp()));
assertEquals("should be message", true,
commandsManager.processCommand(user, String.format("#%d", mid), emptyUri).getText().startsWith("@me"));
int readerUid = userService.createUser("dummyReader", "dummySecret");
@@ -643,6 +645,8 @@ public class ServerTests {
URI.create("http://static.juick.com/settings/facebook.png")).getText());
Message reply = messagesService.getReplies(user, mid).stream().filter(m -> m.getRid() == 3).findFirst()
.orElse(new Message());
+ Timestamp lastreply = jdbcTemplate.queryForObject("SELECT lastmessage FROM users WHERE id=?", Timestamp.class, user.getUid());
+ assertThat(lastreply.toInstant(), equalTo(reply.getTimestamp()));
assertEquals("should be reply to second comment", 2, reply.getReplyto());
assertEquals("tags should NOT be updated", "It is not your message",
commandsManager.processCommand(readerUser, "#" + mid + " *yo *there", emptyUri)