From ffb24e0d469aaafa35ad5f460a7bdf771382f0ff Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 16 Nov 2016 14:30:50 +0300 Subject: server-core -> juick-server --- .../com/juick/service/PMQueriesServiceImpl.java | 165 +++++++++++++++++++++ 1 file changed, 165 insertions(+) create mode 100644 juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java (limited to 'juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java') diff --git a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java new file mode 100644 index 00000000..5c28c2ba --- /dev/null +++ b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java @@ -0,0 +1,165 @@ +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); + } +} -- cgit v1.2.3