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.java204
1 files changed, 60 insertions, 144 deletions
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index c4b4fa2d..a2d4545c 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -27,6 +27,8 @@ import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
/**
*
@@ -34,54 +36,24 @@ import java.util.logging.Logger;
*/
public class PMQueries {
- private static final Logger logger = Logger.getLogger(PMQueries.class.getName());
+ private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName());
- public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) {
- boolean ret = false;
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)");
- stmt.setInt(1, uid_from);
- stmt.setInt(2, uid_to);
- stmt.setString(3, body);
- ret = stmt.executeUpdate() > 0;
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ 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 (?,?,?)",
+ new Object[] {uid_from, uid_to, body}) > 0;
if (ret) {
- PreparedStatement stmt2 = null;
- try {
- stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1");
- stmt2.setInt(1, uid_from);
- stmt2.setInt(2, uid_to);
- stmt2.executeUpdate();
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt2);
- }
+ sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) "
+ + "VALUES (?,?,NOW(),1) "
+ + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1",
+ new Object[] {uid_from, uid_to});
}
-
return ret;
}
- public static boolean addPMinRoster(Connection sql, int uid, String jid) {
- boolean ret = false;
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)");
- 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 addPMinRoster(JdbcTemplate sql, int uid, String jid) {
+ return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)",
+ new Object[] {uid, jid}) > 0;
}
public static boolean removePMinRoster(Connection sql, int uid, String jid) {
@@ -93,32 +65,18 @@ public class PMQueries {
stmt.setString(2, jid);
ret = stmt.executeUpdate() > 0;
} catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
+ LOGGER.log(Level.SEVERE, "sql exception", e);
} finally {
Utils.finishSQL(null, stmt);
}
return ret;
}
- public static boolean havePMinRoster(Connection sql, int uid, String jid) {
- boolean ret = false;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?");
- stmt.setInt(1, uid);
- stmt.setString(2, jid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- ret = true;
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return ret;
+ public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) {
+ List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster "
+ + "WHERE user_id=? AND jid=?", Integer.class,
+ new Object[] {uid, jid});
+ return res.size() > 0;
}
public static String getLastView(Connection sql, int uid_from, int uid_to) {
@@ -135,61 +93,42 @@ public class PMQueries {
ret = rs.getString(1);
}
} catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
+ LOGGER.log(Level.SEVERE, "sql exception", e);
} finally {
Utils.finishSQL(rs, stmt);
}
return ret;
}
- public static List<com.juick.User> getPMLastConversationsUsers(Connection sql, int uid, int cnt) {
- List<User> users = new ArrayList<>();
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt);
- stmt.setInt(1, uid);
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- com.juick.User u = new com.juick.User();
- u.UID = rs.getInt(1);
- u.MessagesCount = rs.getInt(2);
- users.add(u);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
+ public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
+ List<User> users = 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.UID = rs.getInt(1);
+ u.MessagesCount = rs.getInt(2);
+ return u;
+ }, new Object[]{uid});
if (users.size() < cnt) {
- try {
- stmt = sql.prepareStatement("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 ?");
- stmt.setInt(1, uid);
- stmt.setInt(2, uid);
- stmt.setInt(3, cnt * 2);
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- int uuid = rs.getInt(1) + rs.getInt(2) - uid;
- if (!haveUserInArray(users, uuid)) {
- com.juick.User u = new com.juick.User();
+ 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.UID = uuid;
- users.add(u);
- if (users.size() >= cnt) {
- break;
- }
+ return u;
+ }, new Object[]{uid, uid, cnt * 2});
+ for (User addUser : addUsers) {
+ if (!haveUserInArray(users, addUser.UID)) {
+ users.add(addUser);
+ if (users.size() >= cnt) {
+ break;
}
}
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
}
}
-
if (!users.isEmpty()) {
UserQueries.fillUsersByID(sql, users);
}
@@ -206,45 +145,22 @@ public class PMQueries {
return false;
}
- public static List<com.juick.Message> getPMMessages(Connection sql, int uid, int uid_to) {
- List<com.juick.Message> msgs = new ArrayList<>(20);
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("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");
- stmt.setInt(1, uid);
- stmt.setInt(2, uid_to);
- stmt.setInt(3, uid);
- stmt.setInt(4, uid_to);
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- com.juick.Message msg = new com.juick.Message();
- int uuid = rs.getInt(1);
- msg.User = new com.juick.User();
- msg.User.UID = uuid;
- msg.Text = rs.getString(2);
- msg.TimestampString = rs.getString(3);
- msgs.add(0, msg);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
-
- PreparedStatement stmt2 = null;
- try {
- stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?");
- stmt2.setInt(1, uid);
- stmt2.setInt(2, uid_to);
- stmt2.executeUpdate();
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt2);
- }
+ public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) {
+ List<com.juick.Message> msgs = sql.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.User = new com.juick.User();
+ msg.User.UID = uuid;
+ msg.Text = rs.getString(2);
+ msg.TimestampString = rs.getString(3);
+ return msg;
+ }, new Object[]{uid, uid_to, uid, uid_to});
+
+ sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 "
+ + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to});
return msgs;
}
@@ -270,7 +186,7 @@ public class PMQueries {
msgs.add(0, msg);
}
} catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
+ LOGGER.log(Level.SEVERE, "sql exception", e);
} finally {
Utils.finishSQL(rs, stmt);
}
@@ -299,7 +215,7 @@ public class PMQueries {
msgs.add(0, msg);
}
} catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
+ LOGGER.log(Level.SEVERE, "sql exception", e);
} finally {
Utils.finishSQL(rs, stmt);
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-27 02:59:26 +0000