spring-jdbc WIP

1 files changed, 67 insertions, 84 deletions

diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index 4d5683bd..a8605d3a 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -17,6 +17,9 @@
  */
 package com.juick.server;
 
+import com.juick.Message;
+import org.springframework.jdbc.core.JdbcTemplate;
+
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -28,6 +31,7 @@ import java.util.Collections;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.core.RowMapper;
 
 /**
  *
@@ -553,50 +557,32 @@ public class MessagesQueries {
         return mids;
     }
 
-    public static List<Integer> getMyFeed(Connection sql, int uid, int before) {
+    public static List<Integer> getMyFeed(JdbcTemplate sql, int uid, int before) {
         List<Integer> mids = new ArrayList<>(40);
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, before);
-                stmt.setInt(3, uid);
-            } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, uid);
-            }
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                mids.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+        if (before > 0) {
+            mids = sql.queryForList("SELECT message_id FROM messages " +
+                            "INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) " +
+                            "WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " +
+                            "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20",
+                    Integer.class, new Object[]{uid, before, uid});
+        } else {
+            mids = sql.queryForList("SELECT message_id FROM messages " +
+                            "INNER JOIN subscr_users ON (subscr_users.suser_id=? " +
+                            "AND subscr_users.user_id=messages.user_id) " +
+                            "WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " +
+                            "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20",
+                    Integer.class, new Object[]{uid, uid});
         }
 
-        try {
-            if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, before);
-            } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
-                stmt.setInt(1, uid);
-            }
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                mids.add(rs.getInt(1));
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
+
+        if (before > 0) {
+            mids.addAll(sql.queryForList("SELECT message_id FROM messages " +
+                            "WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20", Integer.class,
+                    new Object[]{uid, before}));
+        } else {
+            mids.addAll(sql.queryForList("SELECT message_id FROM messages " +
+                            "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", Integer.class,
+                    new Object[]{uid}));
         }
 
         Collections.sort(mids, Collections.reverseOrder());
@@ -945,50 +931,47 @@ public class MessagesQueries {
         return mids;
     }
 
-    public static List<com.juick.Message> getMessages(Connection sql, List<Integer> mids) {
+    public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) {
         List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT STRAIGHT_JOIN messages.message_id,messages.user_id,users.nick,messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,messages_txt.repliesby,messages.attach,messages.lat,messages.lon,messages.likes FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id IN (" + Utils.convertArrayInt2String(mids) + ") ORDER BY messages.message_id DESC");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                msg.User = new com.juick.User();
-
-                msg.MID = rs.getInt(1);
-                msg.User.UID = rs.getInt(2);
-                msg.User.UName = rs.getString(3);
-                if (rs.getString(4) != null) {
-                    msg.parseTags(rs.getString(4));
-                }
-                msg.ReadOnly = rs.getInt(5) == 1;
-                msg.Privacy = rs.getInt(6);
-                msg.FriendsOnly = msg.Privacy < 0;
-                msg.Text = rs.getString(7);
-                msg.TimeAgo = rs.getInt(8);
-                msg.TimestampString = rs.getString(9);
-                msg.Replies = rs.getInt(10);
-                msg.RepliesBy = rs.getString(11);
-                msg.AttachmentType = rs.getString(12);
-                if (rs.getDouble(13) != 0) {
-                    msg.Place = new com.juick.Place();
-                    msg.Place.lat = rs.getDouble(13);
-                    msg.Place.lon = rs.getDouble(14);
-                }
-                msg.Likes = rs.getInt(15);
-
-                msgs.add(msg);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return msgs;
+        
+        return sql.query("SELECT messages.message_id,messages.user_id,users.nick,"
+                + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,"
+                + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
+                + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,"
+                + "messages_txt.repliesby,messages.attach,messages.lat,"
+                + "messages.lon,messages.likes "
+                + "FROM (messages INNER JOIN messages_txt "
+                + "ON messages.message_id=messages_txt.message_id) "
+                + "INNER JOIN users ON messages.user_id=users.id "
+                + "WHERE messages.message_id "
+                + "IN (" + Utils.convertArrayInt2String(mids) + ") "
+                + "ORDER BY messages.message_id DESC", (rs, rowNum) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    msg.User = new com.juick.User();
+                    
+                    msg.MID = rs.getInt(1);
+                    msg.User.UID = rs.getInt(2);
+                    msg.User.UName = rs.getString(3);
+                    if (rs.getString(4) != null) {
+                        msg.parseTags(rs.getString(4));
+                    }
+                    msg.ReadOnly = rs.getInt(5) == 1;
+                    msg.Privacy = rs.getInt(6);
+                    msg.FriendsOnly = msg.Privacy < 0;
+                    msg.Text = rs.getString(7);
+                    msg.TimeAgo = rs.getInt(8);
+                    msg.TimestampString = rs.getString(9);
+                    msg.Replies = rs.getInt(10);
+                    msg.RepliesBy = rs.getString(11);
+                    msg.AttachmentType = rs.getString(12);
+                    if (rs.getDouble(13) != 0) {
+                        msg.Place = new com.juick.Place();
+                        msg.Place.lat = rs.getDouble(13);
+                        msg.Place.lon = rs.getDouble(14);
+                    }
+                    msg.Likes = rs.getInt(15);
+                    return msg;
+        });
     }
 
     public static List<com.juick.Message> getReplies(Connection sql, int mid) {