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.springframework.dao.DuplicateKeyException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; 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; 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) { 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); } return hash; } @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) { String hash = UUID.randomUUID().toString(); getJdbcTemplate().update( "INSERT INTO telegram(tg_id, loginhash, tg_name) VALUES (?, ?, ?)", telegramId, hash, username); return hash; } } @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) { try { return Optional.of( getJdbcTemplate().queryForObject("SELECT id, nick,banned FROM users WHERE id=?", new UserMapper(), uid)); } catch (EmptyResultDataAccessException e) { return Optional.empty(); } } @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; } } @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; } } @Transactional(readOnly = true) @Override public List getUsersByName(final List 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(); } @Transactional(readOnly = true) @Override public List getUsersByID(final List 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(); } @Transactional(readOnly = true) @Override public List getUsersByJID(final List 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(); } @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) { try { return getJdbcTemplate().queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid); } catch (EmptyResultDataAccessException e) { 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; } } @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; } } @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=?", new UserMapper(), hash); user.setAuthHash(hash); return user; } catch (EmptyResultDataAccessException e) { 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) { 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; }); return hash; } } @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; } } else { return -1; } } catch (EmptyResultDataAccessException e) { 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; } @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; } return ""; } @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) { return defaultValue; } } @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); } @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(); } } @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) { 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; } } @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; } } @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; } } @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; } } @Transactional(readOnly = true) @Override public List checkBL(final int visitor, final List 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<>(); } } @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; } } @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) { try { return getJdbcTemplate().queryForObject("SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", Integer.class, uid); } catch (EmptyResultDataAccessException e) { return 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; } } @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; } } @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; } } @Transactional @Override 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; } 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) { 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; } } @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 ""; } } }