From 714a267ae35354e86512288e0ac91990937f95ab Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 16 Jan 2016 18:23:21 +0300 Subject: almost working api project --- .../java/com/juick/server/MessagesQueries.java | 187 +++++++++------------ 1 file changed, 83 insertions(+), 104 deletions(-) (limited to 'src/main/java/com/juick/server/MessagesQueries.java') 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 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 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); -- cgit v1.2.3