aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2016-01-10 16:19:30 +0300
committerGravatar Vitaly Takmazov2016-01-10 16:19:30 +0300
commit439d42ef6d60f0a535fdfa2457da8417b70d966a (patch)
tree190ec750a1c3520743965afe8b6b015e9176bb95
parent2713251c8503f6a0b5ba7cb45f91cf71fa8f81b2 (diff)
ShowQueries
-rw-r--r--src/main/java/com/juick/server/ShowQueries.java72
-rw-r--r--src/main/java/com/juick/server/UserQueries.java3
2 files changed, 74 insertions, 1 deletions
diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java
new file mode 100644
index 00000000..06aafb2d
--- /dev/null
+++ b/src/main/java/com/juick/server/ShowQueries.java
@@ -0,0 +1,72 @@
+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;
+
+/**
+ * Created by vt on 10/01/16.
+ */
+public class ShowQueries {
+
+ private static final Logger logger = Logger.getLogger(ShowQueries.class.getName());
+
+ public static List<String> getRecommendedUsers(Connection sql, User forUser) {
+ List<String> result = new ArrayList<>();
+ PreparedStatement preparedStatement = null;
+ ResultSet rs = null;
+ try {
+ preparedStatement = sql.prepareStatement("SELECT users.nick FROM subscr_users INNER JOIN users " +
+ "ON subscr_users.user_id=users.id " +
+ "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " +
+ "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " +
+ "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");
+ preparedStatement.setInt(1, forUser.UID);
+ preparedStatement.setInt(2, forUser.UID);
+ preparedStatement.setInt(3, forUser.UID);
+ preparedStatement.setInt(4, forUser.UID);
+ rs = preparedStatement.executeQuery();
+ rs.beforeFirst();
+ while (rs.next()) {
+ result.add(rs.getString(1));
+ }
+ } catch (SQLException e) {
+ logger.log(Level.SEVERE, "sql error", e);
+ } finally {
+ Utils.finishSQL(rs, preparedStatement);
+ }
+ return result;
+ }
+
+ public static List<String> getTopUsers(Connection sql) {
+ List<String> result = new ArrayList<>();
+ PreparedStatement preparedStatement = null;
+ ResultSet rs = null;
+ try {
+ preparedStatement = sql.prepareStatement("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " +
+ "FROM (subscr_users INNER JOIN users ON subscr_users.user_id=users.id) " +
+ "INNER JOIN useroptions ON users.id=useroptions.user_id " +
+ "WHERE useroptions.privacy_view>0 AND users.lastmessage>UNIX_TIMESTAMP()-259200 " +
+ "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10");
+ rs = preparedStatement.executeQuery();
+ rs.beforeFirst();
+ while (rs.next()) {
+ result.add(rs.getString(1));
+ }
+ } catch (SQLException e) {
+ logger.log(Level.SEVERE, "sql error", e);
+ } finally {
+ Utils.finishSQL(rs, preparedStatement);
+ }
+ return result;
+ }
+}
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index cb192b73..767a68d2 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -27,6 +27,7 @@ import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;
import java.util.UUID;
+import java.util.logging.Level;
import java.util.logging.Logger;
/**
@@ -641,7 +642,7 @@ public class UserQueries {
preparedStatement.setString(3, JID);
return preparedStatement.executeUpdate() >= 0;
} catch (SQLException e) {
- e.printStackTrace();
+ logger.log(Level.SEVERE, "sql error", e);
} finally {
Utils.finishSQL(null, preparedStatement);
}