diff options
author | 2018-04-15 22:11:41 +0300 | |
---|---|---|
committer | 2018-04-15 22:11:41 +0300 | |
commit | 312a462685fce047d4712780ae46f2b910f5d057 (patch) | |
tree | bdb37547a6a3d8fc1976f3c9435de26b24310482 /juick-server-jdbc/src/main | |
parent | fa9788960e934d9b78e5968eb2c384254218090a (diff) |
jdbc: update lastmessage user column
Diffstat (limited to 'juick-server-jdbc/src/main')
-rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java | 34 | ||||
-rw-r--r-- | juick-server-jdbc/src/main/resources/schema.sql | 2 |
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', |