almost working api project

4 files changed, 103 insertions, 151 deletions

diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index 9cbf4240..fd664909 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -19,7 +19,10 @@ package com.juick.server;
 
 import com.juick.Message;
 import com.juick.User;
+import org.springframework.dao.EmptyResultDataAccessException;
 import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
+import org.springframework.jdbc.support.KeyHolder;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -41,31 +44,25 @@ public class MessagesQueries {
 
     private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName());
 
-    public static int createMessage(Connection sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) {
-        int mid = 0;
-
-        PreparedStatement stmt = null;
-        ResultSet rs;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
+    public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) {
+        KeyHolder holder = new GeneratedKeyHolder();
+        sql.update(con -> {
+            PreparedStatement stmt = con.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)",
+                    Statement.RETURN_GENERATED_KEYS);
             stmt.setInt(1, uid);
             if (attachment != null) {
                 stmt.setString(2, attachment);
             } else {
                 stmt.setNull(2, Types.VARCHAR);
             }
-            stmt.executeUpdate();
-            rs = stmt.getGeneratedKeys();
-            if (rs.first()) {
-                mid = rs.getInt(1);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
+            return stmt;
+        }, holder);
+
+        int mid = holder.getKey().intValue();
+
 
         if (mid > 0) {
+
             String tagsNames = "";
             String tagsIDs = "";
 
@@ -77,42 +74,33 @@ public class MessagesQueries {
                 tagsNames += tags.get(i).Name;
                 tagsIDs += "(" + mid + "," + tags.get(i).TID + ")";
             }
-
-            try {
-                stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs);
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                LOGGER.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt);
+            if (tags.size() > 0) {
+                sql.execute("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs);
             }
-
-            try {
-                stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)");
+            final String finalTagsNames = tagsNames;
+            sql.update(con -> {
+                PreparedStatement stmt = con.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) " +
+                        "VALUES (?,?,?)", Statement.NO_GENERATED_KEYS);
                 stmt.setInt(1, mid);
-                if (tagsNames.isEmpty()) {
+                if (finalTagsNames.isEmpty()) {
                     stmt.setNull(2, Types.VARCHAR);
                 } else {
-                    stmt.setString(2, tagsNames);
+                    stmt.setString(2, finalTagsNames);
                 }
                 stmt.setString(3, txt);
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                LOGGER.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt);
-            }
+                return stmt;
+            });
         }
 
         return mid;
     }
 
-    public static int createReply(Connection sql, int mid, int rid, int uid, String txt, String attachment) {
+    public static int createReply(JdbcTemplate sql, int mid, int rid, int uid, String txt, String attachment) {
         int ridnew = getReplyIDIncrement(sql, mid);
 
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id,replyto,attach,txt) VALUES (?,?,?,?,?,?)");
+        sql.update( con -> {
+            PreparedStatement stmt = con.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id," +
+                    "replyto,attach,txt) VALUES (?,?,?,?,?,?)", Statement.NO_GENERATED_KEYS);
             stmt.setInt(1, mid);
             stmt.setInt(2, ridnew);
             stmt.setInt(3, uid);
@@ -123,32 +111,18 @@ public class MessagesQueries {
                 stmt.setNull(5, Types.VARCHAR);
             }
             stmt.setString(6, txt);
-            if (stmt.executeUpdate() == 0) {
-                ridnew = 0;
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
+            return stmt;
+        });
 
         if (ridnew > 0) {
-            SQLHelpers.executeInt(sql, "UPDATE messages SET replies=replies+1 WHERE message_id=?", mid);
+            sql.update("UPDATE messages SET replies=replies+1 WHERE message_id=?", mid);
         }
 
         return ridnew;
     }
 
-    public static int getReplyIDIncrement(Connection sql, int mid) {
-        int rid = 0;
-
-        SQLHelpers.execute(sql, "LOCK TABLES messages WRITE");
-        if (SQLHelpers.executeInt(sql, "UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid) == 1) {
-            rid = SQLHelpers.getInt(sql, "SELECT maxreplyid FROM messages WHERE message_id=?", mid, 0);
-        }
-        SQLHelpers.execute(sql, "UNLOCK TABLES");
-
-        return rid;
+    public static int getReplyIDIncrement(JdbcTemplate sql, int mid) {
+        return sql.update("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid);
     }
 
     public static boolean recommendMessage(Connection sql, int mid, int vuid) {
@@ -230,51 +204,56 @@ public class MessagesQueries {
         return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4);
     }
 
+
     public static com.juick.Message getMessage(JdbcTemplate sql, int mid) {
-        return sql.queryForObject("SELECT messages.user_id,users.nick,"
-                + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
-                + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),"
-                + "messages.readonly,messages.privacy,messages.replies,"
-                + "messages.attach,messages.place_id,messages.lat,"
-                + "messages.lon,messages.likes,messages.hidden,"
-                + "txt.tags,txt.repliesby,txt.txt FROM messages "
-                + "INNER JOIN users ON messages.user_id=users.id "
-                + "INNER JOIN messages_txt AS txt "
-                + "ON messages.message_id=txt.message_id "
-                + "WHERE messages.message_id=?", new Object[]{mid},
-                (rs, rowNum) -> {
-                    Message msg = new Message();
-                    msg.setMID(mid);
-                    msg.setUser(new User());
-                    msg.getUser().setUID(rs.getInt(1));
-                    msg.getUser().setUName(rs.getString(2));
-                    msg.TimeAgo = rs.getInt(3);
-                    msg.TimestampString = rs.getString(4);
-                    msg.ReadOnly = rs.getBoolean(5);
-                    msg.Privacy = rs.getInt(6);
-                    msg.FriendsOnly = msg.Privacy < 0;
-                    msg.Replies = rs.getInt(7);
-                    msg.AttachmentType = rs.getString(8);
-                    //if (rs.getInt(9) > 0) {
-                    //    msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9));
-                    //}
-                    if (rs.getDouble(10) != 0) {
-                        msg.Place = new com.juick.Place();
-                        msg.Place.lat = rs.getDouble(10);
-                        msg.Place.lon = rs.getDouble(11);
-                    }
-                    msg.Likes = rs.getInt(12);
-                    msg.Hidden = rs.getBoolean(13);
-                    msg.parseTags(rs.getString(14));
-                    msg.RepliesBy = rs.getString(15);
-                    msg.setText(rs.getString(16));
-                    return msg;
-                });
+        try {
+            return sql.queryForObject("SELECT messages.user_id,users.nick,"
+                            + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
+                            + "messages.ts,"
+                            + "messages.readonly,messages.privacy,messages.replies,"
+                            + "messages.attach,messages.place_id,messages.lat,"
+                            + "messages.lon,messages.likes,messages.hidden,"
+                            + "txt.tags,txt.repliesby,txt.txt FROM messages "
+                            + "INNER JOIN users ON messages.user_id=users.id "
+                            + "INNER JOIN messages_txt AS txt "
+                            + "ON messages.message_id=txt.message_id "
+                            + "WHERE messages.message_id=?", new Object[]{mid},
+                    (rs, rowNum) -> {
+                        Message msg = new Message();
+                        msg.setMID(mid);
+                        msg.setUser(new User());
+                        msg.getUser().setUID(rs.getInt(1));
+                        msg.getUser().setUName(rs.getString(2));
+                        msg.TimeAgo = rs.getInt(3);
+                        msg.setDate(rs.getDate(4));
+                        msg.ReadOnly = rs.getBoolean(5);
+                        msg.Privacy = rs.getInt(6);
+                        msg.FriendsOnly = msg.Privacy < 0;
+                        msg.Replies = rs.getInt(7);
+                        msg.AttachmentType = rs.getString(8);
+                        //if (rs.getInt(9) > 0) {
+                        //    msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9));
+                        //}
+                        if (rs.getDouble(10) != 0) {
+                            msg.Place = new com.juick.Place();
+                            msg.Place.lat = rs.getDouble(10);
+                            msg.Place.lon = rs.getDouble(11);
+                        }
+                        msg.Likes = rs.getInt(12);
+                        msg.Hidden = rs.getBoolean(13);
+                        msg.parseTags(rs.getString(14));
+                        msg.RepliesBy = rs.getString(15);
+                        msg.setText(rs.getString(16));
+                        return msg;
+                    });
+        } catch (EmptyResultDataAccessException e) {
+            return null;
+        }
     }
 
     public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) {
         return sql.queryForObject("SELECT replies.user_id,users.nick,"
-                + "replies.replyto,DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),"
+                + "replies.replyto,replies.ts,"
                 + "replies.attach,replies.txt FROM replies INNER JOIN users "
                 + "ON replies.user_id=users.id "
                 + "WHERE replies.message_id=? AND replies.reply_id=?",
@@ -286,7 +265,7 @@ public class MessagesQueries {
                     msg.getUser().setUID(rs.getInt(1));
                     msg.getUser().setUName(rs.getString(2));
                     msg.ReplyTo = rs.getInt(3);
-                    msg.TimestampString = rs.getString(4);
+                    msg.setDate(rs.getDate(4));
                     msg.AttachmentType = rs.getString(5);
                     msg.setText(rs.getString(6));
                     return msg;
@@ -883,7 +862,7 @@ public class MessagesQueries {
         return sql.query("SELECT messages.message_id,messages.user_id,users.nick,"
                 + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,"
                 + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
-                + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,"
+                + "messages.ts,messages.replies,"
                 + "messages_txt.repliesby,messages.attach,messages.lat,"
                 + "messages.lon,messages.likes "
                 + "FROM (messages INNER JOIN messages_txt "
@@ -906,7 +885,7 @@ public class MessagesQueries {
                     msg.FriendsOnly = msg.Privacy < 0;
                     msg.setText(rs.getString(7));
                     msg.TimeAgo = rs.getInt(8);
-                    msg.TimestampString = rs.getString(9);
+                    msg.setDate(rs.getDate(9));
                     msg.Replies = rs.getInt(10);
                     msg.RepliesBy = rs.getString(11);
                     msg.AttachmentType = rs.getString(12);
@@ -926,7 +905,7 @@ public class MessagesQueries {
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
-            stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC");
+            stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC");
             stmt.setInt(1, mid);
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -941,7 +920,7 @@ public class MessagesQueries {
                 msg.getUser().Banned = rs.getBoolean(5);
                 msg.setText(rs.getString(6));
                 msg.TimeAgo = rs.getInt(7);
-                msg.TimestampString = rs.getString(8);
+                msg.setDate(rs.getDate(8));
                 msg.AttachmentType = rs.getString(9);
 
                 replies.add(msg);
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index 7984844a..345f641f 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -154,7 +154,7 @@ public class PMQueries {
                     msg.setUser(new User());
                     msg.getUser().setUID(uuid);
                     msg.setText(rs.getString(2));
-                    msg.TimestampString = rs.getString(3);
+                    msg.setDate(rs.getDate(3));
                     return msg;
                 }, new Object[]{uid, uid_to, uid, uid_to});
 
@@ -170,7 +170,7 @@ public class PMQueries {
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
-            stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20");
+            stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20");
             stmt.setInt(1, uid);
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -181,7 +181,7 @@ public class PMQueries {
                 msg.getUser().setUName(rs.getString(2));
                 msg.setText(rs.getString(3));
                 msg.TimeAgo = rs.getInt(4);
-                msg.TimestampString = rs.getString(5);
+                msg.setDate(rs.getDate(5));
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
@@ -199,7 +199,7 @@ public class PMQueries {
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
-            stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20");
+            stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20");
             stmt.setInt(1, uid);
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -210,7 +210,7 @@ public class PMQueries {
                 msg.getUser().setUName(rs.getString(2));
                 msg.setText(rs.getString(3));
                 msg.TimeAgo = rs.getInt(4);
-                msg.TimestampString = rs.getString(5);
+                msg.setDate(rs.getDate(5));
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java
index 5bc87e28..22839ad0 100644
--- a/src/main/java/com/juick/server/TagQueries.java
+++ b/src/main/java/com/juick/server/TagQueries.java
@@ -40,28 +40,15 @@ public class TagQueries {
 
     private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName());
 
-    public static com.juick.Tag getTag(Connection sql, int tid) {
-        com.juick.Tag ret = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?");
-            stmt.setInt(1, tid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = new com.juick.Tag();
-                ret.TID = tid;
-                ret.SynonymID = rs.getInt(1);
-                ret.Name = rs.getString(2);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return ret;
+    public static com.juick.Tag getTag(JdbcTemplate sql, int tid) {
+        return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?",
+                new Object[]{tid}, (rs, num) -> {
+                    Tag ret = new Tag();
+                    ret.TID = tid;
+                    ret.SynonymID = rs.getInt(1);
+                    ret.Name = rs.getString(2);
+                    return ret;
+                });
     }
 
     public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) {
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index 2cd557c6..5588d8e4 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -199,27 +199,13 @@ public class UserQueries {
         return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+");
     }
 
-    public static int checkPassword(Connection sql, String username, String password) {
-        int uid = 0;
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?");
-            stmt.setString(1, username);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                if (password.equals(rs.getString(2))) {
-                    uid = rs.getInt(1);
-                } else {
-                    uid = -1;
-                }
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+    public static int checkPassword(JdbcTemplate sql, String username, String password) {
+        String realPassword = sql.queryForObject("SELECT passw FROM users WHERE nick=?", String.class, username);
+        if (realPassword.equals(password)) {
+            return UserQueries.getUserByName(sql, username).getUID();
+        } else {
+            return -1;
         }
-        return uid;
     }
 
     public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) {