repository improvenments: using Collection in params, forse using exists in some query, force using Lists as query result

1 files changed, 36 insertions, 41 deletions

diff --git a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java
index 5c28c2ba..e6a8617d 100644
--- a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java
+++ b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java
@@ -1,8 +1,9 @@
 package com.juick.service;
 
 import com.juick.User;
-import org.springframework.dao.EmptyResultDataAccessException;
 import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
+import org.springframework.jdbc.core.namedparam.SqlParameterSource;
 import org.springframework.stereotype.Repository;
 import org.springframework.transaction.annotation.Transactional;
 
@@ -22,17 +23,16 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
 
     @Transactional
     @Override
-    public boolean createPM(final int uid_from, final int uid_to, final String body) {
+    public boolean createPM(final int uidFrom, final int uid_to, final String body) {
         boolean ret = getJdbcTemplate().update(
-                "INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)",
-                uid_from, uid_to, body) > 0;
+                "INSERT INTO pm(user_id, user_id_to, txt) VALUES (?, ?, ?)",
+                uidFrom, uid_to, body) > 0;
 
         if (ret) {
             getJdbcTemplate().update(
-                    "INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) "
-                            + "VALUES (?,?,NOW(),1) "
-                            + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1",
-                    uid_from, uid_to);
+                    "INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1) "
+                            + "ON DUPLICATE KEY UPDATE lastmessage = NOW(), unread = unread + 1",
+                    uidFrom, uid_to);
         }
         return ret;
     }
@@ -41,40 +41,36 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
     @Override
     public boolean addPMinRoster(final int uid, final String jid) {
         return getJdbcTemplate().update(
-                "INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)",
-                uid, jid) > 0;
+                "INSERT INTO pm_inroster(user_id, jid) VALUES (?, ?)", uid, jid) > 0;
     }
 
     @Transactional
     @Override
     public boolean removePMinRoster(final int uid, final String jid) {
         return getJdbcTemplate().update(
-                "DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0;
+                "DELETE FROM pm_inroster WHERE user_id = ? AND jid = ?", uid, jid) > 0;
     }
 
     @Transactional
     @Override
     public boolean havePMinRoster(final int uid, final String jid) {
         List<Integer> res = getJdbcTemplate().queryForList(
-                "SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?",
+                "SELECT 1 FROM pm_inroster WHERE user_id = ? AND jid = ?",
                 Integer.class,
-                uid,
-                jid);
+                uid, jid);
         return res.size() > 0;
     }
 
     @Transactional(readOnly = true)
     @Override
-    public String getLastView(final int uid_from, final int uid_to) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?",
-                    String.class,
-                    uid_from,
-                    uid_to);
-        } catch (EmptyResultDataAccessException e) {
-            return null;
-        }
+    public String getLastView(final int uidFrom, final int uidTo) {
+        List<String> list = getJdbcTemplate().queryForList(
+                "SELECT lastview FROM pm_streams WHERE user_id = ? AND user_id_to = ?",
+                String.class,
+                uidFrom, uidTo);
+
+        return list.isEmpty() ?
+                null : list.get(0);
     }
 
     @Transactional(readOnly = true)
@@ -84,7 +80,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                 "SELECT pm_streams.user_id, users.nick, pm_streams.unread FROM pm_streams "
                         + "INNER JOIN users ON users.id = pm_streams.user_id "
                         + "WHERE pm_streams.user_id_to=? "
-                        + "ORDER BY pm_streams.unread DESC, pm_streams.lastmessage DESC LIMIT " + cnt,
+                        + "ORDER BY pm_streams.unread DESC, pm_streams.lastmessage DESC LIMIT ?",
                 (rs, rowNum) -> {
                     com.juick.User u = new com.juick.User();
                     u.setUid(rs.getInt(1));
@@ -92,16 +88,20 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                     u.setUnreadCount(rs.getInt(3));
                     return u;
                 },
-                uid);
+                uid, cnt);
     }
 
     @Transactional
     @Override
-    public List<com.juick.Message> getPMMessages(final int uid, final int uid_to) {
-        List<com.juick.Message> msgs = getJdbcTemplate().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",
+    public List<com.juick.Message> getPMMessages(final int uid, final int uidTo) {
+        SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+                .addValue("uid", uid)
+                .addValue("uidTo", uidTo);
+
+        List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query(
+                "SELECT user_id, txt,ts FROM pm WHERE (user_id = :uid AND user_id_to = :uidTo) "
+                        + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC LIMIT 20",
+                sqlParameterSource,
                 (rs, rowNum) -> {
                     com.juick.Message msg = new com.juick.Message();
                     int uuid = rs.getInt(1);
@@ -110,16 +110,11 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                     msg.setText(rs.getString(2));
                     msg.setDate(rs.getTimestamp(3));
                     return msg;
-                },
-                uid,
-                uid_to,
-                uid,
-                uid_to);
-
-        getJdbcTemplate().update(
-                "UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?",
-                uid,
-                uid_to);
+                });
+
+        getNamedParameterJdbcTemplate().update(
+                "UPDATE pm_streams SET lastview = NOW(), unread = 0 WHERE user_id_to = :uid AND user_id = :uidTo",
+                sqlParameterSource);
 
         return msgs;
     }