aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/server/PMQueries.java
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/java/com/juick/server/PMQueries.java')
-rw-r--r--src/main/java/com/juick/server/PMQueries.java173
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);
}
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-05 09:00:51 +0000