spring-jdbc WIP

4 files changed, 191 insertions, 356 deletions

diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index 4d5683bd..a8605d3a 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -17,6 +17,9 @@
  */
 package com.juick.server;
 
+import com.juick.Message;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -28,6 +31,7 @@ import java.util.Collections;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.core.RowMapper;
 
 /**
  *
@@ -553,50 +557,32 @@ public class MessagesQueries {
         return mids;
     }
 
-    public static List<Integer> getMyFeed(Connection sql, int uid, int before) {
+    public static List<Integer> getMyFeed(JdbcTemplate sql, int uid, int before) {
         List<Integer> mids = new ArrayList<>(40);
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, before);
-                stmt.setInt(3, uid);
-            } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, uid);
-            }
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                mids.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+        if (before > 0) {
+            mids = sql.queryForList("SELECT message_id FROM messages " +
+                            "INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) " +
+                            "WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " +
+                            "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20",
+                    Integer.class, new Object[]{uid, before, uid});
+        } else {
+            mids = sql.queryForList("SELECT message_id FROM messages " +
+                            "INNER JOIN subscr_users ON (subscr_users.suser_id=? " +
+                            "AND subscr_users.user_id=messages.user_id) " +
+                            "WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " +
+                            "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20",
+                    Integer.class, new Object[]{uid, uid});
         }
 
-        try {
-            if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, before);
-            } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-            }
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                mids.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+
+        if (before > 0) {
+            mids.addAll(sql.queryForList("SELECT message_id FROM messages " +
+                            "WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20", Integer.class,
+                    new Object[]{uid, before}));
+        } else {
+            mids.addAll(sql.queryForList("SELECT message_id FROM messages " +
+                            "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", Integer.class,
+                    new Object[]{uid}));
         }
 
         Collections.sort(mids, Collections.reverseOrder());
@@ -945,50 +931,47 @@ public class MessagesQueries {
         return mids;
     }
 
-    public static List<com.juick.Message> getMessages(Connection sql, List<Integer> mids) {
+    public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) {
         List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT STRAIGHT_JOIN messages.message_id,messages.user_id,users.nick,messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,messages_txt.repliesby,messages.attach,messages.lat,messages.lon,messages.likes FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id IN (" + Utils.convertArrayInt2String(mids) + ") ORDER BY messages.message_id DESC");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                msg.User = new com.juick.User();
-
-                msg.MID = rs.getInt(1);
-                msg.User.UID = rs.getInt(2);
-                msg.User.UName = rs.getString(3);
-                if (rs.getString(4) != null) {
-                    msg.parseTags(rs.getString(4));
-                }
-                msg.ReadOnly = rs.getInt(5) == 1;
-                msg.Privacy = rs.getInt(6);
-                msg.FriendsOnly = msg.Privacy < 0;
-                msg.Text = rs.getString(7);
-                msg.TimeAgo = rs.getInt(8);
-                msg.TimestampString = rs.getString(9);
-                msg.Replies = rs.getInt(10);
-                msg.RepliesBy = rs.getString(11);
-                msg.AttachmentType = rs.getString(12);
-                if (rs.getDouble(13) != 0) {
-                    msg.Place = new com.juick.Place();
-                    msg.Place.lat = rs.getDouble(13);
-                    msg.Place.lon = rs.getDouble(14);
-                }
-                msg.Likes = rs.getInt(15);
-
-                msgs.add(msg);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return msgs;
+        
+        return sql.query("SELECT messages.message_id,messages.user_id,users.nick,"
+                + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,"
+                + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
+                + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,"
+                + "messages_txt.repliesby,messages.attach,messages.lat,"
+                + "messages.lon,messages.likes "
+                + "FROM (messages INNER JOIN messages_txt "
+                + "ON messages.message_id=messages_txt.message_id) "
+                + "INNER JOIN users ON messages.user_id=users.id "
+                + "WHERE messages.message_id "
+                + "IN (" + Utils.convertArrayInt2String(mids) + ") "
+                + "ORDER BY messages.message_id DESC", (rs, rowNum) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    msg.User = new com.juick.User();
+                    
+                    msg.MID = rs.getInt(1);
+                    msg.User.UID = rs.getInt(2);
+                    msg.User.UName = rs.getString(3);
+                    if (rs.getString(4) != null) {
+                        msg.parseTags(rs.getString(4));
+                    }
+                    msg.ReadOnly = rs.getInt(5) == 1;
+                    msg.Privacy = rs.getInt(6);
+                    msg.FriendsOnly = msg.Privacy < 0;
+                    msg.Text = rs.getString(7);
+                    msg.TimeAgo = rs.getInt(8);
+                    msg.TimestampString = rs.getString(9);
+                    msg.Replies = rs.getInt(10);
+                    msg.RepliesBy = rs.getString(11);
+                    msg.AttachmentType = rs.getString(12);
+                    if (rs.getDouble(13) != 0) {
+                        msg.Place = new com.juick.Place();
+                        msg.Place.lat = rs.getDouble(13);
+                        msg.Place.lon = rs.getDouble(14);
+                    }
+                    msg.Likes = rs.getInt(15);
+                    return msg;
+        });
     }
 
     public static List<com.juick.Message> getReplies(Connection sql, int mid) {
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index c4b4fa2d..a2d4545c 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -27,6 +27,8 @@ import java.util.ArrayList;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
 
 /**
  *
@@ -34,54 +36,24 @@ import java.util.logging.Logger;
  */
 public class PMQueries {
 
-    private static final Logger logger = Logger.getLogger(PMQueries.class.getName());
+    private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName());
 
-    public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)");
-            stmt.setInt(1, uid_from);
-            stmt.setInt(2, uid_to);
-            stmt.setString(3, body);
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
+    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 (?,?,?)",
+                new Object[] {uid_from, uid_to, body}) > 0;            
 
         if (ret) {
-            PreparedStatement stmt2 = null;
-            try {
-                stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1");
-                stmt2.setInt(1, uid_from);
-                stmt2.setInt(2, uid_to);
-                stmt2.executeUpdate();
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt2);
-            }
+            sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) "
+                    + "VALUES (?,?,NOW(),1) "
+                    + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1",
+                    new Object[] {uid_from, uid_to});                
         }
-
         return ret;
     }
 
-    public static boolean addPMinRoster(Connection sql, int uid, String jid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)");
-            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 addPMinRoster(JdbcTemplate sql, int uid, String jid) {
+        return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)",
+                new Object[] {uid, jid}) > 0;        
     }
 
     public static boolean removePMinRoster(Connection sql, int uid, String jid) {
@@ -93,32 +65,18 @@ public class PMQueries {
             stmt.setString(2, jid);
             ret = stmt.executeUpdate() > 0;
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(null, stmt);
         }
         return ret;
     }
 
-    public static boolean havePMinRoster(Connection sql, int uid, String jid) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, jid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = true;
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return ret;
+    public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) {
+        List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster "
+                + "WHERE user_id=? AND jid=?", Integer.class,
+                new Object[] {uid, jid});
+            return res.size() > 0;        
     }
 
     public static String getLastView(Connection sql, int uid_from, int uid_to) {
@@ -135,61 +93,42 @@ public class PMQueries {
                 ret = rs.getString(1);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
         return ret;
     }
 
-    public static List<com.juick.User> getPMLastConversationsUsers(Connection sql, int uid, int cnt) {
-        List<User> users = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt);
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.User u = new com.juick.User();
-                u.UID = rs.getInt(1);
-                u.MessagesCount = rs.getInt(2);
-                users.add(u);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+    public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
+        List<User> users = 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.UID = rs.getInt(1);
+                    u.MessagesCount = rs.getInt(2);
+                    return u;
+                }, new Object[]{uid});
 
         if (users.size() < cnt) {
-            try {
-                stmt = sql.prepareStatement("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 ?");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, uid);
-                stmt.setInt(3, cnt * 2);
-                rs = stmt.executeQuery();
-                rs.beforeFirst();
-                while (rs.next()) {
-                    int uuid = rs.getInt(1) + rs.getInt(2) - uid;
-                    if (!haveUserInArray(users, uuid)) {
-                        com.juick.User u = new com.juick.User();
+            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.UID = uuid;
-                        users.add(u);
-                        if (users.size() >= cnt) {
-                            break;
-                        }
+                        return u;
+                    }, new Object[]{uid, uid, cnt * 2});
+            for (User addUser : addUsers) {
+                if (!haveUserInArray(users, addUser.UID)) {
+                    users.add(addUser);
+                    if (users.size() >= cnt) {
+                        break;
                     }
                 }
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
             }
         }
-
         if (!users.isEmpty()) {
             UserQueries.fillUsersByID(sql, users);
         }
@@ -206,45 +145,22 @@ public class PMQueries {
         return false;
     }
 
-    public static List<com.juick.Message> getPMMessages(Connection sql, int uid, int uid_to) {
-        List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT user_id,txt,ts FROM pm WHERE (user_id=? AND user_id_to=?) OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, uid_to);
-            stmt.setInt(3, uid);
-            stmt.setInt(4, uid_to);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                int uuid = rs.getInt(1);
-                msg.User = new com.juick.User();
-                msg.User.UID = uuid;
-                msg.Text = rs.getString(2);
-                msg.TimestampString = rs.getString(3);
-                msgs.add(0, msg);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        PreparedStatement stmt2 = null;
-        try {
-            stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?");
-            stmt2.setInt(1, uid);
-            stmt2.setInt(2, uid_to);
-            stmt2.executeUpdate();
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt2);
-        }
+    public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) {
+        List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm "
+                + "WHERE (user_id=? AND user_id_to=?) "
+                + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20",
+                (rs, rowNum) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    int uuid = rs.getInt(1);
+                    msg.User = new com.juick.User();
+                    msg.User.UID = uuid;
+                    msg.Text = rs.getString(2);
+                    msg.TimestampString = rs.getString(3);
+                    return msg;
+                }, new Object[]{uid, uid_to, uid, uid_to});
+
+        sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 "
+                + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to});
 
         return msgs;
     }
@@ -270,7 +186,7 @@ public class PMQueries {
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -299,7 +215,7 @@ public class PMQueries {
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java
index 1c5e27d1..076019ff 100644
--- a/src/main/java/com/juick/server/TagQueries.java
+++ b/src/main/java/com/juick/server/TagQueries.java
@@ -18,6 +18,8 @@
 package com.juick.server;
 
 import com.juick.Tag;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -28,6 +30,8 @@ import java.util.ArrayList;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
+import org.springframework.jdbc.support.KeyHolder;
 
 /**
  *
@@ -61,26 +65,15 @@ public class TagQueries {
         return ret;
     }
 
-    public static com.juick.Tag getTag(Connection sql, String tag, boolean autoCreate) {
-        com.juick.Tag ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT tag_id,synonym_id,name FROM tags WHERE name=?");
-            stmt.setString(1, tag);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = new com.juick.Tag();
-                ret.TID = rs.getInt(1);
-                ret.SynonymID = rs.getInt(2);
-                ret.Name = rs.getString(3);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+    public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) {
+        Tag ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?",
+                new Object[]{tag}, (rs, rowNum) -> {
+                    Tag ret1 = new Tag();
+                    ret1.TID = rs.getInt(1);
+                    ret1.SynonymID = rs.getInt(2);
+                    ret1.Name = rs.getString(3);
+                    return ret1;
+                });
 
         if (ret == null && autoCreate) {
             ret = new com.juick.Tag();
@@ -91,7 +84,7 @@ public class TagQueries {
         return ret;
     }
 
-    public static List<com.juick.Tag> getTags(Connection sql, String[] tags, boolean autoCreate) {
+    public static List<com.juick.Tag> getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) {
         List<Tag> ret = new ArrayList<>();
 
         for (String tag : tags) {
@@ -127,26 +120,16 @@ public class TagQueries {
         return ret;
     }
 
-    public static int createTag(Connection sql, String name) {
-        int ret = 0;
+    public static int createTag(JdbcTemplate sql, String name) {
+        KeyHolder holder = new GeneratedKeyHolder();
+        sql.update(con -> {
+            PreparedStatement stmt = con.prepareStatement("INSERT INTO tags(name) VALUES (?)",
+                    Statement.RETURN_GENERATED_KEYS);
+                stmt.setString(1, name);                
+                return stmt;
+        }, holder);
 
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO tags(name) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
-            stmt.setString(1, name);
-            stmt.executeUpdate();
-            rs = stmt.getGeneratedKeys();
-            if (rs.first()) {
-                ret = rs.getInt(1);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return ret;
+        return holder.getKey().intValue();        
     }
 
     public static List<com.juick.Tag> getUserTagsAll(Connection sql, int uid) {
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index bf56e974..27ae2870 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -19,7 +19,6 @@ package com.juick.server;
 
 import com.juick.User;
 import org.springframework.jdbc.core.JdbcTemplate;
-import org.springframework.jdbc.core.PreparedStatementCreator;
 import org.springframework.jdbc.core.RowMapper;
 import org.springframework.jdbc.support.GeneratedKeyHolder;
 import org.springframework.jdbc.support.KeyHolder;
@@ -29,10 +28,7 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Random;
-import java.util.UUID;
+import java.util.*;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 
@@ -44,7 +40,7 @@ public class UserQueries {
 
     static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
 
-    private static Logger logger = Logger.getLogger(UserQueries.class.getName());
+    private static final Logger LOGGER = Logger.getLogger(UserQueries.class.getName());
 
     static class UserMapper implements RowMapper<User> {
         @Override
@@ -115,7 +111,7 @@ public class UserQueries {
                 uids.toArray());
     }
 
-    public static boolean fillUsersByID(Connection sql, List<com.juick.User> users) {
+    public static boolean fillUsersByID(JdbcTemplate sql, List<User> users) {
         boolean ret = false;
 
         String uids = "";
@@ -127,54 +123,27 @@ public class UserQueries {
             uids += users.get(i).UID;
         }
 
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + uids + ")");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                int uid = rs.getInt(1);
-                for (int i = 0; i < usersSize; i++) {
-                    if (users.get(i).UID == uid) {
-                        users.get(i).UName = rs.getString(2);
-                        ret = true;
-                    }
-                }
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+        sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")",
+                (rs, num) -> {
+                    User u = users.get(num);
+                    u.UName = rs.getString(2);
+                    return u;
+                });        
 
-        return ret;
+        return true;
     }
 
-    public static List<com.juick.User> getUsersByJID(Connection sql, List<String> jids) {
-        List<com.juick.User> users = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users " +
-                    "INNER JOIN jids ON jids.user_id=users.id " +
-                    "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.User user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-                user.JID = rs.getString(3);
-                users.add(user);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return users;
+    public static List<com.juick.User> getUsersByJID(JdbcTemplate sql, List<String> jids) {
+        return sql.query("SELECT users.id,users.nick,jids.jid FROM users "
+                + "INNER JOIN jids ON jids.user_id=users.id "
+                + "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")",
+                (rs, rowNum) -> {
+                    com.juick.User user = new com.juick.User();
+                    user.UID = rs.getInt(1);
+                    user.UName = rs.getString(2);
+                    user.JID = rs.getString(3);
+                    return user;
+                });
     }
 
     public static String getJIDbyUID(JdbcTemplate sql, int uid) {
@@ -246,7 +215,7 @@ public class UserQueries {
                 }
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -266,7 +235,7 @@ public class UserQueries {
                 ret = rs.getInt(1);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -281,7 +250,7 @@ public class UserQueries {
             stmt.setInt(2, uid);
             stmt.executeUpdate();
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(null, stmt);
         }
@@ -300,7 +269,7 @@ public class UserQueries {
                 ret = rs.getInt(1) < 3600;
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -321,7 +290,7 @@ public class UserQueries {
                 ret = rs.getInt(1) == 1;
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -342,34 +311,18 @@ public class UserQueries {
                 ret = rs.getInt(1) == 1;
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
         return ret;
     }
 
-    public static boolean isInBLAny(Connection sql, int uid, int uid2) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE (user_id=? AND bl_user_id=?) OR (user_id=? AND bl_user_id=?)");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, uid2);
-            stmt.setInt(3, uid2);
-            stmt.setInt(4, uid);
-            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 ret;
+    public static boolean isInBLAny(JdbcTemplate sql, int uid, int uid2) {
+        List<Integer> res = sql.queryForList("SELECT 1 FROM bl_users "
+                + "WHERE (user_id=? AND bl_user_id=?) "
+                + "OR (user_id=? AND bl_user_id=?)", Integer.class, new Object[] {uid, uid2, uid2, uid});
+        return res.get(0) == 1;        
     }
 
     public static List<Integer> checkBL(Connection sql, int visitor, List<Integer> uids) {
@@ -386,7 +339,7 @@ public class UserQueries {
                 ret.add(rs.getInt(1));
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -408,15 +361,15 @@ public class UserQueries {
                 ret = rs.getInt(1) == 1;
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
         return ret;
     }
 
-    public static List<Integer> getUserRead(Connection sql, int uid) {
-        return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
+    public static List<Integer> getUserRead(JdbcTemplate sql, int uid) {
+        return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid);
     }
 
     public static List<com.juick.User> getUserReadLeastPopular(Connection sql, int uid, int cnt) {
@@ -437,7 +390,7 @@ public class UserQueries {
                 users.add(u);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -445,8 +398,8 @@ public class UserQueries {
         return users;
     }
 
-    public static List<Integer> getUserReaders(Connection sql, int uid) {
-        return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
+    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 static List<com.juick.User> getUserBLUsers(Connection sql, int uid) {
@@ -466,7 +419,7 @@ public class UserQueries {
                 users.add(u);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -496,7 +449,7 @@ public class UserQueries {
                 return stmt2.execute();
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         }
         return false;
     }