From f14eea2cfc80dac5e9b304a57457e7f82842a8ed Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 15:49:29 +0300 Subject: spring-jdbc WIP --- src/main/java/com/juick/server/ShowQueries.java | 58 ++++--------------------- 1 file changed, 9 insertions(+), 49 deletions(-) (limited to 'src/main/java/com/juick') 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 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 " + + public static List 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 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 " + + public static List 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); } } -- cgit v1.2.3