aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/server/MessagesQueries.java
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/java/com/juick/server/MessagesQueries.java')
-rw-r--r--src/main/java/com/juick/server/MessagesQueries.java230
1 files changed, 79 insertions, 151 deletions
diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index cff8caffd..8f658b420 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -17,6 +17,8 @@
*/
package com.juick.server;
+import com.juick.Message;
+import com.juick.User;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.Connection;
@@ -30,6 +32,7 @@ import java.util.Collections;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
+import org.springframework.jdbc.core.RowMapper;
/**
*
@@ -228,67 +231,46 @@ public class MessagesQueries {
return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4);
}
- public static com.juick.Message getMessage(Connection sql, int mid) {
- com.juick.Message msg = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT messages.user_id,users.nick,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.readonly,messages.privacy,messages.replies,messages.attach,messages.place_id,messages.lat,messages.lon,messages.likes,messages.hidden FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?");
- stmt.setInt(1, mid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- msg = new com.juick.Message();
- msg.MID = mid;
- msg.User = new com.juick.User();
- msg.User.UID = rs.getInt(1);
- msg.User.UName = rs.getString(2);
- msg.TimeAgo = rs.getInt(3);
- msg.TimestampString = rs.getString(4);
- msg.ReadOnly = rs.getBoolean(5);
- msg.Privacy = rs.getInt(6);
- msg.FriendsOnly = msg.Privacy < 0;
- msg.Replies = rs.getInt(7);
- msg.AttachmentType = rs.getString(8);
- //if (rs.getInt(9) > 0) {
- // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9));
- //}
- if (rs.getDouble(10) != 0) {
- msg.Place = new com.juick.Place();
- msg.Place.lat = rs.getDouble(10);
- msg.Place.lon = rs.getDouble(11);
- }
- msg.Likes = rs.getInt(12);
- msg.Hidden = rs.getBoolean(13);
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
-
- if (msg == null) {
- return null;
- }
-
- try {
- stmt = sql.prepareStatement("SELECT tags,repliesby,txt FROM messages_txt WHERE message_id=?");
- stmt.setInt(1, mid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- if (rs.getString(1) != null) {
- msg.parseTags(rs.getString(1));
- }
- msg.RepliesBy = rs.getString(2);
- msg.Text = rs.getString(3);
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
-
- return msg;
+ public static com.juick.Message getMessage(JdbcTemplate sql, int mid) {
+ return sql.queryForObject("SELECT messages.user_id,users.nick,"
+ + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
+ + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),"
+ + "messages.readonly,messages.privacy,messages.replies,"
+ + "messages.attach,messages.place_id,messages.lat,"
+ + "messages.lon,messages.likes,messages.hidden,"
+ + "txt.tags,txt.repliesby,txt.txt FROM messages "
+ + "INNER JOIN users ON messages.user_id=users.id "
+ + "INNER JOIN messages_txt "
+ + "ON messages.message_id = messages_txt.message_id "
+ + "WHERE messages.message_id=?", new Object[]{mid},
+ (rs, rowNum) -> {
+ Message msg = new Message();
+ msg.MID = mid;
+ msg.User = new com.juick.User();
+ msg.User.UID = rs.getInt(1);
+ msg.User.UName = rs.getString(2);
+ msg.TimeAgo = rs.getInt(3);
+ msg.TimestampString = rs.getString(4);
+ msg.ReadOnly = rs.getBoolean(5);
+ msg.Privacy = rs.getInt(6);
+ msg.FriendsOnly = msg.Privacy < 0;
+ msg.Replies = rs.getInt(7);
+ msg.AttachmentType = rs.getString(8);
+ //if (rs.getInt(9) > 0) {
+ // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9));
+ //}
+ if (rs.getDouble(10) != 0) {
+ msg.Place = new com.juick.Place();
+ msg.Place.lat = rs.getDouble(10);
+ msg.Place.lon = rs.getDouble(11);
+ }
+ msg.Likes = rs.getInt(12);
+ msg.Hidden = rs.getBoolean(13);
+ msg.parseTags(rs.getString(14));
+ msg.RepliesBy = rs.getString(15);
+ msg.Text = rs.getString(16);
+ return msg;
+ });
}
public static com.juick.Message getReply(Connection sql, int mid, int rid) {
@@ -322,26 +304,16 @@ public class MessagesQueries {
return msg;
}
- public static com.juick.User getMessageAuthor(Connection sql, int mid) {
- com.juick.User user = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT messages.user_id,users.nick FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?");
- stmt.setInt(1, mid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- user = new com.juick.User();
- user.UID = rs.getInt(1);
- user.UName = rs.getString(2);
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return user;
+ public static User getMessageAuthor(JdbcTemplate sql, int mid) {
+ return sql.queryForObject("SELECT messages.user_id,users.nick "
+ + "FROM messages INNER JOIN users "
+ + "ON messages.user_id=users.id WHERE messages.message_id=?",
+ new Object[]{mid}, (rs, num) -> {
+ User res = new com.juick.User();
+ res.UID = rs.getInt(1);
+ res.UName = rs.getString(2);
+ return res;
+ });
}
public static List<com.juick.Tag> getMessageTags(Connection sql, int mid) {
@@ -829,31 +801,18 @@ public class MessagesQueries {
return mids;
}
- public static List<Integer> getUserRecommendations(Connection sql, int UID, int before) {
- List<Integer> mids = new ArrayList<>(20);
+ public static List<Integer> getUserRecommendations(JdbcTemplate sql, int UID, int before) {
+ if (before > 0) {
+ return sql.queryForList("SELECT message_id FROM favorites "
+ + "WHERE user_id=? AND message_id<? "
+ + "ORDER BY message_id DESC LIMIT 20", Integer.class,
+ new Object[]{UID, before});
+ } else {
+ return sql.queryForList("SELECT message_id FROM favorites "
+ + "WHERE user_id=? ORDER BY message_id DESC LIMIT 20",
+ Integer.class, new Object[]{UID});
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- if (before > 0) {
- stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20");
- stmt.setInt(1, UID);
- stmt.setInt(2, before);
- } else {
- stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
- stmt.setInt(1, UID);
- }
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- mids.add(rs.getInt(1));
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
}
- return mids;
}
public static List<Integer> getUserPhotos(Connection sql, int UID, int privacy, int before) {
@@ -1007,58 +966,27 @@ public class MessagesQueries {
return replies;
}
- public static boolean setMessagePopular(Connection sql, int mid, int popular) {
- boolean ret = false;
-
- PreparedStatement stmt = null;
- try {
- if (popular == -2) {
- stmt = sql.prepareStatement("UPDATE messages SET hidden=1 WHERE message_id=?");
- stmt.setInt(1, mid);
- } else if (popular == -1) {
- stmt = sql.prepareStatement("UPDATE messages SET popular=0 WHERE message_id=?");
- stmt.setInt(1, mid);
- } else {
- stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?");
- stmt.setInt(1, popular);
- stmt.setInt(2, mid);
- }
- ret = stmt.executeUpdate() > 0;
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
+ public static boolean setMessagePopular(JdbcTemplate sql, int mid, int popular) {
+ boolean ret;
+ switch (popular) {
+ case -2:
+ ret = sql.update("UPDATE messages SET hidden=1 WHERE message_id=?", mid) > 0;
+ break;
+ case -1:
+ ret = sql.update("UPDATE messages SET popular=0 WHERE message_id=?", mid) > 0;
+ break;
+ default:
+ ret = sql.update("UPDATE messages SET popular=? WHERE message_id=?", popular, mid) > 0;
+ break;
}
-
if (popular == -1) {
- try {
- stmt = sql.prepareStatement("INSERT INTO top_ignore_messages VALUES (?)");
- stmt.setInt(1, mid);
- stmt.executeUpdate();
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ ret = sql.update("INSERT INTO top_ignore_messages VALUES (?)", mid) > 0;
}
-
return ret;
}
- public static boolean setMessagePrivacy(Connection sql, int mid) {
- boolean ret = false;
-
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("UPDATE messages SET privacy=1 WHERE message_id=?");
- stmt.setInt(1, mid);
- ret = stmt.executeUpdate() > 0;
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
-
- return ret;
+ public static boolean setMessagePrivacy(JdbcTemplate sql, int mid) {
+ return sql.update("UPDATE messages SET privacy=1 WHERE message_id=?",
+ mid) > 0;
}
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-03 18:59:08 +0000