jdbc: update lastmessage user column

2 files changed, 15 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',