From d03e8b19d346563afec5fb2b6b9c9dcea68040e0 Mon Sep 17 00:00:00 2001 From: Alexander Alexeev Date: Mon, 14 Nov 2016 15:56:32 +0700 Subject: jdbc services --- .../com/juick/service/PMQueriesServiceImpl.java | 150 ++++++++++++++++++++- 1 file changed, 149 insertions(+), 1 deletion(-) (limited to 'server-core/src/main/java/com/juick/service/PMQueriesServiceImpl.java') diff --git a/server-core/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/server-core/src/main/java/com/juick/service/PMQueriesServiceImpl.java index 4c8f4ae9..30231f98 100644 --- a/server-core/src/main/java/com/juick/service/PMQueriesServiceImpl.java +++ b/server-core/src/main/java/com/juick/service/PMQueriesServiceImpl.java @@ -1,10 +1,158 @@ package com.juick.service; +import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Transactional; + +import java.util.List; /** * Created by aalexeev on 11/13/16. */ @Repository -public class PMQueriesServiceImpl extends BaseJdbcDao implements PushQueriesService { +public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesService { + + @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.setUName(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().setUName(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().setUName(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