package com.juick.service; import com.juick.User; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import javax.inject.Inject; import java.util.List; /** * Created by aalexeev on 11/13/16. */ @Repository public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesService { @Inject public PMQueriesServiceImpl(JdbcTemplate jdbcTemplate) { super(jdbcTemplate, null); } @Transactional @Override public boolean createPM(final int uid_from, 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; 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); } return ret; } @Transactional @Override public boolean addPMinRoster(final int uid, final String jid) { return getJdbcTemplate().update( "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; } @Transactional @Override public boolean havePMinRoster(final int uid, final String jid) { List res = getJdbcTemplate().queryForList( "SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?", Integer.class, 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; } } @Transactional(readOnly = true) @Override public List getPMLastConversationsUsers(final int uid, final int cnt) { return getJdbcTemplate().query( "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, (rs, rowNum) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); u.setUnreadCount(rs.getInt(3)); return u; }, uid); } @Transactional @Override public List getPMMessages(final int uid, final int uid_to) { List 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", (rs, rowNum) -> { com.juick.Message msg = new com.juick.Message(); int uuid = rs.getInt(1); msg.setUser(new User()); msg.getUser().setUid(uuid); 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); return msgs; } @Transactional(readOnly = true) @Override public List getLastPMInbox(final int uid) { return getJdbcTemplate().query( "SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", (rs, num) -> { com.juick.Message msg = new com.juick.Message(); msg.setUser(new User()); msg.getUser().setUid(rs.getInt(1)); msg.getUser().setName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.setDate(rs.getTimestamp(5)); return msg; }, uid); } @Transactional(readOnly = true) @Override public List getLastPMSent(final int uid) { return getJdbcTemplate().query( "SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", (rs, num) -> { com.juick.Message msg = new com.juick.Message(); msg.setUser(new User()); msg.getUser().setUid(rs.getInt(1)); msg.getUser().setName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.setDate(rs.getTimestamp(5)); return msg; }, uid); } }