package com.juick.service; import com.juick.Message; import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.PrivacyOpts; import com.juick.server.helpers.ResponseReply; import com.juick.service.search.SearchService; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.JdbcTemplate; 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.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; import javax.inject.Inject; import java.sql.*; import java.util.*; /** * Created by aalexeev on 11/13/16. */ @Repository public class MessagesServiceImpl extends BaseJdbcService implements MessagesService { private final UserService userService; private final SearchService searchService; @Inject public MessagesServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, SearchService searchService) { super(jdbcTemplate, null); Assert.notNull(userService, "UserService must be initialized"); this.userService = userService; Assert.notNull(searchService, "SearchService must be initialized"); this.searchService = searchService; } private class MessageMapper implements RowMapper { @Override public Message mapRow(ResultSet rs, int rowNum) throws SQLException { Message msg = new Message(); msg.setMid(rs.getInt(1)); msg.setRid(rs.getInt(2)); msg.setReplyto(rs.getInt(3)); User user = new User(); user.setUid(rs.getInt(4)); user.setName(rs.getString(5)); user.setBanned(rs.getBoolean(6)); msg.setUser(user); msg.TimeAgo = rs.getInt(7); msg.setDate(rs.getTimestamp(8)); msg.ReadOnly = rs.getBoolean(9); msg.setPrivacy(rs.getInt(10)); msg.FriendsOnly = msg.getPrivacy() < 0; msg.setReplies(rs.getInt(11)); msg.setAttachmentType(rs.getString(12)); if (rs.getDouble(13) != 0) { msg.Place = new com.juick.Place(); msg.Place.lat = rs.getDouble(14); msg.Place.lon = rs.getDouble(15); } msg.setLikes(rs.getInt(16)); msg.Hidden = rs.getBoolean(17); String tagsStr = rs.getString(18); msg.parseTags(tagsStr); msg.setRepliesBy(rs.getString(19)); msg.setText(rs.getString(20)); msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(21))); return msg; } } /** * @see Java, JDBC and MySQL Types */ @Transactional @Override public int createMessage(final int uid, final String txt, final String attachment, final Collection 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(); if (mid > 0) { String tagsNames = StringUtils.EMPTY; if (CollectionUtils.isNotEmpty(tags)) { StringBuilder tasNamesBuilder = new StringBuilder(); List params = new ArrayList<>(tags.size()); boolean next = false; for (Tag tag : tags) { if (next) { tasNamesBuilder.append(" "); } else next = true; tasNamesBuilder.append(tag.getName()); params.add(new Object[]{mid, tag.TID}); } tagsNames = tasNamesBuilder.toString(); getJdbcTemplate().batchUpdate( "INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", params, new int[]{Types.INTEGER, Types.INTEGER}); } getJdbcTemplate().update( "INSERT INTO messages_txt(message_id, tags, txt) VALUES (?, ?, ?)", new Object[]{mid, tagsNames, txt}, new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR}); } return mid; } /** * @param mid * @param rid * @param uid * @param txt * @param attachment * @return * @see Java, JDBC and MySQL Types */ @Transactional @Override public int createReply(final int mid, final int rid, final int uid, final String txt, final String attachment) { int ridnew = getReplyIDIncrement(mid); getJdbcTemplate().update( "INSERT INTO replies(message_id, reply_id, user_id, replyto, attach, txt) VALUES (?, ?, ?, ?, ?, ?)", new Object[]{mid, ridnew, uid, rid, attachment, txt}, new int[]{Types.INTEGER, Types.SMALLINT, Types.INTEGER, Types.SMALLINT, Types.CHAR, Types.VARCHAR}); if (ridnew > 0) getJdbcTemplate().update( "UPDATE messages SET replies = replies + 1 WHERE message_id = ?", mid); return ridnew; } @Override public int getReplyIDIncrement(final int mid) { return getJdbcTemplate().execute((ConnectionCallback) conn -> { conn.setAutoCommit(false); final int replyNo; try (PreparedStatement ps = conn.prepareStatement("START TRANSACTION")) { ps.executeUpdate(); } try (PreparedStatement ps = conn.prepareStatement("SELECT maxreplyid+1 FROM messages WHERE message_id=? FOR UPDATE")) { ps.setInt(1, mid); try (ResultSet resultSet = ps.executeQuery()) { if (resultSet.next()) { replyNo = resultSet.getInt(1); } else { throw new IncorrectResultSizeDataAccessException("while getting getReplyIDIncrement, mid=" + mid, 1, 0); } } } try (PreparedStatement ps = conn.prepareStatement("UPDATE messages SET maxreplyid=? WHERE message_id=?")) { ps.setInt(1, replyNo); ps.setInt(2, mid); if (ps.executeUpdate() != 1) { throw new IncorrectResultSizeDataAccessException("Cannot find a message to update: " + mid, 1, 0); } } conn.commit(); return replyNo; }); } @Transactional @Override public boolean recommendMessage(final int mid, final int vuid) { boolean res = getJdbcTemplate().update( "INSERT IGNORE INTO favorites(user_id, message_id, ts) VALUES (?, ?, NOW())", vuid, mid) == 1; if (res) getJdbcTemplate().update( "UPDATE messages SET likes = likes + 1 WHERE message_id = ?", mid); return res; } @Transactional(readOnly = true) @Override public boolean canViewThread(final int mid, final int uid) { List list = getJdbcTemplate().query( "SELECT user_id, privacy FROM messages WHERE message_id = ?", (rs, rowNum) -> { PrivacyOpts res = new PrivacyOpts(); res.setUid(rs.getInt(1)); res.setPrivacy(rs.getInt(2)); return res; }, mid); PrivacyOpts privacyOpts = list.isEmpty() ? null : list.get(0); return privacyOpts == null || privacyOpts.getPrivacy() >= 0 || uid == privacyOpts.getUid() || ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0 && userService.isInWL(privacyOpts.getUid(), uid)); } @Transactional(readOnly = true) @Override public boolean isReadOnly(final int mid) { List list = getJdbcTemplate().queryForList( "SELECT readonly FROM messages WHERE message_id = ?", new Object[]{mid}, Integer.class); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public boolean isSubscribed(final int uid, final int mid) { List list = getJdbcTemplate().queryForList( "SELECT 1 FROM subscr_messages WHERE suser_id = ? AND message_id = ?", new Object[]{uid, mid}, Integer.class); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public int getMessagePrivacy(final int mid) { List list = getJdbcTemplate().queryForList( "SELECT privacy FROM messages WHERE message_id = ?", new Object[]{mid}, Integer.class); return list.isEmpty() ? -4 : list.get(0); } @Transactional(readOnly = true) @Override public com.juick.Message getMessage(final int mid) { List list = getJdbcTemplate().query( "SELECT messages.message_id, 0 as rid, 0 as replyto, " + "messages.user_id, users.nick, users.banned as banned, " + "TIMESTAMPDIFF(MINUTE, messages.ts, NOW())," + "messages.ts," + "messages.readonly, messages.privacy, messages.replies," + "messages.attach, messages.place_id, messages.lat," + "messages.lon, messages.likes, messages.hidden," + "txt.tags, txt.repliesby, txt.txt, '' as q FROM messages " + "INNER JOIN users ON messages.user_id = users.id " + "INNER JOIN messages_txt AS txt " + "ON messages.message_id = txt.message_id " + "WHERE messages.message_id = ?", new MessageMapper(), mid); return list.isEmpty() ? null : list.get(0); } @Transactional(readOnly = true) @Override public com.juick.Message getReply(final int mid, final int rid) { List list = getJdbcTemplate().query( "SELECT replies.user_id, users.nick," + "replies.replyto, replies.ts," + "replies.attach, replies.txt, IFNULL(q.txt,t.txt) as quote " + "FROM replies INNER JOIN users ON replies.user_id = users.id " + "LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.reply_id " + "LEFT JOIN messages_txt t ON replies.message_id = t.message_id " + "WHERE replies.message_id = ? AND replies.reply_id = ?", (rs, num) -> { Message msg = new Message(); msg.setMid(mid); msg.setRid(rid); msg.setUser(new User()); msg.getUser().setUid(rs.getInt(1)); msg.getUser().setName(rs.getString(2)); msg.setReplyto(rs.getInt(3)); msg.setDate(rs.getTimestamp(4)); msg.setAttachmentType(rs.getString(5)); msg.setText(rs.getString(6)); String quote = rs.getString(7); if (!StringUtils.isEmpty(quote)) { msg.setReplyQuote(MessageUtils.formatQuote(quote)); } return msg; }, mid, rid); return list.isEmpty() ? null : list.get(0); } @Transactional(readOnly = true) @Override public User getMessageAuthor(final int mid) { List list = getJdbcTemplate().query( "SELECT messages.user_id, users.nick " + "FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.message_id = ?", new Object[]{mid}, (rs, num) -> { User res = new com.juick.User(); res.setUid(rs.getInt(1)); res.setName(rs.getString(2)); return res; }); return list.isEmpty() ? null : list.get(0); } @Transactional(readOnly = true) @Override public List getMessageRecommendations(final int mid) { return getJdbcTemplate().queryForList( "SELECT users.nick FROM favorites INNER JOIN users " + "ON (favorites.message_id = ? AND favorites.user_id = users.id)", new Object[]{mid}, String.class); } @Transactional(readOnly = true) @Override public List getAll(final int visitorUid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("before", before) .addValue("visitorUid", visitorUid); return getNamedParameterJdbcTemplate().queryForList( "SELECT m.message_id FROM messages m WHERE " + (before > 0 ? " m.message_id < :before AND " : StringUtils.EMPTY) + " m.hidden = 0 AND (m.privacy > 0" + (visitorUid > 1 ? " OR m.user_id = :visitorUid) AND NOT EXISTS (" + " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : ")") + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id) ORDER BY m.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getTag(final int tid, final int visitorUid, final int before, final int cnt) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("tid", tid) .addValue("cnt", cnt) .addValue("before", before) .addValue("visitorUid", visitorUid); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM (tags INNER JOIN messages_tags " + "ON ((tags.synonym_id = :tid OR tags.tag_id = :tid) AND tags.tag_id = messages_tags.tag_id)) " + "INNER JOIN messages USING(message_id) WHERE " + (before > 0 ? " messages.message_id < :before AND " : StringUtils.EMPTY) + "(messages.privacy > 0 OR messages.user_id = :visitorUid) ORDER BY message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getTags(final String tids, final int visitorUid, final int before, final int cnt) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("cnt", cnt) .addValue("before", before) .addValue("visitorUid", visitorUid); return getNamedParameterJdbcTemplate().queryForList( "SELECT messages.message_id FROM messages_tags " + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " AND (messages.privacy > 0 OR messages.user_id = :visitorUid) " + "ORDER BY messages.message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getPlace(final int placeId, final int visitorUid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("placeId", placeId) .addValue("before", before) .addValue("visitorUid", visitorUid); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE place_id = :placeId " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getMyFeed(final int uid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("before", before); List mids = getNamedParameterJdbcTemplate().queryForList( "(SELECT message_id FROM messages " + " INNER JOIN subscr_users ON (subscr_users.suser_id = :uid AND subscr_users.user_id = messages.user_id) " + " WHERE " + (before > 0 ? " message_id < :before AND " : StringUtils.EMPTY) + " (privacy >= 0 OR (privacy >= -2 AND privacy <= -1" + " AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + " UNION " + " (SELECT message_id FROM messages WHERE user_id=:uid " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + ") ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); return mids; } @Transactional(readOnly = true) @Override public List getPrivate(final int uid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList ("SELECT message_id FROM messages WHERE user_id = :uid AND privacy < 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getDiscussions(final int uid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM subscr_messages WHERE suser_id = :uid" + (before > 0 ? " AND message_id < :before " : " ") + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getRecommended(final int uid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT f.message_id FROM favorites f WHERE " + "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" + (before > 0 ? " AND f.message_id < :before " : StringUtils.EMPTY) + "ORDER BY f.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getPopular(final int visitorUid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("vid", visitorUid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT m.message_id FROM messages m WHERE m.privacy > 0 " + (before > 0 ? " AND m.message_id < :before " : StringUtils.EMPTY) + " AND m.popular > 0 AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) " + " ORDER BY m.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getPhotos(final int visitorUid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("vid", visitorUid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT m.message_id FROM messages m WHERE (m.privacy > 0 OR m.user_id = :vid) " + (before > 0 ? " AND m.message_id < :before " : StringUtils.EMPTY) + " AND m.attach IS NOT NULL " + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id) " + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) " + " ORDER BY m.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getSearch(final String search, final int before) { List mids = searchService.searchInAllMessages(search, before); if (!mids.isEmpty()) return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy > 0 ORDER BY message_id DESC LIMIT 20", new MapSqlParameterSource("ids", mids), Integer.class); return mids; } @Transactional(readOnly = true) @Override public List getUserBlog(final int uid, final int privacy, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserTag(final int uid, final int tid, final int privacy, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("tid", tid) .addValue("privacy", privacy) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT messages.message_id FROM messages_tags INNER JOIN messages " + " USING (message_id) WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid " + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " AND messages.privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserBlogAtDay(final int uid, final int privacy, final int daysback) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) .addValue("daysback", daysback); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE user_id = :uid" + (daysback > 0 ? " AND ts >= date(NOW() - INTERVAL :daysback day)" + " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserBlogWithRecommendations(final int uid, final int privacy, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM " + "(SELECT message_id FROM favorites " + " WHERE user_id = :uid " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20) as r" + " UNION ALL " + "SELECT message_id FROM " + "(SELECT message_id FROM messages WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserRecommendations(final int uid, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM favorites " + " WHERE user_id = :uid " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserPhotos(final int uid, final int privacy, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE user_id = :uid " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List getUserSearch(final int UID, final String search, final int privacy, final int before) { List mids = searchService.searchByStringAndUser(search, UID, before); if (!mids.isEmpty()) { return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy >= :privacy ORDER BY message_id DESC", new MapSqlParameterSource("ids", mids) .addValue("privacy", privacy), Integer.class); } return mids; } @Transactional(readOnly = true) @Override public List getMessages(final Collection mids) { if (CollectionUtils.isNotEmpty(mids)) { return getNamedParameterJdbcTemplate().query( "SELECT messages.message_id, 0 as rid, 0 as replyto, " + "messages.user_id,users.nick, 0 as banned, " + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + "messages.ts," + "messages.readonly,messages.privacy,messages.replies," + "messages.attach,messages.place_id,messages.lat," + "messages.lon,messages.likes,messages.hidden," + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q " + "FROM (messages INNER JOIN messages_txt " + "ON messages.message_id=messages_txt.message_id) " + "INNER JOIN users ON messages.user_id=users.id " + "WHERE messages.message_id IN (:ids) ORDER BY messages.message_id DESC", new MapSqlParameterSource("ids", mids), new MessageMapper()); } return Collections.emptyList(); } @Transactional(readOnly = true) @Override public List getReplies(final int mid) { 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 replies.message_id = :mid ORDER BY replies.reply_id ASC", new MapSqlParameterSource("mid", mid), new MessageMapper()); } @Transactional @Override public boolean setMessagePopular(final int mid, final int popular) { int ret; MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("mid", mid) .addValue("popular", popular); switch (popular) { case -2: ret = getNamedParameterJdbcTemplate().update( "UPDATE messages SET hidden = 1 WHERE message_id = :mid", sqlParameterSource); break; case -1: sqlParameterSource.addValue("popular", 0); default: ret = getNamedParameterJdbcTemplate().update( "UPDATE messages SET popular = :popular WHERE message_id = :mid", sqlParameterSource); break; } if (popular == -1) ret = getNamedParameterJdbcTemplate().update( "INSERT INTO top_ignore_messages VALUES (:mid)", sqlParameterSource); return ret > 0; } @Transactional @Override public boolean setMessagePrivacy(final int mid) { return getJdbcTemplate().update("UPDATE messages SET privacy=1 WHERE message_id=?", mid) > 0; } @Transactional @Override public boolean deleteMessage(final int uid, final int mid) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("mid", mid) .addValue("uid", uid); if (getNamedParameterJdbcTemplate().update( "DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", sqlParameterSource) > 0) { getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", sqlParameterSource); getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", sqlParameterSource); getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", sqlParameterSource); getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", sqlParameterSource); return true; } return false; } @Transactional(readOnly = true) @Override public List getLastMessages(int hours) { return getJdbcTemplate().queryForList("SELECT message_id FROM messages WHERE messages.ts>TIMESTAMPADD(HOUR,?,NOW())", Integer.class, -hours); } @Transactional(readOnly = true) @Override public List getLastReplies(int hours) { return getJdbcTemplate().query("SELECT users2.nick,replies.message_id,replies.reply_id," + "users.nick,replies.txt," + "replies.ts,replies.attach,replies.ts+0 " + "FROM ((replies INNER JOIN users ON replies.user_id=users.id) " + "INNER JOIN messages ON replies.message_id=messages.message_id) " + "INNER JOIN users AS users2 ON messages.user_id=users2.id " + "WHERE replies.ts>TIMESTAMPADD(HOUR,?,NOW()) AND messages.privacy>0", (rs, rowNum) -> { ResponseReply reply = new ResponseReply(); reply.setMuname(rs.getString(1)); reply.setMid(rs.getInt(2)); reply.setRid(rs.getInt(3)); reply.setUname(rs.getString(4)); reply.setDescription(rs.getString(5)); reply.setPubDate(rs.getTimestamp(6)); reply.setAttachmentType(rs.getString(7)); return reply; }, -hours); } }