aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2016-01-15 15:49:29 +0300
committerGravatar Vitaly Takmazov2016-01-15 15:49:29 +0300
commitf14eea2cfc80dac5e9b304a57457e7f82842a8ed (patch)
treedbdb133df0cd878bbe62557918851f43c2e8c862 /src/main/java/com/juick
parent0ac9d76d6348f487ada697678395d15abee244c9 (diff)
spring-jdbc WIP
Diffstat (limited to 'src/main/java/com/juick')
-rw-r--r--src/main/java/com/juick/server/ShowQueries.java58
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);
}
}