aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/main/java/com/juick/server/MessagesQueries.java187
-rw-r--r--src/main/java/com/juick/server/PMQueries.java10
-rw-r--r--src/main/java/com/juick/server/TagQueries.java31
-rw-r--r--src/main/java/com/juick/server/UserQueries.java26
4 files changed, 103 insertions, 151 deletions
diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java
index 9cbf4240..fd664909 100644
--- a/src/main/java/com/juick/server/MessagesQueries.java
+++ b/src/main/java/com/juick/server/MessagesQueries.java
@@ -19,7 +19,10 @@ package com.juick.server;
import com.juick.Message;
import com.juick.User;
+import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
+import org.springframework.jdbc.support.KeyHolder;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -41,31 +44,25 @@ public class MessagesQueries {
private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName());
- public static int createMessage(Connection sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) {
- int mid = 0;
-
- PreparedStatement stmt = null;
- ResultSet rs;
- try {
- stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
+ public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List<com.juick.Tag> tags) {
+ KeyHolder holder = new GeneratedKeyHolder();
+ sql.update(con -> {
+ PreparedStatement stmt = con.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)",
+ Statement.RETURN_GENERATED_KEYS);
stmt.setInt(1, uid);
if (attachment != null) {
stmt.setString(2, attachment);
} else {
stmt.setNull(2, Types.VARCHAR);
}
- stmt.executeUpdate();
- rs = stmt.getGeneratedKeys();
- if (rs.first()) {
- mid = rs.getInt(1);
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ return stmt;
+ }, holder);
+
+ int mid = holder.getKey().intValue();
+
if (mid > 0) {
+
String tagsNames = "";
String tagsIDs = "";
@@ -77,42 +74,33 @@ public class MessagesQueries {
tagsNames += tags.get(i).Name;
tagsIDs += "(" + mid + "," + tags.get(i).TID + ")";
}
-
- try {
- stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs);
- stmt.executeUpdate();
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
+ if (tags.size() > 0) {
+ sql.execute("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs);
}
-
- try {
- stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)");
+ final String finalTagsNames = tagsNames;
+ sql.update(con -> {
+ PreparedStatement stmt = con.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) " +
+ "VALUES (?,?,?)", Statement.NO_GENERATED_KEYS);
stmt.setInt(1, mid);
- if (tagsNames.isEmpty()) {
+ if (finalTagsNames.isEmpty()) {
stmt.setNull(2, Types.VARCHAR);
} else {
- stmt.setString(2, tagsNames);
+ stmt.setString(2, finalTagsNames);
}
stmt.setString(3, txt);
- stmt.executeUpdate();
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ return stmt;
+ });
}
return mid;
}
- public static int createReply(Connection sql, int mid, int rid, int uid, String txt, String attachment) {
+ public static int createReply(JdbcTemplate sql, int mid, int rid, int uid, String txt, String attachment) {
int ridnew = getReplyIDIncrement(sql, mid);
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id,replyto,attach,txt) VALUES (?,?,?,?,?,?)");
+ sql.update( con -> {
+ PreparedStatement stmt = con.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id," +
+ "replyto,attach,txt) VALUES (?,?,?,?,?,?)", Statement.NO_GENERATED_KEYS);
stmt.setInt(1, mid);
stmt.setInt(2, ridnew);
stmt.setInt(3, uid);
@@ -123,32 +111,18 @@ public class MessagesQueries {
stmt.setNull(5, Types.VARCHAR);
}
stmt.setString(6, txt);
- if (stmt.executeUpdate() == 0) {
- ridnew = 0;
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ return stmt;
+ });
if (ridnew > 0) {
- SQLHelpers.executeInt(sql, "UPDATE messages SET replies=replies+1 WHERE message_id=?", mid);
+ sql.update("UPDATE messages SET replies=replies+1 WHERE message_id=?", mid);
}
return ridnew;
}
- public static int getReplyIDIncrement(Connection sql, int mid) {
- int rid = 0;
-
- SQLHelpers.execute(sql, "LOCK TABLES messages WRITE");
- if (SQLHelpers.executeInt(sql, "UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid) == 1) {
- rid = SQLHelpers.getInt(sql, "SELECT maxreplyid FROM messages WHERE message_id=?", mid, 0);
- }
- SQLHelpers.execute(sql, "UNLOCK TABLES");
-
- return rid;
+ public static int getReplyIDIncrement(JdbcTemplate sql, int mid) {
+ return sql.update("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid);
}
public static boolean recommendMessage(Connection sql, int mid, int vuid) {
@@ -230,51 +204,56 @@ public class MessagesQueries {
return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4);
}
+
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 AS txt "
- + "ON messages.message_id=txt.message_id "
- + "WHERE messages.message_id=?", new Object[]{mid},
- (rs, rowNum) -> {
- Message msg = new Message();
- msg.setMID(mid);
- msg.setUser(new User());
- msg.getUser().setUID(rs.getInt(1));
- msg.getUser().setUName(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.setText(rs.getString(16));
- return msg;
- });
+ try {
+ return sql.queryForObject("SELECT messages.user_id,users.nick,"
+ + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
+ + "messages.ts,"
+ + "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 AS txt "
+ + "ON messages.message_id=txt.message_id "
+ + "WHERE messages.message_id=?", new Object[]{mid},
+ (rs, rowNum) -> {
+ Message msg = new Message();
+ msg.setMID(mid);
+ msg.setUser(new User());
+ msg.getUser().setUID(rs.getInt(1));
+ msg.getUser().setUName(rs.getString(2));
+ msg.TimeAgo = rs.getInt(3);
+ msg.setDate(rs.getDate(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.setText(rs.getString(16));
+ return msg;
+ });
+ } catch (EmptyResultDataAccessException e) {
+ return null;
+ }
}
public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) {
return sql.queryForObject("SELECT replies.user_id,users.nick,"
- + "replies.replyto,DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),"
+ + "replies.replyto,replies.ts,"
+ "replies.attach,replies.txt FROM replies INNER JOIN users "
+ "ON replies.user_id=users.id "
+ "WHERE replies.message_id=? AND replies.reply_id=?",
@@ -286,7 +265,7 @@ public class MessagesQueries {
msg.getUser().setUID(rs.getInt(1));
msg.getUser().setUName(rs.getString(2));
msg.ReplyTo = rs.getInt(3);
- msg.TimestampString = rs.getString(4);
+ msg.setDate(rs.getDate(4));
msg.AttachmentType = rs.getString(5);
msg.setText(rs.getString(6));
return msg;
@@ -883,7 +862,7 @@ public class MessagesQueries {
return sql.query("SELECT messages.message_id,messages.user_id,users.nick,"
+ "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,"
+ "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
- + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,"
+ + "messages.ts,messages.replies,"
+ "messages_txt.repliesby,messages.attach,messages.lat,"
+ "messages.lon,messages.likes "
+ "FROM (messages INNER JOIN messages_txt "
@@ -906,7 +885,7 @@ public class MessagesQueries {
msg.FriendsOnly = msg.Privacy < 0;
msg.setText(rs.getString(7));
msg.TimeAgo = rs.getInt(8);
- msg.TimestampString = rs.getString(9);
+ msg.setDate(rs.getDate(9));
msg.Replies = rs.getInt(10);
msg.RepliesBy = rs.getString(11);
msg.AttachmentType = rs.getString(12);
@@ -926,7 +905,7 @@ public class MessagesQueries {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
- stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC");
+ stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC");
stmt.setInt(1, mid);
rs = stmt.executeQuery();
rs.beforeFirst();
@@ -941,7 +920,7 @@ public class MessagesQueries {
msg.getUser().Banned = rs.getBoolean(5);
msg.setText(rs.getString(6));
msg.TimeAgo = rs.getInt(7);
- msg.TimestampString = rs.getString(8);
+ msg.setDate(rs.getDate(8));
msg.AttachmentType = rs.getString(9);
replies.add(msg);
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java
index 7984844a..345f641f 100644
--- a/src/main/java/com/juick/server/PMQueries.java
+++ b/src/main/java/com/juick/server/PMQueries.java
@@ -154,7 +154,7 @@ public class PMQueries {
msg.setUser(new User());
msg.getUser().setUID(uuid);
msg.setText(rs.getString(2));
- msg.TimestampString = rs.getString(3);
+ msg.setDate(rs.getDate(3));
return msg;
}, new Object[]{uid, uid_to, uid, uid_to});
@@ -170,7 +170,7 @@ public class PMQueries {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
- stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20");
+ 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();
@@ -181,7 +181,7 @@ public class PMQueries {
msg.getUser().setUName(rs.getString(2));
msg.setText(rs.getString(3));
msg.TimeAgo = rs.getInt(4);
- msg.TimestampString = rs.getString(5);
+ msg.setDate(rs.getDate(5));
msgs.add(0, msg);
}
} catch (SQLException e) {
@@ -199,7 +199,7 @@ public class PMQueries {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
- stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20");
+ 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();
@@ -210,7 +210,7 @@ public class PMQueries {
msg.getUser().setUName(rs.getString(2));
msg.setText(rs.getString(3));
msg.TimeAgo = rs.getInt(4);
- msg.TimestampString = rs.getString(5);
+ msg.setDate(rs.getDate(5));
msgs.add(0, msg);
}
} catch (SQLException e) {
diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java
index 5bc87e28..22839ad0 100644
--- a/src/main/java/com/juick/server/TagQueries.java
+++ b/src/main/java/com/juick/server/TagQueries.java
@@ -40,28 +40,15 @@ public class TagQueries {
private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName());
- public static com.juick.Tag getTag(Connection sql, int tid) {
- com.juick.Tag ret = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?");
- stmt.setInt(1, tid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- ret = new com.juick.Tag();
- ret.TID = tid;
- ret.SynonymID = rs.getInt(1);
- ret.Name = rs.getString(2);
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
-
- return ret;
+ public static com.juick.Tag getTag(JdbcTemplate sql, int tid) {
+ return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?",
+ new Object[]{tid}, (rs, num) -> {
+ Tag ret = new Tag();
+ ret.TID = tid;
+ ret.SynonymID = rs.getInt(1);
+ ret.Name = rs.getString(2);
+ return ret;
+ });
}
public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) {
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index 2cd557c6..5588d8e4 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -199,27 +199,13 @@ public class UserQueries {
return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+");
}
- public static int checkPassword(Connection sql, String username, String password) {
- int uid = 0;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?");
- stmt.setString(1, username);
- rs = stmt.executeQuery();
- if (rs.first()) {
- if (password.equals(rs.getString(2))) {
- uid = rs.getInt(1);
- } else {
- uid = -1;
- }
- }
- } catch (SQLException e) {
- LOGGER.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
+ public static int checkPassword(JdbcTemplate sql, String username, String password) {
+ String realPassword = sql.queryForObject("SELECT passw FROM users WHERE nick=?", String.class, username);
+ if (realPassword.equals(password)) {
+ return UserQueries.getUserByName(sql, username).getUID();
+ } else {
+ return -1;
}
- return uid;
}
public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) {
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-03 06:52:47 +0000