diff options
author | 2016-06-27 10:33:04 +0300 | |
---|---|---|
committer | 2016-06-27 10:33:04 +0300 | |
commit | 9e740f48cca7332c67a165c6cf60e09bc549e686 (patch) | |
tree | 08b35a43e538bb50679080c9fc4ee42c716f18eb /src/main/java/com/juick/server/PMQueries.java | |
parent | eca30236892175d7071e6d0d39f474148d655ffa (diff) |
more spring-jdbc
Diffstat (limited to 'src/main/java/com/juick/server/PMQueries.java')
-rw-r--r-- | src/main/java/com/juick/server/PMQueries.java | 173 |
1 files changed, 69 insertions, 104 deletions
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 6de30995..772a1c28 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -18,16 +18,10 @@ package com.juick.server; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; import java.util.List; -import java.util.logging.Level; -import java.util.logging.Logger; -import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -35,8 +29,6 @@ import org.springframework.jdbc.core.JdbcTemplate; */ public class PMQueries { - private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", uid_from, uid_to, body) > 0; @@ -55,20 +47,8 @@ public class PMQueries { uid, jid) > 0; } - public static boolean removePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; + public static boolean removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; } public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { @@ -78,37 +58,49 @@ public class PMQueries { return res.size() > 0; } - public static String getLastView(Connection sql, int uid_from, int uid_to) { - String ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { try { - stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getString(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; } - return ret; } public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { - return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " + - "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " + - "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - u.MessagesCount = rs.getInt(3); - return u; - }, uid, cnt); + List<User> qusers = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? AND unread>0 " + + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + + if (qusers.size() < cnt) { + List<User> addUsers = sql.query("SELECT user_id,user_id_to " + + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " + + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " + + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { + User u = new com.juick.User(); + int uuid = rs.getInt(1) + rs.getInt(2) - uid; + u.setUID(uuid); + return u; + }, uid, uid, cnt * 2); + for (User addUser : addUsers) { + if (!haveUserInArray(qusers, addUser.getUID())) { + qusers.add(addUser); + if (qusers.size() >= cnt) { + break; + } + } + } + } + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; } public static boolean haveUserInArray(List<com.juick.User> arr, int uid) { @@ -140,61 +132,34 @@ public class PMQueries { return msgs; } - public static List<com.juick.Message> getLastPMInbox(Connection sql, int uid) { - List<com.juick.Message> msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("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"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - 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)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List<com.juick.Message> getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.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); } - public static List<com.juick.Message> getLastPMSent(Connection sql, int uid) { - List<com.juick.Message> msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("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"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - 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)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List<com.juick.Message> getLastPMSent(JdbcTemplate sql, int uid) { + return sql.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); } } |