spring-jdbc near complete

14 files changed, 225 insertions, 292 deletions

diff --git a/deps/com.juick.server b/deps/com.juick.server
-Subproject 9e740f48cca7332c67a165c6cf60e09bc549e68
+Subproject 6362eab34390b187944c7e3c12eb444e2fb2c45
diff --git a/src/main/java/com/juick/CrosspostComponent.java b/src/main/java/com/juick/CrosspostComponent.java
index d5f13ab2..d7ee8724 100644
--- a/src/main/java/com/juick/CrosspostComponent.java
+++ b/src/main/java/com/juick/CrosspostComponent.java
@@ -24,6 +24,9 @@ import com.juick.xmpp.Stream;
 import com.juick.xmpp.StreamComponent;
 import com.juick.xmpp.extensions.JuickMessage;
 import org.apache.commons.codec.binary.Base64;
+import org.apache.commons.lang3.tuple.Pair;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
 import javax.crypto.Mac;
 import javax.crypto.spec.SecretKeySpec;
@@ -35,7 +38,6 @@ import java.net.Socket;
 import java.net.URL;
 import java.net.URLEncoder;
 import java.security.Key;
-import java.sql.Connection;
 import java.sql.Driver;
 import java.sql.DriverManager;
 import java.sql.SQLException;
@@ -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", ""));
+                setupSql(conf.getProperty("datasource_driver", "com.mysql.jdbc.Driver"), conf.getProperty("datasource_url", ""));
                 setupXmppComponent(conf.getProperty("xmpp_password", ""));
             } catch (Exception e) {
                 logger.log(Level.SEVERE, e.getMessage(), e);
@@ -115,15 +116,11 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
         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 driver, String url) {
+        DriverManagerDataSource dataSource = new DriverManagerDataSource();
+        dataSource.setDriverClassName(driver);
+        dataSource.setUrl(url);
+        sql = new JdbcTemplate(dataSource);
     }
 
     public void setupXmppComponent(String password) {
@@ -161,8 +158,8 @@ public class CrosspostComponent implements ServletContextListener,  Stream.Strea
     }
 
     public boolean facebookPost(com.juick.Message jmsg) {
-        String token = CrosspostQueries.getFacebookToken(sql, jmsg.getUser().getUID());
-        if (token == null) {
+        String token = CrosspostQueries.getFacebookToken(sql, jmsg.getUser().getUID()).orElse("");
+        if (token.isEmpty()) {
             return false;
         }
 
@@ -198,8 +195,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()).orElse(Pair.of("", ""));
+        if (tokens.getLeft().isEmpty() || tokens.getRight().isEmpty()) {
             return false;
         }
 
@@ -209,7 +206,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 +232,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()).orElse(Pair.of("", ""));
+        if (tokens.getLeft().isEmpty() || tokens.getRight().isEmpty()) {
             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 0cb8e626..e33d5fc3 100644
--- a/src/main/java/com/juick/PushComponent.java
+++ b/src/main/java/com/juick/PushComponent.java
@@ -24,6 +24,8 @@ import com.google.android.gcm.server.Sender;
 import com.juick.json.MessageSerializer;
 import com.juick.server.MessagesQueries;
 import com.juick.server.PushQueries;
+import com.juick.server.SubscriptionsQueries;
+import com.juick.server.UserQueries;
 import com.juick.xmpp.JID;
 import com.juick.xmpp.Message.MessageListener;
 import com.juick.xmpp.Stream;
@@ -45,6 +47,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;
@@ -60,6 +64,7 @@ import java.util.concurrent.Executors;
 import java.util.logging.Level;
 import java.util.logging.LogManager;
 import java.util.logging.Logger;
+import java.util.stream.Collectors;
 
 /**
  *
@@ -72,7 +77,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 +96,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", "com.mysql.jdbc.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) {
@@ -127,15 +131,11 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
         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 driver, String url) {
+        DriverManagerDataSource dataSource = new DriverManagerDataSource();
+        dataSource.setDriverClassName(driver);
+        dataSource.setUrl(url);
+        sql = new JdbcTemplate(dataSource);
     }
 
     public void setupXmppComponent(JID jid, String host, int port, String password) {
@@ -157,42 +157,36 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
     }
 
     @Override
-    public void onStreamFail(String e) {logger.log(Level.SEVERE, "XMPP STREAM FAIL", e);}
+    public void onStreamFail(Exception e) {logger.log(Level.SEVERE, "XMPP STREAM FAIL", e);}
 
     @Override
     public void onMessage(com.juick.xmpp.Message msg) {
-        JuickMessage jmsg = (JuickMessage) msg.getChild(JuickMessage.XMLNS);
-        if (jmsg == null) {
-            return;
-        }
-        logger.info("Message to push: " + msg.toString());
-
+        JuickMessage jmsg = (JuickMessage)msg.getChild(JuickMessage.XMLNS);
+        List<User> subscribedUsers = new ArrayList<>();
         boolean isPM = jmsg.getMID() == 0;
         boolean isReply = jmsg.getRID() > 0;
-        int senderID = 0, recipientID = 0;
-        if (isReply) {
-            senderID = jmsg.getUser().getUID();
-        }
+        int pmTo = 0;
         if (isPM) {
-            // PM
-            try {
-                recipientID = Integer.parseInt(msg.to.Username);
-            } catch (NumberFormatException e) {
-                logger.info("Wrong PM recipient: " + msg.to.Username);
-                return;
+            pmTo = Integer.parseInt(msg.to.Username);
+        } else {
+            if (isReply) {
+                subscribedUsers =
+                        SubscriptionsQueries.getUsersSubscribedToComments(sql, jmsg.getMID(), jmsg.getUser().getUID());
+            } else {
+                // new message
+                subscribedUsers = SubscriptionsQueries.getSubscribedUsers(sql, jmsg.getUser().getUID(), jmsg.getMID());
             }
         }
 
         /*** ANDROID ***/
-        List<String> regids;
+        final List<String> regids = new ArrayList<>();
         if (isPM) {
-            regids = new ArrayList<>();
-            String targetId = PushQueries.getAndroidRegID(sql, recipientID);
-            if (targetId != null && !targetId.isEmpty()) {
-                regids.add(targetId);
-            }
+            PushQueries.getAndroidRegID(sql, pmTo).ifPresent(regids::add);
         } else {
-            regids = isReply ? PushQueries.getAndroidSubscribersToComments(sql, jmsg.getMID(), senderID) : PushQueries.getAndroidSubscribers(sql, senderID);
+            List<Integer> uids = subscribedUsers.stream().map(User::getUID).collect(Collectors.toList());
+            if (uids.size() > 0) {
+                regids.addAll(PushQueries.getAndroidTokens(sql, uids));
+            }
         }
 
         if (!regids.isEmpty()) {
@@ -216,15 +210,14 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
         }
 
         /*** WinPhone ***/
-        List<String> urls;
+        final List<String> urls = new ArrayList<>();
         if (isPM) {
-            urls = new ArrayList<>();
-            String targetURL = PushQueries.getWinPhoneURL(sql, recipientID);
-            if (!TextUtils.isEmpty(targetURL)) {
-                urls.add(targetURL);
-            }
+            PushQueries.getWinPhoneURL(sql, pmTo).ifPresent(urls::add);
         } else {
-            urls = isReply ? PushQueries.getWindowsSubscribersToComments(sql, jmsg.getMID(), senderID) :PushQueries.getWinPhoneSubscribers(sql, senderID);
+            List<Integer> uids = subscribedUsers.stream().map(User::getUID).collect(Collectors.toList());
+            if (uids.size() > 0) {
+                urls.addAll(PushQueries.getWindowsTokens(sql, uids));
+            }
         }
 
 
@@ -262,15 +255,14 @@ public class PushComponent implements ServletContextListener, Stream.StreamListe
         }
 
         /*** iOS ***/
-        List<String> tokens;
+        final List<String> tokens = new ArrayList<>();
         if (isPM) {
-            tokens = new ArrayList<>();
-            String targetToken = PushQueries.getAPNSToken(sql, recipientID);
-            if (targetToken != null && !targetToken.isEmpty()) {
-                tokens.add(targetToken);
-            }
+            PushQueries.getAPNSToken(sql, pmTo).ifPresent(tokens::add);
         } else {
-            tokens = isReply ? PushQueries.getAppleSubscribersToComments(sql, jmsg.getMID(), senderID) : PushQueries.getAPNSSubscribers(sql, senderID);
+            List<Integer> uids = subscribedUsers.stream().map(User::getUID).collect(Collectors.toList());
+            if (uids.size() > 0) {
+                tokens.addAll(PushQueries.getAPNSTokens(sql, uids));
+            }
         }
         if (!tokens.isEmpty()) {
             ApnsService service = APNS.newService().withCert("/etc/juick/ios.p12", "juick")
diff --git a/src/main/java/com/juick/http/www/Errors.java b/src/main/java/com/juick/http/www/Errors.java
index 2161daf1..0044c209 100644
--- a/src/main/java/com/juick/http/www/Errors.java
+++ b/src/main/java/com/juick/http/www/Errors.java
@@ -20,7 +20,7 @@ public class Errors {
         com.juick.User visitor = Utils.getVisitorUser(sql, request, response);
 
         if (tagsHTML == null) {
-            tagsHTML = PageTemplates.getPopularTags(sql, 80);
+            tagsHTML = PageTemplates.formatPopularTags(sql, 80);
         }
 
         response.setStatus(404);
diff --git a/src/main/java/com/juick/http/www/Main.java b/src/main/java/com/juick/http/www/Main.java
index 604ccf9a..63bf1c1f 100644
--- a/src/main/java/com/juick/http/www/Main.java
+++ b/src/main/java/com/juick/http/www/Main.java
@@ -104,7 +104,7 @@ public class Main extends HttpServlet implements Stream.StreamListener {
     }
 
     @Override
-    public void onStreamFail(String e) {log("XMPP STREAM FAIL:" + e);}
+    public void onStreamFail(Exception e) {log("XMPP STREAM FAIL:" + e);}
 
     @Override
     public void onStreamReady() {
diff --git a/src/main/java/com/juick/http/www/PM.java b/src/main/java/com/juick/http/www/PM.java
index b64e6c43..b351eb0e 100644
--- a/src/main/java/com/juick/http/www/PM.java
+++ b/src/main/java/com/juick/http/www/PM.java
@@ -25,14 +25,12 @@ import com.juick.xmpp.Stream;
 import com.juick.xmpp.extensions.JuickMessage;
 import org.springframework.jdbc.core.JdbcTemplate;
 
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.util.ArrayList;
-import java.util.List;
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
+import java.util.List;
 
 /**
  *
@@ -74,7 +72,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=\"" + PageTemplates.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() + "\"/>");
@@ -148,7 +146,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=\"" + PageTemplates.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>");
@@ -204,8 +202,8 @@ public class PM {
             msg.to.Host = "ws.juick.com";
             xmpp.send(msg);
 
-            String jid = UserQueries.getJIDbyUID(sql, uid);
-            if (jid != null) {
+            List<String> jids = UserQueries.getJIDsbyUID(sql, uid);
+            for (String jid : jids) {
                 Message mm = new Message();
                 mm.to = new JID(jid);
                 mm.type = Message.Type.chat;
diff --git a/src/main/java/com/juick/http/www/PageTemplates.java b/src/main/java/com/juick/http/www/PageTemplates.java
index 89b0c737..7021ccba 100644
--- a/src/main/java/com/juick/http/www/PageTemplates.java
+++ b/src/main/java/com/juick/http/www/PageTemplates.java
@@ -50,7 +50,7 @@ import ru.sape.Sape;
 public class PageTemplates {
 
     public static Sape sape = null;
-    private static final SimpleDateFormat sdfSQL = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+    protected static final SimpleDateFormat sdfSQL = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     private static SimpleDateFormat sdfSimple = new SimpleDateFormat("d MMM");
     private static SimpleDateFormat sdfFull = new SimpleDateFormat("d MMM yyyy");
     private static String tagsHTML = null;
@@ -226,7 +226,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) {
@@ -265,17 +265,16 @@ public class PageTemplates {
             }
             return days + " " + unit + " назад";
         } else {
-            String ret = fulldate;
+            String ret = sdfFull.format(fulldate);
             synchronized (sdfSQL) {
                 try {
-                    Date pDate = sdfSQL.parse(fulldate);
                     Calendar c = Calendar.getInstance();
                     int curyear = c.get(Calendar.YEAR);
-                    c.setTime(pDate);
+                    c.setTime(fulldate);
                     if (c.get(Calendar.YEAR) == curyear) {
-                        ret = sdfSimple.format(pDate);
+                        ret = sdfSimple.format(fulldate);
                     } else {
-                        ret = sdfFull.format(pDate);
+                        ret = sdfFull.format(fulldate);
                     }
                 } catch (Exception e) {
                     System.err.println("PARSE EXCEPTION: " + fulldate);
@@ -285,20 +284,11 @@ public class PageTemplates {
         }
     }
 
-    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) {
@@ -452,7 +442,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/Settings.java b/src/main/java/com/juick/http/www/Settings.java
index f197ff12..54ee0ee9 100644
--- a/src/main/java/com/juick/http/www/Settings.java
+++ b/src/main/java/com/juick/http/www/Settings.java
@@ -19,15 +19,12 @@ package com.juick.http.www;
 
 import org.springframework.jdbc.core.JdbcTemplate;
 
-import java.io.IOException;
-import java.io.PrintWriter;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.SQLException;
 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;
 
 /**
  *
diff --git a/src/main/java/com/juick/http/www/User.java b/src/main/java/com/juick/http/www/User.java
index a4fdcf21..d0c1aaee 100644
--- a/src/main/java/com/juick/http/www/User.java
+++ b/src/main/java/com/juick/http/www/User.java
@@ -17,6 +17,7 @@
  */
 package com.juick.http.www;
 
+import com.juick.Tag;
 import com.juick.server.MessagesQueries;
 import com.juick.server.TagQueries;
 import com.juick.server.UserQueries;
@@ -195,25 +196,14 @@ 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<com.juick.User> friends = UserQueries.getUserFriends(sql, user.getUID());
+            for (int i = 0; i < friends.size(); i++) {
+                if (i % 3 == 0 && i > 0) {
+                    out.print("</tr><tr>");
                 }
-            } catch (SQLException e) {
-                System.err.println(e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
+                out.print("<td><a href=\"/" + friends.get(i).getUName()
+                        + "/\"><img src=\"//i.juick.com/as/" + friends.get(i).getUID() + ".png\"/>"
+                        + friends.get(i).getUName() + "</a></td>");
             }
 
             out.println("</tr></table>");
@@ -241,25 +231,14 @@ 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<com.juick.User> readers = UserQueries.getUserReaders(sql, user.getUID());
+            for (int i = 0; i < readers.size(); i++) {
+                if (i % 3 == 0 && i > 0) {
+                    out.print("</tr><tr>");
                 }
-            } catch (SQLException e) {
-                System.err.println(e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
+                out.print("<td><a href=\"/" + readers.get(i).getUName()
+                        + "/\"><img src=\"//i.juick.com/as/" + readers.get(i).getUID() + ".png\"/>"
+                        + readers.get(i).getUName() + "</a></td>");
             }
 
             out.println("</tr></table>");
@@ -340,55 +319,20 @@ public class User {
     }
 
     public static String pageUserTags(JdbcTemplate 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);
-        }
-
-        if (tags != null && cnt > 0) {
-            Arrays.sort(tags, 0, cnt);
-        }
-
+        List<Tag> tags = TagQueries.getUserTagsAll(sql, user.getUID());
+        int maxUsageCnt = tags.stream().map(t -> t.UsageCnt).max(Integer::max).get();
         String ret = "";
         for (int i = 0; i < cnt; i++) {
-            String tag = Utils.encodeHTML(tags[i].Name);
+            String tag = Utils.encodeHTML(tags.get(i).Name);
             try {
-                tag = "<a href=\"./?tag=" + URLEncoder.encode(tags[i].Name, "UTF-8") + "\" title=\"" + tags[i].UsageCnt + "\" rel=\"nofollow\">" + tag + "</a>";
+                tag = "<a href=\"./?tag=" + URLEncoder.encode(tags.get(i).Name, "UTF-8") + "\" title=\""
+                        + tags.get(i).UsageCnt + "\" rel=\"nofollow\">" + tag + "</a>";
             } catch (UnsupportedEncodingException e) {
             }
 
-            if (tags[i].UsageCnt > maxUsageCnt / 3 * 2) {
+            if (tags.get(i).UsageCnt > maxUsageCnt / 3 * 2) {
                 ret += "<big>" + tag + "</big> ";
-            } else if (tags[i].UsageCnt > maxUsageCnt / 3) {
+            } else if (tags.get(i).UsageCnt > maxUsageCnt / 3) {
                 ret += "<small>" + tag + "</small> ";
             } else {
                 ret += tag + " ";
diff --git a/src/main/java/com/juick/http/www/UserThread.java b/src/main/java/com/juick/http/www/UserThread.java
index 4ee290ab..638e3a3b 100644
--- a/src/main/java/com/juick/http/www/UserThread.java
+++ b/src/main/java/com/juick/http/www/UserThread.java
@@ -21,9 +21,10 @@ 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 java.io.IOException;
 import java.io.PrintWriter;
-import java.sql.Connection;
 import java.util.ArrayList;
 import java.util.List;
 import javax.servlet.ServletException;
@@ -36,7 +37,7 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class UserThread {
 
-    protected void doGetThread(Connection sql, HttpServletRequest request, HttpServletResponse response, int MID) throws ServletException, IOException {
+    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)) {
@@ -98,7 +99,7 @@ public class UserThread {
         }
     }
 
-    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 +129,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,7 +190,7 @@ 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>();
@@ -297,7 +298,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=\"" + PageTemplates.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>");
@@ -341,7 +342,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=\"" + PageTemplates.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/xmpp/extensions/JuickMessage.java b/src/main/java/com/juick/xmpp/extensions/JuickMessage.java
index 53dd6deb..220caba7 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;
 
@@ -31,12 +35,17 @@ 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) {
         super(msg);
+        df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+        df.setTimeZone(TimeZone.getTimeZone("UTC"));
     }
 
     @Override
@@ -45,7 +54,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 +81,7 @@ 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 + "\"";
diff --git a/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java b/src/main/java/com/juick/xmpp/s2s/ConnectionRouter.java
index 443bfa82..d0b3a93b 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.*;
 import com.juick.xmpp.extensions.JuickMessage;
 import com.juick.xmpp.extensions.Nickname;
@@ -9,6 +11,8 @@ import com.juick.xmpp.extensions.XOOB;
 
 import java.io.IOException;
 import java.net.Socket;
+import java.util.ArrayList;
+import java.util.Collections;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
@@ -45,13 +49,18 @@ public class ConnectionRouter implements Stream.StreamListener,
     }
 
     public void sendJuickMessage(JuickMessage jmsg) {
-        List<String> jids;
+        List<String> jids = new ArrayList<>();
 
         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());
+                List<User> users = SubscriptionsQueries.getSubscribedUsers(XMPPComponent.sql, jmsg.getUser().getUID(), jmsg.getMID());
+                for (User user : users) {
+                    for (String jid : UserQueries.getJIDsbyUID(XMPPComponent.sql, user.getUID())) {
+                        jids.add(jid);
+                    }
+                }
             }
         }
 
@@ -86,14 +95,14 @@ public class ConnectionRouter implements Stream.StreamListener,
     }
 
     public void sendJuickComment(JuickMessage jmsg) {
-        List<String> jids;
+        List<User> users;
         String replyQuote;
         String replyTo;
 
         synchronized (XMPPComponent.sqlSync) {
-            jids = SubscriptionsQueries.getJIDSubscribedToComments(XMPPComponent.sql, jmsg.getMID(), jmsg.getUser().getUID());
-	    com.juick.Message replyMessage = jmsg.ReplyTo > 0 ? MessagesQueries.getReply(XMPPComponent.sql, jmsg.getMID(), jmsg.ReplyTo)
-	    		: MessagesQueries.getMessage(XMPPComponent.sql, jmsg.getMID());
+            users = SubscriptionsQueries.getUsersSubscribedToComments(XMPPComponent.sql, jmsg.getMID(), jmsg.getUser().getUID());
+            com.juick.Message replyMessage = jmsg.ReplyTo > 0 ? MessagesQueries.getReply(XMPPComponent.sql, jmsg.getMID(), jmsg.ReplyTo)
+                    : MessagesQueries.getMessage(XMPPComponent.sql, jmsg.getMID());
             replyTo = replyMessage.getUser().getUName();
             replyQuote = getReplyQuote(replyMessage);
         }
@@ -110,9 +119,13 @@ public class ConnectionRouter implements Stream.StreamListener,
         msg.body = txt;
         msg.type = Message.Type.chat;
         msg.addChild(jmsg);
-        for (String jid : jids) {
-            msg.to = new JID(jid);
-            XMPPComponent.sendOut(msg);
+        for (User user : users) {
+            synchronized (XMPPComponent.sqlSync) {
+                for (String jid : UserQueries.getJIDsbyUID(XMPPComponent.sql, user.getUID())) {
+                    msg.to = new JID(jid);
+                    XMPPComponent.sendOut(msg);
+                }
+            }
         }
     }
 
@@ -127,11 +140,11 @@ public class ConnectionRouter implements Stream.StreamListener,
     }
 
     public void sendJuickRecommendation(JuickMessage recomm) {
-        List<String> jids;
+        List<User> users;
         JuickMessage jmsg;
         synchronized (XMPPComponent.sqlSync) {
             jmsg = new JuickMessage(MessagesQueries.getMessage(XMPPComponent.sql, recomm.getMID()));
-            jids = SubscriptionsQueries.getJIDSubscribedToUserRecommendations(XMPPComponent.sql,
+            users = SubscriptionsQueries.getUsersSubscribedToUserRecommendations(XMPPComponent.sql,
                     recomm.getUser().getUID(), recomm.getMID(), jmsg.getUser().getUID());
         }
 
@@ -168,9 +181,13 @@ public class ConnectionRouter implements Stream.StreamListener,
             msg.addChild(oob);
         }
 
-        for (String jid : jids) {
-            msg.to = new JID(jid);
-            XMPPComponent.sendOut(msg);
+        for (User user : users) {
+            synchronized (XMPPComponent.sqlSync) {
+                for (String jid : UserQueries.getJIDsbyUID(XMPPComponent.sql, user.getUID())) {
+                    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 25c75dfe..1172d75f 100644
--- a/src/main/java/com/juick/xmpp/s2s/JuickBot.java
+++ b/src/main/java/com/juick/xmpp/s2s/JuickBot.java
@@ -194,7 +194,7 @@ public class JuickBot {
             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(user_from);
             }
             jmsg.setText(msg.body);
             m.childs.add(jmsg);
@@ -203,29 +203,25 @@ public class JuickBot {
             m.to.Host = "ws.juick.com";
             XMPPComponent.connRouter.router.send(m.toString());
 
-            String jid;
+            List<String> jids;
             boolean inroster = false;
             synchronized (XMPPComponent.sqlSync) {
-                jid = UserQueries.getJIDbyUID(XMPPComponent.sql, uid_to);
-                if (jid != null) {
+                jids = UserQueries.getJIDsbyUID(XMPPComponent.sql, uid_to);
+                for (String jid : jids) {
+                    Message mm = new Message();
+                    mm.to = new JID(jid);
+                    mm.type = Message.Type.chat;
                     inroster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid);
+                    if (inroster) {
+                        mm.from = new JID(jmsg.getUser().getUName(), "juick.com", "Juick");
+                        mm.body = msg.body;
+                    } else {
+                        mm.from = new JID("juick", "juick.com", "Juick");
+                        mm.body = "Private message from @" + jmsg.getUser().getUName() + ":\n" + msg.body;
+                    }
+                    XMPPComponent.sendOut(mm);
                 }
             }
-
-            if (jid != null) {
-                Message mm = new Message();
-                mm.to = new JID(jid);
-                mm.type = Message.Type.chat;
-                if (inroster) {
-                    mm.from = new JID(jmsg.getUser().getUName(), "juick.com", "Juick");
-                    mm.body = msg.body;
-                } else {
-                    mm.from = new JID("juick", "juick.com", "Juick");
-                    mm.body = "Private message from @" + jmsg.getUser().getUName() + ":\n" + msg.body;
-                }
-                XMPPComponent.sendOut(mm);
-            }
-
         } else {
             Message reply = new Message(msg.to, msg.from, Message.Type.error);
             reply.id = msg.id;
@@ -300,7 +296,7 @@ public class JuickBot {
         int ret = 0;
 
         int uid_to = 0;
-        String jid_to = null;
+        List<String> jids_to = null;
         boolean haveInRoster = false;
 
         synchronized (XMPPComponent.sqlSync) {
@@ -313,10 +309,7 @@ public class JuickBot {
             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);
-                        }
+                        jids_to = UserQueries.getJIDsbyUID(XMPPComponent.sql, uid_to);
                         ret = 200;
                     } else {
                         ret = 500;
@@ -327,45 +320,46 @@ public class JuickBot {
             } else {
                 ret = 404;
             }
-        }
 
-        if (ret == 200) {
-            Message msg = new Message();
-            msg.from = new JID("juick", "juick.com", null);
-            msg.to = new JID(Integer.toString(uid_to), "push.juick.com", null);
-            JuickMessage jmsg = new JuickMessage();
-            jmsg.setUser(user_from);
-            jmsg.setText(body);
-            msg.childs.add(jmsg);
-            XMPPComponent.connRouter.router.send(msg.toString());
-
-            msg.to.Host = "ws.juick.com";
-            XMPPComponent.connRouter.router.send(msg.toString());
-
-            if (jid_to != null) {
-                Message mm = new Message();
-                mm.to = new JID(jid_to);
-                mm.type = Message.Type.chat;
-                if (haveInRoster) {
-                    mm.from = new JID(user_from.getUName(), "juick.com", "Juick");
-                    mm.body = body;
-                } else {
-                    mm.from = new JID("juick", "juick.com", "Juick");
-                    mm.body = "Private message from @" + user_from.getUName() + ":\n" + body;
+            if (ret == 200) {
+                Message msg = new Message();
+                msg.from = new JID("juick", "juick.com", null);
+                msg.to = new JID(Integer.toString(uid_to), "push.juick.com", null);
+                JuickMessage jmsg = new JuickMessage();
+                jmsg.setUser(user_from);
+                jmsg.setText(body);
+                msg.childs.add(jmsg);
+                XMPPComponent.connRouter.router.send(msg.toString());
+
+                msg.to.Host = "ws.juick.com";
+                XMPPComponent.connRouter.router.send(msg.toString());
+
+                for (String jid : jids_to) {
+                    Message mm = new Message();
+                    mm.to = new JID(jid);
+                    mm.type = Message.Type.chat;
+                    haveInRoster = PMQueries.havePMinRoster(XMPPComponent.sql, user_from.getUID(), jid);
+                    if (haveInRoster) {
+                        mm.from = new JID(user_from.getUName(), "juick.com", "Juick");
+                        mm.body = body;
+                    } else {
+                        mm.from = new JID("juick", "juick.com", "Juick");
+                        mm.body = "Private message from @" + user_from.getUName() + ":\n" + body;
+                    }
+                    XMPPComponent.sendOut(mm);
                 }
-                XMPPComponent.sendOut(mm);
             }
-        }
 
-        Message reply = new Message(m.to, m.from);
-        if (ret == 200) {
-            reply.type = m.type;
-            reply.body = "Private message sent";
-        } else {
-            reply.type = Message.Type.error;
-            reply.body = "Error " + ret;
+            Message reply = new Message(m.to, m.from);
+            if (ret == 200) {
+                reply.type = m.type;
+                reply.body = "Private message sent";
+            } else {
+                reply.type = Message.Type.error;
+                reply.body = "Error " + ret;
+            }
+            XMPPComponent.sendOut(reply);
         }
-        XMPPComponent.sendOut(reply);
     }
 
     private static void commandBLShow(Message m, User user_from) throws Exception {
@@ -379,8 +373,8 @@ public class JuickBot {
 
         String txt = "";
         if (bltags.size() > 0) {
-            for (int i = 0; i < bltags.size(); i++) {
-                txt += "*" + bltags.get(i) + "\n";
+            for (String bltag : bltags) {
+                txt += "*" + bltag + "\n";
             }
 
             if (blusers.size() > 0) {
@@ -388,8 +382,8 @@ public class JuickBot {
             }
         }
         if (blusers.size() > 0) {
-            for (int i = 0; i < blusers.size(); i++) {
-                txt += "@" + blusers.get(i).getUName() + "\n";
+            for (User bluser : blusers) {
+                txt += "@" + bluser.getUName() + "\n";
             }
         }
         if (txt.isEmpty()) {
diff --git a/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java b/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
index 13df5fd0..70f01553 100644
--- a/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
+++ b/src/main/java/com/juick/xmpp/s2s/XMPPComponent.java
@@ -3,6 +3,8 @@ 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 org.springframework.jdbc.datasource.DriverManagerDataSource;
 import org.xmlpull.v1.XmlPullParserException;
 
 import javax.servlet.ServletContextEvent;
@@ -42,7 +44,7 @@ public class XMPPComponent implements ServletContextListener {
     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) {
@@ -176,9 +178,12 @@ public class XMPPComponent implements ServletContextListener {
                 keystore = conf.getProperty("keystore");
                 keystorePassword = conf.getProperty("keystore_password");
                 brokenSSLhosts = Arrays.asList(conf.getProperty("broken_ssl_hosts", "").split(","));
-                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", ""));
+                DriverManagerDataSource dataSource = new DriverManagerDataSource();
+                dataSource.setDriverClassName(conf.getProperty("datasource_driver", "com.mysql.jdbc.Driver"));
+                dataSource.setUrl(conf.getProperty("datasource_url"));
+                DriverManagerDataSource dataSourceSearch = new DriverManagerDataSource();
+                dataSourceSearch.setDriverClassName(conf.getProperty("datasource_driver", "com.mysql.jdbc.Driver"));
+                sql = new JdbcTemplate(dataSource);
 
                 childParsers.put(JuickMessage.XMLNS, new JuickMessage());
                 executorService.submit(() -> connRouter = new ConnectionRouter(componentName, conf.getProperty("xmpp_password")));
@@ -215,17 +220,6 @@ public class XMPPComponent implements ServletContextListener {
         } catch (IOException e) {
             LOGGER.log(Level.WARNING, "router warning", e);
         }
-
-        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();