diff options
author | Vitaly Takmazov | 2016-01-15 15:49:29 +0300 |
---|---|---|
committer | Vitaly Takmazov | 2016-01-15 15:49:29 +0300 |
commit | f14eea2cfc80dac5e9b304a57457e7f82842a8ed (patch) | |
tree | dbdb133df0cd878bbe62557918851f43c2e8c862 /src | |
parent | 0ac9d76d6348f487ada697678395d15abee244c9 (diff) |
spring-jdbc WIP
Diffstat (limited to 'src')
-rw-r--r-- | src/main/java/com/juick/server/ShowQueries.java | 58 |
1 files changed, 9 insertions, 49 deletions
diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java index 06aafb2d..05a58e9a 100644 --- a/src/main/java/com/juick/server/ShowQueries.java +++ b/src/main/java/com/juick/server/ShowQueries.java @@ -2,71 +2,31 @@ 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; +import org.springframework.jdbc.core.JdbcTemplate; /** * 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 " + + public static List<String> getRecommendedUsers(JdbcTemplate sql, User forUser) { + return sql.queryForList("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; + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", + String.class, new Object[] {forUser.UID, forUser.UID, forUser.UID, forUser.UID}); } - 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 " + + public static List<String> getTopUsers(JdbcTemplate sql) { + return sql.queryForList("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; + "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10", + String.class); } } |