From de32a6e9e994b830fdf0768d0825559a85d19654 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 16 Jan 2016 23:36:20 +0300 Subject: message queries almost done --- .../java/com/juick/server/MessagesQueries.java | 807 ++++++--------------- .../com/juick/server/SubscriptionsQueries.java | 127 +--- src/main/java/com/juick/server/UserQueries.java | 42 +- .../java/com/juick/server/helpers/PrivacyOpts.java | 29 + 4 files changed, 303 insertions(+), 702 deletions(-) create mode 100644 src/main/java/com/juick/server/helpers/PrivacyOpts.java (limited to 'src') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 1d8eceb3..bcb2d08d 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -19,21 +19,18 @@ package com.juick.server; import com.juick.Message; import com.juick.User; +import com.juick.server.helpers.PrivacyOpts; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; -import java.sql.Connection; import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Collections; import java.util.List; -import java.util.logging.Level; import java.util.logging.Logger; /** @@ -125,83 +122,53 @@ public class MessagesQueries { return sql.update("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid); } - public static boolean recommendMessage(Connection sql, int mid, int vuid) { - boolean res = SQLHelpers.execute(sql, "INSERT IGNORE INTO favorites(user_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + public static boolean recommendMessage(JdbcTemplate sql, int mid, int vuid) { + boolean res = sql.update("INSERT IGNORE INTO favorites(user_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; if (res) { - SQLHelpers.executeInt(sql, "UPDATE messages SET likes=likes+1 WHERE message_id=?", mid); + sql.update("UPDATE messages SET likes=likes+1 WHERE message_id=?", mid); } return res; } - public static boolean canViewThread(Connection sql, int mid, int uid) { + public static boolean canViewThread(JdbcTemplate sql, int mid, int uid) { - int privacy = 0; - int owner_uid = 0; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id,privacy FROM messages WHERE messages.message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - owner_uid = rs.getInt(1); - privacy = rs.getInt(2); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + PrivacyOpts privacyOpts = sql.queryForObject("SELECT user_id,privacy FROM messages WHERE messages.message_id=?", + new Object[] {mid}, + (rs, rowNum) -> { + PrivacyOpts res = new PrivacyOpts(); + res.setUid(rs.getInt(1)); + res.setPrivacy(rs.getInt(2)); + return res; + }); - return privacy >= 0 - || uid == owner_uid - || ((privacy == -1 || privacy == -2) && uid > 0 && UserQueries.isInWL(sql, owner_uid, uid)); + return privacyOpts.getPrivacy() >= 0 + || uid == privacyOpts.getUid() + || ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0 + && UserQueries.isInWL(sql, privacyOpts.getUid(), uid)); } - public static boolean isReadOnly(Connection sql, int mid) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isReadOnly(JdbcTemplate sql, int mid) { try { - stmt = sql.prepareStatement("SELECT readonly FROM messages WHERE message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1) == 1; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT readonly FROM messages WHERE message_id=?", new Object[]{mid}, Integer.class) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } - public static boolean isSubscribed(Connection sql, int uid, int mid) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isSubscribed(JdbcTemplate sql, int uid, int mid) { try { - stmt = sql.prepareStatement("SELECT 1 FROM subscr_messages WHERE suser_id=? AND message_id=?"); - stmt.setInt(1, uid); - stmt.setInt(2, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1) == 1; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT 1 FROM subscr_messages WHERE suser_id=? AND message_id=?", new Object[]{uid, mid}, Integer.class) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } - public static int getMessagePrivacy(Connection sql, int mid) { - return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4); + public static int getMessagePrivacy(JdbcTemplate sql, int mid) { + try { + return sql.queryForObject("SELECT privacy FROM messages WHERE message_id=?", new Object[]{mid}, Integer.class); + } catch (EmptyResultDataAccessException e) { + return -4; + } } @@ -252,24 +219,28 @@ public class MessagesQueries { } public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) { - return sql.queryForObject("SELECT replies.user_id,users.nick," - + "replies.replyto,replies.ts," - + "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) -> { - Message msg = new Message(); - msg.setMID(mid); - msg.setRID(rid); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.ReplyTo = rs.getInt(3); - msg.setDate(rs.getTimestamp(4)); - msg.AttachmentType = rs.getString(5); - msg.setText(rs.getString(6)); - return msg; - }); + try { + return sql.queryForObject("SELECT replies.user_id,users.nick," + + "replies.replyto,replies.ts," + + "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) -> { + Message msg = new Message(); + msg.setMID(mid); + msg.setRID(rid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.ReplyTo = rs.getInt(3); + msg.setDate(rs.getTimestamp(4)); + msg.AttachmentType = rs.getString(5); + msg.setText(rs.getString(6)); + return msg; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } } public static User getMessageAuthor(JdbcTemplate sql, int mid) { @@ -284,215 +255,96 @@ public class MessagesQueries { }); } - public static List getMessageTags(Connection sql, int mid) { - List tags = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("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)"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Tag t = new com.juick.Tag(); - t.TID = rs.getInt(1); - t.SynonymID = rs.getInt(2); - t.Name = rs.getString(3); - t.UsageCnt = rs.getInt(4); - tags.add(t); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return tags; + public static List getMessageTags(JdbcTemplate sql, int mid) { + 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(); + t.TID = rs.getInt(1); + t.SynonymID = rs.getInt(2); + t.Name = rs.getString(3); + t.UsageCnt = rs.getInt(4); + return t; + }); } - public static List getMessageTagsIDs(Connection sql, int mid) { - List tids = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tag_id FROM messages_tags WHERE message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - tids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return tids; + public static List getMessageTagsIDs(JdbcTemplate sql, int mid) { + return sql.queryForList("SELECT tag_id FROM messages_tags WHERE message_id=?", new Object[] {mid}, Integer.class); } - public static ArrayList getMessageRecommendations(Connection sql, int mid) { - ArrayList users = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT users.nick FROM favorites INNER JOIN users ON (favorites.message_id=? AND favorites.user_id=users.id)"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - users.add(rs.getString(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return users; + public static List getMessageRecommendations(JdbcTemplate sql, int mid) { + return sql.queryForList("SELECT users.nick FROM favorites INNER JOIN users " + + "ON (favorites.message_id=? AND favorites.user_id=users.id)", + new Object[] {mid}, String.class); } - public static List getAll(Connection sql, int visitor_uid, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (visitor_uid > 1) { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + - " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, before); - stmt.setInt(2, visitor_uid); - stmt.setInt(3, visitor_uid); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0" + - " AND (privacy>0 OR user_id=?)" + - " AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + - " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, visitor_uid); - stmt.setInt(2, visitor_uid); - } + public static List getAll(JdbcTemplate sql, int visitor_uid, int before) { + if (visitor_uid > 1) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + new Object[]{before, visitor_uid, visitor_uid}, Integer.class); } else { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND user_id NOT IN (SELECT id from users WHERE banned=1) " + - " ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, before); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0 AND privacy>0" + - " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); - } - - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); + return sql.queryForList("SELECT message_id FROM messages WHERE hidden=0" + + " AND (privacy>0 OR user_id=?)" + + " AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?)" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + new Object[]{visitor_uid, visitor_uid}, Integer.class); } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return mids; - } - - public static List getTag(Connection sql, int tid, int visitor_uid, int before, int cnt) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { + } else { if (before > 0) { -// stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (SELECT tag_id FROM tags WHERE tag_id=? OR synonym_id=?) AND messages.message_id0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); - stmt = sql.prepareStatement("SELECT message_id FROM (tags INNER JOIN messages_tags ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) INNER JOIN messages USING(message_id) WHERE messages.message_id0 OR messages.user_id=?) ORDER BY message_id DESC LIMIT ?"); - stmt.setInt(1, tid); - stmt.setInt(2, tid); - stmt.setInt(3, before); - stmt.setInt(4, visitor_uid); - stmt.setInt(5, cnt); + return sql.queryForList("SELECT message_id FROM messages WHERE message_id0 AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + " ORDER BY message_id DESC LIMIT 20", + new Object[]{before}, Integer.class); } else { - //stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (SELECT tag_id FROM tags WHERE tag_id=? OR synonym_id=?) AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); - stmt = sql.prepareStatement("SELECT message_id FROM (tags INNER JOIN messages_tags ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) INNER JOIN messages USING(message_id) WHERE messages.privacy>0 OR messages.user_id=? ORDER BY message_id DESC LIMIT ?"); - stmt.setInt(1, tid); - stmt.setInt(2, tid); - stmt.setInt(3, visitor_uid); - stmt.setInt(4, cnt); + return sql.queryForList("SELECT message_id FROM messages WHERE hidden=0 AND privacy>0" + + " AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20", + Integer.class); } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); } - return mids; } - public static List getTags(Connection sql, String tids, int visitor_uid, int before, int cnt) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") AND messages.message_id0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); - stmt.setInt(1, before); - stmt.setInt(2, visitor_uid); - stmt.setInt(3, cnt); - } else { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); - stmt.setInt(1, visitor_uid); - stmt.setInt(2, cnt); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getTag(JdbcTemplate sql, int tid, int visitor_uid, int before, int cnt) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM (tags INNER JOIN messages_tags " + + "ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) " + + "INNER JOIN messages USING(message_id) WHERE messages.message_id0 OR messages.user_id=?) ORDER BY message_id DESC LIMIT ?", + new Object[]{tid, tid, before, visitor_uid, cnt}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM (tags INNER JOIN messages_tags " + + "ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) " + + "INNER JOIN messages USING(message_id) WHERE messages.privacy>0 OR messages.user_id=? " + + "ORDER BY message_id DESC LIMIT ?", + new Object[]{tid, tid, visitor_uid, cnt}, Integer.class); } - return mids; } - public static List getPlace(Connection sql, int place_id, int visitor_uid, int before) { - List mids = new ArrayList<>(20); + public static List getTags(JdbcTemplate sql, String tids, int visitor_uid, int before, int cnt) { + if (before > 0) { + return sql.queryForList("SELECT messages.message_id FROM messages_tags " + + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + + "AND messages.message_id0 OR messages.user_id=?) " + + "ORDER BY messages.message_id DESC LIMIT ?", new Object[]{before, visitor_uid, cnt}, Integer.class); + } else { + return sql.queryForList("SELECT messages.message_id FROM messages_tags " + + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + + "AND (messages.privacy>0 OR messages.user_id=?) " + + "ORDER BY messages.message_id DESC LIMIT ?", new Object[]{visitor_uid, cnt}, Integer.class); + } + } - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND message_id0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, place_id); - stmt.setInt(2, before); - stmt.setInt(3, visitor_uid); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, place_id); - stmt.setInt(2, visitor_uid); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getPlace(JdbcTemplate sql, int place_id, int visitor_uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE place_id=? AND message_id0 OR user_id=?) ORDER BY message_id DESC LIMIT 20", + new Object[]{place_id, before, visitor_uid}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE place_id=? AND (privacy>0 OR user_id=?) " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{place_id, visitor_uid}, Integer.class); } - return mids; } public static List getMyFeed(JdbcTemplate sql, int uid, int before) { @@ -532,241 +384,103 @@ public class MessagesQueries { return mids; } - public static List getPrivate(Connection sql, int uid, int before) { - List mids = new ArrayList<>(20); + public static List getPrivate(JdbcTemplate sql, int uid, int before) { - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy<0 AND message_id 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy<0 AND message_id getDiscussions(Connection sql, int uid, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM subscr_messages WHERE suser_id=? AND message_id getDiscussions(JdbcTemplate sql, int uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM subscr_messages WHERE suser_id=? AND message_id getRecommended(Connection sql, int uid, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) AND message_id getRecommended(JdbcTemplate sql, int uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM favorites WHERE user_id " + + "IN (SELECT user_id FROM subscr_users WHERE suser_id=?) AND message_id getPopular(Connection sql, int before) { - List mids = new ArrayList<>(20); + public static List getPopular(JdbcTemplate sql, int before) { - PreparedStatement stmt = null; - ResultSet rs = null; - try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND popular>0 ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, before); + return sql.queryForList("SELECT message_id FROM messages WHERE message_id0 " + + "AND popular>0 ORDER BY message_id DESC LIMIT 20", new Object[] {before}, Integer.class); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND popular>0 ORDER BY message_id DESC LIMIT 20"); + return sql.queryForList("SELECT message_id FROM messages WHERE privacy>0 " + + "AND popular>0 ORDER BY message_id DESC LIMIT 20", Integer.class); } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return mids; } - public static List getPhotos(Connection sql, int visitor_uid, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, before); - stmt.setInt(2, visitor_uid); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, visitor_uid); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getPhotos(JdbcTemplate sql, int visitor_uid, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) " + + "AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{before, visitor_uid}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) " + + "AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) " + + "ORDER BY message_id DESC LIMIT 20", new Object[]{visitor_uid}, Integer.class); } - return mids; } - public static List getSearch(Connection sql, Connection sqlSearch, String search, int before) { - List mids0 = new ArrayList<>(20); + public static List getSearch(JdbcTemplate sql, JdbcTemplate sqlSearch, String search, int before) { + List mids; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - - if (before > 0) { - stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id 0) { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id mids = new ArrayList<>(20); - if (mids0.size() > 0) { - try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20"); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + if (mids.size() > 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id " + + "IN (" + Utils.convertArrayInt2String(mids) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20", + Integer.class); } - return mids; } - public static List getUserBlog(Connection sql, int UID, int privacy, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id=" + privacy + " ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, UID); - stmt.setInt(2, before); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + " ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, UID); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List 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=" + + privacy + " ORDER BY message_id DESC LIMIT 20", new Object[]{UID, before}, Integer.class); + } 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); } - return mids; } - public static List getUserTag(Connection sql, int UID, int TID, int privacy, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.message_id=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); - stmt.setInt(1, UID); - stmt.setInt(2, TID); - stmt.setInt(3, before); - } else { - stmt = sql.prepareStatement("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"); - stmt.setInt(1, UID); - stmt.setInt(2, TID); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getUserTag(JdbcTemplate sql, int UID, int TID, int privacy, int before) { + if (before > 0) { + 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.message_id=" + privacy + + " ORDER BY messages.message_id DESC LIMIT 20", + new Object[] {UID, TID, before}, Integer.class); + } 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); } - return mids; } public static List getUserRecommendations(JdbcTemplate sql, int UID, int before) { @@ -783,76 +497,35 @@ public class MessagesQueries { } } - public static List getUserPhotos(Connection sql, int UID, int privacy, int before) { - List mids = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id=" + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, UID); - stmt.setInt(2, before); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, UID); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getUserPhotos(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=" + + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", + new Object[] {UID, before}, Integer.class); + } else { + return sql.queryForList("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", + new Object[] {UID}, Integer.class); } - return mids; } - public static List getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { - List mids0 = new ArrayList<>(20); + public static List getUserSearch(JdbcTemplate sql, JdbcTemplate sqlSearch, int UID, String search, int privacy, int before) { + List mids; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND id mids = new ArrayList<>(20); - if (mids0.size() > 0) { - try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC"); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + if (before > 0) { + mids = sqlSearch.queryForList("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND id 0) { + return sql.queryForList("SELECT message_id FROM messages WHERE message_id IN (" + + Utils.convertArrayInt2String(mids) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC", + Integer.class); + } return mids; } @@ -899,39 +572,25 @@ public class MessagesQueries { }); } - public static List getReplies(Connection sql, int mid) { - List replies = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.setMID(mid); - msg.setRID(rs.getInt(1)); - msg.ReplyTo = rs.getInt(2); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(3)); - msg.getUser().setUName(rs.getString(4)); - msg.getUser().Banned = rs.getBoolean(5); - msg.setText(rs.getString(6)); - msg.TimeAgo = rs.getInt(7); - msg.setDate(rs.getTimestamp(8)); - msg.AttachmentType = rs.getString(9); - - replies.add(msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return replies; + public static List getReplies(JdbcTemplate sql, int mid) { + return sql.query("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt," + + "TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users " + + "ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC", + new Object[]{mid}, (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setMID(mid); + msg.setRID(rs.getInt(1)); + msg.ReplyTo = rs.getInt(2); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(3)); + msg.getUser().setUName(rs.getString(4)); + msg.getUser().Banned = rs.getBoolean(5); + msg.setText(rs.getString(6)); + msg.TimeAgo = rs.getInt(7); + msg.setDate(rs.getTimestamp(8)); + msg.AttachmentType = rs.getString(9); + return msg; + }); } public static boolean setMessagePopular(JdbcTemplate sql, int mid, int popular) { diff --git a/src/main/java/com/juick/server/SubscriptionsQueries.java b/src/main/java/com/juick/server/SubscriptionsQueries.java index 637c2bcf..8207f499 100644 --- a/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -4,12 +4,17 @@ */ package com.juick.server; +import com.juick.User; +import org.springframework.jdbc.core.JdbcTemplate; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.HashSet; import java.util.List; +import java.util.Set; import java.util.logging.Level; import java.util.logging.Logger; @@ -48,99 +53,31 @@ public class SubscriptionsQueries { return jids; } - public static List getJIDSubscribedToUserAndTags(Connection sql, int uid, int mid) { - List jids = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - - String tbl = "subscr_jids_" + mid; + public static List getSubscribedUsers(JdbcTemplate sql, int uid, int mid) { + List userids = UserQueries.getUserRead(sql, uid); List tags = MessagesQueries.getMessageTagsIDs(sql, mid); - - try { - stmt = sql.prepareStatement("CREATE TEMPORARY TABLE " + tbl + "(user_id INT UNSIGNED NOT NULL) ENGINE=MEMORY"); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - - try { - String query = "INSERT INTO " + tbl + " SELECT suser_id FROM subscr_users WHERE user_id=" + uid; - if (!tags.isEmpty()) { - query += " UNION DISTINCT SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + Utils.convertArrayInt2String(tags) + ") AND suser_id!=" + uid; - } - stmt = sql.prepareStatement(query); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - - - try { - String query = "SELECT jids.jid FROM " + tbl + " INNER JOIN jids ON (" + tbl + ".user_id=jids.user_id) WHERE jids.active=1 AND " + tbl + ".user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + uid + ")"; - if (!tags.isEmpty()) { - query += " AND " + tbl + ".user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + Utils.convertArrayInt2String(tags) + "))"; - } - stmt = sql.prepareStatement(query); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - jids.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - try { - stmt = sql.prepareStatement("DROP TABLE " + tbl); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - - return jids; + List tagUsers = sql.queryForList("SELECT suser_id FROM subscr_tags " + + "WHERE tag_id IN (" + Utils.convertArrayInt2String(tags) + ") AND suser_id!=" + uid, Integer.class); + Set set = new HashSet<>(); + set.addAll(userids); + set.addAll(tagUsers); + return UserQueries.getUsersByID(sql, new ArrayList<>(set)); } - public static List getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) { - List jids = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_messages INNER JOIN jids ON (subscr_messages.message_id=? AND subscr_messages.suser_id=jids.user_id) WHERE jids.user_id!=? AND jids.active=1"); - stmt.setInt(1, mid); - stmt.setInt(2, ignore_uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - jids.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getUsersSubscribedToComments(JdbcTemplate sql, int mid, int ignore_uid) { + List userids = sql.queryForList("SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", + new Object[] {mid, ignore_uid}, Integer.class); + if (userids.size() > 0) { + return UserQueries.getUsersByID(sql, userids); + } else { + return new ArrayList<>(); } - return jids; } - public static List getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { - List jids = new ArrayList<>(); - + public static List getUsersSubscribedToUserRecommendations(JdbcTemplate sql, int uid, int mid, int muid) { List tags = MessagesQueries.getMessageTagsIDs(sql, mid); - PreparedStatement stmt = null; - ResultSet rs = null; - try { - String query = "SELECT jid FROM jids WHERE active=1 AND user_id!=" + uid; - query += " AND user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=" + uid + ")"; + String query = "SELECT user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=" + uid + ")"; query += " AND user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + muid + ")"; query += " AND user_id NOT IN (SELECT suser_id FROM subscr_users WHERE user_id=" + muid + ")"; query += " AND user_id NOT IN (SELECT suser_id FROM subscr_messages WHERE message_id=" + mid + ")"; @@ -151,23 +88,11 @@ public class SubscriptionsQueries { query += " AND user_id NOT IN (SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + tagsStr + "))"; query += " AND user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))"; } - - stmt = sql.prepareStatement(query); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - jids.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return jids; + List userids = sql.queryForList(query, Integer.class); + return UserQueries.getUsersByID(sql, userids); } - public static boolean subscribeMessage(Connection sql, int mid, int vuid) { - return SQLHelpers.execute(sql, "INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + public static boolean subscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("INSERT INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; } } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 5588d8e4..21373b7d 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -18,6 +18,7 @@ package com.juick.server; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; @@ -46,9 +47,9 @@ public class UserQueries { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); - user.setUID(rs.getInt("id")); - user.setUName(rs.getString("nick")); - user.Banned = rs.getBoolean("banned"); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.Banned = rs.getBoolean(3); return user; } } @@ -88,9 +89,13 @@ public class UserQueries { } public static User getUserByName(JdbcTemplate sql, String username) { - return sql.queryForObject("SELECT id,nick,banned FROM users WHERE nick=?", - new Object[] {username}, - new UserMapper()); + try { + return sql.queryForObject("SELECT id,nick,banned FROM users WHERE nick=?", + new Object[]{username}, + new UserMapper()); + } catch (EmptyResultDataAccessException e) { + return null; + } } public static User getUserByJID(JdbcTemplate sql, String jid) { @@ -107,8 +112,7 @@ public class UserQueries { public static List getUsersByID(JdbcTemplate sql, List uids) { return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")", - new UserMapper(), - uids.toArray()); + new UserMapper()); } public static boolean fillUsersByID(JdbcTemplate sql, List users) { @@ -262,25 +266,9 @@ public class UserQueries { return ret; } - public static boolean isInWL(Connection sql, int uid, int check) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?"); - stmt.setInt(1, uid); - stmt.setInt(2, check); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1) == 1; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; + public static boolean isInWL(JdbcTemplate sql, int uid, int check) { + return sql.queryForObject("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?", + new Object[] {uid, check}, Integer.class) == 1; } public static boolean isInBL(Connection sql, int uid, int check) { diff --git a/src/main/java/com/juick/server/helpers/PrivacyOpts.java b/src/main/java/com/juick/server/helpers/PrivacyOpts.java new file mode 100644 index 00000000..66cf9410 --- /dev/null +++ b/src/main/java/com/juick/server/helpers/PrivacyOpts.java @@ -0,0 +1,29 @@ +package com.juick.server.helpers; + +/** + * Created by vt on 16/01/16. + */ +public class PrivacyOpts { + private int uid; + private int privacy; + + public PrivacyOpts() { + + } + + public int getUid() { + return uid; + } + + public void setUid(int uid) { + this.uid = uid; + } + + public int getPrivacy() { + return privacy; + } + + public void setPrivacy(int privacy) { + this.privacy = privacy; + } +} -- cgit v1.2.3