diff options
Diffstat (limited to 'juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java')
| -rw-r--r-- | juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java | 77 |
1 files changed, 36 insertions, 41 deletions
diff --git a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java index 5c28c2ba..e6a8617d 100644 --- a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java @@ -1,8 +1,9 @@ package com.juick.service; import com.juick.User; -import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; +import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -22,17 +23,16 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe @Transactional @Override - public boolean createPM(final int uid_from, final int uid_to, final String body) { + public boolean createPM(final int uidFrom, 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; + "INSERT INTO pm(user_id, user_id_to, txt) VALUES (?, ?, ?)", + uidFrom, 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); + "INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1) " + + "ON DUPLICATE KEY UPDATE lastmessage = NOW(), unread = unread + 1", + uidFrom, uid_to); } return ret; } @@ -41,40 +41,36 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe @Override public boolean addPMinRoster(final int uid, final String jid) { return getJdbcTemplate().update( - "INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", - uid, jid) > 0; + "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; + "DELETE FROM pm_inroster WHERE user_id = ? AND jid = ?", uid, jid) > 0; } @Transactional @Override public boolean havePMinRoster(final int uid, final String jid) { List<Integer> res = getJdbcTemplate().queryForList( - "SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?", + "SELECT 1 FROM pm_inroster WHERE user_id = ? AND jid = ?", Integer.class, - uid, - jid); + 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; - } + public String getLastView(final int uidFrom, final int uidTo) { + List<String> list = getJdbcTemplate().queryForList( + "SELECT lastview FROM pm_streams WHERE user_id = ? AND user_id_to = ?", + String.class, + uidFrom, uidTo); + + return list.isEmpty() ? + null : list.get(0); } @Transactional(readOnly = true) @@ -84,7 +80,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe "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, + + "ORDER BY pm_streams.unread DESC, pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { com.juick.User u = new com.juick.User(); u.setUid(rs.getInt(1)); @@ -92,16 +88,20 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe u.setUnreadCount(rs.getInt(3)); return u; }, - uid); + uid, cnt); } @Transactional @Override - public List<com.juick.Message> getPMMessages(final int uid, final int uid_to) { - List<com.juick.Message> 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", + public List<com.juick.Message> getPMMessages(final int uid, final int uidTo) { + SqlParameterSource sqlParameterSource = new MapSqlParameterSource() + .addValue("uid", uid) + .addValue("uidTo", uidTo); + + List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query( + "SELECT user_id, txt,ts FROM pm WHERE (user_id = :uid AND user_id_to = :uidTo) " + + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC LIMIT 20", + sqlParameterSource, (rs, rowNum) -> { com.juick.Message msg = new com.juick.Message(); int uuid = rs.getInt(1); @@ -110,16 +110,11 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe 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); + }); + + getNamedParameterJdbcTemplate().update( + "UPDATE pm_streams SET lastview = NOW(), unread = 0 WHERE user_id_to = :uid AND user_id = :uidTo", + sqlParameterSource); return msgs; } |