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

1 files changed, 223 insertions, 245 deletions

diff --git a/juick-server/src/main/java/com/juick/service/UserServiceImpl.java b/juick-server/src/main/java/com/juick/service/UserServiceImpl.java
index 63785cff..2c52582d 100644
--- a/juick-server/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/juick-server/src/main/java/com/juick/service/UserServiceImpl.java
@@ -5,15 +5,16 @@ import com.juick.server.helpers.Auth;
 import com.juick.server.helpers.EmailOpts;
 import com.juick.server.helpers.UserInfo;
 import com.juick.util.UserUtils;
+import org.apache.commons.collections4.CollectionUtils;
+import org.apache.commons.lang3.StringUtils;
 import org.springframework.dao.DuplicateKeyException;
-import org.springframework.dao.EmptyResultDataAccessException;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;
+import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
 import org.springframework.jdbc.support.GeneratedKeyHolder;
 import org.springframework.jdbc.support.KeyHolder;
 import org.springframework.stereotype.Repository;
 import org.springframework.transaction.annotation.Transactional;
-import org.springframework.util.StringUtils;
 
 import javax.inject.Inject;
 import java.sql.PreparedStatement;
@@ -47,33 +48,32 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional
     @Override
     public String getSignUpHashByJID(final String jid) {
-        String hash;
-        try {
-            hash = getJdbcTemplate().queryForObject(
-                    "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL",
-                    String.class,
-                    jid);
-        } catch (EmptyResultDataAccessException e) {
-            hash = UUID.randomUUID().toString();
-            getJdbcTemplate().update("INSERT INTO jids(jid,loginhash) VALUES (?,?)", jid, hash);
+        List<String> list = getJdbcTemplate().queryForList(
+                "SELECT loginhash FROM jids WHERE jid = ? AND user_id IS NULL", String.class, jid);
+
+        if (list.isEmpty()) {
+            String hash = UUID.randomUUID().toString();
+            getJdbcTemplate().update("INSERT INTO jids(jid, loginhash) VALUES (?, ?)", jid, hash);
+            return hash;
         }
-        return hash;
+        return list.get(0);
     }
 
     @Transactional
     @Override
     public String getSignUpHashByTelegramID(final Long telegramId, final String username) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT loginhash FROM telegram WHERE tg_id=? AND user_id IS NULL",
-                    String.class,
-                    telegramId);
-        } catch (EmptyResultDataAccessException e) {
+        List<String> list = getJdbcTemplate().queryForList(
+                "SELECT loginhash FROM telegram WHERE tg_id = ? AND user_id IS NULL",
+                String.class,
+                telegramId);
+
+        if (list.isEmpty()) {
             String hash = UUID.randomUUID().toString();
             getJdbcTemplate().update(
                     "INSERT INTO telegram(tg_id, loginhash, tg_name) VALUES (?, ?, ?)", telegramId, hash, username);
             return hash;
         }
+        return list.get(0);
     }
 
     @Transactional
@@ -81,14 +81,16 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public int createUser(final String username, final String password) {
         KeyHolder holder = new GeneratedKeyHolder();
         try {
-            getJdbcTemplate().update(con -> {
-                PreparedStatement stmt = con.prepareStatement(
-                        "INSERT INTO users(nick,passw) VALUES (?,?)",
-                        Statement.RETURN_GENERATED_KEYS);
-                stmt.setString(1, username);
-                stmt.setString(2, password);
-                return stmt;
-            }, holder);
+            getJdbcTemplate().update(
+                    con -> {
+                        PreparedStatement stmt = con.prepareStatement(
+                                "INSERT INTO users(nick,passw) VALUES (?,?)",
+                                Statement.RETURN_GENERATED_KEYS);
+                        stmt.setString(1, username);
+                        stmt.setString(2, password);
+                        return stmt;
+                    },
+                    holder);
         } catch (DuplicateKeyException e) {
             return -1;
         }
@@ -96,7 +98,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
         int uid = holder.getKey().intValue();
 
         getJdbcTemplate().update("INSERT INTO useroptions(user_id) VALUES (?)", uid);
-        getJdbcTemplate().update("INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
+        getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid);
 
         return uid;
     }
@@ -104,190 +106,194 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public Optional<User> getUserByUID(final int uid) {
-        try {
-            return Optional.of(
-                    getJdbcTemplate().queryForObject("SELECT id, nick,banned FROM users WHERE id=?",
-                            new UserMapper(),
-                            uid));
-        } catch (EmptyResultDataAccessException e) {
-            return Optional.empty();
-        }
+        List<User> list = getJdbcTemplate().query(
+                "SELECT id, nick,banned FROM users WHERE id = ?", new UserMapper(), uid);
+
+        return list.isEmpty() ?
+                Optional.empty() : Optional.of(list.get(0));
     }
 
     @Transactional(readOnly = true)
     @Override
     public User getUserByName(final String username) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT id,nick,banned FROM users WHERE nick=?",
-                    new UserMapper(),
-                    username);
-        } catch (EmptyResultDataAccessException e) {
-            return null;
-        }
+        List<User> list = getJdbcTemplate().query(
+                "SELECT id, nick, banned FROM users WHERE nick = ?", new UserMapper(), username);
+
+        return list.isEmpty() ?
+                null : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public User getUserByJID(final String jid) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)",
-                    new UserMapper(),
-                    jid);
-        } catch (EmptyResultDataAccessException e) {
-            return null;
-        }
+        List<User> list = getJdbcTemplate().query(
+                "SELECT id, nick, banned FROM users WHERE id = (SELECT user_id FROM jids WHERE jid = ?)",
+                new UserMapper(),
+                jid);
+
+        return list.isEmpty() ?
+                null : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
-    public List<User> getUsersByName(final List<String> unames) {
-        if (!unames.isEmpty()) {
-            return getJdbcTemplate().query(
-                    "SELECT id,nick,banned FROM users WHERE nick IN (\"" + StringUtils.arrayToDelimitedString(unames.toArray(), "\",\"") + "\")",
-                    new UserMapper());
-        }
-        return Collections.emptyList();
+    public List<User> getUsersByName(final Collection<String> unames) {
+        if (CollectionUtils.isEmpty(unames))
+            return Collections.emptyList();
+
+        return getNamedParameterJdbcTemplate().query(
+                "SELECT id, nick, banned FROM users WHERE nick IN (:unames)",
+                new MapSqlParameterSource("unames", unames),
+                new UserMapper());
     }
 
     @Transactional(readOnly = true)
     @Override
-    public List<User> getUsersByID(final List<Integer> uids) {
-        if (!uids.isEmpty()) {
-            return getJdbcTemplate().query(
-                    "SELECT id,nick,banned FROM users WHERE id IN (" + StringUtils.arrayToCommaDelimitedString(uids.toArray()) + ")",
-                    new UserMapper());
-        }
-        return Collections.emptyList();
+    public List<User> getUsersByID(final Collection<Integer> uids) {
+        if (CollectionUtils.isEmpty(uids))
+            return Collections.emptyList();
+
+        return getNamedParameterJdbcTemplate().query(
+                "SELECT id, nick, banned FROM users WHERE id IN (:ids)",
+                new MapSqlParameterSource("ids", uids),
+                new UserMapper());
     }
 
     @Transactional(readOnly = true)
     @Override
-    public List<com.juick.User> getUsersByJID(final List<String> jids) {
-        if (!jids.isEmpty()) {
-            return getJdbcTemplate().query(
-                    "SELECT users.id,users.nick,jids.jid FROM users "
-                            + "INNER JOIN jids ON jids.user_id=users.id "
-                            + "WHERE jids.jid IN (\"" + StringUtils.arrayToDelimitedString(jids.toArray(), "\",\"") + "\")",
-                    (rs, rowNum) -> {
-                        com.juick.User user = new com.juick.User();
-                        user.setUid(rs.getInt(1));
-                        user.setName(rs.getString(2));
-                        user.setJID(rs.getString(3));
-                        return user;
-                    });
-        }
-        return Collections.emptyList();
+    public List<com.juick.User> getUsersByJID(final Collection<String> jids) {
+        if (CollectionUtils.isEmpty(jids))
+            return Collections.emptyList();
+
+        return getNamedParameterJdbcTemplate().query(
+                "SELECT users.id, users.nick, jids.jid FROM users " +
+                        " INNER JOIN jids ON jids.user_id = users.id " +
+                        " WHERE jids.jid IN (:jids)",
+                new MapSqlParameterSource("jids", jids),
+                (rs, rowNum) -> {
+                    com.juick.User user = new com.juick.User();
+                    user.setUid(rs.getInt(1));
+                    user.setName(rs.getString(2));
+                    user.setJID(rs.getString(3));
+                    return user;
+                });
     }
 
     @Transactional(readOnly = true)
     @Override
     public List<String> getJIDsbyUID(final int uid) {
-        return getJdbcTemplate().queryForList("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid);
+        return getJdbcTemplate().queryForList("SELECT jid FROM jids WHERE user_id = ? AND active = 1", String.class, uid);
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getUIDbyJID(final String jid) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
+        if (StringUtils.isNotBlank(jid)) {
+            List<Integer> list = getJdbcTemplate().queryForList(
+                    "SELECT user_id FROM jids WHERE jid = ?", Integer.class, jid);
+
+            if (!list.isEmpty())
+                return list.get(0);
         }
+        return 0;
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getUIDbyName(final String uname) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT id FROM users WHERE nick=?", Integer.class, uname);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
+        if (StringUtils.isNotBlank(uname)) {
+            List<Integer> list = getJdbcTemplate().queryForList(
+                    "SELECT id FROM users WHERE nick = ?", Integer.class, uname);
+
+            if (!list.isEmpty())
+                return list.get(0);
         }
+        return 0;
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getUIDbyHash(final String hash) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT user_id FROM logins WHERE hash=?", Integer.class, hash);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
+        if (StringUtils.isNotBlank(hash)) {
+            List<Integer> list = getJdbcTemplate().queryForList(
+                    "SELECT user_id FROM logins WHERE hash = ?", Integer.class, hash);
+
+            if (!list.isEmpty())
+                return list.get(0);
         }
+        return 0;
     }
 
     @Transactional(readOnly = true)
     @Override
     public com.juick.User getUserByHash(final String hash) {
-        try {
-            User user = getJdbcTemplate().queryForObject(
-                    "SELECT logins.user_id,users.nick, users.banned FROM logins " +
-                            "INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?",
+        if (StringUtils.isNotBlank(hash)) {
+            List<User> list = getJdbcTemplate().query(
+                    "SELECT logins.user_id, users.nick, users.banned FROM logins " +
+                            "INNER JOIN users ON logins.user_id = users.id WHERE logins.hash = ?",
                     new UserMapper(),
                     hash);
-            user.setAuthHash(hash);
-            return user;
-        } catch (EmptyResultDataAccessException e) {
-            return new User();
+
+            if (!list.isEmpty()) {
+                User user = list.get(0);
+                user.setAuthHash(hash);
+                return user;
+            }
         }
+        return new User();
     }
 
     @Transactional
     @Override
     public String getHashByUID(final int uid) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT hash FROM logins WHERE user_id=?", String.class, uid);
-        } catch (EmptyResultDataAccessException e) {
+        List<String> list = getJdbcTemplate().queryForList(
+                "SELECT hash FROM logins WHERE user_id = ?", String.class, uid);
+
+        if (list.isEmpty()) {
             String hash = UserUtils.generateHash(16);
-            getJdbcTemplate().update(con -> {
-                PreparedStatement stmt = con.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
-                stmt.setInt(1, uid);
-                stmt.setString(2, hash);
-                return stmt;
-            });
+            getJdbcTemplate().update("INSERT INTO logins(user_id, hash) VALUES (?, ?)", uid, hash);
             return hash;
         }
+        return list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public int checkPassword(final String username, final String password) {
-        try {
-            String realPassword = getJdbcTemplate().queryForObject(
-                    "SELECT passw FROM users WHERE nick=?", String.class, username);
-            if (realPassword.equals(password)) {
-                User user = getUserByName(username);
-                if (user != null) {
-                    return user.getUid();
-                } else {
-                    return -1;
+        if (StringUtils.isNotBlank(username)) {
+            List<String> list = getJdbcTemplate().queryForList(
+                    "SELECT passw FROM users WHERE nick = ?", String.class, username);
+
+            if (!list.isEmpty()) {
+                String realPassword = list.get(0);
+                if (realPassword.equals(password)) {
+                    User user = getUserByName(username);
+                    if (user != null)
+                        return user.getUid();
                 }
-            } else {
-                return -1;
             }
-        } catch (EmptyResultDataAccessException e) {
-            return -1;
         }
+        return -1;
     }
 
     @Transactional
     @Override
     public boolean updatePassword(final User user, final String newPassword) {
-        return user.getUid() > 0 && getJdbcTemplate().update("UPDATE users SET passw=? WHERE id=?", newPassword, user.getUid()) > 0;
+        return user != null &&
+                user.getUid() > 0 &&
+                getJdbcTemplate().update("UPDATE users SET passw = ? WHERE id = ?", newPassword, user.getUid()) > 0;
     }
 
     @Transactional
     @Override
     public String updateSecretEmail(final User user) {
-        String newHash = UserUtils.generateHash(16);
-        if (getJdbcTemplate().update(
-                "INSERT INTO mail(user_id,hash) VALUES (?,?) ON DUPLICATE KEY UPDATE hash=?",
-                user.getUid(),
-                newHash,
-                newHash) > 0) {
-            return newHash;
+        if (user != null) {
+            String newHash = UserUtils.generateHash(16);
+            int ret = getJdbcTemplate().update(
+                    "INSERT INTO mail(user_id, hash) VALUES (?, ?) ON DUPLICATE KEY UPDATE hash = ?",
+                    user.getUid(), newHash, newHash);
+            if (ret > 0)
+                return newHash;
         }
         return "";
     }
@@ -295,43 +301,48 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public int getUserOptionInt(final int uid, final String option, final int defaultValue) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT " + option + " FROM useroptions WHERE user_id=?", Integer.class, uid);
-        } catch (EmptyResultDataAccessException e) {
+        if (StringUtils.isBlank(option))
             return defaultValue;
-        }
+
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT " + option + " FROM useroptions WHERE user_id = ?", Integer.class, uid);
+
+        return list.isEmpty() ? defaultValue : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
-    public void setUserOptionInt(final int uid, final String option, final int value) {
-        getJdbcTemplate().update("UPDATE useroptions SET " + option + "=? WHERE user_id=?", value, uid);
+    public int setUserOptionInt(final int uid, final String option, final int value) {
+        if (StringUtils.isBlank(option))
+            return 0;
+
+        return getJdbcTemplate().update("UPDATE useroptions SET " + option + "= ? WHERE user_id = ?", value, uid);
     }
 
     @Transactional(readOnly = true)
     @Override
     public UserInfo getUserInfo(final User user) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT fullname,country,url,descr FROM usersinfo WHERE user_id=?",
-                    ((rs, rowNum) -> {
-                        UserInfo info = new UserInfo();
-                        info.setFullName(rs.getString(1));
-                        info.setCountry(rs.getString(2));
-                        info.setUrl(rs.getString(3));
-                        info.setDescription(rs.getString(4));
-                        return info;
-                    }), user.getUid());
-        } catch (EmptyResultDataAccessException e) {
-            return new UserInfo();
-        }
+        List<UserInfo> list = getJdbcTemplate().query(
+                "SELECT fullname, country, url, descr FROM usersinfo WHERE user_id = ?",
+                ((rs, rowNum) -> {
+                    UserInfo info = new UserInfo();
+                    info.setFullName(rs.getString(1));
+                    info.setCountry(rs.getString(2));
+                    info.setUrl(rs.getString(3));
+                    info.setDescription(rs.getString(4));
+                    return info;
+                }),
+                user.getUid());
+
+        return list.isEmpty() ? new UserInfo() : list.get(0);
     }
 
     @Transactional
     @Override
     public boolean updateUserInfo(final User user, final UserInfo info) {
         return getJdbcTemplate().update(
-                "INSERT INTO usersinfo(user_id,fullname,country,url,descr) VALUES (?,?,?,?,?) " +
-                        "ON DUPLICATE KEY UPDATE fullname=?,country=?,url=?,descr=?",
+                "INSERT INTO usersinfo(user_id, fullname, country, url, descr) VALUES (?, ?, ?, ?, ?) " +
+                        "ON DUPLICATE KEY UPDATE fullname = ?, country = ?, url = ?, descr = ?",
                 user.getUid(),
                 info.getFullName(),
                 info.getCountry(),
@@ -346,76 +357,67 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public boolean getCanMedia(final int uid) {
-        try {
-            int res = getJdbcTemplate().queryForObject(
-                    "SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?",
-                    Integer.class,
-                    uid);
-            return res < 3600;
-        } catch (EmptyResultDataAccessException e) {
-            return false;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT users.lastphoto - UNIX_TIMESTAMP() FROM users WHERE id = ?",
+                Integer.class,
+                uid);
+
+        return !list.isEmpty() && list.get(0) < 3600;
     }
 
     @Transactional(readOnly = true)
     @Override
     public boolean isInWL(final int uid, final int check) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?",
-                    Integer.class, uid, check) == 1;
-        } catch (EmptyResultDataAccessException e) {
-            return false;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT 1 FROM wl_users WHERE user_id = ? AND wl_user_id = ?",
+                Integer.class, uid, check);
+
+        return !list.isEmpty() && list.get(0) == 1;
     }
 
     @Transactional(readOnly = true)
     @Override
     public boolean isInBL(final int uid, final int check) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?",
-                    Integer.class, uid, check) == 1;
-        } catch (EmptyResultDataAccessException e) {
-            return false;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT 1 FROM bl_users WHERE user_id = ? AND bl_user_id = ?", Integer.class, uid, check);
+
+        return !list.isEmpty() && list.get(0) == 1;
     }
 
     @Transactional(readOnly = true)
     @Override
     public boolean isInBLAny(final int uid, final int uid2) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT 1 FROM bl_users "
-                    + "WHERE (user_id=? AND bl_user_id=?) "
-                    + "OR (user_id=? AND bl_user_id=?)", new Object[]{uid, uid2, uid2, uid}, Integer.class) == 1;
-        } catch (EmptyResultDataAccessException e) {
-            return false;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT 1 FROM bl_users WHERE (user_id = ? AND bl_user_id = ?) "
+                        + "OR (user_id = ? AND bl_user_id = ?)",
+                new Object[]{uid, uid2, uid2, uid},
+                Integer.class);
+
+        return !list.isEmpty() && list.get(0) == 1;
     }
 
     @Transactional(readOnly = true)
     @Override
-    public List<Integer> checkBL(final int visitor, final List<Integer> uids) {
-        if (!uids.isEmpty()) {
-            return getJdbcTemplate().queryForList(
-                    "SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" +
-                            StringUtils.collectionToCommaDelimitedString(uids) + ")",
-                    Integer.class,
-                    visitor);
-        } else {
-            return new ArrayList<>();
-        }
+    public List<Integer> checkBL(final int visitor, final Collection<Integer> uids) {
+        if (CollectionUtils.isEmpty(uids))
+            return Collections.emptyList();
+
+        return getNamedParameterJdbcTemplate().queryForList(
+                "SELECT user_id FROM bl_users WHERE bl_user_id = :visitor and user_id IN (:ids)",
+                new MapSqlParameterSource()
+                        .addValue("visitor", visitor)
+                        .addValue("ids", uids),
+                Integer.class);
     }
 
     @Transactional(readOnly = true)
     @Override
     public boolean isSubscribed(final int uid, final int check) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?",
-                    Integer.class, uid, check) == 1;
-        } catch (EmptyResultDataAccessException e) {
-            return false;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT 1 FROM subscr_users WHERE suser_id = ? AND user_id = ?",
+                Integer.class, uid, check);
+
+        return !list.isEmpty() && list.get(0) == 1;
     }
 
     @Transactional(readOnly = true)
@@ -508,41 +510,30 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public int getStatsIRead(final int uid) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", Integer.class, uid);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList(
+                "SELECT COUNT(*) FROM subscr_users WHERE suser_id = ?", Integer.class, uid);
+        return list.isEmpty() ? 0 : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getStatsMyReaders(final int uid) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT COUNT(*) FROM subscr_users WHERE user_id=?", Integer.class, uid);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM subscr_users WHERE user_id = ?", Integer.class, uid);
+        return list.isEmpty() ? 0 : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getStatsMessages(final int uid) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT COUNT(*) FROM messages WHERE user_id=?", Integer.class, uid);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM messages WHERE user_id = ?", Integer.class, uid);
+        return list.isEmpty() ? 0 : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public int getStatsReplies(final int uid) {
-        try {
-            return getJdbcTemplate().queryForObject("SELECT COUNT(*) FROM replies WHERE user_id=?", Integer.class, uid);
-        } catch (EmptyResultDataAccessException e) {
-            return 0;
-        }
+        List<Integer> list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM replies WHERE user_id = ?", Integer.class, uid);
+        return list.isEmpty() ? 0 : list.get(0);
     }
 
     @Transactional
@@ -550,16 +541,10 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public boolean setActiveStatusForJID(final String JID, final UserService.ActiveStatus jidStatus) {
         User user = getUserByJID(JID);
         if (user != null) {
-            return getJdbcTemplate().update(con -> {
-                PreparedStatement preparedStatement = con.prepareStatement(
-                        "UPDATE jids SET active=? WHERE user_id=? AND jid=?");
-                int newStatus = jidStatus == UserService.ActiveStatus.Active ? 1 : 0;
-                preparedStatement.setInt(1, newStatus);
-                preparedStatement.setInt(2, user.getUid());
-                preparedStatement.setString(3, JID);
-                return preparedStatement;
-
-            }) >= 0;
+            int newStatus = jidStatus == UserService.ActiveStatus.Active ? 1 : 0;
+            return getJdbcTemplate().update(
+                    "UPDATE jids SET active = ? WHERE user_id = ? AND jid = ?",
+                    newStatus, user.getUid(), JID) >= 0;
         }
         return false;
     }
@@ -589,26 +574,19 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public EmailOpts getEmailOpts(final User user) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT email,subscr_hour FROM emails WHERE user_id=? AND subscr_hour IS NOT NULL",
-                    (rs, num) -> new EmailOpts(rs.getString(1), rs.getInt(2)), user.getUid());
-        } catch (EmptyResultDataAccessException e) {
-            return null;
-        }
+        List<EmailOpts> list = getJdbcTemplate().query(
+                "SELECT email,subscr_hour FROM emails WHERE user_id=? AND subscr_hour IS NOT NULL",
+                (rs, num) -> new EmailOpts(rs.getString(1), rs.getInt(2)), user.getUid());
+        return list.isEmpty() ? null : list.get(0);
     }
 
     @Transactional(readOnly = true)
     @Override
     public String getEmailHash(final User user) {
-        try {
-            return getJdbcTemplate().queryForObject(
-                    "SELECT hash FROM mail WHERE user_id=?",
-                    String.class,
-                    user.getUid())
-                    + "@mail.juick.com";
-        } catch (EmptyResultDataAccessException e) {
-            return "";
-        }
+        List<String> list = getJdbcTemplate().queryForList(
+                "SELECT hash FROM mail WHERE user_id = ?",
+                String.class,
+                user.getUid());
+        return list.isEmpty() ? "" : list.get(0) + "@mail.juick.com";
     }
 }