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/TagQueries.java | 58 +++++++++-----------------
 1 file changed, 19 insertions(+), 39 deletions(-)

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

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);
+    }
 }
-- 
cgit v1.2.3