From d00fd7705cb1a4b085eed7f34df2f1c6d9a69f76 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 24 Oct 2015 18:09:15 +0300 Subject: moving to Gradle --- src/main/java/com/juick/server/PMQueries.java | 303 ++++++++++++++++++++++++++ 1 file changed, 303 insertions(+) create mode 100644 src/main/java/com/juick/server/PMQueries.java (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java new file mode 100644 index 00000000..56b91abf --- /dev/null +++ b/src/main/java/com/juick/server/PMQueries.java @@ -0,0 +1,303 @@ +/* + * 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 . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; + +/** + * + * @author Ugnich Anton + */ +public class PMQueries { + + public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)"); + stmt.setInt(1, uid_from); + stmt.setInt(2, uid_to); + stmt.setString(3, body); + ret = stmt.executeUpdate() > 0; + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + if (ret) { + PreparedStatement stmt2 = null; + try { + stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1"); + stmt2.setInt(1, uid_from); + stmt2.setInt(2, uid_to); + stmt2.executeUpdate(); + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt2); + } + } + + return ret; + } + + public static boolean addPMinRoster(Connection sql, int uid, String jid) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)"); + stmt.setInt(1, uid); + stmt.setString(2, jid); + ret = stmt.executeUpdate() > 0; + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + return ret; + } + + public static boolean removePMinRoster(Connection sql, int uid, String jid) { + boolean ret = false; + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?"); + stmt.setInt(1, uid); + stmt.setString(2, jid); + ret = stmt.executeUpdate() > 0; + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + return ret; + } + + public static boolean havePMinRoster(Connection sql, int uid, String jid) { + boolean ret = false; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?"); + stmt.setInt(1, uid); + stmt.setString(2, jid); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = true; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static String getLastView(Connection sql, int uid_from, int uid_to) { + String ret = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?"); + stmt.setInt(1, uid_from); + stmt.setInt(2, uid_to); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getString(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static ArrayList getPMLastConversationsUsers(Connection sql, int uid, int cnt) { + ArrayList users = new ArrayList(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.User u = new com.juick.User(); + u.UID = rs.getInt(1); + u.MessagesCount = rs.getInt(2); + users.add(u); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (users.size() < cnt) { + try { + stmt = sql.prepareStatement("SELECT user_id,user_id_to FROM pm_streams WHERE (user_id=? OR user_id_to=?) AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) ORDER BY lastmessage DESC LIMIT ?"); + stmt.setInt(1, uid); + stmt.setInt(2, uid); + stmt.setInt(3, cnt * 2); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + int uuid = rs.getInt(1) + rs.getInt(2) - uid; + if (!haveUserInArray(users, uuid)) { + com.juick.User u = new com.juick.User(); + u.UID = uuid; + users.add(u); + if (users.size() >= cnt) { + break; + } + } + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + if (!users.isEmpty()) { + UserQueries.fillUsersByID(sql, users); + } + + return users; + } + + public static boolean haveUserInArray(ArrayList arr, int uid) { + int s = arr.size(); + for (int i = 0; i < s; i++) { + if (arr.get(i).UID == uid) { + return true; + } + } + return false; + } + + public static ArrayList getPMMessages(Connection sql, int uid, int uid_to) { + ArrayList msgs = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT user_id,txt,ts FROM pm WHERE (user_id=? AND user_id_to=?) OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, uid_to); + stmt.setInt(3, uid); + stmt.setInt(4, uid_to); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Message msg = new com.juick.Message(); + int uuid = rs.getInt(1); + msg.User = new com.juick.User(); + msg.User.UID = uuid; + msg.Text = rs.getString(2); + msg.TimestampString = rs.getString(3); + msgs.add(0, msg); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + PreparedStatement stmt2 = null; + try { + stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?"); + stmt2.setInt(1, uid); + stmt2.setInt(2, uid_to); + stmt2.executeUpdate(); + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt2); + } + + return msgs; + } + + public static ArrayList getLastPMInbox(Connection sql, int uid) { + ArrayList msgs = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Message msg = new com.juick.Message(); + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.Text = rs.getString(3); + msg.TimeAgo = rs.getInt(4); + msg.TimestampString = rs.getString(5); + msgs.add(0, msg); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msgs; + } + + public static ArrayList getLastPMSent(Connection sql, int uid) { + ArrayList msgs = new ArrayList(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Message msg = new com.juick.Message(); + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.Text = rs.getString(3); + msg.TimeAgo = rs.getInt(4); + msg.TimestampString = rs.getString(5); + msgs.add(0, msg); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msgs; + } +} -- cgit v1.2.3 From c37ed56f884661666b964e47efd23a36be9b24d4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 10 Jan 2016 16:56:51 +0300 Subject: logger, diamond and arraylist refactoring --- src/main/java/com/juick/server/AdsQueries.java | 6 +- .../java/com/juick/server/CrosspostQueries.java | 8 +- .../java/com/juick/server/MessagesQueries.java | 173 +++++++++++---------- src/main/java/com/juick/server/PMQueries.java | 54 ++++--- src/main/java/com/juick/server/PushQueries.java | 20 ++- src/main/java/com/juick/server/SQLHelpers.java | 25 +-- .../com/juick/server/SubscriptionsQueries.java | 39 +++-- src/main/java/com/juick/server/TagQueries.java | 37 +++-- src/main/java/com/juick/server/UserQueries.java | 79 +++++----- src/main/java/com/juick/server/Utils.java | 6 +- 10 files changed, 244 insertions(+), 203 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java index 0fb4c0a6..ad9b7149 100644 --- a/src/main/java/com/juick/server/AdsQueries.java +++ b/src/main/java/com/juick/server/AdsQueries.java @@ -20,6 +20,8 @@ package com.juick.server; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -27,6 +29,8 @@ import java.sql.SQLException; */ public class AdsQueries { + private static final Logger logger = Logger.getLogger(AdsQueries.class.getName()); + public static int getAdMID(Connection sql, int uid) { if (uid > 0) { return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0); @@ -43,7 +47,7 @@ public class AdsQueries { stmt.setInt(2, mid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java index fd677048..b44b6b59 100644 --- a/src/main/java/com/juick/server/CrosspostQueries.java +++ b/src/main/java/com/juick/server/CrosspostQueries.java @@ -21,6 +21,8 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -28,6 +30,8 @@ import java.sql.SQLException; */ public class CrosspostQueries { + private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName()); + public static String[] getTwitterTokens(Connection sql, int uid) { String tokens[] = null; @@ -43,7 +47,7 @@ public class CrosspostQueries { tokens[1] = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -70,7 +74,7 @@ public class CrosspostQueries { tokens[1] = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 82fef672..4d5683bd 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -25,6 +25,9 @@ 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; /** * @@ -32,11 +35,13 @@ import java.util.Collections; */ public class MessagesQueries { - public static int createMessage(Connection sql, int uid, String txt, String attachment, ArrayList tags) { + private static final Logger logger = Logger.getLogger(MessagesQueries.class.getName()); + + public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { int mid = 0; PreparedStatement stmt = null; - ResultSet rs = null; + ResultSet rs; try { stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, uid); @@ -51,7 +56,7 @@ public class MessagesQueries { mid = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -73,7 +78,7 @@ public class MessagesQueries { stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -81,7 +86,7 @@ public class MessagesQueries { try { stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)"); stmt.setInt(1, mid); - if (tagsNames == "") { + if (tagsNames.isEmpty()) { stmt.setNull(2, Types.VARCHAR); } else { stmt.setString(2, tagsNames); @@ -89,7 +94,7 @@ public class MessagesQueries { stmt.setString(3, txt); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -118,7 +123,7 @@ public class MessagesQueries { ridnew = 0; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -166,7 +171,7 @@ public class MessagesQueries { privacy = rs.getInt(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -189,7 +194,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -210,7 +215,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -255,7 +260,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -276,7 +281,7 @@ public class MessagesQueries { msg.Text = rs.getString(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -307,7 +312,7 @@ public class MessagesQueries { msg.Text = rs.getString(6); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -330,15 +335,15 @@ public class MessagesQueries { user.UName = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return user; } - public static ArrayList getMessageTags(Connection sql, int mid) { - ArrayList tags = new ArrayList(); + public static List getMessageTags(Connection sql, int mid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -356,7 +361,7 @@ public class MessagesQueries { tags.add(t); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -364,8 +369,8 @@ public class MessagesQueries { return tags; } - public static ArrayList getMessageTagsIDs(Connection sql, int mid) { - ArrayList tids = new ArrayList(); + public static List getMessageTagsIDs(Connection sql, int mid) { + List tids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -378,7 +383,7 @@ public class MessagesQueries { tids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -387,7 +392,7 @@ public class MessagesQueries { } public static ArrayList getMessageRecommendations(Connection sql, int mid) { - ArrayList users = new ArrayList(); + ArrayList users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -400,7 +405,7 @@ public class MessagesQueries { users.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -408,8 +413,8 @@ public class MessagesQueries { return users; } - public static ArrayList getAll(Connection sql, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getAll(Connection sql, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -448,15 +453,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getTag(Connection sql, int tid, int visitor_uid, int before, int cnt) { - ArrayList mids = new ArrayList(20); + 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; @@ -483,15 +488,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getTags(Connection sql, String tids, int visitor_uid, int before, int cnt) { - ArrayList mids = new ArrayList(20); + 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; @@ -512,15 +517,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getPlace(Connection sql, int place_id, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPlace(Connection sql, int place_id, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -541,15 +546,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getMyFeed(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(40); + public static List getMyFeed(Connection sql, int uid, int before) { + List mids = new ArrayList<>(40); PreparedStatement stmt = null; ResultSet rs = null; try { @@ -569,7 +574,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -589,7 +594,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -603,8 +608,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getPrivate(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPrivate(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -623,15 +628,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getDiscussions(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getDiscussions(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -650,7 +655,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -658,8 +663,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getRecommended(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getRecommended(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -678,7 +683,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -687,8 +692,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getPopular(Connection sql, int before) { - ArrayList mids = new ArrayList(20); + public static List getPopular(Connection sql, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -705,15 +710,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getPhotos(Connection sql, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPhotos(Connection sql, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -732,15 +737,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getSearch(Connection sql, Connection sqlSearch, String search, int before) { - ArrayList mids0 = new ArrayList(20); + public static List getSearch(Connection sql, Connection sqlSearch, String search, int before) { + List mids0 = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -760,12 +765,12 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } - ArrayList mids = new ArrayList(20); + List 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"); @@ -775,7 +780,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -784,8 +789,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getUserBlog(Connection sql, int UID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserBlog(Connection sql, int UID, int privacy, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -804,15 +809,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserTag(Connection sql, int UID, int TID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + 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; @@ -833,15 +838,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserRecommendations(Connection sql, int UID, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserRecommendations(Connection sql, int UID, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -860,15 +865,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserPhotos(Connection sql, int UID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserPhotos(Connection sql, int UID, int privacy, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -887,15 +892,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { - ArrayList mids0 = new ArrayList(20); + public static List getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { + List mids0 = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -916,12 +921,12 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } - ArrayList mids = new ArrayList(20); + List 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"); @@ -931,7 +936,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -940,8 +945,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getMessages(Connection sql, ArrayList mids) { - ArrayList msgs = new ArrayList(20); + public static List getMessages(Connection sql, List mids) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -978,7 +983,7 @@ public class MessagesQueries { msgs.add(msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -986,8 +991,8 @@ public class MessagesQueries { return msgs; } - public static ArrayList getReplies(Connection sql, int mid) { - ArrayList replies = new ArrayList(); + public static List getReplies(Connection sql, int mid) { + List replies = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -1013,7 +1018,7 @@ public class MessagesQueries { replies.add(msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -1039,7 +1044,7 @@ public class MessagesQueries { } ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1050,7 +1055,7 @@ public class MessagesQueries { stmt.setInt(1, mid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1068,7 +1073,7 @@ public class MessagesQueries { stmt.setInt(1, mid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 56b91abf..c4b4fa2d 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -17,11 +17,16 @@ */ package com.juick.server; +import com.juick.User; + 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; /** * @@ -29,6 +34,8 @@ import java.util.ArrayList; */ public class PMQueries { + private static final Logger logger = Logger.getLogger(PMQueries.class.getName()); + public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) { boolean ret = false; PreparedStatement stmt = null; @@ -39,7 +46,7 @@ public class PMQueries { stmt.setString(3, body); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -52,7 +59,7 @@ public class PMQueries { stmt2.setInt(2, uid_to); stmt2.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt2); } @@ -70,7 +77,7 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -86,7 +93,7 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -107,7 +114,7 @@ public class PMQueries { ret = true; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -128,15 +135,15 @@ public class PMQueries { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getPMLastConversationsUsers(Connection sql, int uid, int cnt) { - ArrayList users = new ArrayList(); + public static List getPMLastConversationsUsers(Connection sql, int uid, int cnt) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -152,7 +159,7 @@ public class PMQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -177,7 +184,7 @@ public class PMQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -190,18 +197,17 @@ public class PMQueries { return users; } - public static boolean haveUserInArray(ArrayList arr, int uid) { - int s = arr.size(); - for (int i = 0; i < s; i++) { - if (arr.get(i).UID == uid) { + public static boolean haveUserInArray(List arr, int uid) { + for (User user : arr) { + if (user.UID == uid) { return true; } } return false; } - public static ArrayList getPMMessages(Connection sql, int uid, int uid_to) { - ArrayList msgs = new ArrayList(20); + public static List getPMMessages(Connection sql, int uid, int uid_to) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -223,7 +229,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -235,7 +241,7 @@ public class PMQueries { stmt2.setInt(2, uid_to); stmt2.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt2); } @@ -243,8 +249,8 @@ public class PMQueries { return msgs; } - public static ArrayList getLastPMInbox(Connection sql, int uid) { - ArrayList msgs = new ArrayList(20); + public static List getLastPMInbox(Connection sql, int uid) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -264,7 +270,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -272,8 +278,8 @@ public class PMQueries { return msgs; } - public static ArrayList getLastPMSent(Connection sql, int uid) { - ArrayList msgs = new ArrayList(20); + public static List getLastPMSent(Connection sql, int uid) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -293,7 +299,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/PushQueries.java b/src/main/java/com/juick/server/PushQueries.java index bf7505ca..c17972b0 100644 --- a/src/main/java/com/juick/server/PushQueries.java +++ b/src/main/java/com/juick/server/PushQueries.java @@ -10,6 +10,8 @@ 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; /** * @@ -17,12 +19,14 @@ import java.util.List; */ public class PushQueries { + private static final Logger logger = Logger.getLogger(PushQueries.class.getName()); + public static String getAndroidRegID(Connection sql, int uid) { return SQLHelpers.getString(sql, "SELECT regid FROM android WHERE user_id=?", uid); } - public static ArrayList getAndroidSubscribers(Connection sql, int uid) { - ArrayList regids = new ArrayList(); + public static List getAndroidSubscribers(Connection sql, int uid) { + List regids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -35,7 +39,7 @@ public class PushQueries { regids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -46,8 +50,8 @@ public class PushQueries { return SQLHelpers.getString(sql, "SELECT url FROM winphone WHERE user_id=?", uid); } - public static ArrayList getWinPhoneSubscribers(Connection sql, int uid) { - ArrayList urls = new ArrayList(); + public static List getWinPhoneSubscribers(Connection sql, int uid) { + List urls = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -60,7 +64,7 @@ public class PushQueries { urls.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -72,7 +76,7 @@ public class PushQueries { } public static List getAPNSSubscribers(Connection sql, int uid) { - List urls = new ArrayList(); + List urls = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -85,7 +89,7 @@ public class PushQueries { urls.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/SQLHelpers.java b/src/main/java/com/juick/server/SQLHelpers.java index f5569993..13ebe98a 100644 --- a/src/main/java/com/juick/server/SQLHelpers.java +++ b/src/main/java/com/juick/server/SQLHelpers.java @@ -9,6 +9,9 @@ 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; /** * @@ -16,6 +19,8 @@ import java.util.ArrayList; */ 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; @@ -23,7 +28,7 @@ public class SQLHelpers { stmt = sql.prepareStatement(query); ret = stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -38,7 +43,7 @@ public class SQLHelpers { stmt.setInt(1, param); ret = stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -56,7 +61,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -75,7 +80,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -94,7 +99,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -113,7 +118,7 @@ public class SQLHelpers { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -132,15 +137,15 @@ public class SQLHelpers { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getArrayInteger(Connection sql, String query, int param) { - ArrayList ret = new ArrayList(); + public static List getArrayInteger(Connection sql, String query, int param) { + List ret = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -153,7 +158,7 @@ public class SQLHelpers { ret.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/SubscriptionsQueries.java b/src/main/java/com/juick/server/SubscriptionsQueries.java index d0f5f308..637c2bcf 100644 --- a/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -9,6 +9,9 @@ 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; /** * @@ -16,8 +19,10 @@ import java.util.ArrayList; */ public class SubscriptionsQueries { - public static ArrayList getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) { - ArrayList jids = new ArrayList(); + private static final Logger logger = Logger.getLogger(SubscriptionsQueries.class.getName()); + + public static List getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) { + List jids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -36,27 +41,27 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return jids; } - public static ArrayList getJIDSubscribedToUserAndTags(Connection sql, int uid, int mid) { - ArrayList jids = new ArrayList(); + 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; - ArrayList tags = MessagesQueries.getMessageTagsIDs(sql, mid); + 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) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -69,7 +74,7 @@ public class SubscriptionsQueries { stmt = sql.prepareStatement(query); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -87,7 +92,7 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -96,7 +101,7 @@ public class SubscriptionsQueries { stmt = sql.prepareStatement("DROP TABLE " + tbl); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -104,8 +109,8 @@ public class SubscriptionsQueries { return jids; } - public static ArrayList getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) { - ArrayList jids = new ArrayList(); + public static List getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) { + List jids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -119,17 +124,17 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return jids; } - public static ArrayList getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { - ArrayList jids = new ArrayList(); + public static List getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { + List jids = new ArrayList<>(); - ArrayList tags = MessagesQueries.getMessageTagsIDs(sql, mid); + List tags = MessagesQueries.getMessageTagsIDs(sql, mid); PreparedStatement stmt = null; ResultSet rs = null; @@ -154,7 +159,7 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 493e8675..1c5e27d1 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -17,12 +17,17 @@ */ package com.juick.server; +import com.juick.Tag; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -30,6 +35,8 @@ import java.util.ArrayList; */ public class TagQueries { + private static final Logger logger = Logger.getLogger(TagQueries.class.getName()); + public static com.juick.Tag getTag(Connection sql, int tid) { com.juick.Tag ret = null; @@ -46,7 +53,7 @@ public class TagQueries { ret.Name = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -70,7 +77,7 @@ public class TagQueries { ret.Name = rs.getString(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -84,12 +91,12 @@ public class TagQueries { return ret; } - public static ArrayList getTags(Connection sql, String[] tags, boolean autoCreate) { - ArrayList ret = new ArrayList(); + public static List getTags(Connection sql, String[] tags, boolean autoCreate) { + List ret = new ArrayList<>(); - for (int i = 0; i < tags.length; i++) { - if (!tags[i].isEmpty()) { - com.juick.Tag t = getTag(sql, tags[i], autoCreate); + for (String tag : tags) { + if (!tag.isEmpty()) { + Tag t = getTag(sql, tag, autoCreate); if (t != null) { ret.add(t); } @@ -112,7 +119,7 @@ public class TagQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -134,7 +141,7 @@ public class TagQueries { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -142,8 +149,8 @@ public class TagQueries { return ret; } - public static ArrayList getUserTagsAll(Connection sql, int uid) { - ArrayList tags = new ArrayList(); + public static List getUserTagsAll(Connection sql, int uid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -159,15 +166,15 @@ public class TagQueries { tags.add(t); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return tags; } - public static ArrayList getUserBLTags(Connection sql, int uid) { - ArrayList tags = new ArrayList(); + public static List getUserBLTags(Connection sql, int uid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -180,7 +187,7 @@ public class TagQueries { tags.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 767a68d2..98573bfb 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -25,6 +25,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.List; import java.util.Random; import java.util.UUID; import java.util.logging.Level; @@ -52,7 +53,7 @@ public class UserQueries { stmt.setString(2, hash); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -76,7 +77,7 @@ public class UserQueries { uid = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -103,7 +104,7 @@ public class UserQueries { user.Banned = rs.getBoolean(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -126,7 +127,7 @@ public class UserQueries { user.Banned = rs.getBoolean(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -149,15 +150,15 @@ public class UserQueries { user.JID = jid; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return user; } - public static ArrayList getUsersByName(Connection sql, ArrayList unames) { - ArrayList users = new ArrayList(); + public static List getUsersByName(Connection sql, List unames) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -172,15 +173,15 @@ public class UserQueries { users.add(user); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return users; } - public static ArrayList getUsersByID(Connection sql, ArrayList uids) { - ArrayList users = new ArrayList(); + public static List getUsersByID(Connection sql, List uids) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -195,7 +196,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -203,7 +204,7 @@ public class UserQueries { return users; } - public static boolean fillUsersByID(Connection sql, ArrayList users) { + public static boolean fillUsersByID(Connection sql, List users) { boolean ret = false; String uids = ""; @@ -231,7 +232,7 @@ public class UserQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -239,8 +240,8 @@ public class UserQueries { return ret; } - public static ArrayList getUsersByJID(Connection sql, ArrayList jids) { - ArrayList users = new ArrayList(); + public static List getUsersByJID(Connection sql, List jids) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -256,7 +257,7 @@ public class UserQueries { users.add(user); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -296,7 +297,7 @@ public class UserQueries { user.AuthHash = hash; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -315,7 +316,7 @@ public class UserQueries { stmt.setString(2, hash); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -353,7 +354,7 @@ public class UserQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -373,7 +374,7 @@ public class UserQueries { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -388,7 +389,7 @@ public class UserQueries { stmt.setInt(2, uid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -407,7 +408,7 @@ public class UserQueries { ret = rs.getInt(1) < 3600; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -428,7 +429,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -449,7 +450,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -472,15 +473,15 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList checkBL(Connection sql, int visitor, ArrayList uids) { - ArrayList ret = new ArrayList(); + public static List checkBL(Connection sql, int visitor, List uids) { + List ret = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -493,7 +494,7 @@ public class UserQueries { ret.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -515,19 +516,19 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getUserRead(Connection sql, int uid) { + public static List getUserRead(Connection sql, int uid) { return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid); } - public static ArrayList getUserReadLeastPopular(Connection sql, int uid, int cnt) { - ArrayList users = new ArrayList(cnt); + public static List getUserReadLeastPopular(Connection sql, int uid, int cnt) { + List users = new ArrayList<>(cnt); PreparedStatement stmt = null; ResultSet rs = null; @@ -544,7 +545,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -552,12 +553,12 @@ public class UserQueries { return users; } - public static ArrayList getUserReaders(Connection sql, int uid) { + public static List getUserReaders(Connection sql, int uid) { return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid); } - public static ArrayList getUserBLUsers(Connection sql, int uid) { - ArrayList users = new ArrayList(); + public static List getUserBLUsers(Connection sql, int uid) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -573,7 +574,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -603,7 +604,7 @@ public class UserQueries { return stmt2.execute(); } } catch (SQLException e) { - logger.throwing(UserQueries.class.getName(), "linkTwitterAccount", e); + logger.log(Level.SEVERE, "sql exception", e); } return false; } @@ -642,7 +643,7 @@ public class UserQueries { preparedStatement.setString(3, JID); return preparedStatement.executeUpdate() >= 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql error", e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, preparedStatement); } diff --git a/src/main/java/com/juick/server/Utils.java b/src/main/java/com/juick/server/Utils.java index f97797d7..1968150e 100644 --- a/src/main/java/com/juick/server/Utils.java +++ b/src/main/java/com/juick/server/Utils.java @@ -21,7 +21,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; -import java.util.ArrayList; +import java.util.List; /** * @@ -29,7 +29,7 @@ import java.util.ArrayList; */ public class Utils { - public static String convertArrayInt2String(ArrayList mids) { + public static String convertArrayInt2String(List mids) { String q = ""; for (int i = 0; i < mids.size(); i++) { if (i > 0) { @@ -40,7 +40,7 @@ public class Utils { return q; } - public static String convertArrayString2String(ArrayList unames) { + public static String convertArrayString2String(List unames) { String q = ""; for (int i = 0; i < unames.size(); i++) { if (i > 0) { -- cgit v1.2.3 From 9bd8a41c0db5a1027a184facfca8d0152945c078 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 12:50:52 +0300 Subject: spring-jdbc WIP --- .../java/com/juick/server/MessagesQueries.java | 151 +++++++-------- src/main/java/com/juick/server/PMQueries.java | 204 ++++++--------------- src/main/java/com/juick/server/TagQueries.java | 63 +++---- src/main/java/com/juick/server/UserQueries.java | 129 +++++-------- 4 files changed, 191 insertions(+), 356 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 4d5683bd..a8605d3a 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -17,6 +17,9 @@ */ package com.juick.server; +import com.juick.Message; +import org.springframework.jdbc.core.JdbcTemplate; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; @@ -28,6 +31,7 @@ import java.util.Collections; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.core.RowMapper; /** * @@ -553,50 +557,32 @@ public class MessagesQueries { return mids; } - public static List getMyFeed(Connection sql, int uid, int before) { + public static List getMyFeed(JdbcTemplate sql, int uid, int before) { List mids = new ArrayList<>(40); - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, before); - stmt.setInt(3, uid); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, 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); + if (before > 0) { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) " + + "WHERE message_id=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, new Object[]{uid, before, uid}); + } else { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? " + + "AND subscr_users.user_id=messages.user_id) " + + "WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, new Object[]{uid, uid}); } - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id 0) { + mids.addAll(sql.queryForList("SELECT message_id FROM messages " + + "WHERE user_id=? AND message_id getMessages(Connection sql, List mids) { + public static List getMessages(JdbcTemplate sql, List mids) { List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT STRAIGHT_JOIN messages.message_id,messages.user_id,users.nick,messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),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 = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - - msg.MID = rs.getInt(1); - msg.User.UID = rs.getInt(2); - msg.User.UName = 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.Text = rs.getString(7); - msg.TimeAgo = rs.getInt(8); - msg.TimestampString = rs.getString(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); - - msgs.add(msg); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + + 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())," + + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),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.User = new com.juick.User(); + + msg.MID = rs.getInt(1); + msg.User.UID = rs.getInt(2); + msg.User.UName = 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.Text = rs.getString(7); + msg.TimeAgo = rs.getInt(8); + msg.TimestampString = rs.getString(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; + }); } public static List getReplies(Connection sql, int mid) { diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index c4b4fa2d..a2d4545c 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -27,6 +27,8 @@ import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; /** * @@ -34,54 +36,24 @@ import java.util.logging.Logger; */ public class PMQueries { - private static final Logger logger = Logger.getLogger(PMQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - stmt.setString(3, body); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { + boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", + new Object[] {uid_from, uid_to, body}) > 0; if (ret) { - PreparedStatement stmt2 = null; - try { - stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1"); - stmt2.setInt(1, uid_from); - stmt2.setInt(2, uid_to); - stmt2.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt2); - } + sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) " + + "VALUES (?,?,NOW(),1) " + + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1", + new Object[] {uid_from, uid_to}); } - return ret; } - public static boolean addPMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; + public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", + new Object[] {uid, jid}) > 0; } public static boolean removePMinRoster(Connection sql, int uid, String jid) { @@ -93,32 +65,18 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } return ret; } - public static boolean havePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = true; - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; + public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { + List res = sql.queryForList("SELECT 1 FROM pm_inroster " + + "WHERE user_id=? AND jid=?", Integer.class, + new Object[] {uid, jid}); + return res.size() > 0; } public static String getLastView(Connection sql, int uid_from, int uid_to) { @@ -135,61 +93,42 @@ public class PMQueries { ret = rs.getString(1); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static List getPMLastConversationsUsers(Connection sql, int uid, int cnt) { - List users = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.MessagesCount = rs.getInt(2); - users.add(u); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { + List users = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? AND unread>0 " + + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.UID = rs.getInt(1); + u.MessagesCount = rs.getInt(2); + return u; + }, new Object[]{uid}); if (users.size() < cnt) { - try { - stmt = sql.prepareStatement("SELECT user_id,user_id_to FROM pm_streams WHERE (user_id=? OR user_id_to=?) AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) ORDER BY lastmessage DESC LIMIT ?"); - stmt.setInt(1, uid); - stmt.setInt(2, uid); - stmt.setInt(3, cnt * 2); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - int uuid = rs.getInt(1) + rs.getInt(2) - uid; - if (!haveUserInArray(users, uuid)) { - com.juick.User u = new com.juick.User(); + List addUsers = sql.query("SELECT user_id,user_id_to " + + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " + + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " + + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { + User u = new com.juick.User(); + int uuid = rs.getInt(1) + rs.getInt(2) - uid; u.UID = uuid; - users.add(u); - if (users.size() >= cnt) { - break; - } + return u; + }, new Object[]{uid, uid, cnt * 2}); + for (User addUser : addUsers) { + if (!haveUserInArray(users, addUser.UID)) { + users.add(addUser); + if (users.size() >= cnt) { + break; } } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); } } - if (!users.isEmpty()) { UserQueries.fillUsersByID(sql, users); } @@ -206,45 +145,22 @@ public class PMQueries { return false; } - public static List getPMMessages(Connection sql, int uid, int uid_to) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id,txt,ts FROM pm WHERE (user_id=? AND user_id_to=?) OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, uid_to); - stmt.setInt(3, uid); - stmt.setInt(4, uid_to); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - int uuid = rs.getInt(1); - msg.User = new com.juick.User(); - msg.User.UID = uuid; - msg.Text = rs.getString(2); - msg.TimestampString = rs.getString(3); - msgs.add(0, msg); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - PreparedStatement stmt2 = null; - try { - stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?"); - stmt2.setInt(1, uid); - stmt2.setInt(2, uid_to); - stmt2.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt2); - } + public static List getPMMessages(JdbcTemplate sql, int uid, int uid_to) { + List msgs = sql.query("SELECT user_id,txt,ts FROM pm " + + "WHERE (user_id=? AND user_id_to=?) " + + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20", + (rs, rowNum) -> { + com.juick.Message msg = new com.juick.Message(); + int uuid = rs.getInt(1); + msg.User = new com.juick.User(); + msg.User.UID = uuid; + msg.Text = rs.getString(2); + msg.TimestampString = rs.getString(3); + return msg; + }, new Object[]{uid, uid_to, uid, uid_to}); + + sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 " + + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to}); return msgs; } @@ -270,7 +186,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -299,7 +215,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 1c5e27d1..076019ff 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -18,6 +18,8 @@ package com.juick.server; import com.juick.Tag; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; import java.sql.Connection; import java.sql.PreparedStatement; @@ -28,6 +30,8 @@ import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; /** * @@ -61,26 +65,15 @@ public class TagQueries { return ret; } - public static com.juick.Tag getTag(Connection sql, String tag, boolean autoCreate) { - com.juick.Tag ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tag_id,synonym_id,name FROM tags WHERE name=?"); - stmt.setString(1, tag); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = new com.juick.Tag(); - ret.TID = rs.getInt(1); - ret.SynonymID = rs.getInt(2); - ret.Name = rs.getString(3); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { + Tag ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?", + new Object[]{tag}, (rs, rowNum) -> { + Tag ret1 = new Tag(); + ret1.TID = rs.getInt(1); + ret1.SynonymID = rs.getInt(2); + ret1.Name = rs.getString(3); + return ret1; + }); if (ret == null && autoCreate) { ret = new com.juick.Tag(); @@ -91,7 +84,7 @@ public class TagQueries { return ret; } - public static List getTags(Connection sql, String[] tags, boolean autoCreate) { + public static List getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) { List ret = new ArrayList<>(); for (String tag : tags) { @@ -127,26 +120,16 @@ public class TagQueries { return ret; } - public static int createTag(Connection sql, String name) { - int ret = 0; + public static int createTag(JdbcTemplate sql, String name) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO tags(name) VALUES (?)", + Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, name); + return stmt; + }, holder); - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("INSERT INTO tags(name) VALUES (?)", Statement.RETURN_GENERATED_KEYS); - stmt.setString(1, name); - stmt.executeUpdate(); - rs = stmt.getGeneratedKeys(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return ret; + return holder.getKey().intValue(); } public static List getUserTagsAll(Connection sql, int uid) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index bf56e974..27ae2870 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -19,7 +19,6 @@ package com.juick.server; import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; -import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; @@ -29,10 +28,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; -import java.util.ArrayList; -import java.util.List; -import java.util.Random; -import java.util.UUID; +import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; @@ -44,7 +40,7 @@ public class UserQueries { static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; - private static Logger logger = Logger.getLogger(UserQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(UserQueries.class.getName()); static class UserMapper implements RowMapper { @Override @@ -115,7 +111,7 @@ public class UserQueries { uids.toArray()); } - public static boolean fillUsersByID(Connection sql, List users) { + public static boolean fillUsersByID(JdbcTemplate sql, List users) { boolean ret = false; String uids = ""; @@ -127,54 +123,27 @@ public class UserQueries { uids += users.get(i).UID; } - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + uids + ")"); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - int uid = rs.getInt(1); - for (int i = 0; i < usersSize; i++) { - if (users.get(i).UID == uid) { - users.get(i).UName = rs.getString(2); - ret = true; - } - } - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", + (rs, num) -> { + User u = users.get(num); + u.UName = rs.getString(2); + return u; + }); - return ret; + return true; } - public static List getUsersByJID(Connection sql, List jids) { - List users = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("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 = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.User user = new com.juick.User(); - user.UID = rs.getInt(1); - user.UName = rs.getString(2); - user.JID = rs.getString(3); - users.add(user); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return users; + public static List getUsersByJID(JdbcTemplate sql, List 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.UID = rs.getInt(1); + user.UName = rs.getString(2); + user.JID = rs.getString(3); + return user; + }); } public static String getJIDbyUID(JdbcTemplate sql, int uid) { @@ -246,7 +215,7 @@ public class UserQueries { } } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -266,7 +235,7 @@ public class UserQueries { ret = rs.getInt(1); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -281,7 +250,7 @@ public class UserQueries { stmt.setInt(2, uid); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -300,7 +269,7 @@ public class UserQueries { ret = rs.getInt(1) < 3600; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -321,7 +290,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -342,34 +311,18 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static boolean isInBLAny(Connection sql, int uid, int uid2) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE (user_id=? AND bl_user_id=?) OR (user_id=? AND bl_user_id=?)"); - stmt.setInt(1, uid); - stmt.setInt(2, uid2); - stmt.setInt(3, uid2); - stmt.setInt(4, uid); - 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 isInBLAny(JdbcTemplate sql, int uid, int uid2) { + List res = sql.queryForList("SELECT 1 FROM bl_users " + + "WHERE (user_id=? AND bl_user_id=?) " + + "OR (user_id=? AND bl_user_id=?)", Integer.class, new Object[] {uid, uid2, uid2, uid}); + return res.get(0) == 1; } public static List checkBL(Connection sql, int visitor, List uids) { @@ -386,7 +339,7 @@ public class UserQueries { ret.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -408,15 +361,15 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static List getUserRead(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid); + public static List getUserRead(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); } public static List getUserReadLeastPopular(Connection sql, int uid, int cnt) { @@ -437,7 +390,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -445,8 +398,8 @@ public class UserQueries { return users; } - public static List getUserReaders(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid); + public static List getUserReaders(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); } public static List getUserBLUsers(Connection sql, int uid) { @@ -466,7 +419,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -496,7 +449,7 @@ public class UserQueries { return stmt2.execute(); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } return false; } -- cgit v1.2.3 From 1ef890d21db2ed9aeefc37b71ff82d0f59df8516 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 13:14:11 +0300 Subject: housekeeping --- .../java/com/juick/server/MessagesQueries.java | 74 +++++++++++----------- src/main/java/com/juick/server/PMQueries.java | 1 - src/main/java/com/juick/server/TagQueries.java | 11 ++-- 3 files changed, 41 insertions(+), 45 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index a8605d3a..cff8caff 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -17,7 +17,6 @@ */ package com.juick.server; -import com.juick.Message; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.Connection; @@ -31,7 +30,6 @@ import java.util.Collections; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; -import org.springframework.jdbc.core.RowMapper; /** * @@ -39,7 +37,7 @@ import org.springframework.jdbc.core.RowMapper; */ public class MessagesQueries { - private static final Logger logger = Logger.getLogger(MessagesQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { int mid = 0; @@ -60,7 +58,7 @@ public class MessagesQueries { mid = rs.getInt(1); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -82,7 +80,7 @@ public class MessagesQueries { stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -98,7 +96,7 @@ public class MessagesQueries { stmt.setString(3, txt); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -127,7 +125,7 @@ public class MessagesQueries { ridnew = 0; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -175,7 +173,7 @@ public class MessagesQueries { privacy = rs.getInt(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -198,7 +196,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -219,7 +217,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -264,7 +262,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -285,7 +283,7 @@ public class MessagesQueries { msg.Text = rs.getString(3); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -316,7 +314,7 @@ public class MessagesQueries { msg.Text = rs.getString(6); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -339,7 +337,7 @@ public class MessagesQueries { user.UName = rs.getString(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -365,7 +363,7 @@ public class MessagesQueries { tags.add(t); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -387,7 +385,7 @@ public class MessagesQueries { tids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -409,7 +407,7 @@ public class MessagesQueries { users.add(rs.getString(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -457,7 +455,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -492,7 +490,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -521,7 +519,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -550,7 +548,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -614,7 +612,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -641,7 +639,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -669,7 +667,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -696,7 +694,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -723,7 +721,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -751,7 +749,7 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -766,7 +764,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -795,7 +793,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -824,7 +822,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -851,7 +849,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -878,7 +876,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -907,7 +905,7 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -922,7 +920,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -1001,7 +999,7 @@ public class MessagesQueries { replies.add(msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -1027,7 +1025,7 @@ public class MessagesQueries { } ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1038,7 +1036,7 @@ public class MessagesQueries { stmt.setInt(1, mid); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1056,7 +1054,7 @@ public class MessagesQueries { stmt.setInt(1, mid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index a2d4545c..d49bb527 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -28,7 +28,6 @@ import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; import org.springframework.jdbc.core.JdbcTemplate; -import org.springframework.jdbc.core.RowMapper; /** * diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 076019ff..5bc87e28 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -19,7 +19,6 @@ package com.juick.server; import com.juick.Tag; import org.springframework.jdbc.core.JdbcTemplate; -import org.springframework.jdbc.core.RowMapper; import java.sql.Connection; import java.sql.PreparedStatement; @@ -39,7 +38,7 @@ import org.springframework.jdbc.support.KeyHolder; */ public class TagQueries { - private static final Logger logger = Logger.getLogger(TagQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName()); public static com.juick.Tag getTag(Connection sql, int tid) { com.juick.Tag ret = null; @@ -57,7 +56,7 @@ public class TagQueries { ret.Name = rs.getString(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -112,7 +111,7 @@ public class TagQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -149,7 +148,7 @@ public class TagQueries { tags.add(t); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -170,7 +169,7 @@ public class TagQueries { tags.add(rs.getString(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } -- cgit v1.2.3 From 09fd6b8ee40802eceff9746d3c23aa8446ccbd64 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 22:56:33 +0300 Subject: update core --- .../java/com/juick/server/MessagesQueries.java | 50 +++++++++++----------- src/main/java/com/juick/server/PMQueries.java | 30 ++++++------- src/main/java/com/juick/server/ShowQueries.java | 2 +- src/main/java/com/juick/server/UserQueries.java | 30 ++++++------- 4 files changed, 56 insertions(+), 56 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 4086cb13..9cbf4240 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -244,10 +244,10 @@ public class MessagesQueries { + "WHERE messages.message_id=?", new Object[]{mid}, (rs, rowNum) -> { Message msg = new Message(); - msg.MID = mid; - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); + 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.TimestampString = rs.getString(4); msg.ReadOnly = rs.getBoolean(5); @@ -267,7 +267,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); msg.parseTags(rs.getString(14)); msg.RepliesBy = rs.getString(15); - msg.Text = rs.getString(16); + msg.setText(rs.getString(16)); return msg; }); } @@ -280,15 +280,15 @@ public class MessagesQueries { + "WHERE replies.message_id=? AND replies.reply_id=?", new Object[]{mid, rid}, (rs, num) -> { Message msg = new Message(); - msg.MID = mid; - msg.RID = rid; - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); + 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.TimestampString = rs.getString(4); msg.AttachmentType = rs.getString(5); - msg.Text = rs.getString(6); + msg.setText(rs.getString(6)); return msg; }); } @@ -299,8 +299,8 @@ public class MessagesQueries { + "ON messages.user_id=users.id WHERE messages.message_id=?", new Object[]{mid}, (rs, num) -> { User res = new com.juick.User(); - res.UID = rs.getInt(1); - res.UName = rs.getString(2); + res.setUID(rs.getInt(1)); + res.setUName(rs.getString(2)); return res; }); } @@ -893,18 +893,18 @@ public class MessagesQueries { + "IN (" + Utils.convertArrayInt2String(mids) + ") " + "ORDER BY messages.message_id DESC", (rs, rowNum) -> { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); + msg.setUser(new User()); - msg.MID = rs.getInt(1); - msg.User.UID = rs.getInt(2); - msg.User.UName = rs.getString(3); + 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.Text = rs.getString(7); + msg.setText(rs.getString(7)); msg.TimeAgo = rs.getInt(8); msg.TimestampString = rs.getString(9); msg.Replies = rs.getInt(10); @@ -932,14 +932,14 @@ public class MessagesQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.MID = mid; - msg.RID = rs.getInt(1); + msg.setMID(mid); + msg.setRID(rs.getInt(1)); msg.ReplyTo = rs.getInt(2); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(3); - msg.User.UName = rs.getString(4); - msg.User.Banned = rs.getBoolean(5); - msg.Text = rs.getString(6); + 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.TimestampString = rs.getString(8); msg.AttachmentType = rs.getString(9); diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index d49bb527..7984844a 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -104,7 +104,7 @@ public class PMQueries { + "WHERE user_id_to=? AND unread>0 " + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); + u.setUID(rs.getInt(1)); u.MessagesCount = rs.getInt(2); return u; }, new Object[]{uid}); @@ -116,11 +116,11 @@ public class PMQueries { + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { User u = new com.juick.User(); int uuid = rs.getInt(1) + rs.getInt(2) - uid; - u.UID = uuid; + u.setUID(uuid); return u; }, new Object[]{uid, uid, cnt * 2}); for (User addUser : addUsers) { - if (!haveUserInArray(users, addUser.UID)) { + if (!haveUserInArray(users, addUser.getUID())) { users.add(addUser); if (users.size() >= cnt) { break; @@ -137,7 +137,7 @@ public class PMQueries { public static boolean haveUserInArray(List arr, int uid) { for (User user : arr) { - if (user.UID == uid) { + if (user.getUID() == uid) { return true; } } @@ -151,9 +151,9 @@ public class PMQueries { (rs, rowNum) -> { com.juick.Message msg = new com.juick.Message(); int uuid = rs.getInt(1); - msg.User = new com.juick.User(); - msg.User.UID = uuid; - msg.Text = rs.getString(2); + msg.setUser(new User()); + msg.getUser().setUID(uuid); + msg.setText(rs.getString(2)); msg.TimestampString = rs.getString(3); return msg; }, new Object[]{uid, uid_to, uid, uid_to}); @@ -176,10 +176,10 @@ public class PMQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); - msg.Text = rs.getString(3); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.TimestampString = rs.getString(5); msgs.add(0, msg); @@ -205,10 +205,10 @@ public class PMQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); - msg.Text = rs.getString(3); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.TimestampString = rs.getString(5); msgs.add(0, msg); diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java index 05a58e9a..3ddff412 100644 --- a/src/main/java/com/juick/server/ShowQueries.java +++ b/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.UID, forUser.UID, forUser.UID, forUser.UID}); + String.class, new Object[] {forUser.getUID(), forUser.getUID(), forUser.getUID(), forUser.getUID()}); } public static List getTopUsers(JdbcTemplate sql) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 27ae2870..2cd557c6 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -46,8 +46,8 @@ public class UserQueries { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); - user.UID = rs.getInt("id"); - user.UName = rs.getString("nick"); + user.setUID(rs.getInt("id")); + user.setUName(rs.getString("nick")); user.Banned = rs.getBoolean("banned"); return user; } @@ -120,13 +120,13 @@ public class UserQueries { if (i > 0) { uids += ","; } - uids += users.get(i).UID; + uids += users.get(i).getUID(); } sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", (rs, num) -> { User u = users.get(num); - u.UName = rs.getString(2); + u.setUName(rs.getString(2)); return u; }); @@ -139,9 +139,9 @@ public class UserQueries { + "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")", (rs, rowNum) -> { com.juick.User user = new com.juick.User(); - user.UID = rs.getInt(1); - user.UName = rs.getString(2); - user.JID = rs.getString(3); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.setJID(rs.getString(3)); return user; }); } @@ -166,7 +166,7 @@ public class UserQueries { User user = sql.queryForObject("SELECT logins.user_id,users.nick, users.banned FROM logins " + "INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?", new UserMapper(), hash); - user.AuthHash = hash; + user.setAuthHash(hash); return user; } @@ -385,8 +385,8 @@ public class UserQueries { rs.beforeFirst(); while (rs.next()) { com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.UName = rs.getString(2); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); users.add(u); } } catch (SQLException e) { @@ -414,8 +414,8 @@ public class UserQueries { rs.beforeFirst(); while (rs.next()) { com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.UName = rs.getString(2); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); users.add(u); } } catch (SQLException e) { @@ -434,7 +434,7 @@ public class UserQueries { sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + "VALUES (?,?,?,?)" + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?"); - stmt.setInt(1, user.UID); + stmt.setInt(1, user.getUID()); stmt.setString(2, accessToken); stmt.setString(3, accessTokenSecret); stmt.setString(4, screenName); @@ -445,7 +445,7 @@ public class UserQueries { PreparedStatement stmt2 = sql.prepareStatement("INSERT INTO subscr_users(user_id,suser_id,jid) " + "VALUES (?,1741,'juick\\@twitter.juick.com')"); - stmt2.setInt(1, user.UID); + stmt2.setInt(1, user.getUID()); return stmt2.execute(); } } catch (SQLException e) { @@ -483,7 +483,7 @@ public class UserQueries { "UPDATE jids SET active=? WHERE user_id=? AND jid=?"); int newStatus = jidStatus == ActiveStatus.Active ? 1 : 0; preparedStatement.setInt(1, newStatus); - preparedStatement.setInt(2, user.UID); + preparedStatement.setInt(2, user.getUID()); preparedStatement.setString(3, JID); return preparedStatement; -- cgit v1.2.3 From 714a267ae35354e86512288e0ac91990937f95ab Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 16 Jan 2016 18:23:21 +0300 Subject: almost working api project --- .../java/com/juick/server/MessagesQueries.java | 187 +++++++++------------ src/main/java/com/juick/server/PMQueries.java | 10 +- src/main/java/com/juick/server/TagQueries.java | 31 +--- src/main/java/com/juick/server/UserQueries.java | 26 +-- 4 files changed, 103 insertions(+), 151 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 9cbf4240..fd664909 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -19,7 +19,10 @@ package com.juick.server; import com.juick.Message; import com.juick.User; +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; @@ -41,31 +44,25 @@ public class MessagesQueries { private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); - public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { - int mid = 0; - - PreparedStatement stmt = null; - ResultSet rs; - try { - stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); + public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List tags) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.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.VARCHAR); } - stmt.executeUpdate(); - rs = stmt.getGeneratedKeys(); - if (rs.first()) { - mid = rs.getInt(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }, holder); + + int mid = holder.getKey().intValue(); + if (mid > 0) { + String tagsNames = ""; String tagsIDs = ""; @@ -77,42 +74,33 @@ public class MessagesQueries { tagsNames += tags.get(i).Name; tagsIDs += "(" + mid + "," + tags.get(i).TID + ")"; } - - try { - stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); + if (tags.size() > 0) { + sql.execute("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); } - - try { - stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)"); + final String finalTagsNames = tagsNames; + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) " + + "VALUES (?,?,?)", Statement.NO_GENERATED_KEYS); stmt.setInt(1, mid); - if (tagsNames.isEmpty()) { + if (finalTagsNames.isEmpty()) { stmt.setNull(2, Types.VARCHAR); } else { - stmt.setString(2, tagsNames); + stmt.setString(2, finalTagsNames); } stmt.setString(3, txt); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }); } return mid; } - public static int createReply(Connection sql, int mid, int rid, int uid, String txt, String attachment) { + public static int createReply(JdbcTemplate sql, int mid, int rid, int uid, String txt, String attachment) { int ridnew = getReplyIDIncrement(sql, mid); - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id,replyto,attach,txt) VALUES (?,?,?,?,?,?)"); + sql.update( con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id," + + "replyto,attach,txt) VALUES (?,?,?,?,?,?)", Statement.NO_GENERATED_KEYS); stmt.setInt(1, mid); stmt.setInt(2, ridnew); stmt.setInt(3, uid); @@ -123,32 +111,18 @@ public class MessagesQueries { stmt.setNull(5, Types.VARCHAR); } stmt.setString(6, txt); - if (stmt.executeUpdate() == 0) { - ridnew = 0; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }); if (ridnew > 0) { - SQLHelpers.executeInt(sql, "UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); + sql.update("UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); } return ridnew; } - public static int getReplyIDIncrement(Connection sql, int mid) { - int rid = 0; - - SQLHelpers.execute(sql, "LOCK TABLES messages WRITE"); - if (SQLHelpers.executeInt(sql, "UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid) == 1) { - rid = SQLHelpers.getInt(sql, "SELECT maxreplyid FROM messages WHERE message_id=?", mid, 0); - } - SQLHelpers.execute(sql, "UNLOCK TABLES"); - - return rid; + public static int getReplyIDIncrement(JdbcTemplate sql, int mid) { + return sql.update("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid); } public static boolean recommendMessage(Connection sql, int mid, int vuid) { @@ -230,51 +204,56 @@ public class MessagesQueries { return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4); } + public static com.juick.Message getMessage(JdbcTemplate sql, int mid) { - return sql.queryForObject("SELECT messages.user_id,users.nick," - + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," - + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s')," - + "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 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 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.TimestampString = rs.getString(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; - }); + try { + return sql.queryForObject("SELECT messages.user_id,users.nick," + + "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 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 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.getDate(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; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } } public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) { return sql.queryForObject("SELECT replies.user_id,users.nick," - + "replies.replyto,DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s')," + + "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=?", @@ -286,7 +265,7 @@ public class MessagesQueries { msg.getUser().setUID(rs.getInt(1)); msg.getUser().setUName(rs.getString(2)); msg.ReplyTo = rs.getInt(3); - msg.TimestampString = rs.getString(4); + msg.setDate(rs.getDate(4)); msg.AttachmentType = rs.getString(5); msg.setText(rs.getString(6)); return msg; @@ -883,7 +862,7 @@ public class MessagesQueries { 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())," - + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies," + + "messages.ts,messages.replies," + "messages_txt.repliesby,messages.attach,messages.lat," + "messages.lon,messages.likes " + "FROM (messages INNER JOIN messages_txt " @@ -906,7 +885,7 @@ public class MessagesQueries { msg.FriendsOnly = msg.Privacy < 0; msg.setText(rs.getString(7)); msg.TimeAgo = rs.getInt(8); - msg.TimestampString = rs.getString(9); + msg.setDate(rs.getDate(9)); msg.Replies = rs.getInt(10); msg.RepliesBy = rs.getString(11); msg.AttachmentType = rs.getString(12); @@ -926,7 +905,7 @@ public class MessagesQueries { 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()),DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC"); + 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(); @@ -941,7 +920,7 @@ public class MessagesQueries { msg.getUser().Banned = rs.getBoolean(5); msg.setText(rs.getString(6)); msg.TimeAgo = rs.getInt(7); - msg.TimestampString = rs.getString(8); + msg.setDate(rs.getDate(8)); msg.AttachmentType = rs.getString(9); replies.add(msg); diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 7984844a..345f641f 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -154,7 +154,7 @@ public class PMQueries { msg.setUser(new User()); msg.getUser().setUID(uuid); msg.setText(rs.getString(2)); - msg.TimestampString = rs.getString(3); + msg.setDate(rs.getDate(3)); return msg; }, new Object[]{uid, uid_to, uid, uid_to}); @@ -170,7 +170,7 @@ public class PMQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); stmt.setInt(1, uid); rs = stmt.executeQuery(); rs.beforeFirst(); @@ -181,7 +181,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.TimestampString = rs.getString(5); + msg.setDate(rs.getDate(5)); msgs.add(0, msg); } } catch (SQLException e) { @@ -199,7 +199,7 @@ public class PMQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); stmt.setInt(1, uid); rs = stmt.executeQuery(); rs.beforeFirst(); @@ -210,7 +210,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.TimestampString = rs.getString(5); + msg.setDate(rs.getDate(5)); msgs.add(0, msg); } } catch (SQLException e) { diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 5bc87e28..22839ad0 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -40,28 +40,15 @@ public class TagQueries { private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName()); - public static com.juick.Tag getTag(Connection sql, int tid) { - com.juick.Tag ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?"); - stmt.setInt(1, tid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = new com.juick.Tag(); - ret.TID = tid; - ret.SynonymID = rs.getInt(1); - ret.Name = rs.getString(2); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return ret; + public static com.juick.Tag getTag(JdbcTemplate sql, int tid) { + return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", + new Object[]{tid}, (rs, num) -> { + Tag ret = new Tag(); + ret.TID = tid; + ret.SynonymID = rs.getInt(1); + ret.Name = rs.getString(2); + return ret; + }); } public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 2cd557c6..5588d8e4 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -199,27 +199,13 @@ public class UserQueries { return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+"); } - public static int checkPassword(Connection sql, String username, String password) { - int uid = 0; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?"); - stmt.setString(1, username); - rs = stmt.executeQuery(); - if (rs.first()) { - if (password.equals(rs.getString(2))) { - uid = rs.getInt(1); - } else { - uid = -1; - } - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static int checkPassword(JdbcTemplate sql, String username, String password) { + String realPassword = sql.queryForObject("SELECT passw FROM users WHERE nick=?", String.class, username); + if (realPassword.equals(password)) { + return UserQueries.getUserByName(sql, username).getUID(); + } else { + return -1; } - return uid; } public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) { -- cgit v1.2.3 From 279765857724cf35addc0e3f5e7180a8724d23b9 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 16 Jan 2016 19:04:26 +0300 Subject: fix time of messages --- src/main/java/com/juick/server/MessagesQueries.java | 8 ++++---- src/main/java/com/juick/server/PMQueries.java | 6 +++--- 2 files changed, 7 insertions(+), 7 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index fd664909..1d8eceb3 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -225,7 +225,7 @@ public class MessagesQueries { msg.getUser().setUID(rs.getInt(1)); msg.getUser().setUName(rs.getString(2)); msg.TimeAgo = rs.getInt(3); - msg.setDate(rs.getDate(4)); + msg.setDate(rs.getTimestamp(4)); msg.ReadOnly = rs.getBoolean(5); msg.Privacy = rs.getInt(6); msg.FriendsOnly = msg.Privacy < 0; @@ -265,7 +265,7 @@ public class MessagesQueries { msg.getUser().setUID(rs.getInt(1)); msg.getUser().setUName(rs.getString(2)); msg.ReplyTo = rs.getInt(3); - msg.setDate(rs.getDate(4)); + msg.setDate(rs.getTimestamp(4)); msg.AttachmentType = rs.getString(5); msg.setText(rs.getString(6)); return msg; @@ -885,7 +885,7 @@ public class MessagesQueries { msg.FriendsOnly = msg.Privacy < 0; msg.setText(rs.getString(7)); msg.TimeAgo = rs.getInt(8); - msg.setDate(rs.getDate(9)); + msg.setDate(rs.getTimestamp(9)); msg.Replies = rs.getInt(10); msg.RepliesBy = rs.getString(11); msg.AttachmentType = rs.getString(12); @@ -920,7 +920,7 @@ public class MessagesQueries { msg.getUser().Banned = rs.getBoolean(5); msg.setText(rs.getString(6)); msg.TimeAgo = rs.getInt(7); - msg.setDate(rs.getDate(8)); + msg.setDate(rs.getTimestamp(8)); msg.AttachmentType = rs.getString(9); replies.add(msg); diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 345f641f..9ff464ba 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -154,7 +154,7 @@ public class PMQueries { msg.setUser(new User()); msg.getUser().setUID(uuid); msg.setText(rs.getString(2)); - msg.setDate(rs.getDate(3)); + msg.setDate(rs.getTimestamp(3)); return msg; }, new Object[]{uid, uid_to, uid, uid_to}); @@ -181,7 +181,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getDate(5)); + msg.setDate(rs.getTimestamp(5)); msgs.add(0, msg); } } catch (SQLException e) { @@ -210,7 +210,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getDate(5)); + msg.setDate(rs.getTimestamp(5)); msgs.add(0, msg); } } catch (SQLException e) { -- cgit v1.2.3 From 69a435dfef1bc656dc1d9d3da0f049f86d18fdaf Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 11 May 2016 03:37:04 +0300 Subject: fix fill users --- src/main/java/com/juick/server/PMQueries.java | 12 ++++++------ src/main/java/com/juick/server/UserQueries.java | 3 ++- 2 files changed, 8 insertions(+), 7 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 9ff464ba..d1d826cc 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -39,20 +39,20 @@ public class PMQueries { public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", - new Object[] {uid_from, uid_to, body}) > 0; + uid_from, uid_to, body) > 0; if (ret) { sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) " + "VALUES (?,?,NOW(),1) " + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1", - new Object[] {uid_from, uid_to}); + uid_from, uid_to); } return ret; } public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) { return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", - new Object[] {uid, jid}) > 0; + uid, jid) > 0; } public static boolean removePMinRoster(Connection sql, int uid, String jid) { @@ -74,7 +74,7 @@ public class PMQueries { public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { List res = sql.queryForList("SELECT 1 FROM pm_inroster " + "WHERE user_id=? AND jid=?", Integer.class, - new Object[] {uid, jid}); + uid, jid); return res.size() > 0; } @@ -107,7 +107,7 @@ public class PMQueries { u.setUID(rs.getInt(1)); u.MessagesCount = rs.getInt(2); return u; - }, new Object[]{uid}); + }, uid); if (users.size() < cnt) { List addUsers = sql.query("SELECT user_id,user_id_to " @@ -118,7 +118,7 @@ public class PMQueries { int uuid = rs.getInt(1) + rs.getInt(2) - uid; u.setUID(uuid); return u; - }, new Object[]{uid, uid, cnt * 2}); + }, uid, uid, cnt * 2); for (User addUser : addUsers) { if (!haveUserInArray(users, addUser.getUID())) { users.add(addUser); diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index cfa52a16..184d26f5 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -127,7 +127,8 @@ public class UserQueries { sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", (rs, num) -> { - User u = users.get(num); + User u = new User(); + u.setUID(rs.getInt(1)); u.setUName(rs.getString(2)); return u; }); -- cgit v1.2.3 From 0a9cd317b878d6c2c777ec4eb19173a7d651abe5 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 11 May 2016 15:34:44 +0300 Subject: fix fill users, part 2 --- src/main/java/com/juick/server/PMQueries.java | 40 ++++++--------------------- 1 file changed, 8 insertions(+), 32 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index d1d826cc..6de30995 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -100,39 +100,15 @@ public class PMQueries { } public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { - List users = sql.query("SELECT user_id,unread FROM pm_streams " - + "WHERE user_id_to=? AND unread>0 " - + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " + + "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " + + "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { com.juick.User u = new com.juick.User(); u.setUID(rs.getInt(1)); - u.MessagesCount = rs.getInt(2); + u.setUName(rs.getString(2)); + u.MessagesCount = rs.getInt(3); return u; - }, uid); - - if (users.size() < cnt) { - List addUsers = sql.query("SELECT user_id,user_id_to " - + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " - + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " - + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { - User u = new com.juick.User(); - int uuid = rs.getInt(1) + rs.getInt(2) - uid; - u.setUID(uuid); - return u; - }, uid, uid, cnt * 2); - for (User addUser : addUsers) { - if (!haveUserInArray(users, addUser.getUID())) { - users.add(addUser); - if (users.size() >= cnt) { - break; - } - } - } - } - if (!users.isEmpty()) { - UserQueries.fillUsersByID(sql, users); - } - - return users; + }, uid, cnt); } public static boolean haveUserInArray(List arr, int uid) { @@ -156,10 +132,10 @@ public class PMQueries { msg.setText(rs.getString(2)); msg.setDate(rs.getTimestamp(3)); return msg; - }, new Object[]{uid, uid_to, uid, uid_to}); + }, uid, uid_to, uid, uid_to); sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 " - + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to}); + + "WHERE user_id_to=? AND user_id=?", uid, uid_to); return msgs; } -- cgit v1.2.3 From 9e740f48cca7332c67a165c6cf60e09bc549e686 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 27 Jun 2016 10:33:04 +0300 Subject: more spring-jdbc --- build.gradle | 3 +- src/main/java/com/juick/server/AdsQueries.java | 40 +++-- .../java/com/juick/server/CrosspostQueries.java | 74 ++++----- src/main/java/com/juick/server/PMQueries.java | 173 ++++++++------------- src/main/java/com/juick/server/TagQueries.java | 58 +++---- src/main/java/com/juick/server/UserQueries.java | 73 +++++---- 6 files changed, 169 insertions(+), 252 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/build.gradle b/build.gradle index b82c3d5c..9f5bc86d 100644 --- a/build.gradle +++ b/build.gradle @@ -5,5 +5,6 @@ repositories { } dependencies { - compile "org.springframework:spring-jdbc:4.2.4.RELEASE" + compile "org.springframework:spring-jdbc:4.3.0.RELEASE" + compile "org.apache.commons:commons-lang3:3.4" } \ No newline at end of file diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java index ad9b7149..06590817 100644 --- a/src/main/java/com/juick/server/AdsQueries.java +++ b/src/main/java/com/juick/server/AdsQueries.java @@ -17,11 +17,8 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -29,27 +26,26 @@ import java.util.logging.Logger; */ public class AdsQueries { - private static final Logger logger = Logger.getLogger(AdsQueries.class.getName()); - - public static int getAdMID(Connection sql, int uid) { + public static int getAdMID(JdbcTemplate sql, int uid) { if (uid > 0) { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages " + + "WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? " + + "AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", + Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } else { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", Integer.class); + } catch (EmptyResultDataAccessException e) { + return 0; + } } } - public static void logAdMID(Connection sql, int uid, int mid) { - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())"); - stmt.setInt(1, uid); - stmt.setInt(2, mid); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static void logAdMID(JdbcTemplate sql, int uid, int mid) { + sql.update("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())", uid, mid); } } diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java index b44b6b59..04898d0a 100644 --- a/src/main/java/com/juick/server/CrosspostQueries.java +++ b/src/main/java/com/juick/server/CrosspostQueries.java @@ -17,12 +17,11 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.apache.commons.lang3.tuple.Pair; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.Optional; /** * @@ -30,55 +29,34 @@ import java.util.logging.Logger; */ public class CrosspostQueries { - private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName()); - - public static String[] getTwitterTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getTwitterTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } - public static String getFacebookToken(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL AND crosspost=1", uid); + public static Optional getFacebookToken(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL " + + "AND crosspost=1", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } } - public static String[] getVKTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getVKTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 6de30995..772a1c28 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -18,16 +18,10 @@ package com.juick.server; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +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.List; -import java.util.logging.Level; -import java.util.logging.Logger; -import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -35,8 +29,6 @@ import org.springframework.jdbc.core.JdbcTemplate; */ public class PMQueries { - private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", uid_from, uid_to, body) > 0; @@ -55,20 +47,8 @@ public class PMQueries { uid, jid) > 0; } - public static boolean removePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; + public static boolean removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; } public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { @@ -78,37 +58,49 @@ public class PMQueries { return res.size() > 0; } - public static String getLastView(Connection sql, int uid_from, int uid_to) { - String ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { try { - stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - 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 sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; } - return ret; } public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { - return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " + - "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " + - "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - u.MessagesCount = rs.getInt(3); - return u; - }, uid, cnt); + List qusers = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? AND unread>0 " + + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + + if (qusers.size() < cnt) { + List addUsers = sql.query("SELECT user_id,user_id_to " + + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " + + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " + + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { + User u = new com.juick.User(); + int uuid = rs.getInt(1) + rs.getInt(2) - uid; + u.setUID(uuid); + return u; + }, uid, uid, cnt * 2); + for (User addUser : addUsers) { + if (!haveUserInArray(qusers, addUser.getUID())) { + qusers.add(addUser); + if (qusers.size() >= cnt) { + break; + } + } + } + } + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; } public static boolean haveUserInArray(List arr, int uid) { @@ -140,61 +132,34 @@ public class PMQueries { return msgs; } - public static List getLastPMInbox(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.setText(rs.getString(3)); - msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " + + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } - public static List getLastPMSent(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.setText(rs.getString(3)); - msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMSent(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," + + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + + "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index f3ab480e..d2159b5a 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -90,25 +90,12 @@ public class TagQueries { return ret; } - public static boolean getTagNoIndex(Connection sql, int tag_id) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) { try { - stmt = sql.prepareStatement("SELECT noindex FROM tags WHERE tag_id=?"); - stmt.setInt(1, tag_id); - 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 noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - - return ret; } public static int createTag(JdbcTemplate sql, String name) { @@ -123,28 +110,17 @@ public class TagQueries { return holder.getKey().intValue(); } - public static List getUserTagsAll(Connection sql, int uid) { - List tags = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tags.name,COUNT(messages.message_id) FROM (messages INNER JOIN messages_tags ON (messages.user_id=? AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Tag t = new com.juick.Tag(); - t.Name = rs.getString(1); - t.UsageCnt = rs.getInt(2); - tags.add(t); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return tags; + public static List getUserTagsAll(JdbcTemplate sql, int uid) { + return sql.query("SELECT tags.name,COUNT(messages.message_id) " + + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + (rs, rowNum) -> { + Tag t = new Tag(); + t.Name = rs.getString(1); + t.UsageCnt = rs.getInt(2); + return t; + }, uid); } public static List getUserBLTags(Connection sql, int uid) { @@ -167,4 +143,8 @@ public class TagQueries { } return tags; } + + public static List getPopularTags(JdbcTemplate sql) { + return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class); + } } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 817ae5d3..0d6a3485 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -23,6 +23,7 @@ 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.Connection; import java.sql.PreparedStatement; @@ -330,47 +331,22 @@ public class UserQueries { } } - public static List checkBL(Connection sql, int visitor, List uids) { - List ret = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + Utils.convertArrayInt2String(uids) + ")"); - stmt.setInt(1, visitor); - 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); + public static List checkBL(JdbcTemplate sql, int visitor, List uids) { + if (!uids.isEmpty()) { + return sql.queryForList("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + + StringUtils.collectionToCommaDelimitedString(uids) + ")", Integer.class, visitor); + } else { + return new ArrayList<>(); } - - return ret; } - public static boolean isSubscribed(Connection sql, int uid, int check) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isSubscribed(JdbcTemplate sql, int uid, int check) { try { - stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND 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 sql.queryForObject("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } public static List getUserRead(JdbcTemplate sql, int uid) { @@ -403,8 +379,29 @@ public class UserQueries { return users; } - public static List getUserReaders(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); + public List getUserReaders(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id, users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.suser_id=users.id " + + "WHERE subscr_users.user_id=? ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); + } + + public List getUserFriends(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id,users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.user_id=users.id " + + "WHERE subscr_users.suser_id=? AND users.id!=? " + + "ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, uid); } public static List getUserBLUsers(Connection sql, int uid) { -- cgit v1.2.3 From 53a4aaacf585e70aadd3b1133319a5bb1e08f0c4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 1 Jul 2016 12:27:19 +0300 Subject: fix pm conversations query --- src/main/java/com/juick/server/PMQueries.java | 24 ++---------------------- 1 file changed, 2 insertions(+), 22 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 772a1c28..74d5df3e 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -69,33 +69,13 @@ public class PMQueries { public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { List qusers = sql.query("SELECT user_id,unread FROM pm_streams " - + "WHERE user_id_to=? AND unread>0 " - + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + + "WHERE user_id_to=? " + + "ORDER BY unread DESC, lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { com.juick.User u = new com.juick.User(); u.setUID(rs.getInt(1)); u.MessagesCount = rs.getInt(2); return u; }, uid); - - if (qusers.size() < cnt) { - List addUsers = sql.query("SELECT user_id,user_id_to " - + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " - + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " - + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { - User u = new com.juick.User(); - int uuid = rs.getInt(1) + rs.getInt(2) - uid; - u.setUID(uuid); - return u; - }, uid, uid, cnt * 2); - for (User addUser : addUsers) { - if (!haveUserInArray(qusers, addUser.getUID())) { - qusers.add(addUser); - if (qusers.size() >= cnt) { - break; - } - } - } - } if (!qusers.isEmpty()) { UserQueries.fillUsersByID(sql, qusers); } -- cgit v1.2.3