From bd3892546b51f0d31e959d45c138b009d35b2692 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Tue, 2 Feb 2016 14:36:04 +0300
Subject: spring-jdbc

---
 src/main/java/com/juick/CrosspostComponent.java    |  52 ++-----
 src/main/java/com/juick/PushComponent.java         |  38 +----
 src/main/java/com/juick/http/www/Discover.java     |   5 +-
 src/main/java/com/juick/http/www/Errors.java       |   4 +-
 .../java/com/juick/http/www/FacebookLogin.java     |  81 +++-------
 src/main/java/com/juick/http/www/Help.java         |  20 +--
 src/main/java/com/juick/http/www/Home.java         |   8 +-
 src/main/java/com/juick/http/www/Login.java        |  39 ++---
 src/main/java/com/juick/http/www/Main.java         |  75 +++------
 src/main/java/com/juick/http/www/NewMessage.java   |  14 +-
 src/main/java/com/juick/http/www/PM.java           |  34 ++---
 .../java/com/juick/http/www/PageTemplates.java     | 125 ++++-----------
 src/main/java/com/juick/http/www/RSS.java          |  16 +-
 src/main/java/com/juick/http/www/Settings.java     |  31 ++--
 src/main/java/com/juick/http/www/SignUp.java       | 169 +++++----------------
 src/main/java/com/juick/http/www/TwitterAuth.java  |   9 +-
 src/main/java/com/juick/http/www/User.java         | 155 ++++++++-----------
 src/main/java/com/juick/http/www/UserThread.java   |  39 ++---
 src/main/java/com/juick/http/www/Utils.java        |   4 +-
 .../java/com/juick/http/www/VKontakteLogin.java    |  45 ++----
 .../com/juick/xmpp/extensions/JuickMessage.java    |  21 ++-
 .../java/com/juick/xmpp/s2s/ConnectionRouter.java  |  62 ++++----
 src/main/java/com/juick/xmpp/s2s/JuickBot.java     |  97 +++++-------
 .../java/com/juick/xmpp/s2s/XMPPComponent.java     |  43 +-----
 24 files changed, 402 insertions(+), 784 deletions(-)

(limited to 'src/main')

diff --git a/src/main/java/com/juick/CrosspostComponent.java b/src/main/java/com/juick/CrosspostComponent.java
index 22de81b6..b188fa16 100644
--- a/src/main/java/com/juick/CrosspostComponent.java
+++ b/src/main/java/com/juick/CrosspostComponent.java
@@ -24,6 +24,8 @@ import com.juick.xmpp.Stream;
 import com.juick.xmpp.StreamComponent;
 import com.juick.xmpp.extensions.JuickMessage;
 import com.juick.xmpp.utils.Base64;
+import org.apache.commons.lang3.tuple.Pair;
+import org.springframework.jdbc.core.JdbcTemplate;
 
 import javax.crypto.Mac;
 import javax.crypto.spec.SecretKeySpec;
@@ -61,7 +63,7 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
     public final static String TWITTERURL = "https://api.twitter.com/1.1/statuses/update.json";
     public final static String FBURL = "https://graph.facebook.com/me/feed";
     public final static String VKURL = "https://api.vk.com/method/wall.post";
-    Connection sql;
+    JdbcTemplate sql;
     Stream xmpp;
     String twitter_consumer_key;
     String twitter_consumer_secret;
@@ -80,8 +82,7 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
                 twitter_consumer_key = conf.getProperty("twitter_consumer_key", "");
                 twitter_consumer_secret = conf.getProperty("twitter_consumer_secret", "");
 
-                setupSql(conf.getProperty("mysql_host"), conf.getProperty("mysql_username"),
-                        conf.getProperty("mysql_password", ""), conf.getProperty("mysql_database", ""));
+                sql = (JdbcTemplate) sce.getServletContext().getAttribute("sql");
                 setupXmppComponent(conf.getProperty("xmpp_password", ""));
             } catch (Exception e) {
                 logger.log(Level.SEVERE, e.getMessage(), e);
@@ -91,41 +92,10 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
 
     @Override
     public void contextDestroyed(ServletContextEvent sce) {
-        // Now deregister JDBC drivers in this context's ClassLoader:
-        // Get the webapp's ClassLoader
-        ClassLoader cl = Thread.currentThread().getContextClassLoader();
-        // Loop through all drivers
-        Enumeration<Driver> drivers = DriverManager.getDrivers();
-        while (drivers.hasMoreElements()) {
-            Driver driver = drivers.nextElement();
-            if (driver.getClass().getClassLoader() == cl) {
-                // This driver was registered by the webapp's ClassLoader, so deregister it:
-                try {
-                    logger.info(String.format("Deregistering JDBC driver %s", driver.toString()));
-                    DriverManager.deregisterDriver(driver);
-                } catch (SQLException ex) {
-                    logger.log(Level.SEVERE, String.format("Error deregistering JDBC driver %s", driver), ex);
-                }
-            } else {
-                // driver was not registered by the webapp's ClassLoader and may be in use elsewhere
-                logger.log(Level.SEVERE, String.format("Not deregistering JDBC driver %s as it does not belong to this webapp's ClassLoader", driver));
-            }
-        }
         executorService.shutdown();
         logger.info("component destroyed");
     }
 
-    public void setupSql(String host, String username, String password, String database) {
-        try {
-            Class.forName("com.mysql.jdbc.Driver");
-            sql = DriverManager.getConnection(
-                    String.format("jdbc:mysql://%s/%s?autoReconnect=true&user=%s&password=%s",
-                            host, database, username, password));
-        } catch (SQLException | ClassNotFoundException e) {
-            logger.log(Level.SEVERE, e.getMessage(), e);
-        }
-    }
-
     public void setupXmppComponent(String password) {
         try {
             Socket socket = new Socket("localhost", 5347);
@@ -198,8 +168,8 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
     }
 
     public boolean vkontaktePost(com.juick.Message jmsg) {
-        String tokens[] = CrosspostQueries.getVKTokens(sql, jmsg.getUser().getUID());
-        if (tokens == null || tokens.length != 2) {
+        Pair<String, String> tokens = CrosspostQueries.getVKTokens(sql, jmsg.getUser().getUID());
+        if (tokens == null) {
             return false;
         }
 
@@ -209,7 +179,7 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
 
         boolean ret = false;
         try {
-            String body = "owner_id=" + tokens[0] + "&access_token=" + URLEncoder.encode(tokens[1], "UTF-8") + "&from_group=1&message=" + URLEncoder.encode(status, "UTF-8");
+            String body = "owner_id=" + tokens.getLeft() + "&access_token=" + URLEncoder.encode(tokens.getRight(), "UTF-8") + "&from_group=1&message=" + URLEncoder.encode(status, "UTF-8");
 
             HttpsURLConnection conn = (HttpsURLConnection) new URL(VKURL).openConnection();
             conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
@@ -235,12 +205,12 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
     }
 
     public boolean twitterPost(com.juick.Message jmsg) {
-        String tokens[] = CrosspostQueries.getTwitterTokens(sql, jmsg.getUser().getUID());
-        if (tokens == null || tokens.length != 2) {
+        Pair<String, String> tokens = CrosspostQueries.getTwitterTokens(sql, jmsg.getUser().getUID());
+        if (tokens == null) {
             return false;
         }
-        String token = percentEncode(tokens[0]);
-        String token_secret = percentEncode(tokens[1]);
+        String token = percentEncode(tokens.getLeft());
+        String token_secret = percentEncode(tokens.getRight());
 
         logger.info("TWITTER: #" + jmsg.getMID());
 
diff --git a/src/main/java/com/juick/PushComponent.java b/src/main/java/com/juick/PushComponent.java
index 0c3d70d7..732fe20d 100644
--- a/src/main/java/com/juick/PushComponent.java
+++ b/src/main/java/com/juick/PushComponent.java
@@ -45,6 +45,8 @@ import org.apache.http.message.BasicNameValuePair;
 import org.apache.http.util.EntityUtils;
 import org.apache.http.util.TextUtils;
 import org.json.JSONObject;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
 import javax.servlet.ServletContextEvent;
 import javax.servlet.ServletContextListener;
@@ -72,7 +74,7 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
     private ExecutorService executorService;
     String wns_application_sip;
     String wns_client_secret;
-    Connection sql;
+    JdbcTemplate sql;
     Socket socket;
     Stream xmpp;
     Sender GCMSender;
@@ -91,8 +93,7 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
                 wns_client_secret = conf.getProperty("wns_client_secret", "");
                 GCMSender = new Sender(conf.getProperty("gcm_key"));
 
-                setupSql(conf.getProperty("mysql_host"), conf.getProperty("mysql_username"),
-                        conf.getProperty("mysql_password", ""), conf.getProperty("mysql_database", ""));
+                setupSql(conf.getProperty("datasource_driver"), conf.getProperty("datasource_url"));
                 setupXmppComponent(new JID("", conf.getProperty("push_jid"), ""), conf.getProperty("xmpp_host", "localhost"),
                         Integer.parseInt(conf.getProperty("xmpp_port", "5347")), conf.getProperty("push_xmpp_password", ""));
             } catch (IOException e) {
@@ -103,39 +104,12 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
 
     @Override
     public void contextDestroyed(ServletContextEvent sce) {
-        // Now deregister JDBC drivers in this context's ClassLoader:
-        // Get the webapp's ClassLoader
-        ClassLoader cl = Thread.currentThread().getContextClassLoader();
-        // Loop through all drivers
-        Enumeration<Driver> drivers = DriverManager.getDrivers();
-        while (drivers.hasMoreElements()) {
-            Driver driver = drivers.nextElement();
-            if (driver.getClass().getClassLoader() == cl) {
-                // This driver was registered by the webapp's ClassLoader, so deregister it:
-                try {
-                    logger.info(String.format("Deregistering JDBC driver %s", driver.toString()));
-                    DriverManager.deregisterDriver(driver);
-                } catch (SQLException ex) {
-                    logger.log(Level.SEVERE, String.format("Error deregistering JDBC driver %s", driver), ex);
-                }
-            } else {
-                // driver was not registered by the webapp's ClassLoader and may be in use elsewhere
-                logger.log(Level.SEVERE, String.format("Not deregistering JDBC driver %s as it does not belong to this webapp's ClassLoader", driver));
-            }
-        }
         executorService.shutdown();
         logger.info("component destroyed");
     }
 
-    public void setupSql(String host, String username, String password, String database) {
-        try {
-            Class.forName("com.mysql.jdbc.Driver");
-            sql = DriverManager.getConnection(
-                    String.format("jdbc:mysql://%s/%s?autoReconnect=true&user=%s&password=%s",
-                            host, database, username, password));
-        } catch (SQLException | ClassNotFoundException e) {
-            logger.log(Level.SEVERE, e.getMessage(), e);
-        }
+    public void setupSql(String driverClass, String url) {
+
     }
 
     public void setupXmppComponent(JID jid, String host, int port, String password) {
diff --git a/src/main/java/com/juick/http/www/Discover.java b/src/main/java/com/juick/http/www/Discover.java
index e7d85d8a..779e6ba0 100644
--- a/src/main/java/com/juick/http/www/Discover.java
+++ b/src/main/java/com/juick/http/www/Discover.java
@@ -20,6 +20,8 @@ package com.juick.http.www;
 import com.juick.server.AdsQueries;
 import com.juick.server.MessagesQueries;
 import com.juick.server.TagQueries;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.net.URLDecoder;
@@ -37,7 +39,8 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Discover {
 
-    protected void doGet(Connection sql, Connection sqlSearch, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, JdbcTemplate sqlSearch, HttpServletRequest request,
+                         HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         String paramTagStr = URLDecoder.decode(request.getRequestURI().substring(5), "UTF-8");
diff --git a/src/main/java/com/juick/http/www/Errors.java b/src/main/java/com/juick/http/www/Errors.java
index f65b6201..f8c347b7 100644
--- a/src/main/java/com/juick/http/www/Errors.java
+++ b/src/main/java/com/juick/http/www/Errors.java
@@ -1,5 +1,7 @@
 package com.juick.http.www;
 
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.sql.Connection;
@@ -15,7 +17,7 @@ public class Errors {
 
     public static String tagsHTML = null;
 
-    public static void doGet404(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    public static void doGet404(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (tagsHTML == null) {
diff --git a/src/main/java/com/juick/http/www/FacebookLogin.java b/src/main/java/com/juick/http/www/FacebookLogin.java
index fcb08d6c..61464f52 100644
--- a/src/main/java/com/juick/http/www/FacebookLogin.java
+++ b/src/main/java/com/juick/http/www/FacebookLogin.java
@@ -18,20 +18,19 @@
 package com.juick.http.www;
 
 import com.juick.server.UserQueries;
+import org.json.JSONObject;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
+
+import javax.servlet.ServletException;
+import javax.servlet.http.Cookie;
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
 import java.net.URLEncoder;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
 import java.util.UUID;
 import java.util.logging.Level;
 import java.util.logging.Logger;
-import javax.servlet.ServletException;
-import javax.servlet.http.Cookie;
-import javax.servlet.http.HttpServletRequest;
-import javax.servlet.http.HttpServletResponse;
-import org.json.JSONObject;
 
 /**
  *
@@ -45,7 +44,8 @@ public class FacebookLogin {
     private static final String FACEBOOK_SECRET = "95813bfb6ab8f473410c50d4f971649e";
     private static final String FACEBOOK_REDIRECT = "http://juick.com/_fblogin";
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response)
+            throws ServletException, IOException {
         String fbstate;
 
         String code = request.getParameter("code");
@@ -132,61 +132,22 @@ public class FacebookLogin {
         }
     }
 
-    private int getUIDbyFBID(Connection sql, long fbID) {
-        int uid = 0;
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    private int getUIDbyFBID(JdbcTemplate sql, long fbID) {
         try {
-            stmt = sql.prepareStatement("SELECT user_id FROM facebook WHERE fb_id=? AND user_id IS NOT NULL");
-            stmt.setLong(1, fbID);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                uid = rs.getInt(1);
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT user_id FROM facebook WHERE fb_id=? AND user_id IS NOT NULL",
+                    Integer.class, fbID);
+        } catch (EmptyResultDataAccessException e) {
+            return 0;
         }
-        return uid;
     }
 
-    private boolean insertDB(Connection sql, long fbID, String loginhash, String token, String fbName, String fbLink) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO facebook(fb_id,loginhash,access_token,fb_name,fb_link) VALUES (?,?,?,?,?)");
-            stmt.setLong(1, fbID);
-            stmt.setString(2, loginhash);
-            stmt.setString(3, token);
-            stmt.setString(4, fbName);
-            stmt.setString(5, fbLink);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean insertDB(JdbcTemplate sql, long fbID, String loginhash, String token, String fbName, String fbLink) {
+        return sql.update("INSERT INTO facebook(fb_id,loginhash,access_token,fb_name,fb_link) VALUES (?,?,?,?,?)",
+            fbID, loginhash, token, fbName, fbLink) > 0;
     }
 
-    private boolean updateDB(Connection sql, long fbID, String token, String fbName, String fbLink) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("UPDATE facebook SET access_token=?,fb_name=?,fb_link=? WHERE fb_id=?");
-            stmt.setString(1, token);
-            stmt.setString(2, fbName);
-            stmt.setString(3, fbLink);
-            stmt.setLong(4, fbID);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean updateDB(JdbcTemplate sql, long fbID, String token, String fbName, String fbLink) {
+        return sql.update("UPDATE facebook SET access_token=?,fb_name=?,fb_link=? WHERE fb_id=?",
+            token, fbName, fbLink, fbID) > 0;
     }
 }
diff --git a/src/main/java/com/juick/http/www/Help.java b/src/main/java/com/juick/http/www/Help.java
index 438fc3a9..e8b7532e 100644
--- a/src/main/java/com/juick/http/www/Help.java
+++ b/src/main/java/com/juick/http/www/Help.java
@@ -17,15 +17,12 @@
  */
 package com.juick.http.www;
 
-import java.io.BufferedReader;
-import java.io.File;
-import java.io.FileReader;
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.*;
 
 /**
  *
@@ -33,11 +30,13 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Help {
 
-    protected void doRedirectToHelpIndex(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doRedirectToHelpIndex(JdbcTemplate sql, HttpServletRequest request,
+                                         HttpServletResponse response) throws ServletException, IOException {
         Utils.sendTemporaryRedirect(response, "/help/ru/");
     }
 
-    protected void doGetHelp(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGetHelp(JdbcTemplate sql, HttpServletRequest request,
+                             HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         String path[] = request.getRequestURI().split("/");
@@ -64,8 +63,7 @@ public class Help {
         }
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             PageTemplates.pageHead(out, "Помощь", null);
             PageTemplates.pageNavigation(out, visitor, null);
 
@@ -81,8 +79,6 @@ public class Help {
 
             PageTemplates.pageFooter(request, out, visitor, false);
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
diff --git a/src/main/java/com/juick/http/www/Home.java b/src/main/java/com/juick/http/www/Home.java
index 8dc3e70c..3688b58e 100644
--- a/src/main/java/com/juick/http/www/Home.java
+++ b/src/main/java/com/juick/http/www/Home.java
@@ -19,6 +19,8 @@ package com.juick.http.www;
 
 import com.juick.server.AdsQueries;
 import com.juick.server.MessagesQueries;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.net.URLEncoder;
@@ -35,7 +37,8 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Home {
 
-    protected void doGet(Connection sql, Connection sqlSearch, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, JdbcTemplate sqlSearch, HttpServletRequest request,
+                         HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
         int paramBefore = 0;
         String paramBeforeStr = request.getParameter("before");
         if (paramBeforeStr != null) {
@@ -107,7 +110,8 @@ public class Home {
             }
             PageTemplates.pageHead(out, title, head);
             PageTemplates.pageNavigation(out, visitor, paramSearch);
-            PageTemplates.pageHomeColumn(out, sql, visitor, paramShow == null && paramBefore == 0 && paramSearch == null && visitor == null);
+            PageTemplates.pageHomeColumn(out, sql, visitor, paramShow == null
+                    && paramBefore == 0 && paramSearch == null && visitor == null);
 
             out.println("<section id=\"content\">");
 
diff --git a/src/main/java/com/juick/http/www/Login.java b/src/main/java/com/juick/http/www/Login.java
index 3e9c5e09..00775fc1 100644
--- a/src/main/java/com/juick/http/www/Login.java
+++ b/src/main/java/com/juick/http/www/Login.java
@@ -17,15 +17,14 @@
  */
 package com.juick.http.www;
 
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.SQLException;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import javax.servlet.ServletException;
 import javax.servlet.http.Cookie;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
 
 /**
  *
@@ -33,7 +32,8 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Login {
 
-    protected void doGetLoginForm(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGetLoginForm(JdbcTemplate sql, HttpServletRequest request,
+                                  HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
         if (visitor != null) {
             Utils.sendTemporaryRedirect(response, "/");
@@ -41,8 +41,7 @@ public class Login {
         }
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             out.println("<!DOCTYPE html>");
             out.println("<html>");
             out.println("<head>");
@@ -182,12 +181,11 @@ public class Login {
 
             out.println("</body>");
             out.println("</html>");
-        } finally {
-            out.close();
         }
     }
 
-    protected void doGetLogin(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGetLogin(JdbcTemplate sql, HttpServletRequest request,
+                              HttpServletResponse response) throws ServletException, IOException {
         String hash = request.getQueryString();
         if (hash.length() > 32) {
             response.sendError(400);
@@ -204,7 +202,8 @@ public class Login {
         }
     }
 
-    protected void doPostLogin(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doPostLogin(JdbcTemplate sql, HttpServletRequest request,
+                               HttpServletResponse response) throws ServletException, IOException {
         String username = request.getParameter("username");
         String password = request.getParameter("password");
         if (username == null || password == null || username.length() > 32 || password.isEmpty()) {
@@ -230,20 +229,10 @@ public class Login {
         }
     }
 
-    protected void doGetLogout(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGetLogout(JdbcTemplate sql, HttpServletRequest request,
+                               HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
-        if (visitor != null) {
-            PreparedStatement stmt = null;
-            try {
-                stmt = sql.prepareStatement("DELETE FROM logins WHERE user_id=?");
-                stmt.setInt(1, visitor.getUID());
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                System.err.println(e);
-            } finally {
-                Utils.finishSQL(null, stmt);
-            }
-        }
+        sql.update("DELETE FROM logins WHERE user_id=?", visitor.getUID());
 
         Cookie c = new Cookie("hash", "-");
         c.setDomain(".juick.com");
diff --git a/src/main/java/com/juick/http/www/Main.java b/src/main/java/com/juick/http/www/Main.java
index cabd50af..85abed00 100644
--- a/src/main/java/com/juick/http/www/Main.java
+++ b/src/main/java/com/juick/http/www/Main.java
@@ -21,6 +21,8 @@ import com.juick.server.UserQueries;
 import com.juick.xmpp.JID;
 import com.juick.xmpp.Stream;
 import com.juick.xmpp.StreamComponent;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.DriverManagerDataSource;
 import ru.sape.Sape;
 
 import javax.servlet.ServletException;
@@ -45,9 +47,8 @@ import java.util.Properties;
 @MultipartConfig(fileSizeThreshold = 1024 * 1024, maxRequestSize = 1024 * 1024 * 10)
 public class Main extends HttpServlet implements Stream.StreamListener {
 
-    Connection sql;
-    Connection sqlSearch;
-    String sqlSearchConnStr = "jdbc:mysql://127.0.0.1:9306?autoReconnect=true&useUnicode=yes&characterEncoding=utf8&maxAllowedPacket=512000";
+    JdbcTemplate sql;
+    JdbcTemplate sqlSearch;
     Stream xmpp;
     Home home = new Home();
     Discover discover = new Discover();
@@ -72,10 +73,16 @@ public class Main extends HttpServlet implements Stream.StreamListener {
             Properties conf = new Properties();
             conf.load(getServletContext().getResourceAsStream("WEB-INF/juick.conf"));
 
-            Class.forName("com.mysql.jdbc.Driver");
-            sql = DriverManager.getConnection("jdbc:mysql://localhost/juick?autoReconnect=true&user=" + conf.getProperty("mysql_username", "") + "&password=" + conf.getProperty("mysql_password", ""));
-            sqlSearch = null; // init this on search, timeout is too low
-
+            DriverManagerDataSource dataSource = new DriverManagerDataSource();
+            dataSource.setDriverClassName(conf.getProperty("datasource_driver", "com.mysql.jdbc.Driver"));
+            dataSource.setUrl(conf.getProperty("datasource_url"));
+            sql = new JdbcTemplate(dataSource);
+            getServletContext().setAttribute("sql", sql);
+            DriverManagerDataSource searchDatasource = new DriverManagerDataSource();
+            searchDatasource.setDriverClassName("com.mysql.jdbc.Driver");
+            searchDatasource.setUrl("jdbc:mysql://127.0.0.1:9306?autoReconnect=true&useUnicode=yes&characterEncoding=utf8&maxAllowedPacket=512000");
+            sqlSearch = new JdbcTemplate(searchDatasource);
+            getServletContext().setAttribute("sqlSearch", sqlSearch);
             setupXmppComponent(conf.getProperty("xmpp_password"));
             twitterAuth = new TwitterAuth(conf.getProperty("twitter_consumer_key"),
                     conf.getProperty("twitter_consumer_secret"));
@@ -84,32 +91,13 @@ public class Main extends HttpServlet implements Stream.StreamListener {
             log(null, e);
         }
     }
-    public void closeSqlSearch() {
-        if (sqlSearch != null) {
-            try {
-                sqlSearch.close();
-                sqlSearch = null;
-            } catch (SQLException e) {
-                log("An error on closing sql search connection", e);
-            }
-        }
-    };
-    public Connection getSqlSearch() {
-        if (sqlSearch == null) {
-            try {
-                sqlSearch = DriverManager.getConnection(sqlSearchConnStr, "", "");
-            }
-            catch (Exception e) {
-                log("Couldn't open sqlSearch connection",e);
-            }
-        }
-        return sqlSearch;
-    }
+
     public void setupXmppComponent(final String password) {
         Thread thr = new Thread(() -> {
             try {
                 Socket socket = new Socket("localhost", 5347);
-                xmpp = new StreamComponent(new JID("", "www.juick.com", ""), socket.getInputStream(), socket.getOutputStream(), password);
+                xmpp = new StreamComponent(new JID("", "www.juick.com", ""), socket.getInputStream(),
+                        socket.getOutputStream(), password);
                 xmpp.addListener(Main.this);
                 xmpp.startParsing();
             } catch (IOException e) {
@@ -127,26 +115,6 @@ public class Main extends HttpServlet implements Stream.StreamListener {
         log("XMPP STREAM READY");
     }
 
-    @Override
-    public void destroy() {
-        super.destroy();
-        if (sql != null) {
-            try {
-                sql.close();
-                sql = null;
-            } catch (SQLException e) {
-                log(null, e);
-            }
-        }
-        if (sqlSearch != null) {
-            try {
-                sqlSearch.close();
-                sqlSearch = null;
-            } catch (SQLException e) {
-                log(null, e);
-            }
-        }
-    }
 
     /**
      * Handles the HTTP <code>GET</code> method.
@@ -168,8 +136,7 @@ public class Main extends HttpServlet implements Stream.StreamListener {
                 Utils.sendPermanentRedirect(response, "/tag/" + URLEncoder.encode(tag, "UTF-8"));
             } else {
                 com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
-                home.doGet(sql, getSqlSearch(), request, response, visitor);
-                closeSqlSearch();
+                home.doGet(sql, sqlSearch, request, response, visitor);
             }
         } else if (uri.equals("/post")) {
             com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
@@ -226,8 +193,7 @@ public class Main extends HttpServlet implements Stream.StreamListener {
         } else if (uri.startsWith("/help/")) {
             help.doGetHelp(sql, request, response);
         } else if (uri.startsWith("/tag/")) {
-            discover.doGet(sql, getSqlSearch(), request, response);
-            closeSqlSearch();
+            discover.doGet(sql, sqlSearch, request, response);
         } else if (uri.matches("^/\\d+$")) {
             String strID = request.getRequestURI().substring(1);
             int mid = 0;
@@ -255,8 +221,7 @@ public class Main extends HttpServlet implements Stream.StreamListener {
             com.juick.User user = com.juick.server.UserQueries.getUserByName(sql, uriparts[1]);
             if (user != null && user.getUName().equals(uriparts[1]) && !user.Banned) {
                 if (uriparts.length == 2) { // http://juick.com/username/
-                    pagesUser.doGetBlog(sql, getSqlSearch(), request, response, user);
-                    closeSqlSearch();
+                    pagesUser.doGetBlog(sql, sqlSearch, request, response, user);
                 } else if (uriparts[2].equals("tags")) {
                     pagesUser.doGetTags(sql, request, response, user);
                 } else if (uriparts[2].equals("friends")) {
diff --git a/src/main/java/com/juick/http/www/NewMessage.java b/src/main/java/com/juick/http/www/NewMessage.java
index 86725db7..22d743b3 100644
--- a/src/main/java/com/juick/http/www/NewMessage.java
+++ b/src/main/java/com/juick/http/www/NewMessage.java
@@ -30,6 +30,8 @@ import com.juick.xmpp.extensions.JuickMessage;
 import com.juick.xmpp.extensions.JuickUser;
 import com.juick.xmpp.extensions.Nickname;
 import com.juick.xmpp.extensions.XOOB;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.io.UnsupportedEncodingException;
@@ -48,7 +50,7 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class NewMessage {
 
-    protected void doGetNewMessage(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
+    protected void doGetNewMessage(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
         response.setContentType("text/html; charset=UTF-8");
         PrintWriter out = response.getWriter();
         try {
@@ -88,7 +90,7 @@ public class NewMessage {
         }
     }
 
-    void printUserTags(Connection sql, PrintWriter out, com.juick.User visitor) {
+    void printUserTags(JdbcTemplate sql, PrintWriter out, com.juick.User visitor) {
         List<Tag> tags = TagQueries.getUserTagsAll(sql, visitor.getUID());
 
         if (tags.isEmpty()) {
@@ -134,7 +136,7 @@ public class NewMessage {
         out.println("</p>");
     }
 
-    public void doPostMessage(Connection sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
+    public void doPostMessage(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
         String body = request.getParameter("body");
         if (body == null || body.length() < 1 || body.length() > 4096) {
             response.sendError(400);
@@ -143,7 +145,7 @@ public class NewMessage {
         body = body.replace("\r", "");
 
         String tagsStr = request.getParameter("tags");
-        List<com.juick.Tag> tags = new ArrayList<Tag>();
+        List<com.juick.Tag> tags = new ArrayList<>();
         String tagsArr[] = new String[1];
         if (tagsStr != null && !tagsStr.isEmpty()) {
             tagsArr = tagsStr.split("[ \\,]");
@@ -282,7 +284,7 @@ public class NewMessage {
         }
     }
 
-    public void doPostComment(Connection sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
+    public void doPostComment(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
         int mid = Utils.parseInt(request.getParameter("mid"), 0);
         if (mid == 0) {
             response.sendError(400);
@@ -383,7 +385,7 @@ public class NewMessage {
         Utils.sendTemporaryRedirect(response, "/" + msg.getUser().getUName() + "/" + mid + "#" + ridnew);
     }
 
-    public void doPostRecomm(Connection sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
+    public void doPostRecomm(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
         int mid = Utils.parseInt(request.getParameter("mid"), 0);
         if (mid == 0) {
             response.sendError(400);
diff --git a/src/main/java/com/juick/http/www/PM.java b/src/main/java/com/juick/http/www/PM.java
index a9505bdb..1ee0b112 100644
--- a/src/main/java/com/juick/http/www/PM.java
+++ b/src/main/java/com/juick/http/www/PM.java
@@ -23,14 +23,15 @@ import com.juick.xmpp.JID;
 import com.juick.xmpp.Message;
 import com.juick.xmpp.Stream;
 import com.juick.xmpp.extensions.JuickMessage;
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.util.ArrayList;
-import java.util.List;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
+import java.text.SimpleDateFormat;
+import java.util.List;
 
 /**
  *
@@ -38,7 +39,9 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class PM {
 
-    protected void doGetInbox(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
+    private static final SimpleDateFormat sdfSQL = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+    protected void doGetInbox(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
         /*
         int paramBefore = 0;
         String paramBeforeStr = request.getParameter("before");
@@ -54,8 +57,7 @@ public class PM {
         List<com.juick.Message> msgs = PMQueries.getLastPMInbox(sql, visitor.getUID());
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             PageTemplates.pageHead(out, title, null);
             PageTemplates.pageNavigation(out, visitor, null);
             PageTemplates.pageHomeColumn(out, sql, visitor);
@@ -73,7 +75,7 @@ public class PM {
                     out.println("    <div class=\"msg-avatar\"><a href=\"/" + msg.getUser().getUName() + "/\"><img src=\"//i.juick.com/a/" + msg.getUser().getUID() + ".png\" alt=\"" + msg.getUser().getUName() + "\"/></a></div>");
                     out.println("    <div class=\"msg-cont\">");
                     out.println("      <div class=\"msg-header\"><a href=\"/" + msg.getUser().getUName() + "/\">@" + msg.getUser().getUName() + "</a>:</div>");
-                    out.println("      <div class=\"msg-ts\"><a href=\"#\" onclick=\"return false\" title=\"" + msg.TimestampString + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.TimestampString) + "</a></div>");
+                    out.println("      <div class=\"msg-ts\"><a href=\"#\" onclick=\"return false\" title=\"" + sdfSQL.format(msg.getDate()) + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.getDate()) + "</a></div>");
                     out.println("      <div class=\"msg-txt\">" + txt + "</div>");
 
                     out.println("      <form action=\"/pm/send\" method=\"POST\" enctype=\"multipart/form-data\"><input type=\"hidden\" name=\"uname\" value=\"" + msg.getUser().getUName() + "\"/>");
@@ -97,12 +99,10 @@ public class PM {
 
             PageTemplates.pageFooter(request, out, visitor, false);
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
-    protected void doGetSent(Connection sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
+    protected void doGetSent(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, com.juick.User visitor) throws ServletException, IOException {
         /*
         int paramBefore = 0;
         String paramBeforeStr = request.getParameter("before");
@@ -123,8 +123,7 @@ public class PM {
         }
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             PageTemplates.pageHead(out, title, null);
             PageTemplates.pageNavigation(out, visitor, null);
             PageTemplates.pageHomeColumn(out, sql, visitor);
@@ -150,7 +149,7 @@ public class PM {
                     out.println("    <div class=\"msg-avatar\"><img src=\"//i.juick.com/a/" + visitor.getUID() + ".png\"/></div>");
                     out.println("    <div class=\"msg-cont\">");
                     out.println("      <div class=\"msg-header\">→ <a href=\"/" + msg.getUser().getUName() + "/\">@" + msg.getUser().getUName() + "</a>:</div>");
-                    out.println("      <div class=\"msg-ts\"><a href=\"#\" onclick=\"return false\" title=\"" + msg.TimestampString + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.TimestampString) + "</a></div>");
+                    out.println("      <div class=\"msg-ts\"><a href=\"#\" onclick=\"return false\" title=\"" + sdfSQL.format(msg.getDate()) + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.getDate()) + "</a></div>");
                     out.println("      <div class=\"msg-txt\">" + txt + "</div>");
                     out.println("    </div>");
                     out.println("  </li>");
@@ -169,12 +168,11 @@ public class PM {
 
             PageTemplates.pageFooter(request, out, visitor, false);
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
-    public void doPostPM(Connection sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp, com.juick.User visitor) throws ServletException, IOException {
+    public void doPostPM(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response, Stream xmpp,
+                         com.juick.User visitor) throws ServletException, IOException {
         String uname = request.getParameter("uname");
         if (uname.startsWith("@")) {
             uname = uname.substring(1);
diff --git a/src/main/java/com/juick/http/www/PageTemplates.java b/src/main/java/com/juick/http/www/PageTemplates.java
index 2f9c8976..0682ed04 100644
--- a/src/main/java/com/juick/http/www/PageTemplates.java
+++ b/src/main/java/com/juick/http/www/PageTemplates.java
@@ -21,13 +21,13 @@ import com.juick.Message;
 import com.juick.Tag;
 import com.juick.server.MessagesQueries;
 import com.juick.server.UserQueries;
+import org.springframework.jdbc.core.JdbcTemplate;
+import ru.sape.Sape;
+
+import javax.servlet.http.HttpServletRequest;
 import java.io.PrintWriter;
 import java.io.UnsupportedEncodingException;
 import java.net.URLEncoder;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Calendar;
@@ -35,8 +35,7 @@ import java.util.Date;
 import java.util.List;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
-import javax.servlet.http.HttpServletRequest;
-import ru.sape.Sape;
+import java.util.stream.Collectors;
 
 /**
  *
@@ -142,11 +141,11 @@ public class PageTemplates {
         out.println("</script>");
     }
 
-    public static void pageHomeColumn(PrintWriter out, Connection sql, com.juick.User visitor) {
+    public static void pageHomeColumn(PrintWriter out, JdbcTemplate sql, com.juick.User visitor) {
         pageHomeColumn(out, sql, visitor, false);
     }
 
-    public static void pageHomeColumn(PrintWriter out, Connection sql, com.juick.User visitor, boolean showAdv) {
+    public static void pageHomeColumn(PrintWriter out, JdbcTemplate sql, com.juick.User visitor, boolean showAdv) {
         if (tagsHTML == null) {
             tagsHTML = PageTemplates.getPopularTags(sql, 80);
         }
@@ -163,64 +162,13 @@ public class PageTemplates {
         out.println("</aside>");
     }
 
-    public static String getPopularTags(Connection sql, int cnt) {
-        String ret = "";
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT name FROM tags WHERE top=1 ORDER BY name ASC");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                if (!ret.isEmpty()) {
-                    ret += " ";
-                }
-                try {
-                    ret += "<a href=\"/tag/" + URLEncoder.encode(rs.getString(1), "UTF-8") + "\">" + Utils.encodeHTML(rs.getString(1)) + "</a>";
-                } catch (UnsupportedEncodingException e) {
-                }
-
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+    public static String getPopularTags(JdbcTemplate sql, int cnt) {
+        List<String> tags = sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class);
+        if (!tags.isEmpty()) {
+            return tags.stream().map(tag -> "<a href=\"/tag/" + URLEncoder.encode(tag) + "\">" + Utils.encodeHTML(tag) + "</a>")
+                    .collect(Collectors.joining(" "));
         }
-
-        return ret;
-    }
-
-    public static void printContestRating(PrintWriter out, Connection sql) {
-        out.println("<hr/>");
-        out.println("<!--noindex-->");
-        out.println("<p style=\"font-size: 14pt\">Кто <a href=\"/help/ru/contest\">выиграет iPod</a>?</p>");
-        out.println("<table width=\"100%\">");
-
-        int i = 0;
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT users.id,users.nick,COUNT(users_refs.user_id) AS cnt FROM users INNER JOIN users_refs ON users.id=users_refs.ref WHERE users.id>2 GROUP BY users_refs.ref ORDER BY cnt DESC LIMIT 10");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                String uname = rs.getString(2);
-                if (i == 0) {
-                    out.println("  <tr><td><b><a href=\"/" + uname + "/\">" + uname + "</a></b></td><td align=\"right\"><b>" + rs.getInt(3) + "</b></td></tr>");
-                } else {
-                    out.println("  <tr><td><a href=\"/" + uname + "/\">" + uname + "</a></td><td align=\"right\">" + rs.getInt(3) + "</td></tr>");
-                }
-                i++;
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        out.println("</table>");
-        out.println("<!--/noindex-->");
+        return "";
     }
 
     public static void pageFooter(HttpServletRequest request, PrintWriter out, com.juick.User visitor, boolean sapeon) {
@@ -312,7 +260,7 @@ public class PageTemplates {
         return ret;
     }
 
-    public static String formatDate(int minutes, String fulldate) {
+    public static String formatDate(int minutes, Date fulldate) {
         if (minutes < 1) {
             return "сейчас";
         } else if (minutes < 60) {
@@ -351,40 +299,24 @@ public class PageTemplates {
             }
             return days + " " + unit + " назад";
         } else {
-            String ret = fulldate;
-            synchronized (sdfSQL) {
-                try {
-                    Date pDate = sdfSQL.parse(fulldate);
-                    Calendar c = Calendar.getInstance();
-                    int curyear = c.get(Calendar.YEAR);
-                    c.setTime(pDate);
-                    if (c.get(Calendar.YEAR) == curyear) {
-                        ret = sdfSimple.format(pDate);
-                    } else {
-                        ret = sdfFull.format(pDate);
-                    }
-                } catch (Exception e) {
-                    System.err.println("PARSE EXCEPTION: " + fulldate);
-                }
+            String ret;
+            Calendar c = Calendar.getInstance();
+            int curyear = c.get(Calendar.YEAR);
+            c.setTime(fulldate);
+            if (c.get(Calendar.YEAR) == curyear) {
+                ret = sdfSimple.format(fulldate);
+            } else {
+                ret = sdfFull.format(fulldate);
             }
             return ret;
         }
     }
 
-    public static String formatJSLocalTime(String ts) {
-        String ret = "";
-        synchronized (sdfSQL) {
-            try {
-                Date date = sdfSQL.parse(ts);
-                ret = "<script type=\"text/javascript\">"
-                        + "var d=new Date(" + date.getTime() + ");"
+    public static String formatJSLocalTime(Date ts) {
+        return "<script type=\"text/javascript\">"
+                        + "var d=new Date(" + ts.getTime() + ");"
                         + "document.write((d.getDate()<10?'0':'')+d.getDate()+'.'+(d.getMonth()<9?'0':'')+(d.getMonth()+1)+'.'+d.getFullYear()+' '+(d.getHours()<10?'0':'')+d.getHours()+':'+(d.getMinutes()<10?'0':'')+d.getMinutes());"
                         + "</script>";
-            } catch (Exception e) {
-                System.err.println("PARSE EXCEPTION: " + ts);
-            }
-        }
-        return ret;
     }
 
     public static String formatReplies(int replies) {
@@ -492,7 +424,8 @@ public class PageTemplates {
         return msg;
     }
 
-    public static void printMessages(PrintWriter out, Connection sql, com.juick.User user, List<Integer> mids, com.juick.User visitor, int YandexID, int ad_mid) {
+    public static void printMessages(PrintWriter out, JdbcTemplate sql, com.juick.User user,
+                                     List<Integer> mids, com.juick.User visitor, int YandexID, int ad_mid) {
         List<com.juick.Message> msgs = MessagesQueries.getMessages(sql, mids);
 
         for (int i = 0; i < msgs.size(); i++) {
@@ -504,7 +437,7 @@ public class PageTemplates {
             }
         }
 
-        List<Integer> blUIDs = new ArrayList<Integer>(20);
+        List<Integer> blUIDs = new ArrayList<>(20);
         if (visitor != null) {
             for (Message msg : msgs) {
                 blUIDs.add(msg.getUser().getUID());
@@ -542,7 +475,7 @@ public class PageTemplates {
             out.println("<article data-mid=\"" + msg.getMID() + "\">");
             out.println("  <aside><a href=\"/" + msg.getUser().getUName() + "/\"><img src=\"//i.juick.com/a/" + msg.getUser().getUID() + ".png\" alt=\"" + msg.getUser().getUName() + "\"/></a></aside>");
             out.println("  <header class=\"u\">@<a href=\"/" + msg.getUser().getUName() + "/\">" + msg.getUser().getUName() + "</a>:" + tagsStr + "</header>");
-            out.println("  <header class=\"t\"><a href=\"/" + msg.getUser().getUName() + "/" + msg.getMID() + "\"><time datetime=\"" + msg.TimestampString + "Z\" title=\"" + msg.TimestampString + " GMT\">" + formatDate(msg.TimeAgo, msg.TimestampString) + "</time></a></header>");
+            out.println("  <header class=\"t\"><a href=\"/" + msg.getUser().getUName() + "/" + msg.getMID() + "\"><time datetime=\"" + sdfSQL.format(msg.getDate()) + "Z\" title=\"" + sdfSQL.format(msg.getDate()) + " GMT\">" + formatDate(msg.TimeAgo, msg.getDate()) + "</time></a></header>");
             if (msg.AttachmentType != null) {
                 String fname = msg.getMID() + "." + msg.AttachmentType;
                 out.println("  <p class=\"ir\"><a href=\"//i.juick.com/photos-512/" + fname + "\" onclick=\"return showPhotoDialog('" + fname + "')\"><img src=\"//i.juick.com/photos-512/" + fname + "\" alt=\"\"/></a></p>");
diff --git a/src/main/java/com/juick/http/www/RSS.java b/src/main/java/com/juick/http/www/RSS.java
index 72893915..c9c9df18 100644
--- a/src/main/java/com/juick/http/www/RSS.java
+++ b/src/main/java/com/juick/http/www/RSS.java
@@ -19,6 +19,8 @@ package com.juick.http.www;
 
 import com.juick.Message;
 import com.juick.server.MessagesQueries;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.IOException;
 import java.io.PrintWriter;
 import java.sql.Connection;
@@ -39,7 +41,8 @@ public class RSS {
     private static final SimpleDateFormat sdfSQL = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     private static final SimpleDateFormat sdfRSS = new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z");
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response, int uid, String uname) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request,
+                         HttpServletResponse response, int uid, String uname) throws ServletException, IOException {
         List<Integer> mids = MessagesQueries.getUserBlog(sql, uid, 0, 0);
         if (mids.isEmpty()) {
             response.sendError(404);
@@ -72,16 +75,7 @@ public class RSS {
                 }
                 out.println("]]></title>");
                 out.println("<description><![CDATA[" + PageTemplates.formatMessage(msg.getText()) + "]]></description>");
-
-                synchronized (sdfSQL) {
-                    try {
-                        Date date = sdfSQL.parse(msg.TimestampString);
-                        out.println("<pubDate>" + sdfRSS.format(date) + "</pubDate>");
-                    } catch (Exception e) {
-                        System.err.println("PARSE EXCEPTION: " + msg.TimestampString);
-                    }
-                }
-
+                out.println("<pubDate>" + sdfRSS.format(msg.getDate()) + "</pubDate>");
                 out.println("<comments>http://juick.com/" + msg.getUser().getUName() + "/" + msg.getMID() + "</comments>");
                 if (!msg.Tags.isEmpty()) {
                     for (int n = 0; n < msg.Tags.size(); n++) {
diff --git a/src/main/java/com/juick/http/www/Settings.java b/src/main/java/com/juick/http/www/Settings.java
index de37bdd0..3713157f 100644
--- a/src/main/java/com/juick/http/www/Settings.java
+++ b/src/main/java/com/juick/http/www/Settings.java
@@ -17,15 +17,14 @@
  */
 package com.juick.http.www;
 
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.SQLException;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import javax.servlet.ServletException;
 import javax.servlet.http.Cookie;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
 
 /**
  *
@@ -33,12 +32,12 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Settings {
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request,
+                         HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             PageTemplates.pageHead(out, "Логин", "");
             PageTemplates.pageNavigation(out, visitor, null);
 
@@ -56,12 +55,11 @@ public class Settings {
 
             PageTemplates.pageFooter(request, out, visitor, false);
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
-    protected void doPost(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doPost(JdbcTemplate sql, HttpServletRequest request,
+                          HttpServletResponse response) throws ServletException, IOException {
         String username = request.getParameter("username");
         String password = request.getParameter("password");
         if (username == null || password == null || username.length() > 32 || password.isEmpty()) {
@@ -79,16 +77,7 @@ public class Settings {
 
 
             if (uid > 0) {
-                PreparedStatement stmt = null;
-                try {
-                    stmt = sql.prepareStatement("DELETE FROM logins WHERE user_id=?");
-                    stmt.setInt(1, uid);
-                    stmt.executeUpdate();
-                } catch (SQLException e) {
-                    System.err.println(e);
-                } finally {
-                    Utils.finishSQL(null, stmt);
-                }
+                sql.update("DELETE FROM logins WHERE user_id=?", uid);
             }
 
             String referer = request.getHeader("Referer");
diff --git a/src/main/java/com/juick/http/www/SignUp.java b/src/main/java/com/juick/http/www/SignUp.java
index 2fae9b69..70ffad3d 100644
--- a/src/main/java/com/juick/http/www/SignUp.java
+++ b/src/main/java/com/juick/http/www/SignUp.java
@@ -18,16 +18,17 @@
 package com.juick.http.www;
 
 import com.juick.server.UserQueries;
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
+import org.apache.commons.lang3.tuple.Pair;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import javax.servlet.ServletException;
 import javax.servlet.http.Cookie;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
+import java.util.List;
 
 /**
  *
@@ -35,12 +36,14 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class SignUp {
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request,
+                         HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         String type = request.getParameter("type");
         String hash = request.getParameter("hash");
-        if (type == null || type.isEmpty() || hash == null || hash.isEmpty() || hash.length() > 36 || !type.matches("^[a-zA-Z0-9\\-]+$") || !hash.matches("^[a-zA-Z0-9\\-]+$")) {
+        if (type == null || type.isEmpty() || hash == null || hash.isEmpty() || hash.length() > 36
+                || !type.matches("^[a-zA-Z0-9\\-]+$") || !hash.matches("^[a-zA-Z0-9\\-]+$")) {
             response.sendError(HttpServletResponse.SC_BAD_REQUEST);
             return;
         }
@@ -59,8 +62,7 @@ public class SignUp {
         }
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             PageTemplates.pageHead(out, "Новый пользователь", null);
             PageTemplates.pageNavigation(out, visitor, null);
 
@@ -106,18 +108,18 @@ public class SignUp {
 
             PageTemplates.pageFooter(request, out, visitor, false);
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
-    protected void doPost(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doPost(JdbcTemplate sql, HttpServletRequest request,
+                          HttpServletResponse response) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
         int uid = 0;
 
         String type = request.getParameter("type");
         String hash = request.getParameter("hash");
-        if (type == null || type.isEmpty() || hash == null || hash.isEmpty() || hash.length() > 36 || !type.matches("^[a-zA-Z0-9\\-]+$") || !hash.matches("^[a-zA-Z0-9\\-]+$")) {
+        if (type == null || type.isEmpty() || hash == null || hash.isEmpty()
+                || hash.length() > 36 || !type.matches("^[a-zA-Z0-9\\-]+$") || !hash.matches("^[a-zA-Z0-9\\-]+$")) {
             response.sendError(HttpServletResponse.SC_BAD_REQUEST);
             return;
         }
@@ -152,7 +154,8 @@ public class SignUp {
         } else { // Create new account
             String username = request.getParameter("username");
             String password = request.getParameter("password");
-            if (username == null || password == null || username.length() < 2 || username.length() > 16 || !username.matches("^[a-zA-Z0-9\\-]+$") || password.length() < 6 || password.length() > 32) {
+            if (username == null || password == null || username.length() < 2 || username.length() > 16
+                    || !username.matches("^[a-zA-Z0-9\\-]+$") || password.length() < 6 || password.length() > 32) {
                 response.sendError(HttpServletResponse.SC_BAD_REQUEST);
                 return;
             }
@@ -198,134 +201,46 @@ public class SignUp {
         response.sendRedirect("/");
     }
 
-    private boolean setUserRef(Connection sql, int uid, int ref) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO users_refs(user_id,ref) VALUES (?,?)");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, ref);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean setUserRef(JdbcTemplate sql, int uid, int ref) {
+        return sql.update("INSERT INTO users_refs(user_id,ref) VALUES (?,?)", uid, ref) > 0;
     }
 
-    private String getFacebookNameByHash(Connection sql, String hash) {
-        String ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    private String getFacebookNameByHash(JdbcTemplate sql, String hash) {
         try {
-            stmt = sql.prepareStatement("SELECT fb_name,fb_link FROM facebook WHERE loginhash=?");
-            stmt.setString(1, hash);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = "<a href=\"" + rs.getString(2) + "\" rel=\"nofollow\">" + rs.getString(1) + "</a>";
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT fb_name,fb_link FROM facebook WHERE loginhash=?", String.class, hash);
+        } catch (EmptyResultDataAccessException e) {
+            return "";
         }
-
-        return ret;
     }
 
-    private boolean setFacebookUser(Connection sql, String hash, int uid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("UPDATE facebook SET user_id=?,loginhash=NULL WHERE loginhash=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, hash);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean setFacebookUser(JdbcTemplate sql, String hash, int uid) {
+        return sql.update("UPDATE facebook SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0;
     }
 
-    private String getVKNameByHash(Connection sql, String hash) {
-        String ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT vk_name,vk_link FROM vk WHERE loginhash=?");
-            stmt.setString(1, hash);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = "<a href=\"http://vk.com/" + rs.getString(2) + "\" rel=\"nofollow\">" + rs.getString(1) + "</a>";
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+    private String getVKNameByHash(JdbcTemplate sql, String hash) {
+        List<Pair<String, String>> logins = sql.query("SELECT vk_name,vk_link FROM vk WHERE loginhash=?",
+                (rs, num) -> {
+                    return Pair.of(rs.getString(1), rs.getString(2));
+                }, hash);
+        if (logins.size() > 0) {
+            return "<a href=\"http://vk.com/" + logins.get(0).getRight() + "\" rel=\"nofollow\">" + logins.get(0).getLeft() + "</a>";
         }
-
-        return ret;
+        return "";
     }
 
-    private boolean setVKUser(Connection sql, String hash, int uid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("UPDATE vk SET user_id=?,loginhash=NULL WHERE loginhash=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, hash);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean setVKUser(JdbcTemplate sql, String hash, int uid) {
+        return sql.update("UPDATE vk SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0;
     }
 
-    private String getJIDByHash(Connection sql, String hash) {
-        String ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    private String getJIDByHash(JdbcTemplate sql, String hash) {
         try {
-            stmt = sql.prepareStatement("SELECT jid FROM jids WHERE loginhash=?");
-            stmt.setString(1, hash);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = rs.getString(1);
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT jid FROM jids WHERE loginhash=?", String.class, hash);
+        } catch (EmptyResultDataAccessException e) {
+            return "";
         }
-
-        return ret;
     }
 
-    private boolean setJIDUser(Connection sql, String hash, int uid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("UPDATE jids SET user_id=?,loginhash=NULL WHERE loginhash=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, hash);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean setJIDUser(JdbcTemplate sql, String hash, int uid) {
+        return  sql.update("UPDATE jids SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0;
     }
 }
diff --git a/src/main/java/com/juick/http/www/TwitterAuth.java b/src/main/java/com/juick/http/www/TwitterAuth.java
index 421c6f64..22ebca84 100644
--- a/src/main/java/com/juick/http/www/TwitterAuth.java
+++ b/src/main/java/com/juick/http/www/TwitterAuth.java
@@ -2,17 +2,20 @@ package com.juick.http.www;
 
 import com.github.scribejava.apis.TwitterApi;
 import com.github.scribejava.core.builder.ServiceBuilder;
-import com.github.scribejava.core.model.*;
+import com.github.scribejava.core.model.OAuthRequest;
+import com.github.scribejava.core.model.Token;
+import com.github.scribejava.core.model.Verb;
+import com.github.scribejava.core.model.Verifier;
 import com.github.scribejava.core.oauth.OAuthService;
 import com.juick.server.UserQueries;
 import org.json.JSONObject;
+import org.springframework.jdbc.core.JdbcTemplate;
 
 import javax.servlet.ServletException;
 import javax.servlet.http.Cookie;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
-import java.sql.Connection;
 
 /**
  * Created by vt on 01.12.2015.
@@ -28,7 +31,7 @@ public class TwitterAuth {
         this.consumerSecret = consumerSecret;
     }
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response)
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response)
             throws ServletException, IOException {
         String hash = "", request_token = "", request_token_secret = "";
         String verifier = request.getParameter("oauth_verifier");
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;
+        }*/
     }
 }
diff --git a/src/main/java/com/juick/http/www/UserThread.java b/src/main/java/com/juick/http/www/UserThread.java
index fe5ca32f..f54aa925 100644
--- a/src/main/java/com/juick/http/www/UserThread.java
+++ b/src/main/java/com/juick/http/www/UserThread.java
@@ -21,14 +21,16 @@ import com.juick.Message;
 import com.juick.Tag;
 import com.juick.server.MessagesQueries;
 import com.juick.server.UserQueries;
+import org.springframework.jdbc.core.JdbcTemplate;
+
+import javax.servlet.ServletException;
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
 import java.io.PrintWriter;
-import java.sql.Connection;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.List;
-import javax.servlet.ServletException;
-import javax.servlet.http.HttpServletRequest;
-import javax.servlet.http.HttpServletResponse;
 
 /**
  *
@@ -36,7 +38,10 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class UserThread {
 
-    protected void doGetThread(Connection sql, HttpServletRequest request, HttpServletResponse response, int MID) throws ServletException, IOException {
+    private static final SimpleDateFormat sdfSQL = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+    protected void doGetThread(JdbcTemplate sql, HttpServletRequest request,
+                               HttpServletResponse response, int MID) throws ServletException, IOException {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (!MessagesQueries.canViewThread(sql, MID, visitor != null ? visitor.getUID() : 0)) {
@@ -68,8 +73,7 @@ public class UserThread {
         }
 
         response.setContentType("text/html; charset=UTF-8");
-        PrintWriter out = response.getWriter();
-        try {
+        try (PrintWriter out = response.getWriter()) {
             String headers = "<link rel=\"alternate\" type=\"application/rss+xml\" title=\"@" + msg.getUser().getUName() + "\" href=\"//rss.juick.com/" + msg.getUser().getUName() + "/blog\"/>";
             if (paramView != null) {
                 headers += "<link rel=\"canonical\" href=\"http://juick.com/" + msg.getUser().getUName() + "/" + msg.getMID() + "\"/>";
@@ -93,12 +97,11 @@ public class UserThread {
             out.println("</script>");
 
             PageTemplates.pageEnd(out);
-        } finally {
-            out.close();
         }
     }
 
-    public static com.juick.Message printMessage(PrintWriter out, Connection sql, com.juick.Message msg, com.juick.User visitor) {
+    public static com.juick.Message printMessage(PrintWriter out, JdbcTemplate sql,
+                                                 com.juick.Message msg, com.juick.User visitor) {
         msg.VisitorCanComment = visitor != null;
 
         List<Tag> tags = MessagesQueries.getMessageTags(sql, msg.getMID());
@@ -128,7 +131,7 @@ public class UserThread {
         out.println("    <div class=\"msg-cont\">");
         out.println("    <div class=\"msg-menu\"><a href=\"#\" onclick=\"showMessageLinksDialog(" + msg.getMID() + "); return false\"></a></div>");
         out.println("    <div class=\"msg-header\"><a href=\"/" + msg.getUser().getUName() + "/\">@" + msg.getUser().getUName() + "</a>:" + tagsStr + "</div>");
-        out.println("    <div class=\"msg-ts\">" + PageTemplates.formatJSLocalTime(msg.TimestampString) + "</div>");
+        out.println("    <div class=\"msg-ts\">" + PageTemplates.formatJSLocalTime(msg.getDate()) + "</div>");
         out.println("    <div class=\"msg-txt\">" + txt + "</div>");
 
         if (msg.AttachmentType != null) {
@@ -189,10 +192,10 @@ public class UserThread {
         return msg;
     }
 
-    public static void printReplies(PrintWriter out, Connection sql, com.juick.Message msg, com.juick.User visitor, boolean listview) {
+    public static void printReplies(PrintWriter out, JdbcTemplate sql, com.juick.Message msg, com.juick.User visitor, boolean listview) {
         List<com.juick.Message> replies = MessagesQueries.getReplies(sql, msg.getMID());
 
-        List<Integer> blUIDs = new ArrayList<Integer>();
+        List<Integer> blUIDs = new ArrayList<>();
         for (int i = 0; i < replies.size(); i++) {
             com.juick.Message reply = replies.get(i);
             if (reply.getUser().getUID() != msg.getUser().getUID() && !blUIDs.contains(reply.getUser().getUID())) {
@@ -200,9 +203,9 @@ public class UserThread {
             }
             if (reply.ReplyTo > 0) {
                 boolean added = false;
-                for (int n = 0; n < replies.size(); n++) {
-                    if (replies.get(n).getRID() == reply.ReplyTo) {
-                        replies.get(n).childs.add(reply);
+                for (Message reply1 : replies) {
+                    if (reply1.getRID() == reply.ReplyTo) {
+                        reply1.childs.add(reply);
                         added = true;
                         break;
                     }
@@ -301,7 +304,7 @@ public class UserThread {
                 } else {
                     out.println("    <div class=\"msg-header\">[удалено]:</div>");
                 }
-                out.println("    <div class=\"msg-ts\"><a href=\"/" + msg.getMID() + "#" + msg.getRID() + "\" title=\"" + msg.TimestampString + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.TimestampString) + "</a></div>");
+                out.println("    <div class=\"msg-ts\"><a href=\"/" + msg.getMID() + "#" + msg.getRID() + "\" title=\"" + sdfSQL.format(msg.getDate()) + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.getDate()) + "</a></div>");
                 out.println("    <div class=\"msg-txt\">" + PageTemplates.formatMessage(msg.getText()) + "</div>");
                 if (msg.AttachmentType != null) {
                     out.println("    <div class=\"msg-media\"><a href=\"//i.juick.com/p/" + msg.getMID() + "-" + msg.getRID() + "." + msg.AttachmentType + "\"><img src=\"//i.juick.com/photos-512/" + msg.getMID() + "-" + msg.getRID() + "." + msg.AttachmentType + "\" alt=\"\"/></a></div>");
@@ -345,7 +348,7 @@ public class UserThread {
             } else {
                 out.println("    <div class=\"msg-header\">[удалено]:</div>");
             }
-            out.println("    <div class=\"msg-ts\"><a href=\"/" + msg.getMID() + "#" + msg.getRID() + "\" title=\"" + msg.TimestampString + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.TimestampString) + "</a></div>");
+            out.println("    <div class=\"msg-ts\"><a href=\"/" + msg.getMID() + "#" + msg.getRID() + "\" title=\"" + sdfSQL.format(msg.getDate()) + " GMT\">" + PageTemplates.formatDate(msg.TimeAgo, msg.getDate()) + "</a></div>");
             out.println("    <div class=\"msg-txt\">" + PageTemplates.formatMessage(msg.getText()) + "</div>");
             if (msg.AttachmentType != null) {
                 out.println("    <div class=\"msg-media\"><a href=\"//i.juick.com/p/" + msg.getMID() + "-" + msg.getRID() + "." + msg.AttachmentType + "\"><img src=\"//i.juick.com/photos-512/" + msg.getMID() + "-" + msg.getRID() + "." + msg.AttachmentType + "\" alt=\"\"/></a></div>");
diff --git a/src/main/java/com/juick/http/www/Utils.java b/src/main/java/com/juick/http/www/Utils.java
index 931e2c21..ab721020 100644
--- a/src/main/java/com/juick/http/www/Utils.java
+++ b/src/main/java/com/juick/http/www/Utils.java
@@ -17,6 +17,8 @@
  */
 package com.juick.http.www;
 
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.io.BufferedReader;
 import java.io.FileOutputStream;
 import java.io.IOException;
@@ -77,7 +79,7 @@ public class Utils {
         return attachmentFName;
     }
 
-    public static com.juick.User getVisitorUser(Connection sql, HttpServletRequest request, HttpServletResponse response) {
+    public static com.juick.User getVisitorUser(JdbcTemplate sql, HttpServletRequest request, HttpServletResponse response) {
         String hash = getCookie(request, "hash");
         if (hash != null) {
             com.juick.User visitor = com.juick.server.UserQueries.getUserByHash(sql, hash);
diff --git a/src/main/java/com/juick/http/www/VKontakteLogin.java b/src/main/java/com/juick/http/www/VKontakteLogin.java
index 5f26fef1..76028b3a 100644
--- a/src/main/java/com/juick/http/www/VKontakteLogin.java
+++ b/src/main/java/com/juick/http/www/VKontakteLogin.java
@@ -31,6 +31,8 @@ import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import org.json.JSONException;
 import org.json.JSONObject;
+import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
 
 /**
  *
@@ -42,7 +44,8 @@ public class VKontakteLogin {
     private static final String VK_SECRET = "z2afNI8jA5lIpZ2jsTm1";
     private static final String VK_REDIRECT = "http://juick.com/_vklogin";
 
-    protected void doGet(Connection sql, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
+    protected void doGet(JdbcTemplate sql, HttpServletRequest request,
+                         HttpServletResponse response) throws ServletException, IOException {
         String code = request.getParameter("code");
         if (code == null || code.equals("")) {
             response.setStatus(HttpServletResponse.SC_MOVED_TEMPORARILY);
@@ -114,42 +117,16 @@ public class VKontakteLogin {
         }
     }
 
-    private int getUIDbyVKID(Connection sql, long vkID) {
-        int uid = 0;
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
+    private int getUIDbyVKID(JdbcTemplate sql, long vkID) {
         try {
-            stmt = sql.prepareStatement("SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL");
-            stmt.setLong(1, vkID);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                uid = rs.getInt(1);
-            }
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+            return sql.queryForObject("SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL", Integer.class, vkID);
+        } catch (EmptyResultDataAccessException e) {
+            return 0;
         }
-        return uid;
     }
 
-    private boolean insertDB(Connection sql, long vkID, String loginhash, String token, String vkName, String vkLink) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO vk(vk_id,loginhash,access_token,vk_name,vk_link) VALUES (?,?,?,?,?)");
-            stmt.setLong(1, vkID);
-            stmt.setString(2, loginhash);
-            stmt.setString(3, token);
-            stmt.setString(4, vkName);
-            stmt.setString(5, vkLink);
-            stmt.executeUpdate();
-            ret = true;
-        } catch (SQLException e) {
-            System.err.println(e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    private boolean insertDB(JdbcTemplate sql, long vkID, String loginhash, String token, String vkName, String vkLink) {
+        return sql.update("INSERT INTO vk(vk_id,loginhash,access_token,vk_name,vk_link) VALUES (?,?,?,?,?)",
+                vkID, loginhash, token, vkName, vkLink) > 0;
     }
 }
diff --git a/src/main/java/com/juick/xmpp/extensions/JuickMessage.java b/src/main/java/com/juick/xmpp/extensions/JuickMessage.java
index 53dd6deb..bbc1568e 100644
--- a/src/main/java/com/juick/xmpp/extensions/JuickMessage.java
+++ b/src/main/java/com/juick/xmpp/extensions/JuickMessage.java
@@ -20,6 +20,10 @@ package com.juick.xmpp.extensions;
 import com.juick.xmpp.utils.XmlUtils;
 import com.juick.xmpp.*;
 import java.io.IOException;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.TimeZone;
+
 import org.xmlpull.v1.XmlPullParser;
 import org.xmlpull.v1.XmlPullParserException;
 
@@ -32,7 +36,11 @@ public class JuickMessage extends com.juick.Message implements StanzaChild {
     public final static String XMLNS = "http://juick.com/message";
     public final static String TagName = "juick";
 
+    private SimpleDateFormat df;
+
     public JuickMessage() {
+        df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+        df.setTimeZone(TimeZone.getTimeZone("UTC"));
     }
 
     public JuickMessage(com.juick.Message msg) {
@@ -45,7 +53,7 @@ public class JuickMessage extends com.juick.Message implements StanzaChild {
     }
 
     @Override
-    public JuickMessage parse(XmlPullParser parser) throws XmlPullParserException, IOException {
+    public JuickMessage parse(XmlPullParser parser) throws XmlPullParserException, IOException, ParseException {
         JuickMessage jmsg = new JuickMessage();
 
         final String sMID = parser.getAttributeValue(null, "mid");
@@ -72,7 +80,8 @@ public class JuickMessage extends com.juick.Message implements StanzaChild {
         if (sReadOnly != null) {
             jmsg.ReadOnly = true;
         }
-        jmsg.TimestampString = parser.getAttributeValue(null, "ts");
+
+        jmsg.setDate(df.parse(parser.getAttributeValue(null, "ts")));
         jmsg.AttachmentType = parser.getAttributeValue(null, "attach");
 
         while (parser.next() == XmlPullParser.START_TAG) {
@@ -112,8 +121,8 @@ public class JuickMessage extends com.juick.Message implements StanzaChild {
         if (ReadOnly) {
             ret += " readonly=\"1\"";
         }
-        if (TimestampString != null) {
-            ret += " ts=\"" + TimestampString + "\"";
+        if (getDate() != null) {
+            ret += " ts=\"" + df.format(getDate()) + "\"";
         }
         if (AttachmentType != null) {
             ret += " attach=\"" + AttachmentType + "\"";
@@ -126,8 +135,8 @@ public class JuickMessage extends com.juick.Message implements StanzaChild {
             ret += "<body>" + XmlUtils.escape(getText()) + "</body>";
         }
         if (!Tags.isEmpty()) {
-            for (int i = 0; i < Tags.size(); i++) {
-                ret += "<tag>" + XmlUtils.escape(Tags.get(i)) + "</tag>";
+            for (String Tag : Tags) {
+                ret += "<tag>" + XmlUtils.escape(Tag) + "</tag>";
             }
         }
         ret += "</" + TagName + ">";
diff --git a/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java b/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java
index 4634ca99..c96a5cce 100644
--- a/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java
+++ b/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java
@@ -1,7 +1,9 @@
 package com.juick.xmpp.s2s;
 
+import com.juick.User;
 import com.juick.server.MessagesQueries;
 import com.juick.server.SubscriptionsQueries;
+import com.juick.server.UserQueries;
 import com.juick.xmpp.JID;
 import com.juick.xmpp.Message;
 import com.juick.xmpp.extensions.JuickMessage;
@@ -9,6 +11,7 @@ import com.juick.xmpp.extensions.Nickname;
 import com.juick.xmpp.extensions.XOOB;
 import com.juick.xmpp.utils.SHA1;
 import com.juick.xmpp.utils.XmlUtils;
+import org.springframework.jdbc.core.JdbcTemplate;
 import org.xmlpull.v1.XmlPullParser;
 
 import java.io.IOException;
@@ -143,16 +146,16 @@ public class ConnectionRouter extends Connection implements Runnable {
     }
 
     public void sendJuickMessage(JuickMessage jmsg) {
-        List<String> jids;
+        List<User> users;
 
-        synchronized (XMPPComponent.sqlSync) {
-            if (jmsg.FriendsOnly) {
-                jids = SubscriptionsQueries.getJIDSubscribedToUser(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.FriendsOnly);
-            } else {
-                jids = SubscriptionsQueries.getJIDSubscribedToUserAndTags(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.getMID());
-            }
+
+        if (jmsg.FriendsOnly) {
+            users = SubscriptionsQueries.getUsersSubscribedToUser(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.FriendsOnly);
+        } else {
+            users = SubscriptionsQueries.getSubscribedUsers(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.getMID());
         }
 
+
         String txt = "@" + jmsg.getUser().getUName() + ":" + jmsg.getTagsString() + "\n";
         String attachment = jmsg.getAttachmentURL();
         if (attachment != null) {
@@ -176,21 +179,19 @@ public class ConnectionRouter extends Connection implements Runnable {
             oob.URL = attachment;
             msg.addChild(oob);
         }
-
-        for (int i = 0; i < jids.size(); i++) {
-            msg.to = new JID(jids.get(i));
-            XMPPComponent.sendOut(msg);
+        for (User user : users) {
+            for (String jid : UserQueries.getActiveJIDs(XMPPComponent.sql, user)) {
+                msg.to = new JID(jid);
+                XMPPComponent.sendOut(msg);
+            }
         }
     }
 
     public void sendJuickComment(JuickMessage jmsg) {
-        List<String> jids;
         String replyQuote;
 
-        synchronized (XMPPComponent.sqlSync) {
-            jids = SubscriptionsQueries.getJIDSubscribedToComments(XMPPComponent.sql, jmsg.getMID(), jmsg.getUser().getUID());
-            replyQuote = getReplyQuote(XMPPComponent.sql, jmsg.getMID(), jmsg.ReplyTo);
-        }
+        List<User> users = SubscriptionsQueries.getSubscribedUsers(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.getMID());
+        replyQuote = getReplyQuote(XMPPComponent.sql, jmsg.getMID(), jmsg.ReplyTo);
 
         String txt = "Reply by @" + jmsg.getUser().getUName() + ":\n" + replyQuote + "\n";
         String attachment = jmsg.getAttachmentURL();
@@ -204,13 +205,16 @@ public class ConnectionRouter extends Connection implements Runnable {
         msg.body = txt;
         msg.type = Message.Type.chat;
         msg.addChild(jmsg);
-        for (int i = 0; i < jids.size(); i++) {
-            msg.to = new JID(jids.get(i));
-            XMPPComponent.sendOut(msg);
+        for (User user : users) {
+            // TODO: make single query
+            for (String jid : UserQueries.getActiveJIDs(XMPPComponent.sql, user)) {
+                msg.to = new JID(jid);
+                XMPPComponent.sendOut(msg);
+            }
         }
     }
 
-    private String getReplyQuote(java.sql.Connection sql, int MID, int ReplyTo) {
+    private String getReplyQuote(JdbcTemplate sql, int MID, int ReplyTo) {
         String quote = "";
         if (ReplyTo > 0) {
             com.juick.Message q = MessagesQueries.getReply(sql, MID, ReplyTo);
@@ -232,13 +236,10 @@ public class ConnectionRouter extends Connection implements Runnable {
     }
 
     public void sendJuickRecommendation(JuickMessage recomm) {
-        List<String> jids;
         JuickMessage jmsg;
-        synchronized (XMPPComponent.sqlSync) {
-            jmsg = new JuickMessage(MessagesQueries.getMessage(XMPPComponent.sql, recomm.getMID()));
-            jids = SubscriptionsQueries.getJIDSubscribedToUserRecommendations(XMPPComponent.sql,
-                    recomm.getUser().getUID(), recomm.getMID(), jmsg.getUser().getUID());
-        }
+        jmsg = new JuickMessage(MessagesQueries.getMessage(XMPPComponent.sql, recomm.getMID()));
+        List<User> users = SubscriptionsQueries.getUsersSubscribedToComments(XMPPComponent.sql,
+                recomm.getMID(), jmsg.getUser().getUID());
 
         String txt = "Recommended by @" + recomm.getUser().getUName() + ":\n";
         txt += "@" + jmsg.getUser().getUName() + ":" + jmsg.getTagsString() + "\n";
@@ -272,10 +273,11 @@ public class ConnectionRouter extends Connection implements Runnable {
             oob.URL = attachment;
             msg.addChild(oob);
         }
-
-        for (int i = 0; i < jids.size(); i++) {
-            msg.to = new JID(jids.get(i));
-            XMPPComponent.sendOut(msg);
+        for (User user : users) {
+            for (String jid : UserQueries.getActiveJIDs(XMPPComponent.sql, user)) {
+                msg.to = new JID(jid);
+                XMPPComponent.sendOut(msg);
+            }
         }
     }
 }
diff --git a/src/main/java/com/juick/xmpp/s2s/JuickBot.java b/src/main/java/com/juick/xmpp/s2s/JuickBot.java
index 182de10d..d82ed939 100644
--- a/src/main/java/com/juick/xmpp/s2s/JuickBot.java
+++ b/src/main/java/com/juick/xmpp/s2s/JuickBot.java
@@ -69,9 +69,7 @@ public class JuickBot {
         } else if (p.type.equals(Presence.Type.probe)) {
             int uid_to = 0;
             if (!toJuick) {
-                synchronized (XMPPComponent.sqlSync) {
-                    uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
-                }
+                uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
             }
 
             if (toJuick || uid_to > 0) {
@@ -94,12 +92,10 @@ public class JuickBot {
             if (toJuick) {
                 canSubscribe = true;
             } else {
-                synchronized (XMPPComponent.sqlSync) {
-                    int uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
-                    if (uid_to > 0) {
-                        PMQueries.addPMinRoster(XMPPComponent.sql, uid_to, p.from.Bare());
-                        canSubscribe = true;
-                    }
+                int uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
+                if (uid_to > 0) {
+                    PMQueries.addPMinRoster(XMPPComponent.sql, uid_to, p.from.Bare());
+                    canSubscribe = true;
                 }
             }
 
@@ -122,11 +118,9 @@ public class JuickBot {
             }
         } else if (p.type.equals(Presence.Type.unsubscribe)) {
             if (!toJuick) {
-                synchronized (XMPPComponent.sqlSync) {
-                    int uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
-                    if (uid_to > 0) {
-                        PMQueries.removePMinRoster(XMPPComponent.sql, uid_to, p.from.Bare());
-                    }
+                int uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
+                if (uid_to > 0) {
+                    PMQueries.removePMinRoster(XMPPComponent.sql, uid_to, p.from.Bare());
                 }
             }
 
@@ -146,11 +140,9 @@ public class JuickBot {
 
         User user_from = null;
         String signuphash = "";
-        synchronized (XMPPComponent.sqlSync) {
-            user_from = UserQueries.getUserByJID(XMPPComponent.sql, msg.from.Bare());
-            if (user_from == null) {
-                signuphash = UserQueries.getSignUpHashByJID(XMPPComponent.sql, msg.from.Bare());
-            }
+        user_from = UserQueries.getUserByJID(XMPPComponent.sql, msg.from.Bare());
+        if (user_from == null) {
+            signuphash = UserQueries.getSignUpHashByJID(XMPPComponent.sql, msg.from.Bare());
         }
 
         if (user_from == null) {
@@ -168,10 +160,7 @@ public class JuickBot {
             return incomingMessageJuick(user_from, msg);
         }
 
-        int uid_to = 0;
-        synchronized (XMPPComponent.sqlSync) {
-            uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
-        }
+        int uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, username);
 
         if (uid_to == 0) {
             Message reply = new Message(msg.to, msg.from, Message.Type.error);
@@ -182,20 +171,18 @@ public class JuickBot {
         }
 
         boolean success = false;
-        synchronized (XMPPComponent.sqlSync) {
-            if (!UserQueries.isInBLAny(XMPPComponent.sql, uid_to, user_from.getUID())) {
-                success = PMQueries.createPM(XMPPComponent.sql, user_from.getUID(), uid_to, msg.body);
-            }
+        if (!UserQueries.isInBLAny(XMPPComponent.sql, uid_to, user_from.getUID())) {
+            success = PMQueries.createPM(XMPPComponent.sql, user_from.getUID(), uid_to, msg.body);
         }
 
+
         if (success) {
             Message m = new Message();
             m.from = new JID("juick", "juick.com", null);
             m.to = new JID(Integer.toString(uid_to), "push.juick.com", null);
             JuickMessage jmsg = new JuickMessage();
-            synchronized (XMPPComponent.sqlSync) {
-                jmsg.setUser(UserQueries.getUserByUID(XMPPComponent.sql, user_from.getUID()));
-            }
+            jmsg.setUser(UserQueries.getUserByUID(XMPPComponent.sql, user_from.getUID()));
+
             jmsg.setText(msg.body);
             m.childs.add(jmsg);
             XMPPComponent.connRouter.sendStanza(m.toString());
@@ -205,11 +192,9 @@ public class JuickBot {
 
             String jid;
             boolean inroster = false;
-            synchronized (XMPPComponent.sqlSync) {
-                jid = UserQueries.getJIDbyUID(XMPPComponent.sql, uid_to);
-                if (jid != null) {
-                    inroster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid);
-                }
+            jid = UserQueries.getJIDbyUID(XMPPComponent.sql, uid_to);
+            if (jid != null) {
+                inroster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid);
             }
 
             if (jid != null) {
@@ -303,32 +288,31 @@ public class JuickBot {
         String jid_to = null;
         boolean haveInRoster = false;
 
-        synchronized (XMPPComponent.sqlSync) {
-            if (user_to.indexOf('@') > 0) {
-                uid_to = UserQueries.getUIDbyJID(XMPPComponent.sql, user_to);
-            } else {
-                uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, user_to);
-            }
+        if (user_to.indexOf('@') > 0) {
+            uid_to = UserQueries.getUIDbyJID(XMPPComponent.sql, user_to);
+        } else {
+            uid_to = UserQueries.getUIDbyName(XMPPComponent.sql, user_to);
+        }
 
-            if (uid_to > 0) {
-                if (!UserQueries.isInBLAny(XMPPComponent.sql, uid_to, user_from.getUID())) {
-                    if (PMQueries.createPM(XMPPComponent.sql, user_from.getUID(), uid_to, body)) {
-                        jid_to = UserQueries.getJIDbyUID(XMPPComponent.sql, uid_to);
-                        if (jid_to != null) {
-                            haveInRoster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid_to);
-                        }
-                        ret = 200;
-                    } else {
-                        ret = 500;
+        if (uid_to > 0) {
+            if (!UserQueries.isInBLAny(XMPPComponent.sql, uid_to, user_from.getUID())) {
+                if (PMQueries.createPM(XMPPComponent.sql, user_from.getUID(), uid_to, body)) {
+                    jid_to = UserQueries.getJIDbyUID(XMPPComponent.sql, uid_to);
+                    if (jid_to != null) {
+                        haveInRoster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid_to);
                     }
+                    ret = 200;
                 } else {
-                    ret = 403;
+                    ret = 500;
                 }
             } else {
-                ret = 404;
+                ret = 403;
             }
+        } else {
+            ret = 404;
         }
 
+
         if (ret == 200) {
             Message msg = new Message();
             msg.from = new JID("juick", "juick.com", null);
@@ -372,10 +356,9 @@ public class JuickBot {
         List<User> blusers;
         List<String> bltags;
 
-        synchronized (XMPPComponent.sqlSync) {
-            blusers = UserQueries.getUserBLUsers(XMPPComponent.sql, user_from.getUID());
-            bltags = TagQueries.getUserBLTags(XMPPComponent.sql, user_from.getUID());
-        }
+        blusers = UserQueries.getUserBLUsers(XMPPComponent.sql, user_from.getUID());
+        bltags = TagQueries.getUserBLTags(XMPPComponent.sql, user_from.getUID());
+
 
         String txt = "";
         if (bltags.size() > 0) {
diff --git a/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java b/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
index 50d2c1e4..1dfdd38d 100644
--- a/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
+++ b/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
@@ -3,6 +3,7 @@ package com.juick.xmpp.s2s;
 import com.juick.xmpp.Stanza;
 import com.juick.xmpp.StanzaChild;
 import com.juick.xmpp.extensions.JuickMessage;
+import org.springframework.jdbc.core.JdbcTemplate;
 
 import javax.servlet.ServletContextEvent;
 import javax.servlet.ServletContextListener;
@@ -32,8 +33,7 @@ public class XMPPComponent implements ServletContextListener {
     static final List<ConnectionIn> inConnections = Collections.synchronizedList(new ArrayList<>());
     static final List<ConnectionOut> outConnections = Collections.synchronizedList(new ArrayList<>());
     static final List<CacheEntry> outCache = Collections.synchronizedList(new ArrayList<>());
-    static final Integer sqlSync = 0;
-    static java.sql.Connection sql;
+    static JdbcTemplate sql;
     final public static HashMap<String, StanzaChild> childParsers = new HashMap<>();
 
     public static void addConnectionIn(ConnectionIn c) {
@@ -159,10 +159,7 @@ public class XMPPComponent implements ServletContextListener {
                 HOSTNAME = conf.getProperty("hostname");
                 String componentName = conf.getProperty("componentname");
                 STATSFILE = conf.getProperty("statsfile");
-
-                Class.forName("com.mysql.jdbc.Driver");
-                sql = DriverManager.getConnection("jdbc:mysql://localhost/juick?autoReconnect=true&user=" +
-                        conf.getProperty("mysql_username", "") + "&password=" + conf.getProperty("mysql_password", ""));
+                sql = (JdbcTemplate) sce.getServletContext().getAttribute("sql");
 
                 childParsers.put(JuickMessage.XMLNS, new JuickMessage());
 
@@ -170,7 +167,7 @@ public class XMPPComponent implements ServletContextListener {
                 executorService.submit(connRouter);
                 executorService.submit(new ConnectionListener());
                 executorService.submit(new CleaningUp());
-            } catch (IOException | ClassNotFoundException | SQLException e) {
+            } catch (IOException e) {
                 LOGGER.log(Level.SEVERE, "XMPPComponent error", e);
             }
         });
@@ -195,39 +192,7 @@ public class XMPPComponent implements ServletContextListener {
                 i.remove();
             }
         }
-
         XMPPComponent.connRouter.closeConnection();
-
-        synchronized (XMPPComponent.sqlSync) {
-            if (XMPPComponent.sql != null) {
-                try {
-                    XMPPComponent.sql.close();
-                    XMPPComponent.sql = null;
-                } catch (SQLException e) {
-                    LOGGER.warning("SQL ERROR: " + e);
-                }
-            }
-        }
-        // Now deregister JDBC drivers in this context's ClassLoader:
-        // Get the webapp's ClassLoader
-        ClassLoader cl = Thread.currentThread().getContextClassLoader();
-        // Loop through all drivers
-        Enumeration<Driver> drivers = DriverManager.getDrivers();
-        while (drivers.hasMoreElements()) {
-            Driver driver = drivers.nextElement();
-            if (driver.getClass().getClassLoader() == cl) {
-                // This driver was registered by the webapp's ClassLoader, so deregister it:
-                try {
-                    LOGGER.info(String.format("Deregistering JDBC driver %s", driver.toString()));
-                    DriverManager.deregisterDriver(driver);
-                } catch (SQLException ex) {
-                    LOGGER.log(Level.SEVERE, String.format("Error deregistering JDBC driver %s", driver), ex);
-                }
-            } else {
-                // driver was not registered by the webapp's ClassLoader and may be in use elsewhere
-                LOGGER.log(Level.SEVERE, String.format("Not deregistering JDBC driver %s as it does not belong to this webapp's ClassLoader", driver));
-            }
-        }
         executorService.shutdown();
         LOGGER.info("component destroyed");
     }
-- 
cgit v1.2.3