diff options
Diffstat (limited to 'src/main/java/com')
| -rw-r--r-- | src/main/java/com/juick/server/MessagesQueries.java | 187 | ||||
| -rw-r--r-- | src/main/java/com/juick/server/PMQueries.java | 10 | ||||
| -rw-r--r-- | src/main/java/com/juick/server/TagQueries.java | 31 | ||||
| -rw-r--r-- | src/main/java/com/juick/server/UserQueries.java | 26 |
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) { |