From ff72f0b789fe7f2e6689de8cc990bdc8fa00f9da Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 3 Dec 2018 16:19:53 +0300 Subject: add verified field for User --- .../java/com/juick/service/UserServiceImpl.java | 60 ++++++++++++++++++---- 1 file changed, 50 insertions(+), 10 deletions(-) (limited to 'src/main/java/com/juick/service') diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index f05fc93f..4d370631 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -55,7 +55,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { private class UserMapper implements RowMapper { @Override - public User mapRow(ResultSet rs, int rowNum) throws SQLException { + public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setUid(rs.getInt(1)); @@ -66,6 +66,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (seen != null) { user.setSeen(seen.toInstant()); } + user.setVerified(rs.getInt(6) > 0); return user; } } @@ -132,7 +133,11 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Override public Optional getUserByUID(final int uid) { List list = getJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = ?", new UserMapper(), uid); + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id WHERE u.id = ?", new UserMapper(), uid); return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0)); } @@ -143,7 +148,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public User getUserByName(final String username) { if (StringUtils.isNotBlank(username)) { List list = getJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick = ?", new UserMapper(), username); + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE u.nick = ?", new UserMapper(), username); if (!list.isEmpty()) return list.get(0); @@ -157,7 +167,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public User getUserByEmail(String email) { if (StringUtils.isNotBlank(email)) { List list = getJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = (SELECT DISTINCT user_id FROM emails WHERE email = ?)", + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE u.id = (SELECT DISTINCT user_id FROM emails WHERE email = ?)", new UserMapper(), email); @@ -174,7 +189,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (StringUtils.isNotBlank(jid)) { List list = getJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = (SELECT user_id FROM jids WHERE jid = ?)", + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE u.id = (SELECT user_id FROM jids WHERE jid = ?)", new UserMapper(), jid); @@ -191,7 +211,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { return Collections.emptyList(); return getNamedParameterJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick IN (:unames)", + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE u.nick IN (:unames)", new MapSqlParameterSource("unames", unames), new UserMapper()); } @@ -203,7 +228,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { return Collections.emptyList(); return getNamedParameterJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE id IN (:ids)", + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE u.id IN (:ids)", new MapSqlParameterSource("ids", uids), new UserMapper()); } @@ -258,8 +288,13 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public com.juick.User getUserByHash(final String hash) { if (StringUtils.isNotBlank(hash)) { List list = getJdbcTemplate().query( - "SELECT logins.user_id, users.nick, users.passw, users.banned, last_seen FROM logins " + - "INNER JOIN users ON logins.user_id = users.id WHERE logins.hash = ?", + "SELECT logins.user_id, u.nick, u.passw, u.banned, u.last_seen," + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM logins INNER JOIN users u ON logins.user_id = u.id " + + "LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE logins.hash = ?", new UserMapper(), hash); @@ -291,7 +326,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public int checkPassword(final String username, final String password) { if (StringUtils.isNotBlank(username)) { List list = getJdbcTemplate().query( - "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick = ?", + "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + + "LEFT JOIN emails e ON e.user_id = u.id " + + "WHERE nick = ?", new UserMapper(), username); -- cgit v1.2.3