From 6145ee50629a75284e790522c9840da7a1a93536 Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Thu, 24 Apr 2014 01:29:44 +0700 Subject: UserQueries.getUserReadLeastPopular --- src/com/juick/server/UserQueries.java | 150 ++++++++++++++++++++-------------- 1 file changed, 88 insertions(+), 62 deletions(-) (limited to 'src/com/juick/server') diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java index 186cacc9..5c1aaccf 100644 --- a/src/com/juick/server/UserQueries.java +++ b/src/com/juick/server/UserQueries.java @@ -31,12 +31,12 @@ import java.util.UUID; * @author Ugnich Anton */ public class UserQueries { - + static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; - + public static String getSignUpHashByJID(Connection sql, String jid) { String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid); - + if (hash == null) { hash = UUID.randomUUID().toString(); PreparedStatement stmt = null; @@ -51,13 +51,13 @@ public class UserQueries { Utils.finishSQL(null, stmt); } } - + return hash; } - + public static int createUser(Connection sql, String username, String password) { int uid = 0; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -74,16 +74,16 @@ public class UserQueries { } finally { Utils.finishSQL(rs, stmt); } - + SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid); SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid); - + return uid; } - + public static com.juick.User getUserByUID(Connection sql, int uid) { com.juick.User user = null; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -102,10 +102,10 @@ public class UserQueries { } return user; } - + public static com.juick.User getUserByName(Connection sql, String username) { com.juick.User user = null; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -124,10 +124,10 @@ public class UserQueries { } return user; } - + public static com.juick.User getUserByJID(Connection sql, String jid) { com.juick.User user = null; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -147,10 +147,10 @@ public class UserQueries { } return user; } - + public static ArrayList getUsersByName(Connection sql, ArrayList unames) { ArrayList users = new ArrayList(); - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -170,10 +170,10 @@ public class UserQueries { } return users; } - + public static ArrayList getUsersByID(Connection sql, ArrayList uids) { ArrayList users = new ArrayList(); - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -191,13 +191,13 @@ public class UserQueries { } finally { Utils.finishSQL(rs, stmt); } - + return users; } - + public static boolean fillUsersByID(Connection sql, ArrayList users) { boolean ret = false; - + String uids = ""; final int usersSize = users.size(); for (int i = 0; i < usersSize; i++) { @@ -206,7 +206,7 @@ public class UserQueries { } uids += users.get(i).UID; } - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -227,13 +227,13 @@ public class UserQueries { } finally { Utils.finishSQL(rs, stmt); } - + return ret; } - + public static ArrayList getUsersByJID(Connection sql, ArrayList jids) { ArrayList users = new ArrayList(); - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -254,26 +254,26 @@ public class UserQueries { } return users; } - + public static String getJIDbyUID(Connection sql, int uid) { return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid); } - + public static int getUIDbyJID(Connection sql, String jid) { return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0); } - + public static int getUIDbyName(Connection sql, String uname) { return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0); } - + public static int getUIDbyHash(Connection sql, String hash) { return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0); } - + public static com.juick.User getUserByHash(Connection sql, String hash) { com.juick.User user = null; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -293,10 +293,10 @@ public class UserQueries { } return user; } - + public static String getHashByUID(Connection sql, int uid) { String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid); - + if (hash == null) { hash = generateHash(16); PreparedStatement stmt = null; @@ -311,10 +311,10 @@ public class UserQueries { Utils.finishSQL(null, stmt); } } - + return hash; } - + public static String generateHash(int len) { Random rnd = new Random(); StringBuilder sb = new StringBuilder(len); @@ -323,11 +323,11 @@ public class UserQueries { } return sb.toString(); } - + public static boolean checkUserNameValid(String uname) { 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; @@ -350,10 +350,10 @@ public class UserQueries { } return uid; } - + public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) { int ret = defaultValue; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -370,7 +370,7 @@ public class UserQueries { } return ret; } - + public static void setUserOptionInt(Connection sql, int uid, String option, int value) { PreparedStatement stmt = null; try { @@ -384,10 +384,10 @@ public class UserQueries { Utils.finishSQL(null, stmt); } } - + public static boolean getCanMedia(Connection sql, int uid) { boolean ret = false; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -404,10 +404,10 @@ public class UserQueries { } return ret; } - + public static boolean isInWL(Connection sql, int uid, int check) { boolean ret = false; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -425,10 +425,10 @@ public class UserQueries { } return ret; } - + public static boolean isInBL(Connection sql, int uid, int check) { boolean ret = false; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -446,10 +446,10 @@ public class UserQueries { } return ret; } - + public static boolean isInBLAny(Connection sql, int uid, int uid2) { boolean ret = false; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -469,10 +469,10 @@ public class UserQueries { } return ret; } - + public static ArrayList checkBL(Connection sql, int visitor, ArrayList uids) { ArrayList ret = new ArrayList(); - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -488,13 +488,13 @@ public class UserQueries { } finally { Utils.finishSQL(rs, stmt); } - + return ret; } - + public static boolean isSubscribed(Connection sql, int uid, int check) { boolean ret = false; - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -512,18 +512,44 @@ public class UserQueries { } return ret; } - + public static ArrayList 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); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT users.id,users.nick FROM (subscr_users INNER JOIN users_subscr ON (subscr_users.suser_id=? AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?"); + stmt.setInt(1, uid); + stmt.setInt(2, cnt); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.User u = new com.juick.User(); + u.UID = rs.getInt(1); + u.UName = rs.getString(2); + users.add(u); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return users; + } + public static ArrayList 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(); - + PreparedStatement stmt = null; ResultSet rs = null; try { @@ -542,22 +568,22 @@ public class UserQueries { } finally { Utils.finishSQL(rs, stmt); } - + return users; } - + public static int getStatsIRead(Connection sql, int uid) { return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0); } - + public static int getStatsMyReaders(Connection sql, int uid) { return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE user_id=?", uid, 0); } - + public static int getStatsMessages(Connection sql, int uid) { return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM messages WHERE user_id=?", uid, 0); } - + public static int getStatsReplies(Connection sql, int uid) { return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM replies WHERE user_id=?", uid, 0); } -- cgit v1.2.3