diff options
author | 2016-01-10 16:19:30 +0300 | |
---|---|---|
committer | 2016-01-10 16:19:30 +0300 | |
commit | 439d42ef6d60f0a535fdfa2457da8417b70d966a (patch) | |
tree | 190ec750a1c3520743965afe8b6b015e9176bb95 /src/main/java/com | |
parent | 2713251c8503f6a0b5ba7cb45f91cf71fa8f81b2 (diff) |
ShowQueries
Diffstat (limited to 'src/main/java/com')
-rw-r--r-- | src/main/java/com/juick/server/ShowQueries.java | 72 | ||||
-rw-r--r-- | src/main/java/com/juick/server/UserQueries.java | 3 |
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); } |