diff options
Diffstat (limited to 'juick-core/src/main/java/com/juick/server/MessagesQueries.java')
| -rw-r--r-- | juick-core/src/main/java/com/juick/server/MessagesQueries.java | 142 |
1 files changed, 61 insertions, 81 deletions
diff --git a/juick-core/src/main/java/com/juick/server/MessagesQueries.java b/juick-core/src/main/java/com/juick/server/MessagesQueries.java index 3d0d734d..3c848f54 100644 --- a/juick-core/src/main/java/com/juick/server/MessagesQueries.java +++ b/juick-core/src/main/java/com/juick/server/MessagesQueries.java @@ -18,24 +18,22 @@ package com.juick.server; import com.juick.Message; +import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.PrivacyOpts; import org.springframework.dao.EmptyResultDataAccessException; 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.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.util.StringUtils; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.Statement; -import java.sql.Types; -import java.util.ArrayList; +import java.sql.*; +import java.util.Arrays; import java.util.Collections; import java.util.List; -import java.util.logging.Logger; /** * @@ -43,7 +41,42 @@ import java.util.logging.Logger; */ public class MessagesQueries { - private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); + public static class MessageMapper implements RowMapper<Message> { + @Override + public Message mapRow(ResultSet rs, int rowNum) throws SQLException { + Message msg = new Message(); + msg.setMID(rs.getInt(1)); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(2)); + msg.getUser().setUName(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + msg.ReadOnly = rs.getBoolean(6); + msg.Privacy = rs.getInt(7); + msg.FriendsOnly = msg.Privacy < 0; + msg.Replies = rs.getInt(8); + msg.AttachmentType = rs.getString(9); + //if (rs.getInt(10) > 0) { + // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); + //} + if (rs.getDouble(10) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(11); + msg.Place.lon = rs.getDouble(12); + } + msg.Likes = rs.getInt(13); + msg.Hidden = rs.getBoolean(14); + // parse tags string + String tagsStr = rs.getString(15); + if (tagsStr != null) { + Arrays.asList(tagsStr.split(" ")) + .stream().forEach(t -> msg.Tags.add(new Tag(t))); + } + msg.RepliesBy = rs.getString(16); + msg.setText(rs.getString(17)); + return msg; + } + } public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) { KeyHolder holder = new GeneratedKeyHolder(); @@ -72,7 +105,7 @@ public class MessagesQueries { tagsNames += " "; tagsIDs += ","; } - tagsNames += tags.get(i).Name; + tagsNames += tags.get(i).getName(); tagsIDs += "(" + mid + "," + tags.get(i).TID + ")"; } if (tags.size() > 0) { @@ -201,7 +234,7 @@ public class MessagesQueries { public static com.juick.Message getMessage(JdbcTemplate sql, int mid) { try { - return sql.queryForObject("SELECT messages.user_id,users.nick," + return sql.queryForObject("SELECT messages.message_id,messages.user_id,users.nick," + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + "messages.ts," + "messages.readonly,messages.privacy,messages.replies," @@ -211,35 +244,8 @@ public class MessagesQueries { + "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 Object[]{mid}, - (rs, rowNum) -> { - Message msg = new Message(); - msg.setMID(mid); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.TimeAgo = rs.getInt(3); - msg.setDate(rs.getTimestamp(4)); - msg.ReadOnly = rs.getBoolean(5); - msg.Privacy = rs.getInt(6); - msg.FriendsOnly = msg.Privacy < 0; - msg.Replies = rs.getInt(7); - msg.AttachmentType = rs.getString(8); - //if (rs.getInt(9) > 0) { - // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); - //} - if (rs.getDouble(10) != 0) { - msg.Place = new com.juick.Place(); - msg.Place.lat = rs.getDouble(10); - msg.Place.lon = rs.getDouble(11); - } - msg.Likes = rs.getInt(12); - msg.Hidden = rs.getBoolean(13); - msg.parseTags(rs.getString(14)); - msg.RepliesBy = rs.getString(15); - msg.setText(rs.getString(16)); - return msg; - }); + + "WHERE messages.message_id=?", + new MessageMapper(), mid); } catch (EmptyResultDataAccessException e) { return null; } @@ -252,7 +258,7 @@ public class MessagesQueries { + "replies.attach,replies.txt FROM replies INNER JOIN users " + "ON replies.user_id=users.id " + "WHERE replies.message_id=? AND replies.reply_id=?", - new Object[]{mid, rid}, (rs, num) -> { + (rs, num) -> { Message msg = new Message(); msg.setMID(mid); msg.setRID(rid); @@ -264,7 +270,7 @@ public class MessagesQueries { msg.AttachmentType = rs.getString(5); msg.setText(rs.getString(6)); return msg; - }); + }, mid, rid); } catch (EmptyResultDataAccessException e) { return null; } @@ -290,10 +296,9 @@ public class MessagesQueries { return sql.query("SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + "INNER JOIN messages_tags ON (messages_tags.message_id=? AND messages_tags.tag_id=tags.tag_id)", new Object[]{mid}, (rs, num) -> { - com.juick.Tag t = new com.juick.Tag(); + com.juick.Tag t = new com.juick.Tag(rs.getString(3)); t.TID = rs.getInt(1); t.SynonymID = rs.getInt(2); - t.Name = rs.getString(3); t.UsageCnt = rs.getInt(4); return t; }); @@ -479,10 +484,10 @@ public class MessagesQueries { if (before > 0) { mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id<? " + - "ORDER BY id DESC LIMIT 25", new Object[]{search, before}, Integer.class); + "ORDER BY id DESC LIMIT 25", Integer.class, search, before); } else { mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE MATCH(?) " + - "ORDER BY id DESC LIMIT 25", new Object[]{search}, Integer.class); + "ORDER BY id DESC LIMIT 25", Integer.class, search); } if (mids.size() > 0) { return sql.queryForList("SELECT message_id FROM messages WHERE message_id " + @@ -495,10 +500,10 @@ public class MessagesQueries { public static List<Integer> getUserBlog(JdbcTemplate sql, int UID, int privacy, int before) { if (before > 0) { return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND message_id<? AND privacy>=" + - privacy + " ORDER BY message_id DESC LIMIT 20", new Object[]{UID, before}, Integer.class); + privacy + " ORDER BY message_id DESC LIMIT 20", Integer.class, UID, before); } else { return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + - " ORDER BY message_id DESC LIMIT 20", new Object[]{UID}, Integer.class); + " ORDER BY message_id DESC LIMIT 20", Integer.class, UID); } } @@ -508,12 +513,12 @@ public class MessagesQueries { "USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? " + "AND messages.message_id<? AND messages.privacy>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20", - new Object[] {UID, TID, before}, Integer.class); + Integer.class, UID, TID, before); } else { return sql.queryForList("SELECT messages.message_id FROM messages_tags INNER JOIN messages " + "USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? " + "AND messages.privacy>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20", - new Object[]{UID, TID}, Integer.class); + Integer.class, UID, TID); } } @@ -522,11 +527,11 @@ public class MessagesQueries { return sql.queryForList("SELECT message_id FROM favorites " + "WHERE user_id=? AND message_id<? " + "ORDER BY message_id DESC LIMIT 20", Integer.class, - new Object[]{UID, before}); + UID, before); } else { return sql.queryForList("SELECT message_id FROM favorites " + "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", - Integer.class, new Object[]{UID}); + Integer.class, UID); } } @@ -566,43 +571,18 @@ public class MessagesQueries { public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) { if (!mids.isEmpty()) { return sql.query("SELECT messages.message_id,messages.user_id,users.nick," - + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt," + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," - + "messages.ts,messages.replies," - + "messages_txt.repliesby,messages.attach,messages.lat," - + "messages.lon,messages.likes " + + "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 " + "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 (" + StringUtils.arrayToCommaDelimitedString(mids.toArray()) + ") " - + "ORDER BY messages.message_id DESC", (rs, rowNum) -> { - com.juick.Message msg = new com.juick.Message(); - msg.setUser(new User()); - - msg.setMID(rs.getInt(1)); - msg.getUser().setUID(rs.getInt(2)); - msg.getUser().setUName(rs.getString(3)); - if (rs.getString(4) != null) { - msg.parseTags(rs.getString(4)); - } - msg.ReadOnly = rs.getInt(5) == 1; - msg.Privacy = rs.getInt(6); - msg.FriendsOnly = msg.Privacy < 0; - msg.setText(rs.getString(7)); - msg.TimeAgo = rs.getInt(8); - msg.setDate(rs.getTimestamp(9)); - msg.Replies = rs.getInt(10); - msg.RepliesBy = rs.getString(11); - msg.AttachmentType = rs.getString(12); - if (rs.getDouble(13) != 0) { - msg.Place = new com.juick.Place(); - msg.Place.lat = rs.getDouble(13); - msg.Place.lon = rs.getDouble(14); - } - msg.Likes = rs.getInt(15); - return msg; - }); + + "ORDER BY messages.message_id DESC", new MessageMapper()); } return Collections.emptyList(); } |