spring-jdbc WIP

1 files changed, 79 insertions, 151 deletions

diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index cff8caff..8f658b42 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -17,6 +17,8 @@
  */
 package com.juick.server;
 
+import com.juick.Message;
+import com.juick.User;
 import org.springframework.jdbc.core.JdbcTemplate;
 
 import java.sql.Connection;
@@ -30,6 +32,7 @@ import java.util.Collections;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.core.RowMapper;
 
 /**
  *
@@ -228,67 +231,46 @@ public class MessagesQueries {
         return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4);
     }
 
-    public static com.juick.Message getMessage(Connection sql, int mid) {
-        com.juick.Message msg = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("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 FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?");
-            stmt.setInt(1, mid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                msg = new com.juick.Message();
-                msg.MID = mid;
-                msg.User = new com.juick.User();
-                msg.User.UID = rs.getInt(1);
-                msg.User.UName = 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);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        if (msg == null) {
-            return null;
-        }
-
-        try {
-            stmt = sql.prepareStatement("SELECT tags,repliesby,txt FROM messages_txt WHERE message_id=?");
-            stmt.setInt(1, mid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                if (rs.getString(1) != null) {
-                    msg.parseTags(rs.getString(1));
-                }
-                msg.RepliesBy = rs.getString(2);
-                msg.Text = rs.getString(3);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return msg;
+    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 "
+                + "ON messages.message_id = messages_txt.message_id "
+                + "WHERE messages.message_id=?", new Object[]{mid},
+                (rs, rowNum) -> {
+                    Message msg = new Message();
+                    msg.MID = mid;
+                    msg.User = new com.juick.User();
+                    msg.User.UID = rs.getInt(1);
+                    msg.User.UName = 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.Text = rs.getString(16);
+                    return msg;
+                });
     }
 
     public static com.juick.Message getReply(Connection sql, int mid, int rid) {
@@ -322,26 +304,16 @@ public class MessagesQueries {
         return msg;
     }
 
-    public static com.juick.User getMessageAuthor(Connection sql, int mid) {
-        com.juick.User user = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT messages.user_id,users.nick FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?");
-            stmt.setInt(1, mid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return user;
+    public static User getMessageAuthor(JdbcTemplate sql, int mid) {
+        return sql.queryForObject("SELECT messages.user_id,users.nick "
+                + "FROM messages INNER JOIN users "
+                + "ON messages.user_id=users.id WHERE messages.message_id=?",
+                new Object[]{mid}, (rs, num) -> {
+                    User res = new com.juick.User();
+                    res.UID = rs.getInt(1);
+                    res.UName = rs.getString(2);
+                    return res;
+                });
     }
 
     public static List<com.juick.Tag> getMessageTags(Connection sql, int mid) {
@@ -829,31 +801,18 @@ public class MessagesQueries {
         return mids;
     }
 
-    public static List<Integer> getUserRecommendations(Connection sql, int UID, int before) {
-        List<Integer> mids = new ArrayList<>(20);
+    public static List<Integer> getUserRecommendations(JdbcTemplate sql, int UID, int before) {
+        if (before > 0) {
+            return sql.queryForList("SELECT message_id FROM favorites "
+                    + "WHERE user_id=? AND message_id<? "
+                    + "ORDER BY message_id DESC LIMIT 20", Integer.class,
+                    new Object[]{UID, before});
+        } else {
+            return sql.queryForList("SELECT message_id FROM favorites "
+                    + "WHERE user_id=? ORDER BY message_id DESC LIMIT 20",
+                    Integer.class, new Object[]{UID});
 
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, UID);
-                stmt.setInt(2, before);
-            } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, UID);
-            }
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                mids.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
         }
-        return mids;
     }
 
     public static List<Integer> getUserPhotos(Connection sql, int UID, int privacy, int before) {
@@ -1007,58 +966,27 @@ public class MessagesQueries {
         return replies;
     }
 
-    public static boolean setMessagePopular(Connection sql, int mid, int popular) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        try {
-            if (popular == -2) {
-                stmt = sql.prepareStatement("UPDATE messages SET hidden=1 WHERE message_id=?");
-                stmt.setInt(1, mid);
-            } else if (popular == -1) {
-                stmt = sql.prepareStatement("UPDATE messages SET popular=0 WHERE message_id=?");
-                stmt.setInt(1, mid);
-            } else {
-                stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?");
-                stmt.setInt(1, popular);
-                stmt.setInt(2, mid);
-            }
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
+    public static boolean setMessagePopular(JdbcTemplate sql, int mid, int popular) {
+        boolean ret;
+        switch (popular) {
+            case -2:
+                ret = sql.update("UPDATE messages SET hidden=1 WHERE message_id=?", mid) > 0;
+                break;
+            case -1:
+                ret = sql.update("UPDATE messages SET popular=0 WHERE message_id=?", mid) > 0;
+                break;
+            default:
+                ret = sql.update("UPDATE messages SET popular=? WHERE message_id=?", popular, mid) > 0;
+                break;
         }
-
         if (popular == -1) {
-            try {
-                stmt = sql.prepareStatement("INSERT INTO top_ignore_messages VALUES (?)");
-                stmt.setInt(1, mid);
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                LOGGER.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt);
-            }
+            ret = sql.update("INSERT INTO top_ignore_messages VALUES (?)", mid) > 0;
         }
-
         return ret;
     }
 
-    public static boolean setMessagePrivacy(Connection sql, int mid) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("UPDATE messages SET privacy=1 WHERE message_id=?");
-            stmt.setInt(1, mid);
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            LOGGER.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-
-        return ret;
+    public static boolean setMessagePrivacy(JdbcTemplate sql, int mid) {
+        return sql.update("UPDATE messages SET privacy=1 WHERE message_id=?", 
+                mid) > 0;            
     }
 }