UserQueries.getUserReadLeastPopular

1 files changed, 88 insertions, 62 deletions

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<com.juick.User> getUsersByName(Connection sql, ArrayList<String> unames) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-        
+
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -170,10 +170,10 @@ public class UserQueries {
         }
         return users;
     }
-    
+
     public static ArrayList<com.juick.User> getUsersByID(Connection sql, ArrayList<Integer> uids) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-        
+
         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<com.juick.User> 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<com.juick.User> getUsersByJID(Connection sql, ArrayList<String> jids) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-        
+
         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<Integer> checkBL(Connection sql, int visitor, ArrayList<Integer> uids) {
         ArrayList<Integer> ret = new ArrayList<Integer>();
-        
+
         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<Integer> getUserRead(Connection sql, int uid) {
         return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
     }
-    
+
+    public static ArrayList<com.juick.User> getUserReadLeastPopular(Connection sql, int uid, int cnt) {
+        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>(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<Integer> getUserReaders(Connection sql, int uid) {
         return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
     }
-    
+
     public static ArrayList<com.juick.User> getUserBLUsers(Connection sql, int uid) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-        
+
         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);
     }