more spring-jdbc

6 files changed, 169 insertions, 252 deletions

diff --git a/build.gradle b/build.gradle
index b82c3d5c..9f5bc86d 100644
--- a/build.gradle
+++ b/build.gradle
@@ -5,5 +5,6 @@ repositories {
 }
 
 dependencies {
-    compile "org.springframework:spring-jdbc:4.2.4.RELEASE"
+    compile "org.springframework:spring-jdbc:4.3.0.RELEASE"
+    compile "org.apache.commons:commons-lang3:3.4"
 }
\ No newline at end of file
diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java
index ad9b7149..06590817 100644
--- a/src/main/java/com/juick/server/AdsQueries.java
+++ b/src/main/java/com/juick/server/AdsQueries.java
@@ -17,11 +17,8 @@
  */
 package com.juick.server;
 
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.SQLException;
-import java.util.logging.Level;
-import java.util.logging.Logger;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
 
 /**
  *
@@ -29,27 +26,26 @@ import java.util.logging.Logger;
  */
 public class AdsQueries {
 
-    private static final Logger logger = Logger.getLogger(AdsQueries.class.getName());
-
-    public static int getAdMID(Connection sql, int uid) {
+    public static int getAdMID(JdbcTemplate sql, int uid) {
         if (uid > 0) {
-            return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0);
+            try {
+                return sql.queryForObject("SELECT message_id FROM ads_messages " +
+                                "WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? " +
+                                "AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1",
+                        Integer.class, uid);
+            } catch (EmptyResultDataAccessException e) {
+                return 0;
+            }
         } else {
-            return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", 0);
+            try {
+                return sql.queryForObject("SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", Integer.class);
+            } catch (EmptyResultDataAccessException e) {
+                return 0;
+            }
         }
     }
 
-    public static void logAdMID(Connection sql, int uid, int mid) {
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, mid);
-            stmt.executeUpdate();
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
+    public static void logAdMID(JdbcTemplate sql, int uid, int mid) {
+        sql.update("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())", uid, mid);
     }
 }
diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java
index b44b6b59..04898d0a 100644
--- a/src/main/java/com/juick/server/CrosspostQueries.java
+++ b/src/main/java/com/juick/server/CrosspostQueries.java
@@ -17,12 +17,11 @@
  */
 package com.juick.server;
 
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.logging.Level;
-import java.util.logging.Logger;
+import org.apache.commons.lang3.tuple.Pair;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
+
+import java.util.Optional;
 
 /**
  *
@@ -30,55 +29,34 @@ import java.util.logging.Logger;
  */
 public class CrosspostQueries {
 
-    private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName());
-
-    public static String[] getTwitterTokens(Connection sql, int uid) {
-        String tokens[] = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    public static Optional<Pair<String, String>> getTwitterTokens(JdbcTemplate sql, int uid) {
         try {
-            stmt = sql.prepareStatement("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                tokens = new String[2];
-                tokens[0] = rs.getString(1);
-                tokens[1] = rs.getString(2);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1",
+                    (rs, num) -> {
+                        return Optional.of(Pair.of(rs.getString(1), rs.getString(2)));
+                    }, uid);
+        } catch (EmptyResultDataAccessException e) {
+            return Optional.empty();
         }
-
-        return tokens;
     }
 
-    public static String getFacebookToken(Connection sql, int uid) {
-        return SQLHelpers.getString(sql, "SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL AND crosspost=1", uid);
+    public static Optional<String> getFacebookToken(JdbcTemplate sql, int uid) {
+        try {
+            return Optional.of(sql.queryForObject("SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL " +
+                    "AND crosspost=1", String.class, uid));
+        } catch (EmptyResultDataAccessException e) {
+            return Optional.empty();
+        }
     }
 
-    public static String[] getVKTokens(Connection sql, int uid) {
-        String tokens[] = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    public static Optional<Pair<String, String>> getVKTokens(JdbcTemplate sql, int uid) {
         try {
-            stmt = sql.prepareStatement("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                tokens = new String[2];
-                tokens[0] = rs.getString(1);
-                tokens[1] = rs.getString(2);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1",
+                    (rs, num) -> {
+                        return Optional.of(Pair.of(rs.getString(1), rs.getString(2)));
+                    }, uid);
+        } catch (EmptyResultDataAccessException e) {
+            return Optional.empty();
         }
-
-        return tokens;
     }
 }
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index 6de30995..772a1c28 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -18,16 +18,10 @@
 package com.juick.server;
 
 import com.juick.User;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
 
-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;
 
 /**
  *
@@ -35,8 +29,6 @@ import org.springframework.jdbc.core.JdbcTemplate;
  */
 public class PMQueries {
 
-    private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName());
-
     public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) {
         boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)",
                 uid_from, uid_to, body) > 0;
@@ -55,20 +47,8 @@ public class PMQueries {
                 uid, jid) > 0;
     }
 
-    public static boolean removePMinRoster(Connection sql, int uid, String jid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, jid);
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    public static boolean removePMinRoster(JdbcTemplate sql, int uid, String jid) {
+        return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0;
     }
 
     public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) {
@@ -78,37 +58,49 @@ public class PMQueries {
             return res.size() > 0;        
     }
 
-    public static String getLastView(Connection sql, int uid_from, int uid_to) {
-        String ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) {
         try {
-            stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?");
-            stmt.setInt(1, uid_from);
-            stmt.setInt(2, uid_to);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = rs.getString(1);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class,
+                    uid_from, uid_to);
+        } catch (EmptyResultDataAccessException e) {
+            return null;
         }
-        return ret;
     }
 
     public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
-        return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " +
-                "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " +
-                "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> {
-                    com.juick.User u = new com.juick.User();
-                    u.setUID(rs.getInt(1));
-                    u.setUName(rs.getString(2));
-                    u.MessagesCount = rs.getInt(3);
-                    return u;
-                }, uid, cnt);
+        List<User> qusers = sql.query("SELECT user_id,unread FROM pm_streams "
+                + "WHERE user_id_to=? AND unread>0 "
+                + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> {
+            com.juick.User u = new com.juick.User();
+            u.setUID(rs.getInt(1));
+            u.MessagesCount = rs.getInt(2);
+            return u;
+        }, uid);
+
+        if (qusers.size() < cnt) {
+            List<User> addUsers = sql.query("SELECT user_id,user_id_to "
+                    + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) "
+                    + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) "
+                    + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> {
+                User u = new com.juick.User();
+                int uuid = rs.getInt(1) + rs.getInt(2) - uid;
+                u.setUID(uuid);
+                return u;
+            }, uid, uid, cnt * 2);
+            for (User addUser : addUsers) {
+                if (!haveUserInArray(qusers, addUser.getUID())) {
+                    qusers.add(addUser);
+                    if (qusers.size() >= cnt) {
+                        break;
+                    }
+                }
+            }
+        }
+        if (!qusers.isEmpty()) {
+            UserQueries.fillUsersByID(sql, qusers);
+        }
+
+        return qusers;
     }
 
     public static boolean haveUserInArray(List<com.juick.User> arr, int uid) {
@@ -140,61 +132,34 @@ public class PMQueries {
         return msgs;
     }
 
-    public static List<com.juick.Message> getLastPMInbox(Connection sql, int uid) {
-        List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                msg.setUser(new User());
-                msg.getUser().setUID(rs.getInt(1));
-                msg.getUser().setUName(rs.getString(2));
-                msg.setText(rs.getString(3));
-                msg.TimeAgo = rs.getInt(4);
-                msg.setDate(rs.getTimestamp(5));
-                msgs.add(0, msg);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return msgs;
+    public static List<com.juick.Message> getLastPMInbox(JdbcTemplate sql, int uid) {
+        return sql.query("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " +
+                        "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20",
+                (rs, num) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    msg.setUser(new User());
+                    msg.getUser().setUID(rs.getInt(1));
+                    msg.getUser().setUName(rs.getString(2));
+                    msg.setText(rs.getString(3));
+                    msg.TimeAgo = rs.getInt(4);
+                    msg.setDate(rs.getTimestamp(5));
+                    return msg;
+                }, uid);
     }
 
-    public static List<com.juick.Message> getLastPMSent(Connection sql, int uid) {
-        List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                msg.setUser(new User());
-                msg.getUser().setUID(rs.getInt(1));
-                msg.getUser().setUName(rs.getString(2));
-                msg.setText(rs.getString(3));
-                msg.TimeAgo = rs.getInt(4);
-                msg.setDate(rs.getTimestamp(5));
-                msgs.add(0, msg);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return msgs;
+    public static List<com.juick.Message> getLastPMSent(JdbcTemplate sql, int uid) {
+        return sql.query("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," +
+                        "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " +
+                        "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20",
+                (rs, num) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    msg.setUser(new User());
+                    msg.getUser().setUID(rs.getInt(1));
+                    msg.getUser().setUName(rs.getString(2));
+                    msg.setText(rs.getString(3));
+                    msg.TimeAgo = rs.getInt(4);
+                    msg.setDate(rs.getTimestamp(5));
+                    return msg;
+                }, uid);
     }
 }
diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java
index f3ab480e..d2159b5a 100644
--- a/src/main/java/com/juick/server/TagQueries.java
+++ b/src/main/java/com/juick/server/TagQueries.java
@@ -90,25 +90,12 @@ public class TagQueries {
         return ret;
     }
 
-    public static boolean getTagNoIndex(Connection sql, int tag_id) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) {
         try {
-            stmt = sql.prepareStatement("SELECT noindex FROM tags WHERE tag_id=?");
-            stmt.setInt(1, tag_id);
-            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 noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1;
+        } catch (EmptyResultDataAccessException e) {
+            return false;
         }
-
-        return ret;
     }
 
     public static int createTag(JdbcTemplate sql, String name) {
@@ -123,28 +110,17 @@ public class TagQueries {
         return holder.getKey().intValue();        
     }
 
-    public static List<com.juick.Tag> getUserTagsAll(Connection sql, int uid) {
-        List<com.juick.Tag> tags = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT tags.name,COUNT(messages.message_id) FROM (messages INNER JOIN messages_tags ON (messages.user_id=? AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Tag t = new com.juick.Tag();
-                t.Name = rs.getString(1);
-                t.UsageCnt = rs.getInt(2);
-                tags.add(t);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return tags;
+    public static List<com.juick.Tag> getUserTagsAll(JdbcTemplate sql, int uid) {
+        return sql.query("SELECT tags.name,COUNT(messages.message_id) " +
+                        "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " +
+                        "AND messages.message_id=messages_tags.message_id)) " +
+                        "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC",
+                (rs, rowNum) -> {
+                    Tag t = new Tag();
+                    t.Name = rs.getString(1);
+                    t.UsageCnt = rs.getInt(2);
+                    return t;
+                }, uid);
     }
 
     public static List<String> getUserBLTags(Connection sql, int uid) {
@@ -167,4 +143,8 @@ public class TagQueries {
         }
         return tags;
     }
+
+    public static List<String> getPopularTags(JdbcTemplate sql) {
+        return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class);
+    }
 }
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) {