diff options
Diffstat (limited to 'juick-core')
9 files changed, 73 insertions, 347 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 a0242543..3d0d734d 100644 --- a/juick-core/src/main/java/com/juick/server/MessagesQueries.java +++ b/juick-core/src/main/java/com/juick/server/MessagesQueries.java @@ -26,6 +26,7 @@ import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.JdbcTemplate; 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; @@ -485,7 +486,7 @@ public class MessagesQueries { } 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", + "IN (" + StringUtils.arrayToCommaDelimitedString(mids.toArray()) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20", Integer.class); } return mids; @@ -556,53 +557,54 @@ public class MessagesQueries { if (mids.size() > 0) { return sql.queryForList("SELECT message_id FROM messages WHERE message_id IN (" + - Utils.convertArrayInt2String(mids) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC", + StringUtils.arrayToCommaDelimitedString(mids.toArray()) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC", Integer.class); } return mids; } public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) { - List<com.juick.Message> msgs = new ArrayList<>(20); - - 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 " - + "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 (" + Utils.convertArrayInt2String(mids) + ") " - + "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; - }); + 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 " + + "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; + }); + } + return Collections.emptyList(); } public static List<com.juick.Message> getReplies(JdbcTemplate sql, int mid) { diff --git a/juick-core/src/main/java/com/juick/server/PMQueries.java b/juick-core/src/main/java/com/juick/server/PMQueries.java index 74d5df3e..10de9a69 100644 --- a/juick-core/src/main/java/com/juick/server/PMQueries.java +++ b/juick-core/src/main/java/com/juick/server/PMQueries.java @@ -83,15 +83,6 @@ public class PMQueries { return qusers; } - public static boolean haveUserInArray(List<com.juick.User> arr, int uid) { - for (User user : arr) { - if (user.getUID() == uid) { - return true; - } - } - return false; - } - public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) { List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm " + "WHERE (user_id=? AND user_id_to=?) " diff --git a/juick-core/src/main/java/com/juick/server/PushQueries.java b/juick-core/src/main/java/com/juick/server/PushQueries.java index 54528993..f4c98fcf 100644 --- a/juick-core/src/main/java/com/juick/server/PushQueries.java +++ b/juick-core/src/main/java/com/juick/server/PushQueries.java @@ -26,11 +26,6 @@ public class PushQueries { } - public static List<String> getAndroidSubscribers(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT regid FROM android INNER JOIN subscr_users " + - "ON (subscr_users.user_id=? AND android.user_id=subscr_users.suser_id)", String.class, uid); - } - public static List<String> getAndroidTokens(JdbcTemplate sql, List<Integer> uids) { return sql.queryForList("SELECT regid FROM android INNER JOIN users " + "ON (users.id=android.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); @@ -44,11 +39,6 @@ public class PushQueries { } } - public static List<String> getWinPhoneSubscribers(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT url FROM winphone INNER JOIN subscr_users " + - "ON (subscr_users.user_id=? AND winphone.user_id=subscr_users.suser_id)", String.class, uid); - } - public static List<String> getWindowsTokens(JdbcTemplate sql, List<Integer> uids) { return sql.queryForList("SELECT url FROM winphone INNER JOIN users " + "ON (users.id=winphone.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); @@ -62,10 +52,6 @@ public class PushQueries { } } - public static List<String> getAPNSSubscribers(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT token FROM ios INNER JOIN subscr_users " + - "ON (subscr_users.user_id=? AND ios.user_id=subscr_users.suser_id)", String.class, uid); - } public static List<String> getAPNSTokens(JdbcTemplate sql, List<Integer> uids) { return sql.queryForList("SELECT token FROM ios INNER JOIN users " + "ON (users.id=ios.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); diff --git a/juick-core/src/main/java/com/juick/server/SQLHelpers.java b/juick-core/src/main/java/com/juick/server/SQLHelpers.java deleted file mode 100644 index 13ebe98a..00000000 --- a/juick-core/src/main/java/com/juick/server/SQLHelpers.java +++ /dev/null @@ -1,168 +0,0 @@ -/* - * To change this template, choose Tools | Templates - * and open the template in the editor. - */ -package com.juick.server; - -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; -import java.util.List; -import java.util.logging.Level; -import java.util.logging.Logger; - -/** - * - * @author ugnich - */ -public class SQLHelpers { - - private static final Logger logger = Logger.getLogger(SQLHelpers.class.getName()); - - public static int execute(Connection sql, String query) { - int ret = -1; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement(query); - ret = stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; - } - - public static int executeInt(Connection sql, String query, int param) { - int ret = -1; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement(query); - stmt.setInt(1, param); - ret = stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; - } - - public static int getInt(Connection sql, String query, int defvalue) { - int ret = defvalue; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; - } - - public static int getInt(Connection sql, String query, int param, int defvalue) { - int ret = defvalue; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - stmt.setInt(1, param); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; - } - - public static int getInt(Connection sql, String query, String param, int defvalue) { - int ret = defvalue; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - stmt.setString(1, param); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; - } - - public static String getString(Connection sql, String query, int param) { - String ret = null; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - stmt.setInt(1, param); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getString(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; - } - - public static String getString(Connection sql, String query, String param) { - String ret = null; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - stmt.setString(1, param); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getString(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; - } - - public static List<Integer> getArrayInteger(Connection sql, String query, int param) { - List<Integer> ret = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement(query); - stmt.setInt(1, param); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - ret.add(rs.getInt(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return ret; - } -} diff --git a/juick-core/src/main/java/com/juick/server/ShowQueries.java b/juick-core/src/main/java/com/juick/server/ShowQueries.java index 3ddff412..b55117c1 100644 --- a/juick-core/src/main/java/com/juick/server/ShowQueries.java +++ b/juick-core/src/main/java/com/juick/server/ShowQueries.java @@ -18,7 +18,7 @@ public class ShowQueries { "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", - String.class, new Object[] {forUser.getUID(), forUser.getUID(), forUser.getUID(), forUser.getUID()}); + String.class, forUser.getUID(), forUser.getUID(), forUser.getUID(), forUser.getUID()); } public static List<String> getTopUsers(JdbcTemplate sql) { diff --git a/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java b/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java index 7c30285b..21a07e05 100644 --- a/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/juick-core/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -9,11 +9,7 @@ import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.util.StringUtils; -import java.util.ArrayList; -import java.util.HashSet; -import java.util.List; -import java.util.Set; -import java.util.logging.Logger; +import java.util.*; import java.util.stream.Collectors; /** @@ -22,8 +18,6 @@ import java.util.stream.Collectors; */ public class SubscriptionsQueries { - private static final Logger logger = Logger.getLogger(SubscriptionsQueries.class.getName()); - public static List<String> getJIDSubscribedToUser(JdbcTemplate sql, int uid, boolean friendsonly) { if (friendsonly == false) { return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + @@ -51,11 +45,11 @@ public class SubscriptionsQueries { public static List<User> getUsersSubscribedToComments(JdbcTemplate sql, int mid, int ignore_uid) { List<Integer> userids = sql.queryForList("SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", - new Object[] {mid, ignore_uid}, Integer.class); + Integer.class, mid, ignore_uid); if (userids.size() > 0) { return UserQueries.getUsersByID(sql, userids); } else { - return new ArrayList<>(); + return Collections.emptyList(); } } @@ -69,7 +63,7 @@ public class SubscriptionsQueries { query += " AND user_id NOT IN (SELECT user_id FROM favorites WHERE message_id=" + mid + ")"; query += " AND user_id NOT IN (SELECT subscr_users.suser_id FROM subscr_users INNER JOIN favorites ON (favorites.message_id=" + mid + " AND subscr_users.user_id=favorites.user_id AND favorites.user_id!=" + uid + "))"; if (!tags.isEmpty()) { - String tagsStr = Utils.convertArrayInt2String(tags); + String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray()); 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 + "))"; } diff --git a/juick-core/src/main/java/com/juick/server/TagQueries.java b/juick-core/src/main/java/com/juick/server/TagQueries.java index 2258a8ea..766f9bcd 100644 --- a/juick-core/src/main/java/com/juick/server/TagQueries.java +++ b/juick-core/src/main/java/com/juick/server/TagQueries.java @@ -41,13 +41,13 @@ public class TagQueries { public static com.juick.Tag getTag(JdbcTemplate sql, int tid) { try { return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", - new Object[]{tid}, (rs, num) -> { + (rs, num) -> { Tag ret = new Tag(); ret.TID = tid; ret.SynonymID = rs.getInt(1); ret.Name = rs.getString(2); return ret; - }); + }, tid); } catch (EmptyResultDataAccessException e) { return null; } @@ -57,13 +57,13 @@ public class TagQueries { Tag ret = null; try { ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?", - new Object[]{tag}, (rs, rowNum) -> { + (rs, rowNum) -> { Tag ret1 = new Tag(); ret1.TID = rs.getInt(1); ret1.SynonymID = rs.getInt(2); ret1.Name = rs.getString(3); return ret1; - }); + }, tag); } catch (EmptyResultDataAccessException e) { // tag not found } diff --git a/juick-core/src/main/java/com/juick/server/UserQueries.java b/juick-core/src/main/java/com/juick/server/UserQueries.java index db380d7a..eb2918c0 100644 --- a/juick-core/src/main/java/com/juick/server/UserQueries.java +++ b/juick-core/src/main/java/com/juick/server/UserQueries.java @@ -118,7 +118,7 @@ public class UserQueries { public static List<User> getUsersByName(JdbcTemplate sql, List<String> unames) { if (!unames.isEmpty()) { - return sql.query("SELECT id,nick,banned FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")", + return sql.query("SELECT id,nick,banned FROM users WHERE nick IN (" + StringUtils.arrayToCommaDelimitedString(unames.toArray()) + ")", new UserMapper()); } return Collections.emptyList(); @@ -126,7 +126,7 @@ public class UserQueries { public static List<User> getUsersByID(JdbcTemplate sql, List<Integer> uids) { if (!uids.isEmpty()) { - return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")", + return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + StringUtils.arrayToCommaDelimitedString(uids.toArray()) + ")", new UserMapper()); } return Collections.emptyList(); @@ -156,16 +156,19 @@ public class UserQueries { } public static List<com.juick.User> getUsersByJID(JdbcTemplate sql, List<String> jids) { - return sql.query("SELECT users.id,users.nick,jids.jid FROM users " - + "INNER JOIN jids ON jids.user_id=users.id " - + "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")", - (rs, rowNum) -> { - com.juick.User user = new com.juick.User(); - user.setUID(rs.getInt(1)); - user.setUName(rs.getString(2)); - user.setJID(rs.getString(3)); - return user; - }); + if (!jids.isEmpty()) { + return sql.query("SELECT users.id,users.nick,jids.jid FROM users " + + "INNER JOIN jids ON jids.user_id=users.id " + + "WHERE jids.jid IN (" + StringUtils.arrayToCommaDelimitedString(jids.toArray()) + ")", + (rs, rowNum) -> { + com.juick.User user = new com.juick.User(); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.setJID(rs.getString(3)); + return user; + }); + } + return Collections.emptyList(); } public static List<String> getJIDsbyUID(JdbcTemplate sql, int uid) { @@ -173,7 +176,11 @@ public class UserQueries { } public static int getUIDbyJID(JdbcTemplate sql, String jid) { - return sql.queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid); + try { + return sql.queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } public static int getUIDbyName(JdbcTemplate sql, String uname) { diff --git a/juick-core/src/main/java/com/juick/server/Utils.java b/juick-core/src/main/java/com/juick/server/Utils.java deleted file mode 100644 index 1968150e..00000000 --- a/juick-core/src/main/java/com/juick/server/Utils.java +++ /dev/null @@ -1,86 +0,0 @@ -/* - * Juick - * Copyright (C) 2008-2011, Ugnich Anton - * - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - */ -package com.juick.server; - -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; -import java.util.List; - -/** - * - * @author Ugnich Anton - */ -public class Utils { - - public static String convertArrayInt2String(List<Integer> mids) { - String q = ""; - for (int i = 0; i < mids.size(); i++) { - if (i > 0) { - q += ","; - } - q += mids.get(i); - } - return q; - } - - public static String convertArrayString2String(List<String> unames) { - String q = ""; - for (int i = 0; i < unames.size(); i++) { - if (i > 0) { - q += ","; - } - q += "\"" + unames.get(i) + "\""; - } - return q; - } - - public static String buildQueryArray(String query1, int length, String query2) { - String ret = query1; - for (int i = 0; i < length; i++) { - if (i > 0) { - ret += ","; - } - ret += "?"; - } - ret += query2; - return ret; - } - - public static void stmtSetStringArray(PreparedStatement stmt, int offset, String strs[]) throws SQLException { - for (int i = 0; i < strs.length; i++) { - stmt.setString(offset + i, strs[i]); - } - } - - public static void finishSQL(ResultSet rs, Statement stmt) { - if (rs != null) { - try { - rs.close(); - } catch (SQLException e) { - } - } - if (stmt != null) { - try { - stmt.close(); - } catch (SQLException e) { - } - } - } -} |