From 9e740f48cca7332c67a165c6cf60e09bc549e686 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Mon, 27 Jun 2016 10:33:04 +0300
Subject: more spring-jdbc

---
 src/main/java/com/juick/server/UserQueries.java | 73 ++++++++++++-------------
 1 file changed, 35 insertions(+), 38 deletions(-)

(limited to 'src/main/java/com/juick/server/UserQueries.java')

diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index 817ae5d3..0d6a3485 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -23,6 +23,7 @@ import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;
 import org.springframework.jdbc.support.GeneratedKeyHolder;
 import org.springframework.jdbc.support.KeyHolder;
+import org.springframework.util.StringUtils;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -330,47 +331,22 @@ public class UserQueries {
         }
     }
 
-    public static List<Integer> checkBL(Connection sql, int visitor, List<Integer> uids) {
-        List<Integer> ret = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + Utils.convertArrayInt2String(uids) + ")");
-            stmt.setInt(1, visitor);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                ret.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+    public static List<Integer> checkBL(JdbcTemplate sql, int visitor, List<Integer> uids) {
+        if (!uids.isEmpty()) {
+            return sql.queryForList("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" +
+                    StringUtils.collectionToCommaDelimitedString(uids) + ")", Integer.class, visitor);
+        } else {
+            return new ArrayList<>();
         }
-
-        return ret;
     }
 
-    public static boolean isSubscribed(Connection sql, int uid, int check) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    public static boolean isSubscribed(JdbcTemplate sql, int uid, int check) {
         try {
-            stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, check);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = rs.getInt(1) == 1;
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?",
+                    Integer.class, uid, check) == 1;
+        } catch (EmptyResultDataAccessException e) {
+            return false;
         }
-        return ret;
     }
 
     public static List<Integer> getUserRead(JdbcTemplate sql, int uid) {
@@ -403,8 +379,29 @@ public class UserQueries {
         return users;
     }
 
-    public static List<Integer> getUserReaders(JdbcTemplate sql, int uid) {
-        return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid);
+    public List<User> getUserReaders(JdbcTemplate sql, int uid) {
+        return sql.query("SELECT users.id, users.nick FROM subscr_users " +
+                        "INNER JOIN users ON subscr_users.suser_id=users.id " +
+                        "WHERE subscr_users.user_id=? ORDER BY users.nick",
+                (rs, num) -> {
+                    com.juick.User u = new com.juick.User();
+                    u.setUID(rs.getInt(1));
+                    u.setUName(rs.getString(2));
+                    return u;
+                }, uid);
+    }
+
+    public List<User> getUserFriends(JdbcTemplate sql, int uid) {
+        return sql.query("SELECT users.id,users.nick FROM subscr_users " +
+                        "INNER JOIN users ON subscr_users.user_id=users.id " +
+                        "WHERE subscr_users.suser_id=? AND users.id!=? " +
+                        "ORDER BY users.nick",
+                (rs, num) -> {
+                    com.juick.User u = new com.juick.User();
+                    u.setUID(rs.getInt(1));
+                    u.setUName(rs.getString(2));
+                    return u;
+                }, uid, uid);
     }
 
     public static List<com.juick.User> getUserBLUsers(Connection sql, int uid) {
-- 
cgit v1.2.3