From 439d42ef6d60f0a535fdfa2457da8417b70d966a Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 10 Jan 2016 16:19:30 +0300 Subject: ShowQueries --- src/main/java/com/juick/server/ShowQueries.java | 72 +++++++++++++++++++++++++ src/main/java/com/juick/server/UserQueries.java | 3 +- 2 files changed, 74 insertions(+), 1 deletion(-) create mode 100644 src/main/java/com/juick/server/ShowQueries.java 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 getRecommendedUsers(Connection sql, User forUser) { + List 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 getTopUsers(Connection sql) { + List 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); } -- cgit v1.2.3