spring-jdbc

1 files changed, 67 insertions, 88 deletions

diff --git a/src/main/java/com/juick/http/www/User.java b/src/main/java/com/juick/http/www/User.java
index 84d1ad9a..f4f873bd 100644
--- a/src/main/java/com/juick/http/www/User.java
+++ b/src/main/java/com/juick/http/www/User.java
@@ -17,9 +17,14 @@
  */
 package com.juick.http.www;
 
+import com.juick.Tag;
 import com.juick.server.MessagesQueries;
 import com.juick.server.TagQueries;
 import com.juick.server.UserQueries;
+import com.juick.xmpp.Stream;
+import org.apache.commons.lang3.tuple.Pair;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.io.UnsupportedEncodingException;
@@ -31,6 +36,7 @@ import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.List;
+import java.util.stream.Collectors;
 import javax.servlet.ServletException;
 import javax.servlet.http.Cookie;
 import javax.servlet.http.HttpServletRequest;
@@ -42,7 +48,8 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class User {
 
-    protected void doGetBlog(Connection sql, Connection sqlSearch, HttpServletRequest request, HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
+    protected void doGetBlog(JdbcTemplate sql, JdbcTemplate sqlSearch, HttpServletRequest request,
+                             HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         List<Integer> mids;
@@ -95,7 +102,8 @@ public class User {
                 mids = MessagesQueries.getUserTag(sql, user.getUID(), paramTag.TID, privacy, paramBefore);
             } else if (paramSearch != null) {
                 title = "Блог " + user.getUName() + ": " + Utils.encodeHTML(paramSearch);
-                mids = MessagesQueries.getUserSearch(sql, sqlSearch, user.getUID(), Utils.encodeSphinx(paramSearch), privacy, paramBefore);
+                mids = MessagesQueries.getUserSearch(sql, sqlSearch, user.getUID(), Utils.encodeSphinx(paramSearch),
+                        privacy, paramBefore);
             } else {
                 title = "Блог " + user.getUName();
                 mids = MessagesQueries.getUserBlog(sql, user.getUID(), privacy, paramBefore);
@@ -158,7 +166,8 @@ public class User {
         }
     }
 
-    protected void doGetTags(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
+    protected void doGetTags(JdbcTemplate sql, HttpServletRequest request,
+                             HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (visitor == null) {
@@ -181,7 +190,8 @@ public class User {
         }
     }
 
-    protected void doGetFriends(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
+    protected void doGetFriends(JdbcTemplate sql, HttpServletRequest request,
+                                HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (visitor == null) {
@@ -198,25 +208,17 @@ public class User {
             out.println("<section id=\"content\">");
             out.println("<table class=\"users\"><tr>");
 
-            PreparedStatement stmt = null;
-            ResultSet rs = null;
-            try {
-                stmt = sql.prepareStatement("SELECT users.id,users.nick FROM subscr_users INNER JOIN users ON subscr_users.user_id=users.id WHERE subscr_users.suser_id=? ORDER BY users.nick");
-                stmt.setInt(1, user.getUID());
-                rs = stmt.executeQuery();
-                rs.beforeFirst();
-                int cnt = 0;
-                while (rs.next()) {
-                    if (cnt % 3 == 0 && cnt > 0) {
-                        out.print("</tr><tr>");
-                    }
-                    out.print("<td><a href=\"/" + rs.getString(2) + "/\"><img src=\"//i.juick.com/as/" + rs.getInt(1) + ".png\"/>" + rs.getString(2) + "</a></td>");
-                    cnt++;
+            List<Pair<Integer, String>> friends = 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=? ORDER BY users.nick",
+                    (rs, num) -> {
+                        return Pair.of(rs.getInt(1), rs.getString(2));
+                    }, user.getUID());
+            for (int cnt = 0; cnt < friends.size(); cnt++) {
+                if (cnt % 3 == 0 && cnt > 0) {
+                    out.print("</tr><tr>");
                 }
-            } catch (SQLException e) {
-                System.err.println(e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
+                out.print("<td><a href=\"/" + friends.get(cnt).getRight() + "/\"><img src=\"//i.juick.com/as/" + friends.get(cnt).getLeft() + ".png\"/>" + friends.get(cnt).getRight() + "</a></td>");
             }
 
             out.println("</tr></table>");
@@ -227,7 +229,8 @@ public class User {
         }
     }
 
-    protected void doGetReaders(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
+    protected void doGetReaders(JdbcTemplate sql, HttpServletRequest request,
+                                HttpServletResponse response, com.juick.User user) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (visitor == null) {
@@ -244,27 +247,18 @@ public class User {
             out.println("<section id=\"content\">");
             out.println("<table class=\"users\"><tr>");
 
-            PreparedStatement stmt = null;
-            ResultSet rs = null;
-            try {
-                stmt = sql.prepareStatement("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");
-                stmt.setInt(1, user.getUID());
-                rs = stmt.executeQuery();
-                rs.beforeFirst();
-                int cnt = 0;
-                while (rs.next()) {
-                    if (cnt % 3 == 0 && cnt > 0) {
-                        out.print("</tr><tr>");
-                    }
-                    out.print("<td><a href=\"/" + rs.getString(2) + "/\"><img src=\"//i.juick.com/as/" + rs.getInt(1) + ".png\"/>" + rs.getString(2) + "</a></td>");
-                    cnt++;
+            List<Pair<Integer, String>> readers = 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) -> {
+                        return Pair.of(rs.getInt(1), rs.getString(2));
+                    }, user.getUID());
+            for (int cnt = 0; cnt < readers.size(); cnt++) {
+                if (cnt % 3 == 0 && cnt > 0) {
+                    out.print("</tr><tr>");
                 }
-            } catch (SQLException e) {
-                System.err.println(e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
+                out.print("<td><a href=\"/" + readers.get(cnt).getRight() + "/\"><img src=\"//i.juick.com/as/" + readers.get(cnt).getLeft() + ".png\"/>" + readers.get(cnt).getRight() + "</a></td>");
             }
-
             out.println("</tr></table>");
             out.println("</section>");
 
@@ -285,7 +279,7 @@ public class User {
         }
     }
 
-    public static void pageUserColumn(PrintWriter out, Connection sql, com.juick.User user, com.juick.User visitor) {
+    public static void pageUserColumn(PrintWriter out, JdbcTemplate sql, com.juick.User user, com.juick.User visitor) {
         out.println("<aside id=\"column\">");
         out.println("  <div id=\"ctitle\"><a href=\"./\"><img src=\"//i.juick.com/as/" + user.getUID() + ".png\" alt=\"\"/>" + user.getUName() + "</a></div>");
         if (visitor != null && visitor.getUID() > 0 && visitor.getUID() != user.getUID()) {
@@ -342,53 +336,39 @@ public class User {
         out.println("</aside>");
     }
 
-    public static String pageUserTags(Connection sql, com.juick.User user, com.juick.User visitor, int cnt) {
-        com.juick.Tag tags[] = null;
-
-        int maxUsageCnt = 0;
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            if (cnt > 0) {
-                stmt = sql.prepareStatement("SELECT tags.name AS name,COUNT(DISTINCT messages_tags.message_id) AS cnt FROM (messages INNER JOIN messages_tags ON (messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages.user_id=? GROUP BY messages_tags.tag_id ORDER BY cnt DESC LIMIT ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
-                stmt.setInt(1, user.getUID());
-                stmt.setInt(2, cnt);
-            } else {
-                stmt = sql.prepareStatement("SELECT tags.name AS name,COUNT(DISTINCT messages_tags.message_id) AS cnt FROM (messages INNER JOIN messages_tags ON (messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages.user_id=? GROUP BY messages_tags.tag_id ORDER BY cnt DESC", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
-                stmt.setInt(1, user.getUID());
-            }
-            rs = stmt.executeQuery();
-            rs.last();
-            tags = new com.juick.Tag[rs.getRow()];
-            rs.beforeFirst();
-            cnt = 0;
-            while (rs.next()) {
-                tags[cnt] = new com.juick.Tag();
-                tags[cnt].Name = rs.getString(1);
-                tags[cnt].UsageCnt = rs.getInt(2);
-                if (tags[cnt].UsageCnt > maxUsageCnt) {
-                    maxUsageCnt = tags[cnt].UsageCnt;
-                }
-                cnt++;
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+    public static String pageUserTags(JdbcTemplate sql, com.juick.User user, com.juick.User visitor, int cnt) {
+        List<Tag> tags;
+        if (cnt > 0) {
+            tags = sql.query("SELECT tags.name AS name,COUNT(DISTINCT messages_tags.message_id) AS cnt " +
+                            "FROM (messages INNER JOIN messages_tags ON (messages.message_id=messages_tags.message_id)) " +
+                            "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages.user_id=? " +
+                            "GROUP BY messages_tags.tag_id ORDER BY cnt DESC LIMIT ?",
+                    (rs, num) -> {
+                        Tag t = new Tag();
+                        t.Name = rs.getString(1);
+                        t.UsageCnt = rs.getInt(2);
+                        return t;
+                    }, user.getUID(), cnt);
+        } else {
+            tags = sql.query("SELECT tags.name AS name,COUNT(DISTINCT messages_tags.message_id) AS cnt " +
+                    "FROM (messages INNER JOIN messages_tags ON (messages.message_id=messages_tags.message_id)) " +
+                    "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages.user_id=? " +
+                    "GROUP BY messages_tags.tag_id ORDER BY cnt DESC", ((rs, rowNum) -> {
+                Tag t = new Tag();
+                t.Name = rs.getString(1);
+                t.UsageCnt = rs.getInt(2);
+                return t;
+            }), user.getUID());
         }
 
-        if (tags != null && cnt > 0) {
-            Arrays.sort(tags, 0, cnt);
-        }
+        int maxUsageCnt = tags.stream().mapToInt(tag -> tag.UsageCnt).max().getAsInt();
 
-        String ret = "";
-        for (int i = 0; i < cnt; i++) {
-            String tag = Utils.encodeHTML(tags[i].Name);
-            try {
-                tag = "<a href=\"./?tag=" + URLEncoder.encode(tags[i].Name, "UTF-8") + "\" title=\"" + tags[i].UsageCnt + "\" rel=\"nofollow\">" + tag + "</a>";
-            } catch (UnsupportedEncodingException e) {
-            }
+        return tags.stream().map(tag -> "<a href=\"./?tag=" + URLEncoder.encode(tag.Name) +
+                "\" title=\"" + tag.UsageCnt + "\" rel=\"nofollow\">" + tag + "</a>")
+                .collect(Collectors.joining(" "));
 
+        /*
+                todo:
             if (tags[i].UsageCnt > maxUsageCnt / 3 * 2) {
                 ret += "<big>" + tag + "</big> ";
             } else if (tags[i].UsageCnt > maxUsageCnt / 3) {
@@ -396,7 +376,6 @@ public class User {
             } else {
                 ret += tag + " ";
             }
-        }
-        return ret;
+        }*/
     }
 }