package com.juick.service; import com.juick.User; 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.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 javax.inject.Inject; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.*; /** * Created by aalexeev on 11/13/16. */ @Repository public class UserServiceImpl extends BaseJdbcService implements UserService { private class UserMapper implements RowMapper { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setUid(rs.getInt(1)); user.setName(rs.getString(2)); user.setBanned(rs.getBoolean(3)); return user; } } @Inject public UserServiceImpl(JdbcTemplate jdbcTemplate) { super(jdbcTemplate, null); } @Transactional @Override public String getSignUpHashByJID(final String jid) { List 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 list.get(0); } @Transactional @Override public String getSignUpHashByTelegramID(final Long telegramId, final String username) { List 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 @Override 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); } catch (DuplicateKeyException e) { return -1; } 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); return uid; } @Transactional(readOnly = true) @Override public Optional getUserByUID(final int uid) { List 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) { List 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) { List 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 getUsersByName(final Collection 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 getUsersByID(final Collection 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 getUsersByJID(final Collection 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 getJIDsbyUID(final int 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) { if (StringUtils.isNotBlank(jid)) { List 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) { if (StringUtils.isNotBlank(uname)) { List 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) { if (StringUtils.isNotBlank(hash)) { List 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) { if (StringUtils.isNotBlank(hash)) { List 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); if (!list.isEmpty()) { User user = list.get(0); user.setAuthHash(hash); return user; } } return new User(); } @Transactional @Override public String getHashByUID(final int uid) { List list = getJdbcTemplate().queryForList( "SELECT hash FROM logins WHERE user_id = ?", String.class, uid); if (list.isEmpty()) { String hash = UserUtils.generateHash(16); 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) { if (StringUtils.isNotBlank(username)) { List 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(); } } } return -1; } @Transactional @Override public boolean updatePassword(final User user, final String newPassword) { 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) { 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 ""; } @Transactional(readOnly = true) @Override public int getUserOptionInt(final int uid, final String option, final int defaultValue) { if (StringUtils.isBlank(option)) return defaultValue; List 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 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) { List 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 = ?", user.getUid(), info.getFullName(), info.getCountry(), info.getUrl(), info.getDescription(), info.getFullName(), info.getCountry(), info.getUrl(), info.getDescription()) > 0; } @Transactional(readOnly = true) @Override public boolean getCanMedia(final int uid) { List 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) { List 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) { List 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) { List 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 checkBL(final int visitor, final Collection 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) { List 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) @Override public List getUserRead(final int uid) { return getJdbcTemplate().queryForList( "SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); } @Transactional(readOnly = true) @Override public List getUserReadLeastPopular(final int uid, final int cnt) { return getJdbcTemplate().query( "SELECT users.id,users.nick FROM (subscr_users " + "INNER JOIN users_subscr ON (subscr_users.suser_id=? " + "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " + "ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?", (rs, num) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid, cnt); } @Transactional(readOnly = true) @Override public List getUserReaders(final int uid) { return getJdbcTemplate().query( "SELECT users.id, users.nick FROM subscr_users " + "INNER JOIN users ON subscr_users.suser_id=users.id " + "WHERE subscr_users.user_id=? ORDER BY users.nick", (rs, num) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid); } @Transactional(readOnly = true) @Override public List getUserFriends(final int uid) { return getJdbcTemplate().query( "SELECT users.id,users.nick FROM subscr_users " + "INNER JOIN users ON subscr_users.user_id=users.id " + "WHERE subscr_users.suser_id=? AND users.id!=? " + "ORDER BY users.nick", (rs, num) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid, uid); } @Transactional(readOnly = true) @Override public List getUserBLUsers(final int uid) { return getJdbcTemplate().query("SELECT users.id,users.nick FROM users INNER JOIN bl_users " + "ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick", (rs, num) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid); } @Transactional @Override public boolean linkTwitterAccount( final User user, final String accessToken, final String accessTokenSecret, final String screenName) { if (getJdbcTemplate().update("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + "VALUES (?,?,?,?)" + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?", user.getUid(), accessToken, accessTokenSecret, screenName, accessToken, accessTokenSecret, screenName) > 0) { return getJdbcTemplate().update("INSERT INTO subscr_users(user_id,suser_id,jid) " + "VALUES (?,1741,'juick\\@twitter.juick.com')", user.getUid()) > 0; } return false; } @Transactional(readOnly = true) @Override public int getStatsIRead(final int uid) { List 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) { List 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) { List 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) { List list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM replies WHERE user_id = ?", Integer.class, uid); return list.isEmpty() ? 0 : list.get(0); } @Transactional @Override public boolean setActiveStatusForJID(final String JID, final UserService.ActiveStatus jidStatus) { User user = getUserByJID(JID); if (user != null) { 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; } @Transactional(readOnly = true) @Override public List getAllJIDs(final User user) { return getJdbcTemplate().queryForList( "SELECT jid FROM jids WHERE user_id=?", String.class, user.getUid()); } @Transactional(readOnly = true) @Override public List getAuthCodes(final User user) { return getJdbcTemplate().query( "SELECT account,authcode FROM auth WHERE user_id=? AND protocol='xmpp'", (rs, num) -> new Auth(rs.getString(1), rs.getString(2)), user.getUid()); } @Transactional(readOnly = true) @Override public List getEmails(final User user) { return getJdbcTemplate().queryForList("SELECT email FROM emails WHERE user_id=?", String.class, user.getUid()); } @Transactional(readOnly = true) @Override public EmailOpts getEmailOpts(final User user) { List 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) { List list = getJdbcTemplate().queryForList( "SELECT hash FROM mail WHERE user_id = ?", String.class, user.getUid()); return list.isEmpty() ? "" : list.get(0) + "@mail.juick.com"; } }