spring-jdbc WIP

1 files changed, 60 insertions, 144 deletions

diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index c4b4fa2d..a2d4545c 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -27,6 +27,8 @@ import java.util.ArrayList;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
 
 /**
  *
@@ -34,54 +36,24 @@ import java.util.logging.Logger;
  */
 public class PMQueries {
 
-    private static final Logger logger = Logger.getLogger(PMQueries.class.getName());
+    private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName());
 
-    public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)");
-            stmt.setInt(1, uid_from);
-            stmt.setInt(2, uid_to);
-            stmt.setString(3, body);
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
+    public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) {
+        boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)",
+                new Object[] {uid_from, uid_to, body}) > 0;            
 
         if (ret) {
-            PreparedStatement stmt2 = null;
-            try {
-                stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1");
-                stmt2.setInt(1, uid_from);
-                stmt2.setInt(2, uid_to);
-                stmt2.executeUpdate();
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt2);
-            }
+            sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) "
+                    + "VALUES (?,?,NOW(),1) "
+                    + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1",
+                    new Object[] {uid_from, uid_to});                
         }
-
         return ret;
     }
 
-    public static boolean addPMinRoster(Connection sql, int uid, String jid) {
-        boolean ret = false;
-        PreparedStatement stmt = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)");
-            stmt.setInt(1, uid);
-            stmt.setString(2, jid);
-            ret = stmt.executeUpdate() > 0;
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt);
-        }
-        return ret;
+    public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) {
+        return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)",
+                new Object[] {uid, jid}) > 0;        
     }
 
     public static boolean removePMinRoster(Connection sql, int uid, String jid) {
@@ -93,32 +65,18 @@ public class PMQueries {
             stmt.setString(2, jid);
             ret = stmt.executeUpdate() > 0;
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(null, stmt);
         }
         return ret;
     }
 
-    public static boolean havePMinRoster(Connection sql, int uid, String jid) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?");
-            stmt.setInt(1, uid);
-            stmt.setString(2, jid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                ret = true;
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return ret;
+    public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) {
+        List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster "
+                + "WHERE user_id=? AND jid=?", Integer.class,
+                new Object[] {uid, jid});
+            return res.size() > 0;        
     }
 
     public static String getLastView(Connection sql, int uid_from, int uid_to) {
@@ -135,61 +93,42 @@ public class PMQueries {
                 ret = rs.getString(1);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
         return ret;
     }
 
-    public static List<com.juick.User> getPMLastConversationsUsers(Connection sql, int uid, int cnt) {
-        List<User> users = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt);
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.User u = new com.juick.User();
-                u.UID = rs.getInt(1);
-                u.MessagesCount = rs.getInt(2);
-                users.add(u);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+    public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
+        List<User> users = sql.query("SELECT user_id,unread FROM pm_streams "
+                + "WHERE user_id_to=? AND unread>0 "
+                + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> {
+                    com.juick.User u = new com.juick.User();
+                    u.UID = rs.getInt(1);
+                    u.MessagesCount = rs.getInt(2);
+                    return u;
+                }, new Object[]{uid});
 
         if (users.size() < cnt) {
-            try {
-                stmt = sql.prepareStatement("SELECT user_id,user_id_to FROM pm_streams WHERE (user_id=? OR user_id_to=?) AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) ORDER BY lastmessage DESC LIMIT ?");
-                stmt.setInt(1, uid);
-                stmt.setInt(2, uid);
-                stmt.setInt(3, cnt * 2);
-                rs = stmt.executeQuery();
-                rs.beforeFirst();
-                while (rs.next()) {
-                    int uuid = rs.getInt(1) + rs.getInt(2) - uid;
-                    if (!haveUserInArray(users, uuid)) {
-                        com.juick.User u = new com.juick.User();
+            List<User> addUsers = sql.query("SELECT user_id,user_id_to "
+                    + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) "
+                    + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) "
+                    + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> {
+                        User u = new com.juick.User();
+                        int uuid = rs.getInt(1) + rs.getInt(2) - uid;
                         u.UID = uuid;
-                        users.add(u);
-                        if (users.size() >= cnt) {
-                            break;
-                        }
+                        return u;
+                    }, new Object[]{uid, uid, cnt * 2});
+            for (User addUser : addUsers) {
+                if (!haveUserInArray(users, addUser.UID)) {
+                    users.add(addUser);
+                    if (users.size() >= cnt) {
+                        break;
                     }
                 }
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(rs, stmt);
             }
         }
-
         if (!users.isEmpty()) {
             UserQueries.fillUsersByID(sql, users);
         }
@@ -206,45 +145,22 @@ public class PMQueries {
         return false;
     }
 
-    public static List<com.juick.Message> getPMMessages(Connection sql, int uid, int uid_to) {
-        List<com.juick.Message> msgs = new ArrayList<>(20);
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT user_id,txt,ts FROM pm WHERE (user_id=? AND user_id_to=?) OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, uid_to);
-            stmt.setInt(3, uid);
-            stmt.setInt(4, uid_to);
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.Message msg = new com.juick.Message();
-                int uuid = rs.getInt(1);
-                msg.User = new com.juick.User();
-                msg.User.UID = uuid;
-                msg.Text = rs.getString(2);
-                msg.TimestampString = rs.getString(3);
-                msgs.add(0, msg);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        PreparedStatement stmt2 = null;
-        try {
-            stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?");
-            stmt2.setInt(1, uid);
-            stmt2.setInt(2, uid_to);
-            stmt2.executeUpdate();
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(null, stmt2);
-        }
+    public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) {
+        List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm "
+                + "WHERE (user_id=? AND user_id_to=?) "
+                + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20",
+                (rs, rowNum) -> {
+                    com.juick.Message msg = new com.juick.Message();
+                    int uuid = rs.getInt(1);
+                    msg.User = new com.juick.User();
+                    msg.User.UID = uuid;
+                    msg.Text = rs.getString(2);
+                    msg.TimestampString = rs.getString(3);
+                    return msg;
+                }, new Object[]{uid, uid_to, uid, uid_to});
+
+        sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 "
+                + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to});
 
         return msgs;
     }
@@ -270,7 +186,7 @@ public class PMQueries {
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }
@@ -299,7 +215,7 @@ public class PMQueries {
                 msgs.add(0, msg);
             }
         } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
+            LOGGER.log(Level.SEVERE, "sql exception", e);
         } finally {
             Utils.finishSQL(rs, stmt);
         }